【MySQL】gap lock 浅析

原创 2015年11月19日 13:16:52

【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)

insert into gap values(17);
insert into gap values(20);
insert into gap values(33);
insert into gap values(39);
insert into gap values(42);
insert into gap values(43);

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.

Gap locking 可以被显示的关掉,如果你改变事务隔离级别为READ COMMITTED 或者 启动innodb_locks_unsafe_for_binlog system variable.

相关文章推荐

MySQL的GAP LOCK(间隙锁) 的陷阱

http://fucheng.blog.51cto.com/2404495/1619359  mysql(innodb storage engine)的行锁主要是通过在相应的索引记录来实现,作为...

理解innodb的锁(record,gap,Next-Key lock)

Record lock 单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚...

间隙锁(gap lock)个人实验(一)

我们都知道间隙锁会锁掉操作表上,可能被修改的数据。那如果修改时,使用了子查询,子查询上的数据该如何上锁呢。 以下是实验的表结构与索引结构 mysql> desc otb; +----...

Android 休眠机制->wake_lock机制浅析

Android的休眠唤醒主要基于wake_lock机制,只要系统中存在任一有效的wake_lock,系统就不能进入深度休眠,但可以进行设备的浅度休眠操作。wake_lock一般在关闭lcd、tp,但系...

浅析Redis实现lock互斥访问资源

在高并发的应用中,很多时候我们需要对某些资源进行竞争访问,比如在很多人下载一个热门资源,就可能存在很多请求去修改某个资源的peer信息(就是保存了当前保种人的ip地址和端口号),需要保证某个请求修改p...
  • oanehc
  • oanehc
  • 2015年01月01日 18:00
  • 2732

代码浅析 Android Lock 、ReentrantLock线程锁及其作用

先来了解什么是“互斥锁”? 百度一下,解释如下:在编程中,引入了对象互斥锁的概念,来保证共享数据操作的完整性。每个对象都对应于一个可称为" 互斥锁" 的标记,这个标记用来保证在任一时刻,只能有一个线...

浅析MYSQL中的并发操作与锁定

  • 2014年04月17日 13:56
  • 26KB
  • 下载

MySQL innodb_lock_wait 锁等待

zhua地址:http://blog.sina.com.cn/s/blog_6bb63c9e0100s7cb.html MySQL 5.5 -- innodb_lock_wait 锁 等待 ...

mysql lock table 和 unlock table

早就听说lock tables和unlock tables这两个命令,从字面也大体知道,前者的作用是锁定表,后者的作用是解除锁定。但是具体如何用,怎么用,不太清楚。今天详细研究了下,总算搞明白了2者的...

解决:Mysql exceeds the lock table size 不指定

故障现象 view plainprint? mysql> delete FROM `tablesname` WHERE datetime ERROR 1206 (HY000): The tota...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【MySQL】gap lock 浅析
举报原因:
原因补充:

(最多只允许输入30个字)