SQL Server 2012 运行一段时间总是死锁，卡住

handsomenku 2014-07-18 05:13:10

SQL Server 2012运行一段时间就容易产生死锁，导致数据库只能查询，不能更新，查找数据库发现有两个进程被系统进程给锁住了，怎么解决？

另外其中一个进程是执行触发器：
CREATE TRIGGER [dbo].[instead_of_insert] ON [dbo].[XML_RECV]
INSTEAD OF INSERT
AS
BEGIN
if exists(select 1 from inserted a join xml_recv b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype)
begin
print 1
update a
set a.operatetype=b.operatetype,a.deviceid=b.deviceid,a.company=b.company,a.subinst = b.subinst,a.organize = b.organize ,a.organize_1=b.organize_1,a.bill_type = b.bill_type,a.terminalno = b.terminalno,
a.merchantno = b.merchantno,a.merchantname = b.merchantname,a.maintenancer = b.maintenancer,a.serialno = b.serialno,a.model=b.model,a.spec=b.spec,a.investor=b.investor,
a.bill_no = b.bill_no,a.deviceno=b.deviceno,a.remark2=b.remark2,a.checkin_datetime=b.checkin_datetime
from xml_recv a
join inserted b
on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype
end
insert into xml_recv(operatetype,deviceid,company,subinst,organize,organize_1,bill_type,terminalno,merchantno,merchantname,maintenancer,serialno,model,spec,investor,bill_no,deviceno,remark2,checkin_datetime)
select a.operatetype,a.deviceid,a.company,a.subinst,a.organize,a.organize_1,a.bill_type,a.terminalno,a.merchantno,a.merchantname,a.maintenancer,a.serialno,a.model,a.spec,a.investor,a.bill_no,a.deviceno,a.remark2,a.checkin_datetime
from inserted a
left join xml_recv b
on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype
where b.deviceid is null
END

(@1 varchar(8000),@2 varchar(8000),@3 varchar(8000))UPDATE [XML_SEND] set [dealstatus] = @1 WHERE [operatetype]=@2 AND [deviceid]=@3

不知道是触发器的原因还是其它的原因，求大神指导

...全文

1037 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

handsomenku 2014-07-21

打赏
举报

select count(*) FROM UMS_terminal a LEFT OUTER JOIN UMS_submerchant x on a.SUBMERCHANTID=x.SUBMERCHANTID and left(x.SUBREGIONCODE,4)=a.REGIONCODE LEFT OUTER JOIN UMS_merchant y on a.merchantno=y.merchantno left join (select distinct a.merchantno,a.terminalno from ums_visitandtrainlist a join ums_visitandtrain b on a.taskid=b.taskid where b.tasktype='16' and b.taskdate between '2014-01-23' and '2014-07-21' )k on a.merchantno=k.merchantno and a.terminalno=k.terminalno WHERE 1=1 and subregioncode like '3713%' and a.regioncode ='3713' and a.runflag = '0' and a.maintenancer = 'wygu' and k.merchantno is null

引用楼主 handsomenku 的回复:

SQL Server 2012运行一段时间就容易产生死锁，导致数据库只能查询，不能更新，查找数据库发现有两个进程被系统进程给锁住了，怎么解决？另外其中一个进程是执行触发器： CREATE TRIGGER [dbo].[instead_of_insert] ON [dbo].[XML_RECV] INSTEAD OF INSERT AS BEGIN if exists(select 1 from inserted a join xml_recv b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype) begin print 1 update a set a.operatetype=b.operatetype,a.deviceid=b.deviceid,a.company=b.company,a.subinst = b.subinst,a.organize = b.organize ,a.organize_1=b.organize_1,a.bill_type = b.bill_type,a.terminalno = b.terminalno, a.merchantno = b.merchantno,a.merchantname = b.merchantname,a.maintenancer = b.maintenancer,a.serialno = b.serialno,a.model=b.model,a.spec=b.spec,a.investor=b.investor, a.bill_no = b.bill_no,a.deviceno=b.deviceno,a.remark2=b.remark2,a.checkin_datetime=b.checkin_datetime from xml_recv a join inserted b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype end insert into xml_recv(operatetype,deviceid,company,subinst,organize,organize_1,bill_type,terminalno,merchantno,merchantname,maintenancer,serialno,model,spec,investor,bill_no,deviceno,remark2,checkin_datetime) select a.operatetype,a.deviceid,a.company,a.subinst,a.organize,a.organize_1,a.bill_type,a.terminalno,a.merchantno,a.merchantname,a.maintenancer,a.serialno,a.model,a.spec,a.investor,a.bill_no,a.deviceno,a.remark2,a.checkin_datetime from inserted a left join xml_recv b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype where b.deviceid is null END (@1 varchar(8000),@2 varchar(8000),@3 varchar(8000))UPDATE [XML_SEND] set [dealstatus] = @1 WHERE [operatetype]=@2 AND [deviceid]=@3 不知道是触发器的原因还是其它的原因，求大神指导

引用楼主 handsomenku 的回复:

