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
不知道是触发器的原因还是其它的原因，求大神指导
------解决方案--------------------
在触发器中弄那么多逻辑,不是个好主意.
------解决方案--------------------
先通过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
检查死锁图，找出原因。
------解决方案--------------------
SQL Server 2012运行一段时间就容易产生死锁，导致数据库只能查询，不能更新，
--> 数据库只能查询不能更新是阻塞,不是死锁.
当SQL Server发现死锁时,会自动选择其中一个进程进行回滚.
建议分析系统DMV视图(sys.sysprocesses,sys.dm_tran_locks,sys.dm_exec_requests等),
分析阻塞的原因,一般是锁未释放造成的.
------解决方案--------------------
给你一个查找死锁的存储过程
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