MysqlInnodb死锁情况分析与归纳.docxVIP

Mysql Innodb死锁情况分析与归纳 TOC \o 1-5 \h \z 案 例 在定时脚本运行过程中，发现当备份表格的sql语句与删除该表部分数据的sql语句同 时运行时，mysql会检测出死锁，并打印出日志。 两 个 sql 语 句 如 下： ( 1 ) in sert into backup_table select * from source_table (2 ) DELETE FROM source_table WHERE ld5 AND titleWeight32768 AND joinTime$daysago_lweek, teamUser 表 的 表 结 构 如 下： PRIMARY KEY (uid,ld), KEY k_id_titleWeight_score (Id/titleWeight/score), TOC \o 1-5 \h \z ENGINE=lnnoDB 两语句对source_table表的使用情况如下： 死锁日志打印出的时间点表明，语句（1）运行过程中，当语句（2）开始运行时，发 生 了 死 锁。 当mysql检测出死锁时，除了查看mysql的Fl志，还可以通过show InnoDB STATUS \G语 句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱，所以，最好 先使用pager less命令，通过文件内容浏览方式查看结果，会更清晰。（以nopager结束） 得到的死锁记录如下： 根据死锁记录的结果，可以看出确实是这两个语句发生了死锁，且锁冲突发生在主键索 引上。那么，为什么两个sql语句会存在锁冲突呢？冲突为什么会在主键索引上呢？语句 （2）得到了主键索引锁，为什么还会再次申请锁呢？ 锁冲突分析 in nodb 的事务与行锁机制 MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关，MylSAM不 支持事务、采用的是表级锁，而InnoDB支持ACID事务、行级锁、并发。MySQL默认的行 为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句作为一个单独 的 事 务 来 处 理。 两语句加锁情况 在innodb默认的事务隔离级别下，普通的SELECT是不需要加行锁的，但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外，此案例中，语句 (1) insert into teamUserselect * from teamUser 会对表 teamllser(ENGINE= MylSAM)加表锁，并对teamUser表所有行的主键索引(即聚簇索引)加共享 锁。 默 认 对 英 使 用 主 键 索 引。 而语句(2) DELETE FROM teamUser WHERE teamld=$teamld AND titleWeight32768 AND joinTime$daysago_lweek为删除操作，会对选中行的主键索引加排他锁。由于此语句还使 用了 非聚簇索引 KEY k_teamid_titleWeight_score ( teamld/titleWeight/score)W前缀索引, 于是，还会对相关行的此非聚簇索引力口排他锁。 2.3锁冲突的产生 由于共享锁与排他锁是互斥的，当一方拥有了某行记录的排他锁后，另一方就不能其拥 有共亨锁，同样，一方拥有了其共亨锁后，另一方也无法得到其排他锁。所 以，当语句 (1)、(2)同时运行时，相当于两个事务会同时申请某相同记录行的锁资源，于是会产生 锁冲突。由于两个事务都会申请主键索引，锁冲突只会发生 在主键索引上。 常常看到一句话：在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的。那就说 明，单个SQL组成的事务锁是一次获得的。而此案例中，语句(2)已经得到了主键索引 的排他锁，为什么还会申请主键索引的排他锁呢？同理，语句(2)已经获得了主键索引的 共享锁，为彳|?么还会申请主键索引的共享锁呢？ 死锁记录中，事务一等待锁的page no与事务二持有锁的page no相同，均为218436, 这 又 彳弋 表 什 么 呢？ 我们的猜想是，innodb存储引擎中获得行锁是逐行获得的，并不是一次获得的。下面来 证明。 死锁产生过程分析 要想知道innodb加锁的过程，唯一的方式就是运行mysql的debug版本，从gdb的输 出中找到结果。根据gdb的结果得到，单个SQL组成的事务，从宏观上来看，锁是在这个 语句上一次获得的，但从底层实现上来看, 该 语句上一次获得的，但从底层实现上来看, 该 行 记 录 的 是逐个记录行查询，得到符合条件的记录即对 索 引 加 锁 Gdb