mysql- 排它锁之行锁、间隙锁、后码锁
START TRANSACTION;update x set id = 1 where id = 1;
-- 事务 B-- 如果事务 A 没有 commit，id=1 的记录拿不到 X 锁，将出现等待 START TRANSACTION;update x set id = 1 where id = 1;
-- 事务 C-- id=2 的记录可以拿到 X 锁，不会出现等待 START TRANSACTION;update x set id = 2 where id = 2;
针对 InnoDB RR 隔离级别，上述 SQL 示例展示了行锁的特点：“锁定特定行不允许进行修改”，但行锁是基于表索引的，如果 where 条件中用的是 num 字段（非索引列）将产生不一样的现象：
-- 事务 ASTART TRANSACTION;update x set num = 1 where num = 1;
-- 事务 B-- 由于事务 A 中 num 字段上没有索引将产生表锁，导致整张表的写操作都会出现等待 START TRANSACTION;update x set num = 1 where num = 1;
-- 事务 C-- 同理，会出现等待 START TRANSACTION;update x set num = 2 where num = 2;
-- 事务 D-- 等待 START TRANSACTION;insert into x values(3, 3);
Gap 锁(Gap Lock)
在 MySQL 中 select 称为快照读，不需要锁，而 insert、update、delete、selec
t for update 则称为当前读，需要给数据加锁，幻读中的“读”即是针对当前读。
RR 事务隔离级别允许存在幻读，但 InnoDB RR 级别却通过 Gap 锁避免了幻读
产生间隙锁的条件（RR 事务隔离级别下）
使用普通索引锁定
使用多列唯一索引
使用唯一索引锁定多行记录
唯一索引的间隙锁
测试环境
MySQL，InnoDB，默认的隔离级别（RR）
数据表
CREATE TABLE test (id int(1) NOT NULL AUTO_INCREMENT,name varchar(8) DEFAULT NULL,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据
INSERT INTO test VALUES ('1', '小罗');INSERT INTO test VALUES ('5', '小黄');INSERT INTO test VALUES ('7', '小明');INSERT INTO test VALUES ('11', '小红');
以上数据，会生成隐藏间隙
(-infinity, 1](1, 5](5, 7](7, 11](11, +infinity]
只使用记录锁，不会产生间隙锁
/* 开启事务 1 /BEGIN;/ 查询 id = 5 的数据并加记录锁 /SELECT * FROM test WHERE id = 5 FOR UPDATE;/ 延迟 30 秒执行，防止锁释放 */SELECT SLEEP(30);
-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务 2 插入一条 name = '小张' 的数据 */INSERT INTO test (id, name) VALUES (4, '小张'); # 正常执行
/* 事务 3 插入一条 name = '小张' 的数据 */INSERT INTO test (id, name) VALUES (8, '小东'); # 正常执行
/* 提交事务 1，释放事务 1 的锁 */COMMIT;
以上，由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。
产生间隙锁
/* 开启事务 1 /BEGIN;/ 查询 id 在 7 - 11 范围的数据并加记录锁 /SELECT * FROM test WHERE id BETWEEN 5 AND 7 FOR UPDATE;/ 延迟 30 秒执行，防止锁释放 */SELECT SLEEP(30);
-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务 2 插入一条 id = 3，name = '小张 1' 的数据 */INSERT INTO test (id, name) VALUES (3, '小张 1'); # 正常执行
/* 事务 3 插入一条 id = 4，name = '小白' 的数据 */INSERT INTO test (id, name) VALUES (4, '小白'); # 正常执行
/* 事务 4 插入一条 id = 6，name = '小东' 的数据 */INSERT INTO test (id, name) VALUES (6, '小东'); # 阻塞
/* 事务 5 插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO test (id, name) VALUES (8, '大罗'); # 阻塞
/* 事务 6 插入一条 id = 9， name = '大东' 的数据 */INSERT INTO test (id, name) VALUES (9, '大东'); # 阻塞
/* 事务 7 插入一条 id = 11， name = '李西' 的数据 */INSERT INTO test (id, name) VALUES (11, '李西'); # 阻塞
/* 事务 8 插入一条 id = 12， name = '张三' 的数据 */INSERT INTO test (id, name) VALUES (12, '张三'); # 正常执行
/* 提交事务 1，释放事务 1 的锁 */COMMIT;
从上面我们可以看到，(5, 7]、(7, 11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以当我们给 (5, 7] 这个区间加锁的时候，会锁住 (5, 7]、(7, 11] 这两个区间。
锁住不存在的数据
/* 开启事务 1 /BEGIN;/ 查询 id = 3 这一条不存在的数据并加记录锁 /SELECT * FROM test WHERE id = 3 FOR UPDATE;/ 延迟 30 秒执行，防止锁释放 */SELECT SLEEP(30);
-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务 2 插入一条 id = 3，name = '小张 1' 的数据 */INSERT INTO test (id, name) VALUES (2, '小张 1'); # 阻塞
/* 事务 3 插入一条 id = 4，name = '小白' 的数据 */INSERT INTO test (id, name) VALUES (4, '小白'); # 阻塞
/* 事务 4 插入一条 id = 6，name = '小东' 的数据 */INSERT INTO test (id, name) VALUES (6, '小东'); # 正常执行
/* 事务 5 插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO test (id, name) VALUES (8, '大罗'); # 正常执行
/* 提交事务 1，释放事务 1 的锁 */COMMIT;
我们可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁
结论
对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE?
id?= 5 FOR UPDATE;对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE?
id?BETWEEN 5 AND 7 FOR UPDATE;
普通索引的间隙锁
数据准备
创建 test1 表：
注意：number 不是唯一值
CREATE TABLE test1 (id int(1) NOT NULL AUTO_INCREMENT,number int(1) NOT NULL COMMENT '数字',PRIMARY KEY (id),KEY number (number) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
id 是主键，number 上建立了一个普通索引。先加一些数据：
INSERT INTO test1 VALUES (1, 1);INSERT INTO test1 VALUES (5, 3);INSERT INTO test1 VALUES (7, 8);INSERT INTO test1 VALUES (11, 12);
test1 表中 number 索引存在的隐藏间隙：
(-infinity, 1](1, 3](3, 8](8, 12](12, +infinity]
执行以下的事务（事务 1 最后提交）
/* 开启事务 1 /BEGIN;/ 查询 number = 5 的数据并加记录锁 /SELECT * FROM test1 WHERE number = 3 FOR UPDATE;/ 延迟 30 秒执行，防止锁释放 */SELECT SLEEP(30);
-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务 2 插入一条 number = 0 的数据 */INSERT INTO test1 (number) VALUES (0); -- 正常执行
/* 事务 3 插入一条 number = 1 的数据 */INSERT INTO test1 (number) VALUES (1); -- 被阻塞
/* 事务 4 插入一条 number = 2 的数据 */INSERT INTO test1 (number) VALUES (2); -- 被阻塞
/* 事务 5 插入一条 number = 4 的数据 */INSERT INTO test1 (number) VALUES (4); -- 被阻塞
/* 事务 6 插入一条 number = 8 的数据 */INSERT INTO test1 (number) VALUES (8); -- 正常执行
/* 事务 7 插入一条 number = 9 的数据 */INSERT INTO test1 (number) VALUES (9); -- 正常执行
/* 事务 8 插入一条 number = 10 的数据 */
评论