sqlserver锁分析,sqlserver锁机制

sqlserver 怎么分析死锁xml

当死锁发生后，通过服务端的Trace就可以将死锁信息传到日志。在SQL Server 2000时代，只能通过Trace flag 1204来开启，由于Trace flag 1204并不能提供XML死锁图，在SQL Server 2005以及之后的版本被Trace flag 1222所取代。

站在用户的角度思考问题，与客户深入沟通，找到革吉网站设计与革吉网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都网站设计、成都网站制作、企业官网、英文网站、手机端网站、网站推广、空间域名、虚拟主机、企业邮箱。业务覆盖革吉地区。

为了在服务端针对所有的Session开启Trace flag 1222。可以通过如代码所示。

DBCC TRACEON(1222,-1)

另一种方法是开启Profiler来捕捉,Profiler捕捉到的图示死锁信息内容就更直观了，

如何分析SQLServer中的deadlocktrace

首先我们来看一个简单的例子，大结构非常简单：

1，process-list显示了两个进程之间发生了死锁process60fb88和processd11902c8。

2，vistim-list显示了process60fb88被选为了牺牲者。

2，后面的resource-list显示了两个进程争取并导致死锁的资源。

[html] view plain copy

deadlock

victim-list

victimProcess id="process60fb88" /

/victim-list

process-list

process id="process60fb88" taskpriority="0" logused="0" waitresource="KEY: 9:72057597664231424 (7506ff9b7b0d)" waittime="4376" ownerId="2656658629" transactionname="SELECT" lasttranstarted="2014-04-09T23:01:35.743" XDES="0x80059940" lockMode="S" schedulerid="4" kpid="10640" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-04-09T23:01:35.657" lastbatchcompleted="2014-04-09T23:01:35.657" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2656658629" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"

executionStack

frame procname="" line="9" stmtstart="336" stmtend="874" sqlhandle="0x030009003d00da3fa6087c0182a200000100000000000000" /

frame procname="" line="20" stmtstart="1022" stmtend="1206" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" /

frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" /

frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" /

/executionStack

inputbuf

DECLARE @logText NVARCHAR(MAX)

EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT

SELECT @logText /inputbuf

/process

process id="processd11902c8" taskpriority="0" logused="232" waitresource="KEY: 9:72057596808265728 (ed2e944beff9)" waittime="4379" ownerId="2656658630" transactionname="UPDATE" lasttranstarted="2014-04-09T23:01:35.743" XDES="0x80048570" lockMode="X" schedulerid="8" kpid="6620" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-04-09T23:01:34.650" lastbatchcompleted="2014-04-09T23:01:34.650" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2656658630" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"

executionStack

frame procname="" line="22" stmtstart="1230" stmtend="1496" sqlhandle="0x030009003d00da3fa6087c0182a200000100000000000000" /

frame procname="" line="20" stmtstart="1022" stmtend="1206" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" /

frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" /

frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" /

/executionStack

inputbuf

DECLARE @logText NVARCHAR(MAX)

EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT

SELECT @logText /inputbuf

/process

/process-list

resource-list

keylock hobtid="72057597664231424" dbid="9" objectname="" indexname="" id="lockc99859500" mode="X" associatedObjectId="72057597664231424"

owner-list

owner id="processd11902c8" mode="X" /

/owner-list

waiter-list

waiter id="process60fb88" mode="S" requestType="wait" /

/waiter-list

/keylock

keylock hobtid="72057596808265728" dbid="9" objectname="" indexname="" id="lock2f4de2d00" mode="S" associatedObjectId="72057596808265728"

owner-list

owner id="process60fb88" mode="S" /

/owner-list

waiter-list

waiter id="processd11902c8" mode="X" requestType="wait" /

/waiter-list

/keylock

/resource-list

/deadlock

下面是详细分析。

1，victim-list没什么可分析的。

2，process-list中关于各个process的详细信息很重要。

waitresource="KEY: 9:72057597664231424 (7506ff9b7b0d)"

当前process正在等待的资源。通常我们在resource-list中可以看到同样的信息。使用下面的sql查询等待的资源是什么：

下面使用的hobtid是heap or b-tree id的缩写。详细见sys.partotions的解释。

[sql] view plain copy

SELECT o.name, i.name

FROM sys.partitions p

JOIN sys.objects o ON p.object_id = o.object_id

JOIN sys.indexes i ON p.object_id = i.object_id

AND p.index_id = i.index_id

WHERE p.hobt_id = 72057597664231

name name

--------------------------------------------------------------------

MatchService PK_Matcher_ID

从结果我们就可以知道，等待的资源是一个表MatchService的主键PK_Matcher_ID。考察另外一个process的waitresource我们可以得知等待的资源是同一个表的另外一个索引。至此我们找到了直接导致死锁的资源是什么。

