最近做性能优化时碰到下面的死锁问题,起初看到这个图感觉很诡异,无法理解,两个查询语句为啥会导致死锁呢?查询语句为啥会导致X(排他)锁的产生呢?最后在查阅了一部分资料之后,终于可以解释通了。
1. 首先,此图中两个Key Lock分别代表数据库表上的两个行各自的行锁,是两个锁,而不是一个锁。图中的两个事务执行的是相同的代码,在事务中,首先做了一个查询,最后对查询的数据进行了更新。因此,就可以理解图中X(排他)锁是因为事务中的更新语句导致的,而不是查询语句导致的(虽然右键两个事务显示的都是相同的查询语句)。
所以,此图的意思是事务762385持有下面方框对应的行X(排他)锁,正在申请上面方框对应的行S(共享)锁。而事务762460持有上面方框对应的行X(排他)锁,正在申请下面方框对应的行S(共享)锁。而X锁和S锁是不能同时获得的,因此就发生了等待死锁。
2. 再来看看查询语句为啥会导致申请S(共享)锁失败,查询语句的执行计划如下:可以看到查询语句使用主键生成的Clustered索引进行了全表扫描,因此它会导致与更新无关的行在扫描的过程当中也需要申请S(共享锁)。
3. 如何只申请需要更新行的S(共享)锁呢?因为我们的查询语句对应的的两个查询条件正好对应有一个索引,所以只需要修改查询语句,使得查询计划使用这个存在的索引,这样就可以达到目的了。但是这样会引入部分需要的字段无法在同一个查询语句中查出来。解决办法是,先通过这个修改的查询语句找到该行的主键,然后再通过主键找到整行,因为主键也不是进行全表扫描,只会申请更新行的S(共享)锁。执行计划如下:
4. 还有一种办法是将事务分为两个小的事务,一部分做查询,一部分做更新。这样就不会出现同一个事务既需要X(排他)锁,又需要S(共享)锁的情况,也可以避免死锁的发生。或者将此部分的逻辑进行顺序化,也可以避免死锁。
参考文档:
1. SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [closed]
此文遇到了类似的问题,但是无解。问题描述相对比较准确。
2. Locking in Microsoft SQL Server (Part 3 – Blocking in the system)
此文详细描述了持有X(排他)锁,申请S(共享锁)导致死锁的情况,和我们遇到的问题一致。
3. SQL Server上的一个奇怪的Deadlock及其分析方法
此文描述了类似的死锁问题,对于原因讲述的比较详尽,操作方法执行比较困难。因为如果打出所有的SQL Trace,会产生数百万条的记录,如何在这些记录当中找出死锁对应的两个事务对应的SQL Trace还是相当有困难的。可以考虑将生成的SQL Trace导入到一张数据库表中,在导入的过程当中每隔5分钟删除所有我们不需要的事务对应的SQL Trace。这样最终得到的SQL Trace会减少到不到一万行。
相关推荐
SQL Server上的一个奇怪的Deadlock及其分析方法
SQL SERVER 的阻塞和死锁,讲解的比较详细,可以参考看看
Key range locks are similar to row locks on index keys (whether clustered or not). The locks are placed on individual keys rather than at the node level. The hash value consists of all the key ...
阵列服务器集群中的死锁问题比较讨厌;而其与性能之间的PK是个人类无奈的话题。需找一些相应的适中机制比较好玩。
操作系统教学课件:ch07-Deadlock-1pp.pdf
With SQL Deadlock Detector, you can: Monitor and detect long-running locks and deadlocks 24/7 Identify blocking SQL code, locked objects and deadlock victims with pinpoint accuracy Accelerate system ...
With this hands-on guide, you'll gain a solid understanding of T-SQL and good programming practices through the right balance of conceptual and practical content. Learn the steps required to develop ...
Deadlock-Free Adaptive Routing in Meshes with Fault-Tolerance Ability Based on Channel Overlapping
4. Planning for and Installing SQL Server [加入我的離線書架] . SQL Server Editions . Hardware Guidelines . Hardware Components . The Operating System . The File System . Security and the User ...
一提到跟踪俩字,很多人想到警匪片中的场景,但这里介绍的可不是一样的哦,下面这篇文章主要给大家介绍了关于sql server deadlock跟踪的4种实现方法,文中通过图文以及示例代码介绍的非常详细,需要的朋友可以参考下
SQL Server死锁总结/Deadlock
实现了不死锁的哲学家问题(进程同步互斥),有简单界面,有图。
sqlserver 解除死锁,网络上看到的很实用分享给大一起用
could run into a deadlock with its peers, preventing autoheal from completing. GitHub issue: rabbitmq-server#1346 Garbage collection of mirrored queue metrics on nodes that did not host a master or ...
pt-deadlock-logger pt-index-usage pt-show-grants pt-table-sync pt-diskstats pt-ioprofile pt-sift pt-table-usage pt-duplicate-key-checker pt-kill pt-slave-delay pt-upgrade pt-fifo-split pt-mext pt-...
A Deadlock-Freeand Connectivity-Guaranteed Methodology for Achieving Fault-tolerance inOn-chip Networks
A General Methodology to Design Deadlock-Free Routing Algorithms for Mesh Networks
1 背景 1.1 报警情况 最近整理笔记,打算全部迁移到EVERNOTE...<deadlock victim=process810b00cf8> <process id=process810b00cf8 taskpriority=0 logused=0 waitresource=RID: 13:1:1541136:62 waittime=7682 own