问题一:删除锁表,还是锁记录? 更新应该是 锁记录对吗???问题二:两个存储过程同时 对一张表执行 更新,发生死锁。 但是 更新的不是同一条记录。 两个存储过程处理的是两类数据,不应该发生死锁。 打出来信息,发现另外一个存储过程的结果集中根本不存在这个记录。为什么发生死锁???存储过程一: SELECT TOP 1000 DANo,UpdateTime,LogTime,DataType,DAType,DAYear,DAMonth,DADay,DAHour,CollegeNo,CampusNo,BuildingNo,BuildingType,SumQty,EType,EIndex,
ESubitem,ESubitem1,ESubitem2,Unit,Lable,Note into #tmpBuildingHourAllData FROM T_DAList
WHERE DataType='Public' and DAType='h' and ESubitem='' and Lable=0 ORDER BY (DAYear+DAMonth+DADay+DAHour) --OPEN Cur
select @Count=count(*) from #tmpBuildingHourAllData --select * into testBuildingHourAllData from #tmpBuildingHourAllData while @Count>0
BEGIN --print '死锁发生前'
select top 1 @DANo=DANo,@UpdateTime=UpdateTime,@LogTime=LogTime,@DataType=DataType,@DAType=DAType,@DAYear=DAYear,@DAMonth=DAMonth,@DADay=DADay,
@DAHour=DAHour,@CollegeNo=CollegeNo,@CampusNo=CampusNo,@BuildingNo=BuildingNo,@BuildingType=BuildingType,@SumQty=SumQty,@EType=EType,@EIndex=EIndex,
@ESubitem=ESubitem,@ESubitem1=ESubitem1,@ESubitem2=ESubitem2,@Unit=Unit,@Lable=Lable,@Note=Note from #tmpBuildingHourAllData; print '@ESubitem1:'+@ESubitem1
print '@ESubitem2:'+@ESubitem2
print @SumQty
print '开始更新' update T_DAList set Lable=1 where DANo=@DANo and CollegeNo=@CollegeNo and DAYear=@DAYear and DAMonth=@DAMonth and DADay=@DADay
and DAHour=@DAHour and DataType='Public' and DAType=@DAType and BuildingNo=@BuildingNo and SumQty=@SumQty
and EType=@EType and EIndex=@EIndex and ESubitem=@ESubitem and ESubitem1=@ESubitem1 and ESubitem2=@ESubitem2--更新索引
delete top(1) from #tmpBuildingHourAllData;
--print '删除数据'
set @Count=@Count-1
End
存储过程二: SELECT TOP 1000 DANo,UpdateTime,LogTime,DataType,DAType,DAYear,DAMonth,DADay,DAHour,CollegeNo,CampusNo,BuildingNo,BuildingType,SumQty,EType,EIndex,
ESubitem,ESubitem1,ESubitem2,Unit,Lable,Note into #tmpBuildingHourTypeData FROM T_DAList
WHERE DataType='Public' and DAType='h' and ESubitem!='' and Lable=0 ORDER BY (DAYear+DAMonth+DADay+DAHour) --OPEN Cur
select @Count=count(*) from #tmpBuildingHourTypeData --select * into testBuildingHourTypeData from #tmpBuildingHourTypeData while @Count>0
BEGIN
print '@ESubitem1:'+@ESubitem1
print '@ESubitem2:'+@ESubitem2
print @SumQty
print '开始更新'
update T_DAList set Lable=1 where DANo=@DANo and CollegeNo=@CollegeNo and DAYear=@DAYear and DAMonth=@DAMonth and DADay=@DADay
and DAHour=@DAHour and DataType='Public' and DAType=@DAType and BuildingNo=@BuildingNo and SumQty=@SumQty
and EType=@EType and EIndex=@EIndex and ESubitem=@ESubitem and ESubitem1=@ESubitem1 and ESubitem2=@ESubitem2问题三:
更新的 数据"ESubitem"的值不一样,确发生了死锁不知道为什么?问题四:死锁的提示信息。
@ESubitem2:
53.000000
开始更新
消息 1205,级别 13,状态 45,过程 CCSP_Data_Building_ByHour_All,第 907 行
事务(进程 ID 54)与另一个进程被死锁在 锁 | 通信缓冲区 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
“开始更新”语句之后只有“update T_DAList set Lable=1 where DANo=@DANo and CollegeNo=@CollegeNo and DAYear=@DAYear and DAMonth=@DAMonth and DADay=@DADay
and DAHour=@DAHour and DataType='Public' and DAType=@DAType and BuildingNo=@BuildingNo and SumQty=@SumQty
and EType=@EType and EIndex=@EIndex and ESubitem=@ESubitem and ESubitem1=@ESubitem1 and ESubitem2=@ESubitem2。”
应该是这一句发生死锁了吧。定位应该没有错。
ESubitem,ESubitem1,ESubitem2,Unit,Lable,Note into #tmpBuildingHourAllData FROM T_DAList
WHERE DataType='Public' and DAType='h' and ESubitem='' and Lable=0 ORDER BY (DAYear+DAMonth+DADay+DAHour) --OPEN Cur
select @Count=count(*) from #tmpBuildingHourAllData --select * into testBuildingHourAllData from #tmpBuildingHourAllData while @Count>0
BEGIN --print '死锁发生前'
select top 1 @DANo=DANo,@UpdateTime=UpdateTime,@LogTime=LogTime,@DataType=DataType,@DAType=DAType,@DAYear=DAYear,@DAMonth=DAMonth,@DADay=DADay,
@DAHour=DAHour,@CollegeNo=CollegeNo,@CampusNo=CampusNo,@BuildingNo=BuildingNo,@BuildingType=BuildingType,@SumQty=SumQty,@EType=EType,@EIndex=EIndex,
@ESubitem=ESubitem,@ESubitem1=ESubitem1,@ESubitem2=ESubitem2,@Unit=Unit,@Lable=Lable,@Note=Note from #tmpBuildingHourAllData; print '@ESubitem1:'+@ESubitem1
print '@ESubitem2:'+@ESubitem2
print @SumQty
print '开始更新' update T_DAList set Lable=1 where DANo=@DANo and CollegeNo=@CollegeNo and DAYear=@DAYear and DAMonth=@DAMonth and DADay=@DADay
and DAHour=@DAHour and DataType='Public' and DAType=@DAType and BuildingNo=@BuildingNo and SumQty=@SumQty
and EType=@EType and EIndex=@EIndex and ESubitem=@ESubitem and ESubitem1=@ESubitem1 and ESubitem2=@ESubitem2--更新索引
delete top(1) from #tmpBuildingHourAllData;
--print '删除数据'
set @Count=@Count-1
End
存储过程二: SELECT TOP 1000 DANo,UpdateTime,LogTime,DataType,DAType,DAYear,DAMonth,DADay,DAHour,CollegeNo,CampusNo,BuildingNo,BuildingType,SumQty,EType,EIndex,
ESubitem,ESubitem1,ESubitem2,Unit,Lable,Note into #tmpBuildingHourTypeData FROM T_DAList
WHERE DataType='Public' and DAType='h' and ESubitem!='' and Lable=0 ORDER BY (DAYear+DAMonth+DADay+DAHour) --OPEN Cur
select @Count=count(*) from #tmpBuildingHourTypeData --select * into testBuildingHourTypeData from #tmpBuildingHourTypeData while @Count>0
BEGIN
print '@ESubitem1:'+@ESubitem1
print '@ESubitem2:'+@ESubitem2
print @SumQty
print '开始更新'
update T_DAList set Lable=1 where DANo=@DANo and CollegeNo=@CollegeNo and DAYear=@DAYear and DAMonth=@DAMonth and DADay=@DADay
and DAHour=@DAHour and DataType='Public' and DAType=@DAType and BuildingNo=@BuildingNo and SumQty=@SumQty
and EType=@EType and EIndex=@EIndex and ESubitem=@ESubitem and ESubitem1=@ESubitem1 and ESubitem2=@ESubitem2问题三:
更新的 数据"ESubitem"的值不一样,确发生了死锁不知道为什么?问题四:死锁的提示信息。
@ESubitem2:
53.000000
开始更新
消息 1205,级别 13,状态 45,过程 CCSP_Data_Building_ByHour_All,第 907 行
事务(进程 ID 54)与另一个进程被死锁在 锁 | 通信缓冲区 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
“开始更新”语句之后只有“update T_DAList set Lable=1 where DANo=@DANo and CollegeNo=@CollegeNo and DAYear=@DAYear and DAMonth=@DAMonth and DADay=@DADay
and DAHour=@DAHour and DataType='Public' and DAType=@DAType and BuildingNo=@BuildingNo and SumQty=@SumQty
and EType=@EType and EIndex=@EIndex and ESubitem=@ESubitem and ESubitem1=@ESubitem1 and ESubitem2=@ESubitem2。”
应该是这一句发生死锁了吧。定位应该没有错。
你这个存储过程是循环执行吗?如果是循环执行 是有可能导致 死锁的。因为就算从表面上看 2个存储过程查询的不是同一个值,但是在实际执行时,如果没有索引,在过滤数据之前,也就是where ESubitem!='' 或者是 ESubitem='' 时,还是要先去锁定数据,读出数据来判断,如果发现不是所需要的数据,然后再释放锁。而在这个时间内,就可能导致死锁的产生。
两个存储过程的数据处理都是循环处理的。如果是给“ESubItem”加上索引。也就是 所有Where 后面的字段设置上非聚集索引,是不是就不会发生死锁了。