常见的表死锁情况及解决方法

1、死锁的第一种情况
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
解决方法
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
2、死锁的第二种情况
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操作，很容易就出现这种死锁的情况。

解决方法
1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

3、死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
解决方法

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

 

转载于：http://blog.csdn.net/a137268431/article/details/50982371

一、DBCC死锁分析

 

--DBCC捕获死锁信息方法
--1、打开跟踪配置
USE master
GO

 

DBCC TRACEON(1222,-1)
GO

 

--2、查看状态
DBCC TRACESTATUS(-1)
GO

 

--*跟踪完毕，请执行下面关闭
DBCC TRACEOFF (1204, 1222, -1)
GO
--split into two stats，效果同上
DBCC TRACEOFF (1204,-1)
GO
DBCC TRACEOFF (1222,-1)
GO

 

--3、数据库发生deadlock后，执行下面
EXEC sys.sp_readerrorlog

 

二、Profiler死锁分析方法

1、启动sql server profiler

2、勾选dealock选项

 

3、ctrl+F 搜索 deallock 字眼

4、选中deadlock项，提取事件数据，存储为xdl，然后更名为xml，打开xml文件

5、死锁信息数据大致如下，仔细阅读，总结即可学会如何从根本上解决死锁

<deadlock-list>
<deadlock victim="process21e48def088">
<process-list>
<process id="process21e48def088" taskpriority="0" logused="256" waitresource="KEY: 10:72057594043301888 (8194443284a0)" waittime="2947" ownerId="856488786" transactionname="user_transaction" lasttranstarted="2019-05-28T16:32:40.950" XDES="0x21f3d3c0490" lockMode="S" schedulerid="6" kpid="7172" status="suspended" spid="76" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-05-28T16:32:44.607" lastbatchcompleted="2019-05-28T16:32:44.603" lastattention="1900-01-01T00:00:00.603" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="CNSPDHLOTTRACE" hostpid="7660" loginname="developer" isolationlevel="read committed (2)" xactid="856488786" currentdb="10" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="4" stmtstart="24" stmtend="96" sqlhandle="0x0200000058b29a123afc09554bf56fb624ca094e650c3a150000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="adhoc" line="4" stmtstart="70" stmtend="132" sqlhandle="0x020000009257d20772bd2e19d62ba8f2cfba59c406b7df5a0000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>

--session3 3 3 3 3 3 3 3 3 3
SELECT * FROM Test_DL WHERE ID=1
</inputbuf>
</process>
<process id="process21f39bbb468" taskpriority="0" logused="256" waitresource="KEY: 10:72057594043301888 (61a06abd401c)" waittime="4583" ownerId="856387716" transactionname="user_transaction" lasttranstarted="2019-05-28T16:19:14.893" XDES="0x21f201cc490" lockMode="S" schedulerid="5" kpid="2936" status="suspended" spid="71" sbid="0" ecid="0" priority="0" trancount="5" lastbatchstarted="2019-05-28T16:32:42.973" lastbatchcompleted="2019-05-28T16:32:42.970" lastattention="2019-05-28T16:17:39.603" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="CNSPDHLOTTRACE" hostpid="7660" loginname="developer" isolationlevel="read committed (2)" xactid="856387716" currentdb="10" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="5" stmtstart="24" stmtend="96" sqlhandle="0x0200000058b29a123afc09554bf56fb624ca094e650c3a150000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="adhoc" line="5" stmtstart="78" stmtend="140" sqlhandle="0x02000000933188389d9131c5b0627d866347a1d71fc2455e0000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>

--session2 2 2 2 2 2 2 2 2 2
SELECT * FROM Test_DL WHERE ID=2
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594043301888" dbid="10" objectname="deadlockd.dbo.Test_DL" indexname="PK__Test_DL__3213E83F6EC1632C" id="lock21db37f0b80" mode="X" associatedObjectId="72057594043301888">
<owner-list>
<owner id="process21f39bbb468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process21e48def088" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594043301888" dbid="10" objectname="deadlockd.dbo.Test_DL" indexname="PK__Test_DL__3213E83F6EC1632C" id="lock21f3db3f800" mode="X" associatedObjectId="72057594043301888">
<owner-list>
<owner id="process21e48def088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process21f39bbb468" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>