mysqlinnodb死锁情况分析与归纳

《mysqlinnodb死锁情况分析与归纳》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、MysqlInnodb死锁情况分析与归纳案例在定时脚本运行过程中，发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时，mysql会检测出死锁，并打印出日志。两个sql语句如下：(1)insertintobackup_tableselect*fromsource_table(2)DELETEFROMsource_tableWHEREld>5ANDtitleWeight<32768ANDjoinTime<'$daysago_lweek,teamUser表的表结构如下：PRIMARYKEY('uid','ld'),KEY'k_id_titleWeight_scor

2、e'('Id'/titleWeight'/score'),ENGINE=lnnoDB两语句对source_table表的使用情况如下：死锁日志打印出的时间点表明，语句（1）运行过程中，当语句（2）开始运行时，发生了死锁。当mysql检测出死锁时，除了查看mysql的Fl志，还可以通过showInnoDBSTATUSG语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱，所以，最好先使用pagerless命令，通过文件内容浏览方式查看结果，会更清晰。（以nopager结束）得到的死锁记录如下：根据死锁记录的结果，可以看出确实是这两个语句发生了死锁，且锁冲

3、突发生在主键索引上。那么，为什么两个sql语句会存在锁冲突呢？冲突为什么会在主键索引上呢？语句（2）得到了主键索引锁，为什么还会再次申请锁呢？锁冲突分析2.1innodb的事务与行锁机制MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关，MylSAM不支持事务、采用的是表级锁，而InnoDB支持ACID事务、行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句作为一个单独的事务来处理。2.2两语句加锁情况在innodb默认的事务隔离级别下，普通的SELECT是不需要加行锁的，但LOCKINSHAREMO

4、DE、FORUPDATE及高串行化级别中的SELECT都要加锁。有一个例外，此案例中，语句(1)insertintoteamUser_20110121select*fromteamUser会对表teamllser_20110121(ENGINE=MylSAM)加表锁，并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对英使用主键索引。而语句(2)DELETEFROMteamUserWHEREteamld=$teamldANDtitleWeight<32768ANDjoinTime<'$daysago_lweek'为删除操作，会对选中行的主键索引加排他锁。由于

5、此语句还使用了非聚簇索引KEYk_teamid_titleWeight_score'(teamld'/titleWeight'/score')W前缀索引,于是，还会对相关行的此非聚簇索引力口排他锁。2.3锁冲突的产生由于共享锁与排他锁是互斥的，当一方拥有了某行记录的排他锁后，另一方就不能其拥有共亨锁，同样，一方拥有了其共亨锁后，另一方也无法得到其排他锁。所以，当语句(1)、(2)同时运行时，相当于两个事务会同时申请某相同记录行的锁资源，于是会产生锁冲突。由于两个事务都会申请主键索引，锁冲突只会发生在主键索引上。常常看到一句话：在InnoDB中，除单个SQL组成的事务外，锁是

6、逐步获得的。那就说明，单个SQL组成的事务锁是一次获得的。而此案例中，语句(2)已经得到了主键索引的排他锁，为什么还会申请主键索引的排他锁呢？同理，语句(2)已经获得了主键索引的共享锁，为彳

7、•么还会申请主键索引的共享锁呢？死锁记录中，事务一等待锁的pageno与事务二持有锁的pageno相同，均为218436,这又彳弋表什么呢？我们的猜想是，innodb存储引擎中获得行锁是逐行获得的，并不是一次获得的。下面来证明。死锁产生过程分析要想知道innodb加锁的过程，唯一的方式就是运行mysql的debug版本，从gdb的输出中找到结果。根据gdb的结果得到，单个SQL组成的事

8、务，从宏观上来看，锁是在这个语句上一次获得的，但从底层实现上来看,该行记录的是逐个记录行查询，得到符合条件的记录即对索引加锁Gdb结果演示如下•■(gdb)block_rec」ockUBreakpoint1at0x867120:filelock/lockOlock.c,line2070.U(gdb)cUContinuing.U[SwitchingtoThread1168550240(LWP5540)]UBreakpointU1,lock_rec_lock(impl=0,mode=5,rec=0x2aedbe01cl