同时可以看到两个process一个是x lock，一个是s lock。因此可以判定发生在该表上的一个修改语句和一个查询语句之间发生了死锁。

另外，上例中可以清晰的看到是keylock导致的死锁，因此查询partitions可以找到对应的object (sys.partitions contains a row for each partition of all

the tables and most types of indexes in the database.)。但有时是其他类型的资源发生了死锁，例如pagelock, waitresource="PAGE: 9:1:28440841" 。 9是dbid; 1是fileid; 28440841是pageid。对于这种情况，使用下面的语句查询对应的资源：

[sql] view plain copy

DBCC TRACEON(3604)

GO

DBCC PAGE (9, 1, 28440841)

GO

DBCC TRACEOFF(3604)

GO

从返回的Metadata: objectId找到对应的objectid。

3，再看process中的inputbuf。这个tag表明了process正在运行的语句，因此对于定位死锁非常重要。但这里有一个问题，比如

上例中，inputbuf是一个存储过程，其中又嵌套了很多其他的存储过程，但inputbuf是用户直接发出的sql，而我们需要在其中找出直接导致死

锁的语句并优化，从而解决或减少死锁。自此我们已经有的信息是：导致死锁的语句由inputbuf中的语句调用，同时导致死锁的语句必定是对表

MatchService的修改语句。如果存储过程很简单，到此DBA已经能够找到直接导致死锁的sql了，分析过程到此结束。而如果存储过程很复杂，则

需要进一步分析。

4，现在再进一步考察tag, executionStack。executionStack表明了死锁发生时，由inputbuf调用的一系列

sql。上例中有4条sql。同时仔细观察上例可以发生，两个process的executionStack是完全相同的，因此考察一个就可以了。另外，

如果procname不为空则直接得到了sql，但上例中该tag为空。

自此我们希望把executionStack中的所有sql显示出来。使用下面的sql找出sqlhandle对应的在内存中的sql。需要注意的

是，如果deadlock已经过去了一段时间，sqlhandle可能已经被从内存中清除掉了，这时就不可查了。还有sqlhandle是

varbinaryd，所以查询时不可加引号。

另外还有一个有趣的地方：和其他程序语言报错时一样，stack最上的一条是最直接的错误，后面的错误都是该错误的上一层错误(这么解释可能有点

乱，写过代码的同学能理解哈)。因此在上面说的存储过程调用存储过程的情况中，executionStack中第一条是直接导致死锁的sql，第二条是调

用该sql的sql，以此类推，最后一条理论上就是inputbuf中的sql。

[sql] view plain copy

SELECT sql_handle AS Handle,

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS Text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

where sql_handle = 0x030009003d00da3fa6087c0182a200000100000000000000

order by sql_handle

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0x030009003D00DA3FA6087C0182A200000100000000000000SELECT

TOP 1 @matcherQueueID = lhs.MatcherService_MatcherQueue_ID,

@rootOperationUID = Root_Operation_UID FROM

MatcherService_MatcherQueue lhs WHERE lhs.Processing_State

= 'MATCHING' OR lhs.Processing_State = 'MATCHED' ORDER BY

Last_Execution_Date ASC

0x030009003D00DA3FA6087C0182A200000100000000000000

SELECT Top 1 @ticketID = OperationLog_ID FROM GEDemo.dbo.OperationLog

WHERE @rootOperationUID = Root_Operation_UID AND Status = 0

ORDER BY OperationLog_ID ASC

0x030009003D00DA3FA6087C0182A200000100000000000000

UPDATE MatcherService_MatcherQueue SET Last_Execution_Date =

GETDATE() WHERE MatcherService_MatcherQueue_ID = @matcherQueueID

注意看起来一个sql_handle有三条语句，原因是这三条sql是属于同一个存储过程的。

如果一个sql_handle包含的语句很多，比如是一个很长的存储过程，那么我们还可以使用一个有力的信息：executionStack中的

line

tag.这条语句表明了到底是哪一个sql直接导致了死锁。如果一条statement中又包含了很多表，那么还需要和死锁的资源结合起来判断是哪个表或

索引的数据发生了死锁。

解析：如何快速掌握SQLServer的锁机制

