mysql导致表死锁的情况_MySQL Innodb表导致死锁日志情况分析与归纳

案例描述

在定时脚本运行过程中，发现当备份表格的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

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