时间：2021-07-01 10:21:17 帮助过：5人阅读
问题：
行级锁表现形式：next-key lock
错误码: 1213
Deadlock found when trying to get lock; try restarting transaction，重点在于：Deadlock FOUND WHEN trying TO get LOCK; 表示行级锁冲突
解决：
解决方案，可能该更新处于一个大表，而且，表中不断有数据插入，将会出现这样的问题，可以使用更新标识在对应的更新分组上，然后，进行多次更新数据，如果出现锁，报错，事务回滚，然后，再次进行数据更新，这个锁发生的几率不大，所以，可以使用该方法进行解决
参考博客：
innodb 行级锁 record-level lock大致有三种：record lock, gap lock and Next-KeyLocks。 record lock 锁住某一行记录 gap lock 锁住某一段范围中的记录 next key lock 是前两者效果的叠加。 下面是MYSQL官方文档中相关内容的链接 http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html 【实验环境】 session 1 20:39:29> show create table gap \G *************************** 1. row *************************** Table: gap Create Table: CREATE TABLE `gap` ( `id` int(11) DEFAULT NULL, KEY `ind_gap_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) session 1 20:39:32> select * from gap; +------+ | id | +------+ | 17 | | 20 | | 33 | | 39 | | 42 | | 43 | +------+ 6 rows in set (0.00 sec) 【实验】 两个会话都在REPEATABLE-READ 事务隔离级别。且都要在事务中进行。 session 1 20:39:37> start transaction; Query OK, 0 rows affected (0.00 sec) session 1 20:39:41> delete from gap where id=33; Query OK, 1 row affected (0.00 sec) session 20:40:07> 在会话2中 插入id <20 和 >=39的值 可以执行成功,而当要插入的id [20,39)的值时 会遇到gap lock 。 session 2 20:40:15> start transaction; Query OK, 0 rows affected (0.00 sec) session 2 20:40:30> insert into gap values(14); Query OK, 1 row affected (0.00 sec) session 2 20:40:59> insert into gap values(18); Query OK, 1 row affected (0.00 sec) session 2 20:41:06> insert into gap values(20); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction session 2 20:41:12> insert into gap values(24); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction session 2 20:42:17> session 2 20:42:53> insert into gap values(35); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction session 2 20:44:09> session 2 20:44:56> insert into gap values(39); Query OK, 1 row affected (0.00 sec) session 2 20:45:13> insert into gap values(40); Query OK, 1 row affected (0.00 sec) 从上面的实验中可以看出会话1 执行删除语句之后,不仅仅锁住 id=33的记录,同时也锁住区间为[20,39)的记录。具体的原因是执行delete from gap where id=33语句,mysql 会执行索引扫描并在该表上施加一个next-key lock ,向左扫描到20,向右扫描到39 ,锁定区间左闭右开,所以lock的范围是 [20,39)。 【gap 锁带来的问题】 生产环境中有这样的一个情况： 程序会对一个表message 进行update 和insert session 1 UPDATE message SET gmt_modified = now(),deal_times = deal_times +1 , status = ‘sending‘ , gmt_retry = ‘2012-11-17 23:54:10‘ WHERE message_id=18; insert into message (body ,user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (‘hello ！‘,-1,‘sending‘,‘instance_status_sync‘,2,127,now(),now(),now()); session 2 UPDATE message SET gmt_modified = now(),deal_times = deal_times +1 , status = ‘sending‘ , gmt_retry = ‘2012-11-17 23:54:10‘ WHERE message_id=19; insert into message (body ,user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (‘hello world！‘,-2,‘sending‘,‘instance_status_sync‘,1,17,now(),now(),now()); 对于上述程序在无并发情况下,运行正常,但是并发量大的情况下,执行顺序可能就会变成下面的： UPDATE message SET gmt_modified = now(),deal_times = deal_times +1 , status = ‘sending‘ , gmt_retry = ‘2012-11-17 23:54:10‘ WHERE message_id= 61; UPDATE message SET gmt_modified = now(),deal_times = deal_times +1 , status = ‘sending‘ , gmt_retry = ‘2012-11-17 23:54:10‘ WHERE message_id= 73; insert into message (body ,user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (‘hello world！‘,-2,‘sending‘,‘instance_status_sync‘,1,17,now(),now(),now()); insert into message (body ,user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (‘hello ！‘,-1,‘sending‘,‘instance_status_sync‘,2,127,now(),now(),now()); 此时 往往会报错 [ERROR] Could not execute Write_rows event on table db.message; Deadlock found when trying toget lock; ; try restarting transaction, Error_code: 1213; 前两条update 类型的语句都已经获得了[59,75 )区间内记录的S锁，然后两个事务又分别对该区间段内的message_id=10这个位置请求X锁，这时就发生死锁，谁都请求不到X锁，因为互相都持有S锁。 【解决方案有两种】 1、改变程序中数据库操作的逻辑 2、取消gap lock机制 Gap locking can be disabled explicitly.This occurs if you change the transaction isolation level to READ COMMITTED orenable the innodb_locks_unsafe_for_binlog system variable. 【参考】 http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html 博客地址：http://blog.itpub.net/22664653/viewspace-750824/MySQL死锁原因分析
标签：