【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)的行锁主要是通过在相应的索引记录来实现,作为...
  • bigtree_3721
  • bigtree_3721
  • 2017年06月26日 14:36
  • 1574

GAP LOCK(间隙锁)

innodb间隙锁就是不仅仅锁住所需要的行(如果锁住的这行不存在)还会锁住一个范围的行,这个范围依据锁住的这行而定。上下刚好是两个相邻索引叶节点的范围。包含下范围,不包含上范围。    在数据库参数...
  • u014558484
  • u014558484
  • 2016年06月28日 21:57
  • 877

MYSQL INNODB中GAP LOCK引起死锁的问题

先了解一下什么是GAP LOCK 在INNODB中,record-level lock大致有三种:Record, Gap, and Next-KeyLocks。简单的说,RECORDLOCK就是锁住...
  • wh62592855
  • wh62592855
  • 2011年12月13日 15:27
  • 8308

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

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

【MySQL】死锁问题分析

1、MySQL常用存储引擎的锁机制:    MyISAM和MEMORY采用表级锁(table-level locking)    BDB采用页面锁(page-level locking)或表级锁,...
  • lapiggy
  • lapiggy
  • 2011年08月24日 16:54
  • 14036

[MySQL][Gap Lock][Next-Key Lock]浅析

MySQL GapLock NextKeyLock
  • lslxdx
  • lslxdx
  • 2017年02月24日 17:42
  • 668

关于mysql Gaplock以及监控锁的相关示例

innodb引擎中,mysql中的行级别的锁大致有三种,recordlock、Gap、Next-KeyLocks。recordlock就是锁住某一行记录;而Gap会锁住某一段范围中的记录;Next-K...
  • yangbutao
  • yangbutao
  • 2013年05月22日 10:20
  • 5309

MySQL锁阻塞分析

日常维护中,经常会碰到线程被阻塞,导致数据库响应非常慢,下面就看看如何获取是哪个线程导致了阻塞的。 1. 环境说明 RHEL 6.4 x86_64 + MySQL 5.6.19 事务隔离级别:RR ...
  • HW_LiBo
  • HW_LiBo
  • 2014年09月05日 15:10
  • 25498

推荐 mysql insert 加锁流程

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key...
  • bigtree_3721
  • bigtree_3721
  • 2017年08月08日 17:49
  • 483

MySQL InnoDB中唯一索引和非唯一索引时的加锁情况

MySQL InnoDB中唯一索引和非唯一索引时的加锁情况 MySQL的默认隔离级别为RR,这篇文章讨论一下唯一索引和非唯一索引时,使用lock read(consistent read不加锁,...
  • qingsong3333
  • qingsong3333
  • 2017年09月18日 23:00
  • 445
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【MySQL】gap lock 浅析
举报原因:
原因补充:

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