SQL Server 2012运行一段时间就容易产生死锁，导致数据库只能查询，不能更新，查找数据库发现有两个进程被系统进程给锁住了，怎么解决？另外其中一个进程是执行触发器： CREATE TRIGGER [dbo].[instead_of_insert] ON [dbo].[XML_RECV] INSTEAD OF INSERT AS BEGIN if exists(select 1 from inserted a join xml_recv b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype) begin print 1 update a set a.operatetype=b.operatetype,a.deviceid=b.deviceid,a.company=b.company,a.subinst = b.subinst,a.organize = b.organize ,a.organize_1=b.organize_1,a.bill_type = b.bill_type,a.terminalno = b.terminalno, a.merchantno = b.merchantno,a.merchantname = b.merchantname,a.maintenancer = b.maintenancer,a.serialno = b.serialno,a.model=b.model,a.spec=b.spec,a.investor=b.investor, a.bill_no = b.bill_no,a.deviceno=b.deviceno,a.remark2=b.remark2,a.checkin_datetime=b.checkin_datetime from xml_recv a join inserted b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype end insert into xml_recv(operatetype,deviceid,company,subinst,organize,organize_1,bill_type,terminalno,merchantno,merchantname,maintenancer,serialno,model,spec,investor,bill_no,deviceno,remark2,checkin_datetime) select a.operatetype,a.deviceid,a.company,a.subinst,a.organize,a.organize_1,a.bill_type,a.terminalno,a.merchantno,a.merchantname,a.maintenancer,a.serialno,a.model,a.spec,a.investor,a.bill_no,a.deviceno,a.remark2,a.checkin_datetime from inserted a left join xml_recv b on a.serialno = b.serialno and a.model=b.model and a.operatetype=b.operatetype where b.deviceid is null END (@1 varchar(8000),@2 varchar(8000),@3 varchar(8000))UPDATE [XML_SEND] set [dealstatus] = @1 WHERE [operatetype]=@2 AND [deviceid]=@3 不知道是触发器的原因还是其它的原因，求大神指导

引用 2 楼 DVD_01 的回复:

先通过sql server profiler 跟踪出死锁位置，再确认如何解决。如，启动SQL Server Profiler工具(在Microsoft SQL Server Management Studio的工具菜单上就发现它)，创建一个Trace,Trace属性选择主要是包含: Deadlock graph Lock: Deadlock Lock: Deadlock Chain RPC:Completed SP:StmtCompleted SQL:BatchCompleted SQL:BatchStarting 检查死锁图，找出原因。

好的今天又查了查好像还有个activity monitor

Q315054403 2014-07-19

打赏
举报

If Exists 判断是多余的，可直接Update... xml_recv 是否有相应的索引？更多问题是设计问题，而非单一SQL指令

Q315054403 2014-07-19

打赏
举报

业务逻辑是固定的，用触发器还是SP，或者前端多次下指令实现有区别么。。设计问题别一味怪DB提供的特性

霜寒月冷 2014-07-19

打赏
举报

给你一个查找死锁的存储过程

alter procedure sp_who_lock   

as     

begin     

   declare @spid int     

   declare @blk int     

   declare @count int     

   declare @index int     

   declare @lock tinyint      

   set @lock=0      

   create table #temp_who_lock      

 (      

  id int identity(1,1),      

  spid int,      

  blk int     

 )      

 if @@error<>0 return @@error      

 insert into #temp_who_lock(spid,blk)      

 select 0 ,blocked       

 from (select * from master..sysprocesses where blocked>0)a      

 where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)      

 union select spid,blocked from  master..sysprocesses where blocked>0      

 if @@error<>0 return @@error      

 select @count=count(*),@index=1 from #temp_who_lock      

 if @@error<>0 return @@error      

 if @count=0      

 begin     

  select '没有阻塞和死锁信息'     

  return 0      

 end     

 while @index<=@count      

 begin     

  if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid))      

  begin     

   set @lock=1      

   select @spid=spid,@blk=blk from #temp_who_lock where id=@index     

   select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'     

   select  @spid, @blk    

   dbcc inputbuffer(@spid)      

   dbcc inputbuffer(@blk)      

  end     

  set @index=@index+1      

 end     

 if @lock=0       

 begin     

  set @index=1      

  while @index<=@count      

  begin     

   select @spid=spid,@blk=blk from #temp_who_lock where id=@index     

   if @spid=0      

    select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下'     

   else      

    select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'     

   dbcc inputbuffer(@spid)    

   dbcc inputbuffer(@blk)      

   set @index=@index+1      

  end     

 end     

 drop table #temp_who_lock      

 return 0      

end           

 

 

GO

---查找出死锁的原因。
exec sp_who_lock

唐诗三百首 2014-07-18

打赏
举报

SQL Server 2012运行一段时间就容易产生死锁，导致数据库只能查询，不能更新， --> 数据库只能查询不能更新是阻塞,不是死锁. 当SQL Server发现死锁时,会自动选择其中一个进程进行回滚. 建议分析系统DMV视图(sys.sysprocesses,sys.dm_tran_locks,sys.dm_exec_requests等), 分析阻塞的原因,一般是锁未释放造成的.

Andy-W 2014-07-18

打赏
举报

先通过sql server profiler 跟踪出死锁位置，再确认如何解决。如，启动SQL Server Profiler工具(在Microsoft SQL Server Management Studio的工具菜单上就发现它)，创建一个Trace,Trace属性选择主要是包含: Deadlock graph Lock: Deadlock Lock: Deadlock Chain RPC:Completed SP:StmtCompleted SQL:BatchCompleted SQL:BatchStarting 检查死锁图，找出原因。