Crazy lens

【转载】MySQL gap lock 浅析 锁
【定义】innodb 行级锁 record-level lock大致有三种:record lock, gap lo...
扫描右侧二维码阅读全文
23
2020/08

【转载】MySQL gap 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

最后修改:2020 年 08 月 23 日 07 : 18 PM
如果觉得我的文章对你有用,请随意赞赏

发表评论