--摘自http://support.microsoft.com/kb/169960/zh-cn
INF ：在 SQL Server 中分析并避免死锁
概要
Microsoft SQL Server 使用锁来维护事务完整性和数据库一致性。与任何关系数据库系统一样，锁定可能导致用户之间出现死锁。
例如，假设用户 1 （或连接 1 ）持有数据项“A” 的锁，并希望获得数据项“B” 的锁。用户 2 持有数据项“B” 的锁，而且现在希望获得数据项“A” 的锁。在此 SQL Server 情况中，用户 1 或用户 2 将为死锁牺牲品，其他用户将被授予所请求的锁。
在 SQL Server 中，应用程序开发人员可以通过使用 SET DEADLOCK_PRIORITY 来确定哪个连接将是死锁牺牲品的候选项。如果开发人员不为死锁指定优先级，SQL Server 将通过选择完成循环锁链的进程来选择死锁牺牲品。
数据库应用程序系统在从一个关系数据库转入另一个关系数据库时，可能会根据关系数据库系统实现的不同而具有不同的行为方式。查找行为更改的一个方面是锁定。本文介绍在 SQL Server 中如何分析死锁以及可用来避免死锁的技术。
更多信息
本文重点介绍如何使用跟踪标志 T1204 的输出来分析死锁。设置跟踪标志 T1204 之后，SQL Server 会在发生死锁时打印有关死锁的信息。要使用此跟踪标志，请在命令提示符处使用以下命令来启动 SQL Server ：
sqlservr -c -T1204
跟踪结果将发送至控制台窗口，但如果设置了跟踪标志 T3605 ，则会将跟踪输出发送至错误日志。
当两个连接以相反顺序更新表时，会发生死锁。例如，一个连接先插入到表“example1” 中，然后插入到“example2” 中，而另一个连接先插入到表“example2” 中，然后插入到事务中的“example1” 中。通过示例可很好地说明如何避免死锁。
下面是用于为此示例创建表的 SQL 语句：
create table example1 (column1 int, column2 char(20), column3 char(50))
go
create table example2 (column1 int, column2 char(20), column3 char(50))
go
declare @lvar int
select @lvar = 0
while @lvar < 500
begin
insert into example1 values (@lvar, 'AAA', 'CCC')
insert into example2 values (@lvar, 'AAA', 'CCC')
select @lvar = @lvar + 1
end
go
create unique clustered index ex1ind1 on example1 (column1, column2)
with fill factor = 90, PAD_INDEX
go
create unique clustered index ex2ind1 on example2 (column1, column2)
with fill factor = 90, PAD_INDEX
go
示例 1 ：顺序相反的表插入
在此示例中，以相反顺序插入两个表，并且发生了死锁。当两个或更多个连接以相反顺序对表执行更新或删除时，也会发生死锁。
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')
Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')
此处，Connection1 可能会阻塞 Connection2 ，因为 Connection2 正在插入的行可能与 Connection1 已插入行并持有锁的位置在同一页上。
Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')
此处，Connection2 可能会阻塞 Connection1 ，因为 Connection1 正在插入的行可能与 Connection2 已插入行并持有锁的位置在同一页上。这会引起死锁。
下面是发生死锁时跟踪标志 1204 的输出：
97/04/20 11:51:57.88 spid13 *** DEADLOCK DETECTED with spid 14 ***
spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:
EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1
pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES (100,
'AAAA', 'CCC')
spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:
EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1
pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (200,
'AAAB', 'CCC')
VICTIM: spid 13, pstat 0x0000 , cputime 30
死锁跟踪的每行都会向用户说明有关死锁的更多信息。Connection1 是 spid 13 ，Connection2 是 spid 14 （通过使用 sp_who 系统存储过程可以确定与连接关联的 spid ）。
>> 97/04/20 11:51:57.88 spid13 *** DEADLOCK DETECTED with spid 14 ***
The deadlock was detected between spid 13 and spid 14.
>> spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:
>> EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1
>> pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES
(100, 'AAAA', 'CCC')
Spid 13 正在请求 EX_PAGE 锁，并被 spid 14 阻塞，后者已经持有 dbid 6 中表 example2 中的页 0x188 的 EX_PAGE 锁。该锁保留在属于群集索引的页上。
Indid Value Description
-------------------------------------
0 Data page if there is no clustered index, or the
leaf page of a clustered index if there is one
1 Non-leaf page of the clustered index page
255 Text/image page
Any other value Non-clustered secondary index
spid 13 执行的当前命令为 INSERT ，跟踪给出输入缓冲区的一部分。
>> spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:
>> EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1
>> pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES
(200, 'AAAB', 'CCC')
Spid 14 正在等待 EX_PAGE 锁并被 spid 13 阻塞，后者已在同一页上持有 EX_PAGE 锁。
>> VICTIM: spid 13, pstat 0x0000 , cputime 30
SQL Server has chosen spid 13 as the deadlock victim.
下面是对该跟踪中各种锁的含义进行的说明：
SH_INT 和 EX_INT
它们是由于锁管理器不了解不同类型的项目（在此示例中为页和表）之间的关系，而在可以获得较低级的锁（例如，页）之前获得的较高级的项目（例如，表）的意图锁。如果在获得页的 EX_PAG 锁之前未获得表的 EX_INT 锁，则其他用户可以获得同一个表的 EX_TAB 锁，而且锁管理器将不会知道存在冲突。目前，SQL Server 仅具有针对表的意图锁。存在两种意图锁：共享 (SH_INT) 和独占 (EX_INT) 锁。
EX_PAGE
这是一个独占页锁，可在页因为 DELETE 、UPDATE 或 INSERT 语句而更新并且已禁用插入行级锁定 (IRL) 时获得。
UP_PAGE
这是一个更新页锁，可在扫描页并且优化器知道页将更新（或使用了 UPDLOCK 提示）时用来代替共享页锁。
PR_EXT 、NX_EXT 、UPD_EXT 和 EX_EXT
在分配或取消分配磁盘空间时可以获得这些锁。在从现有范围内分配或取消分配页时可获得 UPD_EXT ，其他锁则在分配或取消分配整个范围时使用。
IX_PAGE 和 LN_PAGE
这些是 IRL 锁。IX_PAGE 是页的 intent-to-do-row-locking 锁。当正在执行 IRL 的页需要拆分时，会获得 LN_PAGE 。
RLOCK 和 XRLOCK
这些短期锁可在遍历索引 b 树时获得。此类锁有两种类型：共享 (RLOCK) 和独占 (XRLOCK) 锁。共享锁在扫描期间获得，而独占锁则在更新期间针对索引页获得。
EX_TAB
这是在 SQL Server 优化器确定表扫描是解决更新查询的最有效方法时（例如，当表中没有索引时）出现的独占表锁。当用 TABLOCKX 提示锁定表时或 SQL Server 将表的页锁升级为表锁时，也会出现 EX_TAB 锁。
SH_TAB
这是当优化器假定将扫描表的大部分内容（或页锁定升级）或使用了 TABLOCK 提示时将使用的共享表锁。
如果两个连接按以下顺序更新表，则可避免前面的死锁示例：
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')
Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')
Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')
示例 2 ：对同一个表的不同部分的插入
如果两个连接以相反顺序插入到同一个表的不同部分中，但行有共享的页，则也会出现此死锁。例如：
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')
Connection1 > INSERT INTO example1 VALUES (400, 'AAAA', 'CCC')
在此示例表中，在 example1 表的第一列中存在一个群集索引。对于第一列而言，具有相同值的行将倾向于位于同一页中。在该示例中，Connection1 插入的第二行很可能与 Connection2 插入的第一行位于同一页上，因为它们都具有群集索引值 400 。这会导致 Connection2 阻塞 Connection1 。
Connection2 > INSERT INTO example1 VALUES (100, 'AAAB', 'CCC')
现在，Connection1 也可能阻塞 Connection2 ，从而导致死锁。以下为死锁跟踪信息：
97/04/20 12:56:01.40 spid16 *** DEADLOCK DETECTED with spid 15 ***
spid 16 requesting EX_PAGE (waittype 0x8005), blocked by:
EX_PAGE: spid 15, dbid 6, page 0x2c5, table example1, indid 0
pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (100,
'AAAB', 'CCC')
spid 15 waiting for EX_PAGE (waittype 0x8005), blocked by:
EX_PAGE: spid 16, dbid 6, page 0x8bd, table example1, indid 0
pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (400,
'AAAA', 'CCC')
VICTIM: spid 16, pstat 0x0000 , cputime 130
Spid 16 对页 0x2c5 的 EX_PAGE 锁的请求被 spid 15 阻塞，spid 15 在执行第一次插入后已持有页 0x2c5 的 EX_PAGE 锁。spid 15 在等待页 0x8db 的 EX_PAGE 锁时也被 spid 16 阻塞，从而导致死锁。
通过使用以下命令为表 example1 启用 IRL 可以避免此死锁：
sp_tableoption 'example1', 'insert row lock', true
示例 3 ：使用 IRL 的插入
当两个或更多个用户只执行插入操作时，IRL 允许这些用户共享页，这通常会提高吞吐量。但是，启用 IRL 不是总能够减少死锁。在某些情况下，IRL 可能会引入死锁。
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
Connection2 > INSERT INTO example1 VALUES (105, 'AAAB', 'CCC')
启用 IRL 后，两个连接都将持有包含两个新行的页的 IX_PAGE 锁。如果禁用 IRL ，Connection1 将获得 EX_PAGE 锁，Connection2 将立即被阻塞。
Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105
and column2 = 'AAAB'
此处，Connection2 需要独占页锁以执行 UPDATE 语句，该锁与 Connection1 的 IX_PAGE 锁不兼容。因此，Connection2 将等待。
Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 100
and column2 = 'AAAA'
现在，Connection1 可能被 Connection2 阻塞，从而导致死锁。以下为死锁跟踪信息：
97/04/20 15:13:50.07 spid17 *** DEADLOCK DETECTED with spid 18 ***
spid 17 requesting UP_PAGE (waittype 0x8007), blocked by:
IX_PAGE: spid 18, dbid 6, page 0x2c5, table example1, indid 0
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCA' where column1 = 100 and column2 = 'AAAA'
spid 18 waiting for UP_PAGE (waittype 0x8007), blocked by:
IX_PAGE: spid 17, dbid 6, page 0x2c5, table example1, indid 0
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCB' where column1 = 105 and column2 = 'AAAB'
VICTIM: spid 17, pstat 0x0000 , cputime 20
Spid 17 (Connection1) 正在等待 UP_PAGE 锁，这是获取独占页锁的第一步。Spid 17 被 spid 18 阻塞，后者持有页 0x2c5 的 IX_PAGE 锁。Spid 18 正在等待同一页的 UP_PAGE 锁，并被 spid 17 持有的 IX_PAGE 锁阻塞。由于 IX_PAGE 锁可共享，而 UP_LOCK 不可共享，因此这会导致死锁。在第一次插入期间，两个 spid 都获得了同一页的 IX_PAGE 锁，后来，它们尝试将该锁升级为 UP_PAGE 锁，由于 UP_PAGE 锁是独占的，因此该升级无法实现。
避免死锁的一种方法是将更新的值直接插入到表中，而不是插入并随后更新同一事务中的行。如果无法实现这种方法，则使用以下命令来禁用 IRL 将有助于避免死锁：
sp_tableoption 'example1', 'insert row lock', false
示例 4 ：向同一页上的行执行的插入
当两个 spid 所处理的行不同但属于同一页时，也会产生死锁。
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')
Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 405
and column2 = 'AAAA'
此处，Connection1 可能被 Connection2 阻塞。因为 Connection1 要更新 Connection2 已插入一行的页中的一行，因此可能会出现这种情况。
Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105
and column2 = 'AAAB'
此处，Connection2 也可能被 Connection1 阻塞，这将导致死锁。当 Connection2 要更新 Connection1 已插入一行的页中的一行时，可能会出现这种情况。以下为死锁跟踪信息：
97/04/20 15:48:21.18 spid20 *** DEADLOCK DETECTED with spid 19 ***
spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:
EX_PAGE: spid 19, dbid 6, page 0x2c4, table example1, indid 0
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCB' where column1 = 105 and column2 = 'AAAB'
spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:
EX_PAGE: spid 20, dbid 6, page 0xc48, table example1, indid 0
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCA' where column1 = 405 and column2 = 'AAAA'
VICTIM: spid 20, pstat 0x0000 , cputime 60
通过将行分布到不同页可以避免此死锁。执行此操作的一种方法是用较大的填充因子对此表重新创建群集索引。下面是以 50% 为填充因子创建群集索引的语句：
create unique clustered index ex1ind1 on example1 (column1, column2)
with fill factor = 50, PAD_INDEX
此语句将创建群集索引，同时将页的一半保留为空，其中包括非叶级群集索引（因为 PAD_INDEX 选项）。表占用空间为实际大小的两倍，每页的行数是实际行数的一半。
不保留表的填充因子，只在创建索引期间用指定的填充因子重新组织表。随着时间的变化，每页的行将发生变化，不再是索引创建期间指定的填充因子。发生这一情况时，最好用期望的填充因子重新创建群集索引。
避 免前面的死锁情况的另一种解决方案是用虚拟列（例如，dummy1 char(255) ）来填充表。这会增大行的大小，并导致每页的行数更少（最少可达每页一行）。由于此类型的填充可随时间变化而维护，因此无需重新创建群 集索引来维护填充（但由于其他原因您可能想重新创建群集索引）。此方法的缺点是要在虚拟字段上浪费存储空间。
示例 5 ：填充行
填充行会导致每页中的行数更少（因此，死锁也更少），但这并不能完全消除死锁。
在此示例表中，example1 进行了填充，使每页占用一行。下面是用于为此示例创建表的语句：
create table example1 (column1 int, column2 char(20), column3 char(50),
dummy_column4 char (255), dummy_column5 char (255), dummy_column6 char
(255))
go
create unique index ex1ind5 on example1 (column3, column2, column1,
dummy_column4, dummy_column5, dummy_column6) with fill factor = 85
go
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC', ' ', ' ',
' ', ' ')
Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC', ' ', ' ',
' ', ' ')
Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 401
and column2 = 'AAAA'
此处，Connection1 在更新行时被 Connection2 阻塞。由于 SQL Server 必须维护页链指针，因此它会锁定上一页、下一页以及正在更新的页。由于 Connection2 持有上一页的锁，因此 Connection1 必须等待，直到 Connection2 提交事务为止。
Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 101
and column2 = 'AAAB'
此处，Connection2 被 Connection1 阻塞，因为它必须锁定上一页，该页当前被 Connection1 锁定。结果就是出现死锁。以下为死锁跟踪信息：
spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:
EX_PAGE: spid 19, dbid 6, page 0x12b5, table example1, indid 0
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCB' where column1 = 101 and column2 = 'AAAB'
spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:
EX_PAGE: spid 20, dbid 6, page 0x1531, table example1, indid 0
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCA' where column1 = 401 and column2 = 'AAAA'
VICTIM: spid 20, pstat 0x0000 , cputime 300
通过在插入、更新或删除的行之间插入虚构行可以避免此死锁。例如，如果 Connection1 对行 pk = 1 进行处理（插入、更新或删除），Connection2 对行 pk = 5 进行处理，则在这两行之间插入行（如包含 pk = 3 的行）将避免死锁。此方法也会增加表的大小，但对于对应用程序至关重要的那些队列表而言，这可能是最佳的解决方案。
示例 6 ：非群集索引
在某些情况下，非群集的辅助索引可能会引入死锁。在此示例中，辅助索引的维护引入了死锁。
下面是用来为此示例创建辅助索引的语句：
create index ex1ind2 on example1 (column3) with fill factor = 90,
PAD_INDEX
Connection1 > BEGIN TRANSACTION
Connection2 > BEGIN TRANSACTION
Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCBA', ' ', '
', ' ', ' ')
Connection2 > INSERT INTO example1 VALUES (300, 'AAAB', 'CCCZ', ' ', '
', ' ', ' ')
Connection2 > UPDATE example1 SET column3 = 'CCBA' where column1 = 105
此处，Connection2 可能被 Connection1 阻塞，因为 Connection1 可能持有 Connection2 需要更新的辅助非群集索引页的锁。
Connection1 > UPDATE example1 SET column3 = 'CCCZ' where column1 = 305
此处，Connection1 可能被 Connection2 阻塞，从而导致死锁。当 Connection1 正在等待锁以更新 Connection2 已在其中插入并持有该页的锁的非群集辅助索引时，会出现这种情况。以下为此死锁示例的死锁跟踪信息：
97/04/20 19:05:38.75 spid11 *** DEADLOCK DETECTED with spid 12 ***
spid 11 requesting EX_PAGE (waittype 0x8005), blocked by:
EX_PAGE: spid 12, dbid 6, page 0x112f, table example1, indid 0x2
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCCZ' where column1 = 305
spid 12 waiting for EX_PAGE (waittype 0x8005), blocked by:
EX_PAGE: spid 11, dbid 6, page 0x1108, table example1, indid 0x2
pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
'CCBA' where column1 = 105
VICTIM: spid 11, pstat 0x0000 , cputime 50
通过删除辅助索引可以避免此死锁。不能通过填充索引来实现每页包含一行，因此只能通过消除非群集辅助索引或修改应用程序来避免这一情况。
死锁可能发生于两个以上的连接中，在这种情况下，死锁跟踪会列出死锁中涉及的 spid 以及彼此冲突的锁。死锁可能发生于 RLOCK 和 XRLOCK 锁中，这些锁在索引遍历期间获得。也可能由于范围锁（PR_EXT 、NX_EXT 、UPD_EXT 和 EX_EXT ）而出现死锁。
要了解有关分析死锁的其他信息，可以启用以下跟踪标志：
T1200
打印所有锁请求/ 释放信息（在请求/ 释放发生时），无论是否出现死锁。这会对性能产生很大影响，但对分析很有用。
T1206
打印死锁中的参与 spid 持有的所有锁。
T1208
打印客户端提供的主机名和程序名。这有助于确定死锁中涉及的客户端，前提是假定客户端为每个连接都指定了唯一值。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
