前言:
Gap Locks译称间隙锁,本文参考官方文档进行学习说明外加实验例子
数据库版本:
SELECT VERSION();
±-----------+
| version() |
±-----------+
| 5.6.34-log |
±-----------+
数据库引擎:
show variables like ‘%engine%’;
±---------------------------±-------+
| Variable_name | Value |
±---------------------------±-------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
±---------------------------±-------+
根据版本信息参考官方文档:
1.MySQL 5.6 Reference Manual-InnoDB Gap Locks(间隙锁)
间隙锁
间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record
可以关闭间隙锁功能,改变事物隔离级别为RC级别或开启innodb_locks_unsafe_for_binlog参数。
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated).
测试间隙锁:
1.创建表:
CREATE TABLE `test_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
PRIMARY KEY (`user_id`),
KEY `index_user` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
2.插入数据:
INSERT INTO `test_user` VALUES (1,'a');
INSERT INTO `test_user` VALUES (3,'c');
3.测试开始:
事务1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_user where name='c' for update;
+---------+------+
| user_id | name |
+---------+------+
| 3 | c |
+---------+------+
1 row in set (0.00 sec)
事务2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_user(user_id,name) values(2,'b');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
结果:事务2在间隙区间内执行insert会阻塞超时。
个人总结:
1.为什么要使用间隙锁? 避免幻读现象,在同一个事务中执行select … for update执行多次,如果没有间隙锁,其他事务执行insert一条数据到查询范围内,则会出现幻读,这违反了RR级别的承诺。