流程是是这样:
程序调用存储过程,添加记录。量在很少的情况下,就产生了死锁。请大家看看有什么好的解决方案,来解决这个问题,谢谢!以下是测试结果:<TABLE bgcolor="#f3f3f3" border="1" cellspacing="0" cellpadding="1" >
<TR>
<TD>终端数</TD>
<TD width="150">并发数量</TD>
<TD width="150">入库线程数</TD>
<TD width="150">每次入库条数</TD>
<TD width="150">SqlServer是否出现死锁</TD>
<TD width="150">Oracle是否出现死锁</TD>
</TR>
<TR>
<TD>1</TD>
<TD>50并发无限次发送</TD>
<TD>1</TD>
<TD>20</TD>
<TD>未</TD>
<TD>未</TD>
</TR>
<TR>
<TD>1</TD>
<TD>50并发无限次发送</TD>
<TD>20</TD>
<TD>20</TD>
<TD>出现死锁</TD>
<TD>未</TD>
</TR>
<TR>
<TD>1</TD>
<TD>50并发无限次发送</TD>
<TD>2</TD>
<TD>20</TD>
<TD>少量死锁</TD>
<TD>未</TD>
</TR>
<TR>
<TD>终端ID不重复</TD>
<TD>50并发无限次发送</TD>
<TD>1</TD>
<TD>20</TD>
<TD>未</TD>
<TD>未</TD>
</TR>
<TR>
<TD>终端ID不重复</TD>
<TD>50并发无限次发送</TD>
<TD>20</TD>
<TD>20</TD>
<TD>出现死锁</TD>
<TD>未</TD>
</TR>
</TABLE>
存储过程为:USE [kyt]
GO
/****** Object: StoredProcedure [dbo].[PROC_ADD_LAST_LOC_OBJ] Script Date: 10/21/2011 17:33:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_ADD_LAST_LOC_OBJ](@P_DEVICE_ID nVARCHAR(50),@P_LONGITUDE FLOAT,@P_LATITUDE FLOAT,@P_SPEED FLOAT,@P_DIRECTION FLOAT,@P_HEIGHT FLOAT,@P_DISTANCE FLOAT,@P_GPSTIME datetime,@P_LOCATE_TYPE nVARCHAR,@P_COORDTYPE NUMERIC,@P_STATE NVARCHAR,@P_ID numeric(32,0), @P_POSDESC nvarchar(1000),@P_OBJ_TYPE NVARCHAR,@P_OBJ_ID NVARCHAR(50),@P_ID_TYPE NVARCHAR(50))
AS
declare
@v_count int=0;
BEGIN
update t_last_locrecord
set longitude=@P_LONGITUDE,LATITUDE=@P_LATITUDE,SPEED=@P_SPEED,DIRECTION=@P_DIRECTION,HEIGHT=@P_HEIGHT,DISTANCE=@P_DISTANCE,GPSTIME=@P_GPSTIME,LOCATE_TYPE=@P_LOCATE_TYPE,inputdate=GETDATE(),COORD_TYPE=@P_COORDTYPE,state=@P_STATE,LOC_ID=@P_ID, POS_DESC=@P_POSDESC,OBJ_TYPE=@P_OBJ_TYPE,OBJ_ID=@P_OBJ_ID
where device_id = @P_DEVICE_ID;
end ;
程序调用存储过程,添加记录。量在很少的情况下,就产生了死锁。请大家看看有什么好的解决方案,来解决这个问题,谢谢!以下是测试结果:<TABLE bgcolor="#f3f3f3" border="1" cellspacing="0" cellpadding="1" >
<TR>
<TD>终端数</TD>
<TD width="150">并发数量</TD>
<TD width="150">入库线程数</TD>
<TD width="150">每次入库条数</TD>
<TD width="150">SqlServer是否出现死锁</TD>
<TD width="150">Oracle是否出现死锁</TD>
</TR>
<TR>
<TD>1</TD>
<TD>50并发无限次发送</TD>
<TD>1</TD>
<TD>20</TD>
<TD>未</TD>
<TD>未</TD>
</TR>
<TR>
<TD>1</TD>
<TD>50并发无限次发送</TD>
<TD>20</TD>
<TD>20</TD>
<TD>出现死锁</TD>
<TD>未</TD>
</TR>
<TR>
<TD>1</TD>
<TD>50并发无限次发送</TD>
<TD>2</TD>
<TD>20</TD>
<TD>少量死锁</TD>
<TD>未</TD>
</TR>
<TR>
<TD>终端ID不重复</TD>
<TD>50并发无限次发送</TD>
<TD>1</TD>
<TD>20</TD>
<TD>未</TD>
<TD>未</TD>
</TR>
<TR>
<TD>终端ID不重复</TD>
<TD>50并发无限次发送</TD>
<TD>20</TD>
<TD>20</TD>
<TD>出现死锁</TD>
<TD>未</TD>
</TR>
</TABLE>
存储过程为:USE [kyt]
GO
/****** Object: StoredProcedure [dbo].[PROC_ADD_LAST_LOC_OBJ] Script Date: 10/21/2011 17:33:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_ADD_LAST_LOC_OBJ](@P_DEVICE_ID nVARCHAR(50),@P_LONGITUDE FLOAT,@P_LATITUDE FLOAT,@P_SPEED FLOAT,@P_DIRECTION FLOAT,@P_HEIGHT FLOAT,@P_DISTANCE FLOAT,@P_GPSTIME datetime,@P_LOCATE_TYPE nVARCHAR,@P_COORDTYPE NUMERIC,@P_STATE NVARCHAR,@P_ID numeric(32,0), @P_POSDESC nvarchar(1000),@P_OBJ_TYPE NVARCHAR,@P_OBJ_ID NVARCHAR(50),@P_ID_TYPE NVARCHAR(50))
AS
declare
@v_count int=0;
BEGIN
update t_last_locrecord
set longitude=@P_LONGITUDE,LATITUDE=@P_LATITUDE,SPEED=@P_SPEED,DIRECTION=@P_DIRECTION,HEIGHT=@P_HEIGHT,DISTANCE=@P_DISTANCE,GPSTIME=@P_GPSTIME,LOCATE_TYPE=@P_LOCATE_TYPE,inputdate=GETDATE(),COORD_TYPE=@P_COORDTYPE,state=@P_STATE,LOC_ID=@P_ID, POS_DESC=@P_POSDESC,OBJ_TYPE=@P_OBJ_TYPE,OBJ_ID=@P_OBJ_ID
where device_id = @P_DEVICE_ID;
end ;
update t_last_locrecord
set longitude=@P_LONGITUDE,LATITUDE=@P_LATITUDE,SPEED=@P_SPEED,DIRECTION=@P_DIRECTION,HEIGHT=@P_HEIGHT,DISTANCE=@P_DISTANCE,GPSTIME=@P_GPSTIME,LOCATE_TYPE=@P_LOCATE_TYPE,inputdate=GETDATE(),COORD_TYPE=@P_COORDTYPE,state=@P_STATE,LOC_ID=@P_ID, POS_DESC=@P_POSDESC,OBJ_TYPE=@P_OBJ_TYPE,OBJ_ID=@P_OBJ_ID
where device_id = @P_DEVICE_ID;
commit tran;加上事务
单一在sql窗口里面执行此SQL,没有死锁问题
各位:
我补充一点:一个线程不会出现死锁的问题,当采用多线程,数据量上来后,比如到测试结果为20时,就会产生死锁
sqlserver阻塞定位 http://www.cnblogs.com/gezifeiyang/archive/2011/05/02/1514263.html