1.1.1 摘要在系统设计过程中系统的稳定性、响应速度和读写速度至关重要就像12306.cn那样当然我们可以通过提高系统并发能力来提高系统性能总体性能但在并发作用下也会出现一些问题例如死锁。今天的博文将着重介绍死锁的原因和解决方法。1.1.2 正文定义死锁是由于并发进程只能按互斥方式访问临界资源等多种因素引起的并且是一种与执行时间和速度密切相关的错误现象。死锁的定义若在一个进程集合中每一个进程都在等待一个永远不会发生的事件而形成一个永久的阻塞状态这种阻塞状态就是死锁。死锁产生的必要条件1.互斥mutual exclusion)系统存在着临界资源2.占有并等待(hold and wait)已经得到某些资源的进程还可以申请其他新资源3.不可剥夺(no preemption)已经分配的资源在其宿主没有释放之前不允许被剥夺4.循环等待(circular waiting)系统中存在多个大于2个进程形成的封闭的进程链链中的每个进程都在等待它的下一个进程所占有的资源图1死锁产生条件我们知道哲学家就餐问题是在计算机科学中的一个经典问题并发和死锁用来演示在并行计算中多线程同步(Synchronization)时产生的问题其中一个问题就是存在死锁风险。图2哲学家就餐问题图片源于wiki而对应到数据库中当两个或多个任务中如果每个任务锁定了其他任务试图锁定的资源此时会造成这些任务阻塞从而出现死锁这些资源可能是单行(RID堆中的单行)、索引中的键(KEY行锁)、页(PAG8KB)、区结构(EXT连续的8页)、堆或B树(HOBT) 、表(TAB包括数据和索引)、文件(File数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。假设我们定义两个进程P1和P2它们分别拥有资源R2和R1但P1需要额外的资源R1恰好P2也需要R2资源而且它们都不释放自己拥有的资源这时资源和进程之间形成了一个环从而形成死锁。图3死锁图片源于wikiSQL Server中死锁排查1.使用SQL Server中系统存储过程sp_who和sp_lock可以查看当前数据库中阻塞进程的情况首先我们在数据库中创建两个表Who和Lock分别用来存放阻塞和锁定的数据SQL代码如下CREATE Table Who(spid int, ecid int, status nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_ID int); CREATE Table Lock(spid int, dpid int, objid int, indld int, [Type] nvarchar(20), Resource nvarchar(50), Mode nvarchar(10), Status nvarchar(10) );接着我们要把阻塞和锁定数据分别存放到Who和Lock表中SQL代码如下INSERT INTO Who -- Diagnose which process causing the block. EXEC sp_who active INSERT INTO Lock -- Check which source has been locked. EXEC sp_lock DECLARE DBName nvarchar(20); SET DBNameYourDatabaseName SELECT Who.* FROM Who WHERE dbnameDBName SELECT Lock.* FROM Lock JOIN Who ON Who.spidLock.spid AND dbnameDBName; -- Displays the last statement sent from -- a client to an instance of Microsoft SQL Server. DECLARE crsr Cursor FOR SELECT blk FROM Who WHERE dbnameDBName AND blk0; DECLARE blk int; open crsr; FETCH NEXT FROM crsr INTO blk; WHILE (FETCH_STATUS 0) BEGIN; dbcc inputbuffer(blk); FETCH NEXT FROM crsr INTO blk; END; close crsr; DEALLOCATE crsr; -- Get the locked source. SELECT Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM Lock JOIN Who ON Who.spidLock.spid AND dbnameDBName WHERE objid0;2.使用SQL Server Profiler分析死锁将Deadlock graph事件类添加到跟踪。此事件类使用死锁涉及到的进程和对象的XML数据填充跟踪中的TextData数据列。SQL Server 事件探查器可以将XML文档提取到死锁XML(.xdl) 文件中以后可在SQL Server Management Studio中查看该文件(下面将给出详细介绍)。死锁的示例和解决方法首先我们在数据库tempdb中创建两个表DlTable1和DlTable2它们都包含两个字段分别是Id和Name接着我们往这两个表中插入数据具体SQL代码如下-- Note we use tempdb for testing. USE tempdb -- Create datatable in tempdb. CREATE TABLE DlTable1 (DL1Id INT, DL1Name VARCHAR(20)) CREATE TABLE DlTable2 (DL2Id INT, DL2Name VARCHAR(20)) -- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2005. INSERT INTO DlTable1 SELECT 1, Deadlock UNION ALL SELECT 2, JKhuang UNION ALL SELECT 3, Test GO INSERT INTO DlTable2 SELECT 1, Deadlock UNION ALL SELECT 2, JacksonHuang UNION ALL SELECT 3, Test GO -- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2008. INSERT INTO DlTable1 VALUES (1, Deadlock), (2, JKhuang), (3, Test) INSERT INTO DlTable2 VALUES (1, Deadlock), (2, JacksonHuang), (3, Test)现在我们执行以上SQL代码成功创建了DlTable1和DlTable2并且插入了数据。图4插入数据到表中接着我们打开两个查询窗口分别创建两个独立的事务A和B如下-- In query window 1. USE tempdb GO -- Create transaction A. BEGIN TRANSACTION UPDATE DlTable1 SET DL1Name Uplock WHERE DL1Id 2 -- Delay 23 second. WAITFOR DELAY 00:00:23 UPDATE DlTable2 SET DL2Name Downlock WHERE DL2Id 2 ROLLBACK TRANSACTION -- In query window 2. USE tempdb GO -- Create transaction B. BEGIN TRANSACTION UPDATE DlTable2 SET DL2Name Downlock WHERE DL2Id 2 -- Delay 23 second. WAITFOR DELAY 00:00:23 UPDATE DlTable1 SET DL1Name Uplock WHERE DL1Id 2 ROLLBACK TRANSACTION上面我们定义了两个独立的事务A和B为了测试死锁这里我们使用WAITFOR DELAY使事务执行产生延时。图5事务执行结果运行完上面的两个查询后我们发现其中一个事务执行失败系统提示该进程和另一个进程发生死锁而另一个事务执行成功这是由于SQL Server自动选择一个事务作为死锁牺牲品。既然发生了死锁那么究竟是哪个资源被锁定了呢现在我们通过死锁排除方法一来查看具体是哪个资源被锁定。现在我们重新执行事务A和B接着使用死锁排除方法一查看更新事务具体使用到的锁。图6更新操作使用的锁通过上图我们知道首先事务A给表DlTable1下了行排他锁RID X然后在下页意向更新锁PAG IX最后给整个DlTable1表下了表意向更新锁TAB IX事务B的使用的锁也是一样的。事务A拥有DL1Id 2行排他锁RID X同时去请求DL2Id 2的行排他锁RID X但我们知道事务B已经拥有DL2Id 2的行排他锁RID X而且去请求DL1Id 2行排他锁RID X由于行排他锁和行排他锁是冲突的所以导致死锁。图7锁的兼容性前面我们介绍了使用sp_lock查看更新操作时SQL Server使用的锁行锁、页锁和表锁现在我们在更新操作后查询操作SQL代码如下现在我们使用SQL Server Profiler分析死锁在本节中我们将看到如何使用SQL Server Profiler来捕获死锁跟踪。1.启动SQL Server事件探查器和连接所需的SQL Server实例2.创建一个新的跟踪3.在事件选择页中取消默认事件选项我们选择“死锁图形”事件、 “锁定死锁”和“锁定死锁链”如下图所示图8事件选择设置4. 启动一个新的跟踪5.在SSMS中开两个查询窗口#1和#2我们重新执行前面两个事务6.事务执行结束一个执行成为另一个发生死锁错误7.我们打开事件探查器如下图所示图9 Deadlock graph8.选择Deadlock graph我们可以直观查看到两个事务之间发生死锁的原因图10 事务进程A上图的椭圆形有一个叉表示事务A被SQL Server选择为死锁牺牲品如果我们把鼠标指针移动到椭圆中会出现一个提示。图11 事务进程B上图的椭圆形表示进程执行成功我们把鼠标指针移动到椭圆中也会出现一个提示。中间的两个矩形框称为资源节点它们代表的数据库对象如表行或索引。由于事务A和B在拥有各自资源时试图获得对方资源的一个独占锁使得进程相互等待对方释放资源从而导致死锁。死锁避免现在让我们回顾一下上了死锁的四个必要条件互斥,占有并等待,不可剥夺和循环等待我们只需破坏其中的一个或多个条件就可以避免死锁发生方法如下(1).按同一顺序访问对象。(注避免出现循环降低了进程的并发执行能力)(2).避免事务中的用户交互。(注减少持有资源的时间减少竞争)(3).保持事务简短并处于一个批处理中。(注同(2)减少持有资源的时间)(4).使用较低的隔离级别。(注使用较低的隔离级别例如已提交读比使用较高的隔离级别例如可序列化持有共享锁的时间更短减少竞争)(5).使用基于行版本控制的隔离级别2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制可以将读与写操作之间发生的死锁几率降至最低SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT 事务隔离级别;SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项没有加with nolock提示)SELECT语句会对请求的资源加S锁(共享锁)而开启了此选项后SELECT不会对请求的资源加S锁。注意设置 READ_COMMITTED_SNAPSHOT选项时数据库中只允许存在执行 ALTER DATABASE命令的连接。在 ALTER DATABASE完成之前数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。在数据库中设置READ COMMITTED SNAPSHOT 或 ALLOW SNAPSHOT ISOLATIONON ON时查询数据时不再使用请求共享锁如果请求的行正被锁定(例如正在被更新)SQL_Server会从行版本存储区返回最早的关于该行的记录(SQL_server会在更新时将之前的行数据在tempdb库中形成一个链接列表。详细请点这里和这里ALTER Database DATABASENAME SET READ_COMMITTED_SNAPSHOT ON(6).使用绑定连接。(注绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别)并可以使用同一数据而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后调用exec sp_getbindtoken Token out;来取得Token然后传入另一个会话并执行EXEC sp_bindsession Token来进行绑定(最后的示例中演示了绑定连接)。解决死锁这里有几个方法可以帮助我们解决死锁问题。优化查询我们在写查询语句时要考虑一下查询是否Join了没有必要的表是否返回数据太多太多的列或行查询是否执行表扫描是否能通过调整查询次序来避免死锁是否应该使用Join的地方使用了Left JoinNot In语句是否考虑周到我们在写查询语句可以根据以上准则来考虑查询是否应该做出优化。慎用With(NoLock)默认情况下SELECT语句会对查询到的资源加S锁(共享锁)由于S锁与X锁(排他锁)不兼容在加上With(NoLock)后SELECT不对查询到的资源加锁(或者加Sch-S锁Sch-S锁可以与任何锁兼容)从而使得查询语句可以更好和其他语句并发执行适用于表数据更新不频繁的情况。也许有些人会提出质疑With(NoLock)可能会导致脏读首先我们要考虑查询的表是否频繁进行更新操作而且是否要读回来的数据会被修改所以衡量是否使用With(NoLock)还是要根据具体实际出发。优化索引是否有任何缺失或多余的索引是否有任何重复的索引处理死锁我们不能时刻都观察死锁的发生但我们可以通过日志来记录系统发生的死锁我们可以把系统的死锁错误写入到表中从而方便分析死锁原因。缓存也许我们正在执行许多相同的查询非常频繁如果我们把这些频繁的操作都放到Cache中执行查询的次数将减少发生死锁的机会。我们可以在数据库的临时表或表或内存或磁盘上应用Cache。1.1.3 总结本文主要介绍了什么是死锁、怎样导致了死锁和死锁的解决方法正如我们可以看到由于导致死锁的原因很多所以死锁的解决方法不尽相同首先我们必须明确死锁发生的地方例如进程为了争夺哪类资源导致死锁的这时我们可以考虑使用Profiler工具进行跟踪查询在清楚死锁发生的地方后我们要检查一下查询是否考虑周到了可以根据以上的方法优化查询语句。参考sys.sp_whoTransact-SQL - SQL Server | Microsoft Learnhttp://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/Silent Void - 博客园感兴趣的同学可以扫描下方二维码一起讨论哦
