如何解决死锁问题
主要介绍如何得到死锁的住处,以及避免死锁产生的常用方法.主要包括:
环境准备:
create table David01(
A varchar(2)
,B varchar(2)
,C varchar(2))
--insert data
insert into David01
select
union all select
union all select
--create table David02
create table David02
(D varchar(2)
,E varchar(2))
----insert data
insert into David02
select
union all select
为了标识死锁，我们首先要得到日志信息：包括死锁的SPID和发生死锁的资源，为了达到这个目的，我们要在SQLServer 2000中增加两个启动参数： -T1204 和 -T3605 ,下面是添加的步骤：
Ø 打开SQLServer企业管理器.
Ø 在发生死锁的服务器右键单击.
Ø 选择 Properties.
Ø 在General 选项卡中选择”Startup Parameters…”
Ø 在弹出 “Startup Parameters”对话框。在Parameters 文本框中键入-T1204.单击 Add.
Ø 同理添加 -T3605
Ø 单击”OK”关闭对话框
Ø 重启SQL Server 服务（这样才能使用参数生效）
-T1204：收集系统按照死锁探测算法探测到的发生死锁的进程和被死锁的资源。
-T3605：把-T1204收集到的信息写入SQL Server 错误日志(以下来源于SQLServer日志，在企业管理器中看到的没有那些被执行的语句)
Note:可以使用T-SQL语句打开此选项.The "-1" indicates all SPIDs.此时并不用重启SQLServer 服务器
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
我们在查询分析器中执行以下语句:
--In the first Connection
begin tran
update David01
set A=
where B=
waitfor delay
update David02
set D=
where E=
--In the second connection
begin tran
update David02
set D=
where E=
waitfor delay
update David01
set A=
where B=
大家可以看到有死锁发生,并且有一个事务被选为死锁牺牲品,同时被选为死锁牺牲品的事务得到以下有错误消息:
(1 row(s) affected)
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
下面我们打开Log文件,默认在C:\Program Files\Microsoft SQL Server\MSSQL\LOG下的 ,我们用记事本打开ERRORLOG(这个对应于企业管理器”Management”->”SQL Server Logs’下面”current日志),
2005-02-16 12:12:23.57 spid3 SQL global counter collection task is created.
2005-02-16 12:12:30.97 spid53 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2005-02-16 12:14:54.42 spid4 Deadlock encountered .... Printing deadlock information
2005-02-16 12:14:54.42 spid4
2005-02-16 12:14:54.42 spid4 Wait-for graph
2005-02-16 12:14:54.42 spid4
2005-02-16 12:14:54.42 spid4 Node:1
2005-02-16 12:14:54.42 spid4 RID: 6:1:35:0 CleanCnt:1 Mode: X Flags: 0x2
2005-02-16 12:14:54.42 spid4 Grant List 0::
2005-02-16 12:14:54.42 spid4 Owner:0x198c32e0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0
2005-02-16 12:14:54.42 spid4 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 1
2005-02-16 12:14:54.44 spid4 Input Buf: Language Event: begin tran
update David02
set D='d5'
where E=
waitfor delay '00:00:3'
update David01
set A='aa'
2005-02-16 12:14:54.44 spid4 Requested By:
2005-02-16 12:14:54.44 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x19FF3580) Value:0x198bf3a0 Cost:(0/98)
2005-02-16 12:14:54.44 spid4
2005-02-16 12:14:54.44 spid4 Node:2
2005-02-16 12:14:54.44 spid4 RID: 6:1:33:1 CleanCnt:1 Mode: X Flags: 0x2
2005-02-16 12:14:54.44 spid4 Grant List 0::
2005-02-16 12:14:54.44 spid4 Owner:0x198c32c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0
2005-02-16 12:14:54.44 spid4 SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 1
2005-02-16 12:14:54.44 spid4 Input Buf: Language Event: begin tran
update David01
set A='aa'
where B='b2'
waitfor delay '00:00:5'
update David02
set D='d5'
where E='e1'
2005-02-16 12:14:54.44 spid4 Requested By:
2005-02-16 12:14:54.44 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec:(0x1A433580) Value:0x198c3260 Cost:(0/DC)
2005-02-16 12:14:54.44 spid4 Victim Resource Owner:
2005-02-16 12:14:54.44 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x19FF3580) Value:0x198bf3a0 Cost:(0/98)
在”Wait-for graph”实体中，我们可以看到Node1 和Node2.在每个Node中都包括”Grant List” 和”Request List”.在每个Node中都包括以下住处：
Ø SPID.
Ø 此 SPID执行的语句
Ø 使用的资源
Ø 资源被锁定的方式
例如,在Node1中，SPID: 55被授予排它锁, Mode : X，在RID: 6:1:35:0 （其中6中DBID）.以及被执行的语句:
update David02
set D=
where E=
waitfor delay
update David01
set A=
同样我们在Node 2中也可以得到类似的信息.
很多原因可以导致死锁的发生,死锁也可以发生在两个,三个甚至更多的SPID中!下面是几个常用的解决死锁发生的方法:
添加或者删除索引
使用索引提示(index hints)
以相同的顺序访问资源
尽可能少的使用触发器(触发器也是一个事务)
保持事务尽可能的短少.
