mysql锁机制
共享锁,排他锁,意向锁
innodb多种锁类型的目的是为了降低锁的粒度,利用X,S,IS,IX四种类型锁实现了行级锁,相比表级锁,粒度已经大大减小
innodb的意向锁很简单,就是为了在行锁加上X或S时防止全表扫描,例如事务A读id=1的记录,在id=1这一行加上S锁的同时,给表也加了IS锁,如果有事务B更新需要给表加X锁,那么此时会冲突,事务B只能等待,这样就不需要扫描全表看是否有锁冲突,只需要判断意向锁就够了。
一直性非锁定读
即mvcc,之前的文章已经说过,不再研究
一致性锁定读
显示的声明锁,以达到数据一致性的目的,例如
select ... for update
select ... lock in share mode
如果查询同一条记录,同一事务加了写锁也时可以读的。select … 加锁时为当前读,即读数据库最新的值,而不受mvcc影响
例子
隔离级别为RR时
begin;
select * from articles where id=1 ;(1)开启事务
修改数据
select * from articles where id=1 for update;(结果为修改后)
select * from articles where id=1 lock in share MODE;(结果为修改后)
select * from articles where id=1 ;(结果为事务开始时的时的数据)
commit;
InnoDB 锁算法
记录锁(Record-Lock)
记录锁是锁住记录的,锁住的是索引记录,而不是我们真正的数据记录:
如果锁的是非主键索引,会在自己的索引上面加锁之后然后再去主键上面加锁锁住。
如果表上没有索引(包括没有主键),则会使用隐藏的主键索引进行加锁(RC情况下是这样的,RR下面由于间隙锁的原因,在更新没有索引的情况下,会锁全表记录)。
如果要锁的没有索引,则会进行全表记录加锁。
间隙锁
首先要明确,间隙锁是为了防止幻读而产生的锁。在RR下才生效,在RC下锁算法就是记录锁,而在RR情况间隙锁会生效,也因此RR的并发性能会比RC下低很多,虽然RR也不能完全避免幻读,因为直接select并不上锁
间隙锁的作用:
比如事务A update table ···· where id >100
那么加上这个锁,不仅id大于100的行记录上锁,还能保证id>100区间在 A未提交之前不能插入新的数据。
间隙锁是封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。
产生间隙锁的条件(RR事务隔离级别下;):
使用普通索引锁定;
使用多列唯一索引;
使用唯一索引锁定多行记录。
以上情况,都会产生间隙锁,
对于使用唯一索引来搜索并给某一行记录加锁的语句,不会产生间隙锁。(这不包括搜索条件仅包括多列唯一索引的一些列的情况;在这种情况下,会产生间隙锁。)例如,如果id列具有唯一索引,则下面的语句仅对具有id值100的行使用记录锁,并不会产生间隙锁:
SELECT * FROM child WHERE id = 100 FOR UPDATE;
这条语句,就只会产生记录锁,不会产生间隙锁。
唯一索引的间隙锁
测试环境:
环境: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’, ‘小红’);
在进行测试之前,我们先来看看test表中存在的隐藏间隙:
(-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 的数据加上记录锁,而不会产生间隙锁。
产生间隙锁
我们继续在 id 唯一索引列上做以下的测试:
/* 开启事务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 这两个字段,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 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行
/* 提交事务1 */
COMMIT;
我们会发现有些语句可以正常执行,有些语句被阻塞了。我们再来看看我们表中的数据:
执行之后的数据
这里可以看到,number (1 - 8) 的间隙中,插入语句都被阻塞了,而不在这个范围内的语句,正常执行,这就是因为有间隙锁的原因。我们再进行以下的测试,方便我们更好的理解间隙锁的区域(我们要将数据还原成原来的那样):
/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行,防止锁释放 */
SELECT SLEEP(30);
/* 事务1插入一条 id = 2, number = 1 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (2, 1); # 阻塞
/* 事务2插入一条 id = 3, number = 2 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (3, 2); # 阻塞
/* 事务3插入一条 id = 6, number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (6, 8); # 阻塞
/* 事务4插入一条 id = 8, number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (8, 8); # 正常执行
/* 事务5插入一条 id = 9, number = 9 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (9, 9); # 正常执行
/* 事务6插入一条 id = 10, number = 12 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (10, 12); # 正常执行
/* 事务7修改 id = 11, number = 12 的数据 */
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞
/* 提交事务1 */
COMMIT;
我们来看看结果:
执行后的数据
这里有一个奇怪的现象:
- 事务3添加 id = 6,number = 8 的数据,给阻塞了;
- 事务4添加 id = 8,number = 8 的数据,正常执行了。
- 事务7将 id = 11,number = 12 的数据修改为 id = 11, number = 5的操作,给阻塞了;
这是为什么呢?我们来看看下边的图,大家就明白了。
隐藏的间隙锁图
从图中可以看出,当 number 相同时,会根据主键 id 来排序,所以:
- 事务3添加的 id = 6,number = 8,这条数据是在 (3, 8) 的区间里边,所以会被阻塞;
- 事务4添加的 id = 8,number = 8,这条数据则是在(8, 12)区间里边,所以不会被阻塞;
- 事务7的修改语句相当于在 (3, 8) 的区间里边插入一条数据,所以也被阻塞了。
结论
- 在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁,这跟唯一索引不一样;
- 在普通索引跟唯一索引中,数据间隙的分析,数据行是优先根据普通索引排序,再根据唯一索引排序。
临键锁(Next-key Locks)
临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。
注:临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。
要点
- 记录锁、间隙锁、临键锁,都属于排它锁;
- 记录锁就是锁住一行记录;
- 间隙锁只有在事务隔离级别 RR 中才会产生;
- 唯一索引只有锁住多条记录或者一条不存在的记录的时候,才会产生间隙锁,指定给某条存在的记录加锁的时候,只会加记录锁,不会产生间隙锁;
- 普通索引不管是锁住单条,还是多条记录,都会产生间隙锁;
- 间隙锁会封锁该条记录相邻两个键之间的空白区域,防止其它事务在这个区域内插入、修改、删除数据,这是为了防止出现 幻读 现象;
- 普通索引的间隙,优先以普通索引排序,然后再根据主键索引排序(多普通索引情况还未研究);
- 事务级别是RC(读已提交)级别的话,间隙锁将会失效。
那么我们说RR级别下的幻读是怎么产生的呢
Mysql官方给出的幻读解释是:只要在一个事务中,第二次select多出了row就算幻读。
a事务先select,b事务insert确实会加一个gap锁,但是如果b事务commit,这个gap锁就会释放(释放后a事务可以随意dml操作),a事务再select出来的结果在MVCC下还和第一次select一样,接着a事务不加条件地update,这个update会作用在所有行上(包括b事务新加的),a事务再次select就会出现b事务中的新行,并且这个新行已经被update修改了,实测在RR级别下确实如此。
如果a select时上锁,那么就会自然加上gap锁,那么就可以避免幻读
插入意向锁
(1)插入意向锁是一种Gap锁,不是意向锁,在insert操作时产生。
(2)在多事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。
(3)假设有一个记录索引包含键值4和7,,在没用间隙锁,行锁,等其他锁的情况下,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。
(4)插入意向锁不会阻止任何锁,但是会被其他锁阻止,例如间隙锁,对于插入的记录会持有一个记录锁。
插入意向锁实际上就是一种间隙锁,不过这种间隙锁不会阻塞其他锁,是对间隙锁针对插入的优化。