各种大型数据库所采用的锁的基本理论是一致的，但在具体实现上各有差别。SQLServer更强调由系统来管理锁。在用户有SQL请求时，系统分析请求，自动在满足锁定条件和系统性能之间为数据库加上适当的锁，同时系统在运行期间常常自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，就需要了解SQLServer的锁机制，掌握数据库锁定方法。 锁是数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。我们知道，多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象。即如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括：丢失更新、脏读、不可重复读和幻觉读： 1．当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。例如，两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。 2.脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。 3．不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。 4．幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。 所以，处理多用户并发访问的方法是加锁。锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。为了控制锁定的资源，应该首先了解系统的空间管理。在SQLServer2000系统中，最小的空间管理单位是页，一个页有8K。所有的数据、日志、索引都存放在页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上，不能跨页。页上面的空间管理单位是盘区，一个盘区是8个连续的页。表和索引的最小占用单位是盘区。数据库是由一个或者多个表或者索引组成，即是由多个盘区组成。放在一个表上的锁限制对整个表的并发访问；放在盘区上的锁限制了对整个盘区的访问；放在数据页上的锁限制了对整个数据页的访问；放在行上的锁只限制对该行的并发访问。 SQLServer2000具有多粒度锁定，允许一个事务锁定不同类型的的资源。为了使锁定的成本减至最少，SQLServer自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其它事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。SQLServer可以锁定行、页、扩展盘区、表、库等资源。 行是可以锁定的最小空间,行级锁占用的数据资源最少，所以在事务的处理过程中，允许其他事务继续操纵同一个表或者同一个页的其他数据，大大降低了其他事务等待处理的时间，提高了系统的并发性。 页级锁是指在事务的操纵过程中，无论事务处理数据的多少，每一次都锁定一页，在这个页上的数据不能被其他事务操纵。在SQLServer7.0以前，使用的是页级锁。页级锁锁定的资源比行级锁锁定的数据资源多。在页级锁中，即使是一个事务只操纵页上的一行数据，那么该页上的其他数据行也不能被其他事务使用。因此，当使用页级锁时，会出现数据的浪费现象，也就是说，在同一个页上会出现数据被占用却没有使用的现象。在这种现象中，数据的浪费最多不超过一个页上的数据行。 表级锁也是一个非常重要的锁。表级锁是指事务在操纵某一个表的数据时，锁定了这个数据所在的整个表，其他事务不能访问该表中的其他数据。当事务处理的数据量比较大时，一般使用表级锁。表级锁的特点是使用比较少的系统资源，但是却占用比较多的数据资源。与行级锁和页级锁相比，表级锁占用的系统资源例如内存比较少，但是占用的数据资源却是最大。在表级锁时，有可能出现数据的大量浪费现象，因为表级锁锁定整个表，那么其他的事务都不能操纵表中的其他数据。 盘区锁是一种特殊类型的锁，只能用在一些特殊的情况下。簇级锁就是指事务占用一个盘区，这个盘区不能同时被其他事务占用。例如在创建数据库和创建表时，系统分配物理空间时使用这种类型的锁。系统是按照盘区分配空间的。当系统分配空间时，使用盘区锁，防止其他事务同时使用同一个盘区。当系统完成分配空间之后，就不再使用这种类型的盘区锁。特别是，当涉及到对数据操作的事务时，不使用盘区锁。 数据库级锁是指锁定整个数据库，防止任何用户或者事务对锁定的数据库进行访问。数据库级锁是一种非常特殊的锁，它只是用于数据库的恢复操作过程中。这种等级的锁是一种最高等级的锁，因为它控制整个数据库的操作。只要对数据库进行恢复操作，那么就需要设置数据库为单用户模式，这样系统就能防止其他用户对该数据库进行各种操作。 行级锁是一种最优锁，因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在SQLServer中，还支持锁升级(lockescalation)。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQLServer中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。

如何处理SQL Server死锁问题？

死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双方互相等待。简单的例子如下：

trans1 trans2

------------------------------------------------------------------------

1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction

2.update table A 2.update table B

3.update table B 3.update table A

4.IDBConnection.Commit 4.IDBConnection.Commit

那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。

好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。

-- Batch #1

CREATE DATABASE deadlocktest

GO

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222, -1)

-- 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc

--traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是1222。

GO

IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1

IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1

IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2

GO

CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))

GO

DECLARE @x int

SET @x = 1

WHILE (@x = 1000) BEGIN

INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)

SET @x = @x + 1

END

GO

CREATE CLUSTERED INDEX cidx ON t1 (c1)

CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)

GO

CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

GO

CREATE PROC p2 @p1 int AS

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1

GO

上述sql创建一个deadlock的示范数据库，插入了1000条数据，并在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外创建了两个sp，分别是从t1中select数据和update数据。

好，打开一个新的查询窗口，我们开始执行下面的query：

-- Batch #2

USE deadlocktest

SET NOCOUNT ON

WHILE (1=1) EXEC p2 4

GO

开始执行后，然后我们打开第三个查询窗口，执行下面的query：

-- Batch #3

USE deadlocktest

SET NOCOUNT ON

CREATE TABLE #t1 (c2 int, c3 int)

GO

WHILE (1=1) BEGIN

INSERT INTO #t1 EXEC p1 4

