死锁3 insert select 造成的死锁(转)

一、死锁的背景

近日在协助同事优化某应用前置程序（简称Y前置）以提高其处理效率的过程中，我们碰到一个奇怪的现象：Y应用主机（简称Y主机）远程对Y前置Sybase数据库表做插入操作时出现死锁，日志显示死锁交易的比例高达当日交易总量的10%，可是被数据库自动回滚的事务处理中只有一条简单的嵌入式插入E-SQL语句。

便于大家了解背景，简要描述与死锁相关的处理流程如下：

（一）Y前置通过MQ统一接入合作方的交易请求，将其插入本地Sybase数据库的交易请求表；

（二）Y主机远程从Y前置交易请求表依次读取请求，将其转发给各一级分行前置系统；

（三）Y主机接收各一级分行前置系统应答之后，将交易处理结果远程写入Y前置Sybase数据库的交易应答表；

（四）Y前置循环读取交易应答表，通过MQ将处理结果逐一返给合作方。

日志显示上述过程中的第3步发生了Y主机事务死锁，我们通过sp_help确认了交易应答表的锁模式为行锁，也就是说对行锁表做单笔插入操作的事务竟有高发性死锁问题。

二、INSERT怎么会死锁

Sybase有三大类型锁：排它锁（X锁）、共享锁（S锁）和更新锁（U锁），三种锁的相容矩阵表如下所示(×表示不兼容,∨表示兼容)：

可申请锁 已有锁	S	U	X
S	∨	∨	×
U	∨	×	×
X	×	×	×

 

Y主机插入操作出现死锁，既说明插入操作占用了其他事务需要的资源，也说明插入操作没有申请到其他事务未释放的资源，可是问题是对于行锁模式的表而言，插入E-SQL除了要申请X行锁(Ex_row)外，它还要申请什么资源呢？

 

 

（一）初步推断

由于生产环境下数据库没有激活“print deadlock information”的设置(激活对系统性能影响较大)，我们不知道死锁事务相关的其他E-SQL语句是什么，但从上述过程可以推断：与第3步死锁冲突的环节应该是第4步，因为只有这两步的处理过程涉及到了发生死锁的交易应答表；其次是在我们优化Y前置应用之前，Y主机日志中从未提示过死锁。

在开始排查之前，我们先回顾一下对Y前置应用所做的优化内容：

首先是优化E-SQL。我们在该应用生产环境下通过人工执行set showplan on和set noexec on，对Y前置程序中的E-SQL逐一排查，凡是执行计划显示为表扫描的，根据业务情况或是补充E-SQL语句的查询条件、降低查询的执行强度，或是根据查询条件增建索引。优化E-SQL引起死锁的可能性不大，所以我们首先排除了它的可能。

其次是优化游标的处理逻辑，改动的地方很多，它的嫌疑最大。我们在优化代码的时候，发现Y前置各处理程序的过程基本一致：

1.声明带WHERE条件的可修改游标(SELECT FOR UPDATE)

2.开始循环

3.打开游标

4.FETCH游标

5.修改数据(WHERE条件按交易流水号定位，未用游标位置)

6.提交修改事务(COMMIT)

7.结束循环

8.释放游标

由于缺乏文档和注释，我们猜测重复打开游标的原因是每次修改后确保能及时提交，但是提交事务后游标自动关闭了，那么只好在每次循环处理开始时重新打开它；我们看到循环前面的代码还有被注释掉的打开游标操作，因此判定原作者的确想打开一次游标，只是没能找到提交事务同时保持游标打开状态的办法。

在优化过程中，我们发现每当客户反映交易长时间没有应答时，Y前置日志文件显示每次打开游标时平均有6秒的等待，由于该应用采用的是按交易提交顺序的联机批量式处理方式，那么这样的效率意味着排在第100笔的请求仅在打开游标一项上就要有10分钟的等待，这样的效率合作方自然无法接受。

使用Sybase ASE数据库时，提交事务同时保持游标打开状态，需要在事务开始前将close on endtran选项关闭，若是12.0之后的ASE还要将事务类型设置为非链式(chainned off)。

鉴于此，我们对Y前置的事务处理进行了改进：首先设置有关保持游标打开的选项；然后定义可修改游标(select for update)；打开游标之后开始循环处理：将应答信息组织成报文通过MQ发给合作方，若发送成功则开始事务，通过游标标记当前记录为已处理，再提交事务；最后在循环结束后关闭并释放游标。

假如说Y前置优化的程序导致了Y主机事务出现死锁，我们判断故障诱因就位于上述内容之中。

（二）尝试游标只读

难道是游标可修改的缘故？可修改游标意味着每次FETCH游标时会申请U锁，我们猜U锁和插入申请的X锁可能会出现死锁，只读游标FETCH时申请的S锁或许就不会冲突。

恢复系统正常运行的压力迫使我们没有仔细推敲，直接就把可修改游标变为了只读，但是更换程序之后，发现Y主机不仅仍出现死锁，死锁占比也丝毫没有下降。

（三）降低隔离级别

