前言
终于进入死锁系列,前面也提到过我一直对隔离级别和死锁以及如何避免死锁等问题模棱两可,所以才鼓起了重新学习SQL Server系列的勇气,本节我们来讲讲SQL Server中的死锁,看到许多文章都只简述不能这样做,这样做会导致死锁,但是未理解其基本原理,下次遇到类似情况依然会犯错,所以基于了解死锁原理并且得到治疗死锁良方,博主不惜花费多天时间来学习死锁最终总结出本文,若有叙述不当之处请在评论中指出。
死锁定义
死锁是两个或多个进程互相阻塞的情况。两个进程死锁的例子是,进程A阻塞进程B且进程B阻塞进程B。涉及多个进程死锁的例子是,进程A阻塞进程B,进程B阻塞进程C且进程C阻塞进程A。在任何一种情况下,SQL Server检测到死锁,都会通过终止其中的一个事务尽心干预。如果SQL Server不干预,涉及的进程永远陷于死锁状态。
除外另外指定,SQL Server选择终止工作最少的事务,因为它便于回滚该事务的工作。但是,SQL Server允许用户设置一个叫做DEADLOCK_PRIORITY的会话选项,可以是范围在-10~10之间的21个值中的任意值,死锁优先级最低的进程将被作为牺牲对象,而不管其做了多少工作。我们可以举一个生活中常见和死锁类似的例子,当在车道上行驶时,快到十字路口的红灯时,此时所有的小车都已经就绪等待红灯,当变绿灯时,此时有驾驶员发现走错了车道,于是开始变换车道,但是别的车道都拥堵在一块根本插不进去,驾驶员只有等待有空隙时再插进去,同时驾驶员车道上后面的小车又在等待驾驶员开到别的车道。这种情况虽然不恰当,但是在一定程度上很好的表现了死锁的情况,所以在开车时尽量别吵吵,否则谁都走不了,keep silence。
下面我们来演示常见的一种死锁情况,然后我们再来讨论如何减少系统中死锁的发生。
读写死锁
在SQL Server数据库中我们打开两个连接并确保都已连接到数据库,在会话一中我们试图去更新Production.Products表中产品2的行。
SET TRAN ISOLATION LEVEL READ COMMITTED
BEGIN TRAN;
UPDATE Production.Products
SET unitprice += 1.00
WHERE productid = 2;
在会话2中再来打开一个事务,更新Sales.OrderDetails表中产品2的行,并使事务保持打开状态
SET TRAN ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE Sales.OrderDetails
SET unitprice += 1.00
WHERE productid = 2;
此时上述会话一和会话二都用其会话的排他锁且都能更新成功,下面我们再来在会话一中进行查询Sale.OrderDetails表中产品2的行并提交事务。
SET TRAN ISOLATION LEVEL READ COMMITTED
BEGIN TRAN;
SELECT orderid, productid, unitprice
FROM Sales.OrderDetails
WHERE productid = 2;
COMMIT TRAN;
因为需要查询Sales.OrderDetails表中产品2的行,但是在之前我们更新产品2的行同时并未提交事务,因为查询的共享锁和排它锁不兼容,最终导致查询会阻塞,接下来我们在会话二中再来查询Producution.Products表中产品为2的行。
SET TRAN ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
此时我们看到在会话二中能成功查询到Production.Products表中产品2的行,同时我们再来看看会话一中查询情况。
上述死锁算是最常见的死锁情况,在会话一(A进程)中去更新Production.Products表中产品2的行,在会话二(B进程)去更新Sales.OrderDetails表中产品2的行,但是接下来在会话一中去查询Sales.OrderDetails表中产品2的行,此时B进程要等待A进程中未提交的事务进行提交,所以导致A进程将阻塞B进程,接着在会话二中去查询Production.Products表中产品2的行,此时A进程要等待B进程中未提交的事务进行提交,所以导致B进程阻塞A进程,最终结果将是死锁。所以到了这里我们能够很清楚地知道在两个或多个事务中注意事务之间不能交叉进行。要是面试时忘记了肿么办,告诉你一个简单的方法,当军训或者上体育课正步走时只有1、2、1,没有1、2、2就行。
写写死锁
想必大家大部分只知道上述情况的死锁,上述情况是什么情况,我们抽象一下则是不同表之间导致的死锁,下面我们来看看同一表中如何产生死锁,这种情况大家更加需要注意了。我们首先创建死锁测试表并对表中列Id,Name创建唯一聚集索引,如下:
USE tempdb
GO
CREATE TABLE DeadlocksExample
(Id INT, Name CHAR(20), Company CHAR(50));
GO
CREATE UNIQUE CLUSTERED INDEX deadlock_idx ON DeadlocksExample (Id, Name)
GO
接下来在会话一中插入一条测试数据开启事务但是并未提交,如下:
BEGIN TRAN
INSERT INTO dbo.DeadlocksExample
VALUES (1, 'Jeffcky', 'KS')
接下来再来打开一个会话二插入一条数据开启事务但是并未提交,如下:
BEGIN TRAN
INSERT INTO DeadlocksExample
VALUES (10, 'KS', 'Jeffcky')
再来在会话一中插入一条数据。
INSERT INTO DeadlocksExample
VALUES (10, 'KS', 'Jeffcky')
此时此次插入将会阻塞,如下:
最后再来在会话二中插入一条数据
INSERT INTO DeadlocksExample
VALUES (1, 'Jeffcky', 'KS')
此时此次插入能进行但是会显示死锁信息,如下:
想必大多数情况下看到的是通过不同表更新行产生的死锁,在这里我们演示了在相同表通过插入行也会导致死锁,死锁真是无处不在。上述发生死锁的主要原因在于第二次在会话一中去插入相同数据行时此时由于我们创建了Id和Name的唯一聚集索引所以SQL Server内部会尝试去读取行导致插入阻塞,在会话一中去插入行同理,最终造成彼此等待而死锁。为了更深入死锁知识,我们来看看如何从底层来探测死锁,上述发生死锁后,我们通过运行如下语句来查询死锁图:
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
FROM ( SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY
TargetData.nodes
('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData ( XEvent )
) AS src;
此时你将发现会出现如下xml的数据:
我们点看死锁图来分析分析:
<deadlock>
<victim-list>
<victimProcess id="process17602d868" />
</victim-list>
<process-list>
<process id="process17602d868" taskpriority="0" logused="300" waitresource="KEY: 2:2089670228247904256 (4e0d37de3c51)" waittime="4222" ownerId="49122" transactionname="user_transaction" lasttranstarted="2017-03-04T21:56:15.447" XDES="0x16db8c3a8" lockMode="X" schedulerid="4" kpid="8296" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-03-04T21:56:47.080" lastbatchcompleted="2017-03-04T21:56:15.450" lastattention="1900-01-01T00:00:00.450" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="WANGPENG" hostpid="1640" loginname="wangpengJeffckyWang" isolationlevel="read committed (2)" xactid="49122" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x02000000ea13d9115e8a4d429bc3d549e9053a3a784358020000000000000000000000000000000000000000">
INSERT INTO [DeadlocksExample] values(@1,@2,@3) </frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000009882c20809f279b6638fea1ef34b7986efb6b60a0000000000000000000000000000000000000000">
INSERT INTO DeadlocksExample
VALUES (1, 'Jeffcky', 'KS') </frame>
</executionStack>
<inputbuf>
INSERT INTO DeadlocksExample
VALUES (1, 'Jeffcky', 'KS') </inputbuf>
</process>
<process id="process17602dc38" taskpriority="0" logused="300" waitresource="KEY: 2:2089670228247904256 (381c351990d5)" waittime="20467" ownerId="49022" transactionname="user_transaction" lasttranstarted="2017-03-04T21:56:06.070" XDES="0x16db8d6a8" lockMode="X" schedulerid="4" kpid="2684" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-03-04T21:56:30.837" lastbatchcompleted="2017-03-04T21:56:06.070" lastattention="1900-01-01T00:00:00.070" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="WANGPENG" hostpid="1640" loginname="wangpengJeffckyWang" isolationlevel="read committed (2)" xactid="49022" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x02000000ea13d9115e8a4d429bc3d549e9053a3a784358020000000000000000000000000000000000000000">
INSERT INTO [DeadlocksExample] values(@1,@