TRUNCATE TABLE #t1

END

GO

开始执行，哈哈，很快，我们看到了这样的错误信息：

Msg 1205, Level 13, State 51, Procedure p1, Line 4

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

spid54发现了死锁。

那么，我们该如何解决它？

在SqlServer 2005中，我们可以这么做：

1.在trans3的窗口中，选择EXEC p1 4，然后right click，看到了菜单了吗？选择Analyse Query in Database Engine Tuning Advisor。

2.注意右面的窗口中，wordload有三个选择：负载文件、表、查询语句，因为我们选择了查询语句的方式，所以就不需要修改这个radio option了。

3.点左上角的Start Analysis按钮

4.抽根烟，回来后看结果吧！出现了一个分析结果窗口，其中，在Index Recommendations中，我们发现了一条信息：大意是，在表t1上增加一个非聚集索引索引：t2+t1。

5.在当前窗口的上方菜单上，选择Action菜单，选择Apply Recommendations，系统会自动创建这个索引。

重新运行batch #3，呵呵，死锁没有了。

这种方式，我们可以解决大部分的Sql Server死锁问题。那么，发生这个死锁的根本原因是什么呢？为什么增加一个non clustered index，问题就解决了呢？ 这次，我们分析一下，为什么会死锁呢？再回顾一下两个sp的写法：

CREATE PROC p1 @p1 int AS

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

GO

CREATE PROC p2 @p1 int AS

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1

GO

很奇怪吧！p1没有insert，没有delete，没有update，只是一个select，p2才是update。这个和我们前面说过的，trans1里面updata A，update B；trans2里面upate B，update A，根本不贴边啊！

那么，什么导致了死锁？

需要从事件日志中，看sql的死锁信息：

Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.

The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.

首先，我们看看p1的执行计划。怎么看呢？可以执行set statistics profile on，这句就可以了。下面是p1的执行计划

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))

|--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] = [@p1] AND [t1].[c2] = [@p1]+(1)) ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

我们看到了一个nested loops，第一行，利用索引t1.c2来进行seek，seek出来的那个rowid，在第二行中，用来通过聚集索引来查找整行的数据。这是什么？就是bookmark lookup啊！为什么？因为我们需要的c2、c3不能完全的被索引t1.c1带出来，所以需要书签查找。

好，我们接着看p2的执行计划。

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

|--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))

|--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))

|--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...

|--Top(ROWCOUNT est 0)

|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)

通过聚集索引的seek找到了一行，然后开始更新。这里注意的是，update的时候，它会申请一个针对clustered index的X锁的。

实际上到这里，我们就明白了为什么update会对select产生死锁。update的时候，会申请一个针对clustered index的X锁，这样就阻塞住了（注意，不是死锁！）select里面最后的那个clustered index seek。死锁的另一半在哪里呢？注意我们的select语句，c2存在于索引idx1中，c1是一个聚集索引cidx。问题就在这里！我们在p2中更新了c2这个值，所以sqlserver会自动更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我们刚才的select语句中。而对这个索引列的更改，意味着索引集合的某个行或者某些行，需要重新排列，而重新排列，需要一个X锁。

SO………，问题就这样被发现了。

总结一下，就是说，某个query使用非聚集索引来select数据，那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上，它需要根据rowid找到对应的聚集索引的那行，然后找到其他数据。而此时，第二个的查询中，update正在聚集索引上忙乎：定位、加锁、修改等。但因为正在修改的某个列，是另外一个非聚集索引的某个列，所以此时，它需要同时更改那个非聚集索引的信息，这就需要在那个非聚集索引上，加第二个X锁。select开始等待update的X锁，update开始等待select的S锁，死锁，就这样发生鸟。

那么，为什么我们增加了一个非聚集索引，死锁就消失鸟？我们看一下，按照上文中自动增加的索引之后的执行计划：

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

|--Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] = [@p1] AND [deadlocktest].[dbo].[t1].[c2] = [@p1]+(1)) ORDERED FORWARD)

哦，对于clustered index的需求没有了，因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。

实际上，在sqlserver 2005中，如果用profiler来抓eventid：1222，那么会出现一个死锁的图，很直观的说。

下面的方法，有助于将死锁减至最少（详细情况，请看SQLServer联机帮助，搜索：将死锁减至最少即可。

按同一顺序访问对象。

避免事务中的用户交互。

保持事务简短并处于一个批处理中。

使用较低的隔离级别。

使用基于行版本控制的隔离级别。

将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。

使用快照隔离。

使用绑定连接。

如何掌握SQLServer的锁机制

SQL SERVER里的锁机制：

NOLOCK（不加锁）

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。 例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。

UPDLOCK（修改锁）

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）

此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。

TABLOCKX（排它表锁）

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。