既然只读游标也解决不了问题，我们还剩下一个终极办法：将Y前置负责处理交易应答的程序事务隔离级别由默认的3级(串行化读)降为1级(读已提交)，一方面这样可使E-SQL语句执行完之后可以尽快释放包括锁在内的各种临界资源，另一方面仔细审查有关源代码之后，我们认为1级读已提交的隔离级别对Y前置应答处理过程的完整性没有实质影响。

假如降为1级隔离之后，Y主机仍出现死锁，那么可以排除掉Y前置引起Y主机死锁的可能性；如果降级能让Y主机的死锁故障消失，那么我们就赢得了时间来进一步查找引发死锁的根源。

当时我意识到，如果Y前置导致了Y主机出现死锁，那么Y主机必定也会导致Y前置出现死锁，或许查看Y前置的运行日志能给我们提供一些帮助，以弥补我们因没有生产环境下的sa权限无法获取死锁详细信息的不足。

事实证实了我们的假设，Y前置日志清楚地表明，自该应用上线以来游标事务发生死锁的提示信息连绵不绝，之前未发现在于Y前置事务被自动回滚时，只是未能从数据库中取出应答数据而已，下次操作会自动重新取，从使用者角度根本发现不了故障。

目前Y主机也出现了死锁，直接导致应答数据无法写入Y前置的数据库，由于该应用的设计没有考虑此类异常的自动恢复，应答数据丢失自然会产生单边账务，隐藏已久的问题终于得到了暴露。

鉴于此，我们对Y前置代码进行了迅速更换，首先合并原来分散于前置不同程序内返回合作方交易结果的代码，以降低前置进程间不必要的内部资源争用；然后在合并后的代码中将事务隔离级别设置为1级(读已提交)，以避免占用过多的临界资源。

经过数日监控，死锁故障终于消失，而且交易高峰期间处理异常缓慢的问题一并得到了解决。

（四）追根溯源

问题虽然得到了解决，但我们并未找到原因，实际上查找死锁原因最简单的办法是在生产系统上激活死锁信息输出选项，这样发生死锁时Sybase的系统日志会清楚地表明死锁相关SQL语句的执行情况，可是我们作为开发人员没有激活的sa权限，加之生产系统已恢复正常，再这样做已经没有意义。

模拟测试也是一种解决问题的途径，我在该应用的开发机兼生产备机上，依据流程编写了并发测试代码，同时激活了测试数据库的死锁输出选项，可是结果令人沮丧，测试程序跑了几天也没有出现过一个死锁，从重现的角度查找原因的办法看来行不通。

既然自己实现不了，或许网上有高人的心得体会。的确，网上可以找到很多Sybase插入与查询间出现死锁的帖子，但多是描述全页锁模式下进行插入与查询并发操作时，申请数据页锁与索引页锁操作之间发生死锁的情况。

为表述清楚，我们将Sybase数据库各种锁模式之间锁操作的区别列表如下：

锁模式	索引锁方式	数据锁方式
All Pages	页锁	页锁
DataPages	不锁	页锁
DataRows	不锁	行锁

可以看出，DataRows模式下Sybase不会锁索引页，Y前置的数据库为DataRows模式，因而网上有关全页锁模式出现死锁的帖子所解释的原因显然并不适合我们碰到的问题。不过最终我们还是在网上找到了一篇与情况类似的案例描述，该案例贴出了Sybase发生死锁时的详细日志，这正是我当初苦苦测试寻求而未得的结果。

该案例描述的日志表明：在3级隔离级别的事务中，Sybase对于包含索引列的行锁模式表做插入时，会先申请数据页X行锁，然后申请下一个依主键序的键锁(next key lock)，这个next key锁正是造成此类死锁的诱因，也是第二节中INSERT申请什么资源的答案。

Sybase网站的有关资料表明，在3级隔离级别状态下，使用索引条件读取数据的游标事务中，为防止出现幻读，打开游标时会申请满足条件范围之后下一个依主键序的键锁(next key lock)，以阻止其他事务在条件范围后面追加记录；另外在3级隔离下，无论游标是否只读，FETCH操作要申请的数据页行锁都是范围扩张锁，每次FETCH其申请到的行锁不会立即释放，而是随着后续FETCH逐步蔓延，直到操作到下一个数据页或关闭游标时，Sybase才会一并释放之前占有的数据页行锁。

这样当FETCH导致范围扩张锁蔓延时，恰好与插入操作事务先申请数据页X锁、再申请next key锁的次序相冲突，满足了死锁的必要条件。

三、结束语

通过探寻这次Sybase INSERT事务死锁的始末，我充分体验到解决此类问题的困难。

一方面是技术上的困难。出现问题时仅凭数据库和应用程序运行日志难以判断故障的根本原因，测试环境难以重现故障现象、分析源代码时，往往发现不仅缺乏有效文档、合理注释，同时连原创人员都早已不知去向。

另一方面是协调上的困难。开发与运维上的分离导致开发人员缺乏快速有效定位系统运行故障的有效手段，仅仅依靠运行日志解决不了深层次问题，很多操作都需要有系统权限，比如数据库要有sa权限才能开启死锁信息输出选项和查看执行计划，系统要有root权限才能跟踪进程和监测有关资源，这些显然是开发人员所不具备的权限，而让运维人员熟悉所有应用也并不现实，如何既能保障生产的安全运行、又能方便应急处理是我们应该思考的问题。