MySQL Innodb表导致死锁日志情况分析与归纳【MySQL】

数据库|mysql教程 bitsCN.com

案例描述
在定时脚本运行过程中，发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时，mysql会检测出死锁，并打印出日志。
两个sql语句如下：
（1）insert into backup_table select * from source_table
（2）DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'
teamUser表的表结构如下：
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
两语句对source_table表的使用情况如下：

死锁日志打印出的时间点表明，语句（1）运行过程中，当语句（2）开始运行时，发生了死锁。
当mysql检测出死锁时，除了查看mysql的日志，还可以通过show InnoDB STATUS /G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱，所以，最好先使用pager less命令，通过文件内容浏览方式查看结果，会更清晰。（以nopager结束）
得到的死锁记录如下：

根据死锁记录的结果，可以看出确实是这两个语句发生了死锁，且锁冲突发生在主键索引上。那么，为什么两个sql语句会存在锁冲突呢？冲突为什么会在主键索引上呢？语句（2）得到了主键索引锁，为什么还会再次申请锁呢？
锁冲突分析
2.1 innodb的事务与行锁机制
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关，MyISAM不支持事务、采用的是表级锁，而InnoDB支持ACID事务、 行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句作为一个单独的事务来处理。
2.2 两语句加锁情况
在innodb默认的事务隔离级别下，普通的SELECT是不需要加行锁的，但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外，此案例中，语句（1）insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121（ENGINE= MyISAM）加表锁，并对teamUser表所有行的主键索引（即聚簇索引）加共享锁。默认对其使用主键索引。
而语句（2）DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'为删除操作，会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引，于是，还会对相关行的此非聚簇索引加排他锁。
2.3 锁冲突的产生
由于共享锁与排他锁是互斥的，当一方拥有了某行记录的排他锁后，另一方就不能其拥有共享锁，同样，一方拥有了其共享锁后，另一方也无法得到其排他锁。所 以，当语句（1）、（2）同时运行时，相当于两个事务会同时申请某相同记录行的锁资源，于是会产生锁冲突。由于两个事务都会申请主键索引，锁冲突只会发生 在主键索引上。
常常看到一句话：在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的。那就说明，单个SQL组成的事务锁是一次获得的。而此案例中，语句（2） 已经得到了主键索引的排他锁，为什么还会申请主键索引的排他锁呢？同理，语句（1）已经获得了主键索引的共享锁，为什么还会申请主键索引的共享锁呢？
死锁记录中，事务一等待锁的page no与事务二持有锁的page no相同，均为218436，这又代表什么呢？
我们的猜想是，innodb存储引擎中获得行锁是逐行获得的，并不是一次获得的。下面来证明。
死锁产生过程分析
要想知道innodb加锁的过程，唯一的方式就是运行mysql的debug版本，从gdb的输出中找到结果。根据gdb的结果得到，单个SQL组成的事 务，从宏观上来看，锁是在这个语句上一次获得的，但从底层实现上来看，是逐个记录行查询，得到符合条件的记录即对该行记录的索引加锁。
Gdb结果演示如下：

(gdb) b lock_rec_lock
 Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.
 (gdb) c
 Continuing.
 [Switching to Thread 1168550240 (LWP 5540)]
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789/200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 Current language: auto; currently c
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “/200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789/200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing. （说明：”789/200″为非聚簇索引，”/200″为主键索引）

Gdb结果显示，语句（1）（2）加锁的获取记录为多行，即逐行获得锁，这样就解释了语句（2）获得了主键索引锁还再次申请主键索引锁的情况。
由于语句（1）使用了主键索引，而语句（2）使用了非聚簇索引，两个事务获得记录行的顺序不同，而加锁的过程是边查边加、逐行获得，于是，就会出现如下情况：

于是，两个事务分别拥有部分锁并等待被对方持有的锁，出现这种资源循环等待的情况，即死锁。此案例中被检测时候的锁冲突就发现在page no为218436和218103的锁上。
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚，此次事务（1）解锁并回滚，语句（2）继续运行直至事务结束。
innodb死锁形式归纳
死锁产生的四要素：互斥条件：一个资源每次只能被一个进程使用；请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；不剥夺条件:进程 已获得的资源，在末使用完之前，不能强行剥夺；循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
Innodb检测死锁有两种情况，一种是满足循环等待条件，还有另一种策略：锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度 时，innodb也会判断为死锁（这是提高性能方面的考虑，避免事务一次占用太多的资源）。这里，我们只考虑满足死锁四要素的情况。
死锁的形式是多样的，但分析到innodb加锁情况的最底层，因循环等待条件而产生的死锁只有可能是四种形式：两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式，再以事例的方式解释这四种死锁情况。
4.1聚簇索引与非聚簇索引介绍
聚簇索引即主键索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序，聚簇索引的索引页面指针指向数据页面。非聚簇索引（即第二主键索 引）不重新组织表中的数据，索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述，那么，聚簇索引的叶节点就是数据节点，而非聚簇 索引的叶节点仍然是索引节点，通常是一个指针指向对应的数据块。
而innodb在非聚簇索引叶子节点包含了主键值作为指针。（这样是为了减少在移动行或数据分页时索引的维护工作。）其结构图如下：

当使用非聚簇索引时，会根据得到的主键值遍历聚簇索引，得到相应的记录。
4.2四种死锁情况
在InnoDB中，使用行锁机制，于是，锁通常是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。
即将分享的四种死锁的锁冲突分别是：不同表的相同记录行索引锁冲突、主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级造成锁队列阻塞。
不同表的相同记录行锁冲突
案例：两个表、两行记录，交叉获得和申请互斥锁

条件：
A、 两事务分别操作两个表、相同表的同一行记录
B、 申请的锁互斥
C、 申请的顺序不一致

主键索引锁冲突
案例：本文案例，产生冲突在主键索引锁上
条件：
A、 两sql语句即两事务操作同一个表、使用不同索引
B、 申请的锁互斥
C、 操作多行记录
D、 查找到记录的顺序不一致

主键索引锁与非聚簇索引锁冲突
案例：同一行记录，两事务使用不同的索引进行更新操作

此案例涉及TSK_TASK表，该表相关字段及索引如下：
ID：主键；
MON_TIME：监测时间；
STATUS_ID：任务状态；
索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

条件：
A、 两事务使用不同索引
B、 申请的锁互斥
C、 操作同一行记录

当执行update、delete操作时，会修改表中的数据信息。由于innodb存储引擎中索引的数据存储结构，会根据修改语句使用的索引以及修改信息 的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时，会首先加使用的索引锁，然后，如果修改了主键信息，会加主键索引锁和所有非聚簇索引锁，修改 了非聚簇索引列值会加该种非聚簇索引锁。
此案例中，事务一使用非聚簇索引查找并修改主键值，事务二使用主键索引查找并修改主键值，加锁顺序不同，导致同时运行时产生资源循环等待。
锁升级造成锁队列阻塞
案例：同一行记录，事务内进行锁升级，与另一等待锁发送锁队列阻塞，导致死锁

条件：
A、 两事务操作同一行记录
B、 一事务对某一记录先申请共享锁，再升级为排他锁
C、 另一事务在过程中申请这一记录的排他锁

避免死锁的方法
InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险，这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说，建议的避免死锁的方法如下：
1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定
2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。
5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。

bitsCN.com