mysql innodb间隙锁示例

innodb的记录锁介绍原文见 http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

innodb的记录锁有三种类型:

  • 记录锁:是加在索引记录上的。
  • 间隙锁:对索引记录间的范围加锁,或者加在最后一个索引记录的前面或者后面
  • Next-key锁:记录锁和间隙锁的组合,间隙锁锁定记录锁之前的范围

间隙锁主要是防止幻象读,用在Repeated-Read(简称RR)隔离级别下。在Read-Commited(简称RC)下,一般没有间隙锁(有外键情况下例外,此处不考虑)。间隙锁还用于statement based replication

间隙锁有些副作用,如果要关闭,一是将会话隔离级别改到RC下,或者开启 innodb_locks_unsafe_for_binlog(默认是OFF)。

间隙锁只会出现在辅助索引上,唯一索引和主键索引是没有间隙锁。间隙锁(无论是S还是X)只会阻塞insert操作。

下面演示一种因为间隙锁而出现等待的情形。


准备脚本

CREATE TABLE `xdual` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_x` (`x`),
  KEY `idx_v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;

root@localhost : test 09:39:47> select * from xdual;
+----+---------------------+------+
| id | x                   | v    |
+----+---------------------+------+
|  2 | 2012-04-19 20:25:40 |    1 |
|  4 | 2012-04-18 00:53:58 |    3 |
|  6 | 2012-04-18 00:54:00 |    5 |
|  8 | 2012-04-18 18:23:16 |    7 |
| 10 | 2012-04-18 00:54:03 |    2 |
| 12 | 2012-04-18 02:26:13 |    4 |
| 14 | 2012-04-18 00:54:06 |    6 |
| 15 | 2012-04-18 02:26:13 |    4 |
| 16 | 2012-04-18 18:24:14 |    7 |
| 18 | 2012-04-18 00:54:10 |    8 |
| 22 | 2012-04-18 15:12:08 |   18 |
| 26 | 2012-04-18 18:23:16 |    7 |
| 34 | 2012-04-18 02:30:09 |    4 |
+----+---------------------+------+
13 rows in set (0.03 sec)


测试场景

#sess1

root@localhost : test 09:45:40> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 09:46:14> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 09:46:22> delete from xdual where v=8;
Query OK, 1 row affected (0.01 sec)

root@localhost : test 09:46:50>

#sess2

root@localhost : test 09:40:20> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 09:46:30> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 09:46:33> insert into xdual values(11,now(),7);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 09:47:08> insert into xdual values(31,now(),7);
(BLOCKING)

此时用innotop查看锁分布

_________________________________________ InnoDB Locks __________________________________________
ID        Type    Waiting  Wait   Active  Mode  DB    Table  Index    Ins Intent  Special       
24066093  RECORD        1  01:11   01:22  X     test  xdual  idx_v             1  gap before rec
24066093  TABLE         0  01:11   01:22  IX    test  xdual                    0                
24066093  RECORD        1  01:11   01:22  X     test  xdual  idx_v             1  gap before rec
24066090  TABLE         0  00:00   01:40  IX    test  xdual                    0                
24066090  RECORD        0  00:00   01:40  X     test  xdual  idx_v             0                
24066090  RECORD        0  00:00   01:40  X     test  xdual  PRIMARY           0  rec but not gap
24066090  RECORD        0  00:00   01:40  X     test  xdual  idx_v             0  gap before rec
Press any key to continue

很快会话2就timeout

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 09:49:20>


分析:

#sess1: delete from xdual where v=8;
这个sql锁定的范围是 (7,18)。此时,#sess2如果想插入一笔v=8的数据,肯定被blocking,但是插入一笔v=7的数据,就要看插入记录的位置是否在这个区间(7,18)以内。
root@localhost : test 10:06:35> select * from xdual where v=7;
+----+---------------------+------+
| id | x                   | v    |
+----+---------------------+------+
|  8 | 2012-04-18 18:23:16 |    7 |
| 16 | 2012-04-18 18:24:14 |    7 |
| 26 | 2012-04-18 18:23:16 |    7 |
+----+---------------------+------+
3 rows in set (0.00 sec)

insert into xdual values(11,now(),7); 要插入的位置在 id=16和id=26之间,不在上面那个区间内,所以不被blocking
insert into xdual values(31,now(),7); 这个就在被锁定的区间内,所以被阻塞。
同理,#sess2 下面的sql也会被阻塞

root@localhost : test 10:06:40> insert into xdual(x,v) values(now(),9);
(BLOCKING)

root@localhost : test 10:06:40> insert into xdual(x,v) values(now(),9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 10:10:50> insert into xdual(id,x,v) values(20,now(),18);
(BLOCKING)

root@localhost : test 10:10:50> insert into xdual(id,x,v) values(20,now(),18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 10:14:35>


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9253450/viewspace-722028/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/9253450/viewspace-722028/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值