[mysql] next-key lock实践.

首先前提是 我们用的是可重复读级别(rr).  读提交级别不需要间隙锁.那怎么解决之前说的binlog的问题呢?需要改binlog为row级别.

本文过长,因为例子太多

1.创建数据库

先来一些准备工作

mysql> CREATE DATABASE twenty_one;

Query OK, 1 row affected (0.00 sec)

2.创建表以及数据

######选择数据库

mysql> use twenty_one;

Database changed

######建表语句

mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

######初始化数据

mysql> insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

Query OK, 6 rows affected (0.01 sec)

Records: 6 Duplicates: 0 Warnings: 0

 

 

我们重置和初始化语句,后面会经常用.

delete from t ;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

 

加锁规则再拷贝一遍.

1.next-lock 的加锁范围是前开后闭区间,(0,4]这样.

2.查找过程中访问到的对象才加锁.!!!

优化:

1.如果是唯一索引等值查询.那么退化为行锁.

2.如果是普通索引等值查询,那么向右遍历,且最后一个值不满足等值条件时,next-key lock退化为间隙锁.

bug

1.唯一索引上的范围查询会访问到不满足条件的第一个值为止.

3.普通索引-等值查询

 

打开两个命令窗口

3.1 执行一个事务,并更新一条语句,id=7

mysql> begin;

mysql> update t set d=d+1 where id=7;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

 

3.2 插入(8,8,8)

mysql> insert into t values(8,8,8);

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

3.3 更新

mysql> update t set d=d+1 where id=10;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

结论:由于3.1中id=7,这条数据没有,因此会使用间隙锁,将(5,10]区间锁住,谁也插入不了.

但是.间隙锁锁住的是插入操作.因此不会导致更新失败.

 

4.普通索引-覆盖索引

4.1

mysql> begin;

mysql> select id from t where c=5 lock in share mode;

+----+

| id |

+----+

| 5 |

+----+

 

加的什么锁?

(0,5]的next-key lock锁,同时对c=5加了行锁,由于是普通索引,因此向右继续扫描,扫描到10] ,是不符合条件,因此(5,10)的间隙锁.

4.2

mysql> update t set d=d+1 where id =5;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

成功:因为4.1使用了覆盖索引,不需要回表,因此主键索引不会被锁.这里成功.

lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

4.3

mysql> insert into t values(7,7,7);

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

这里由于加了间隙锁,因此7,肯定插入不成功,那如果我插入的是7,10,7呢?

 

4.4

mysql> insert into t values(7,10,7);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t values(7,11,7);

Query OK, 1 row affected (0.00 sec)

 

这里为什么插入10就不行,插入11就行呢????不是c是索引,根据优化2.间隙锁是(5,10),吗

 

 

这个例子说明，锁是加在索引上的.

 

5.主键索引范围查询的锁

 

 

mysql> select * from t where id=10 for update;

mysql> select * from t where id>=10 and id<11 for update;

这俩的差别是啥?

 

5.1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from t where id>=10 and id<11 for update;

+----+------+------+

| id | c | d |

+----+------+------+

| 10 | 10 | 10 |

+----+------+------+

1 row in set (0.00 sec)

 

先查找id=10,由于是主键,(5,10]的next-key lock退化为10的行锁;

范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。

5.2

mysql> insert into t values(8,8,8);

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t values(13,13,13);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

5.3

mysql> update t set d=d+1 where id =15;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

6.普通索引的范围锁

 

6.1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from t where c>=10 and c<11 for update;

+----+------+------+

| id | c | d |

+----+------+------+

| 10 | 10 | 10 |

+----+------+------+

1 row in set (0.01 sec)

 

(5,10] (10,15]锁

6.2

mysql> update t set d=d+1 where c=10; a

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t values(8,8,8); b

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update t set d=d+1 where c=15; c

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update t set d=d+1 where id=10; d

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

锁的有点多.

a: 因为加的锁是(5,10],(10,15]. 更新c=10肯定不行

b: 由于间隙锁,插入肯定是阻塞的

c: 更新c=15,记得第一个案例,索引等值查询,向右遍历到第一个不符合要求的记录时,会退化为间隙锁.不会锁那个索引项. 但是这里,不是等值查询,所以将右边也锁住了.这个就是行锁了.所以阻塞了

d: 根据主键去更新.那么问题来了,根据规则2. 扫描到的对象会加锁,我们的select由于查询了所有,所以回表的时候,把对应的主键索引项也都加上了对应的锁.!!!!

 

 

 

7.唯一索引范围锁 bug

7.1

mysql> begin;

mysql> select * from t where id>10 and id<=15 for update;

+----+------+------+

| id | c | d |

+----+------+------+

| 15 | 15 | 15 |

+----+------+------+

1 row in set (0.01 sec)

 

7.2

mysql> update t set d=d+1 where id =20;

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

为啥说是bug呢?因为按道理锁住 (10,15]就行了,但实际上锁住了(10,15],(15,20].此bug已经这个 bug 在 MySQL 8.0.18 版本已经修复了。

 

 

8.普通索引上存在"等值"的

 

 

 

重要概念:

林晓斌老师讲的很好,但是从讲到间隙锁和next lock,就一直在懵逼,文中一直在说加 next-key lock,(5,10] 一会又说退化为间隙锁.(5,10).

一直感觉老师说的 next-key lock,是一个间隙锁+一个行锁. 直到文章结尾,才说出了 next-key加锁的过程,就是先加间隙锁,再加行锁.

 

从一个例子体会下

第一个

 

SessionA	SessionB	Session C
begin select * from t where c=10 lock in share mode;   (5,10)的间隙锁,c=10的行锁(读)
	begin select * from t where c=10 lock in share mode;   (5,10)的间隙锁,c=10的行锁(读锁)
		update t set d=d+1 where c=10; (block) (5,10)的间隙锁,c=10的写锁
insert into t values(8,8,8);   完蛋,被C的间隙锁锁住了!!!

 

实际验证:

 

SessionA

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where c=10 lock in share mode;

+----+------+------+

| id | c | d |

+----+------+------+

| 10 | 10 | 10 |

| 30 | 10 | 30 |

+----+------+------+

2 rows in set (0.00 sec)

 

mysql> insert into t values(8,8,8);

Query OK, 1 row affected (0.01 sec)

 

SessionB

mysql> update t set d=d+1 where c=10; //当sessionA执行insert后,这里里面报死锁的错误

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

9.非索引字段等值查询

以上都是有索引字段加的锁,那么如果在非索引字段上加锁呢?

1.新建事务

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where d=12 for update;

Empty set (0.00 sec)

 

2.另起一个窗口

mysql> update t set d=1 where id=1;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

 

mysql> update t set d=1 where id=10;

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

可以看到

更新id=10时,被阻塞.

 

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是 通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

这里是否正确??

测试1

窗口1:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t set d=d+1 where d=5;

 

窗口2:

mysql> show OPEN TABLES where In_use > 0;

Empty set (0.00 sec)

mysql> select * from t;

+----+------+------+

| id | c | d |

+----+------+------+

| 0 | 0 | 0 |

| 5 | 5 | 5 |

| 8 | 8 | 8 |

| 10 | 10 | 10 |

| 15 | 15 | 15 |

| 20 | 20 | 20 |

| 25 | 25 | 25 |

| 30 | 10 | 30 |

+----+------+------+

 

继续

mysql> select * from t lock in share mode;

(block)

 

如果是加表锁了,为什么查不到?还有表锁的释放时机是什么?

希望大神可以指导哇!

 

测试2:

窗口1

mysql> begin;

mysql> select * from t where d= 1 for update;

Empty set (0.00 sec)

 

窗口2

mysql> select * from t where d=2 for update;

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我觉得也可能是 对每一行都加锁了,而且对每个间隙都加锁了呢? 但是问题来了,之前说锁是加载索引上的,如果是行锁+间隙锁,那么行锁是加在哪里呢?主键索引?

 

 

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的

11.使用相同的索引,也会锁

 

当前数据,其中id是主键 c是普通索引 d是非索引字段

mysql> select * from t;

+----+------+------+

| id | c | d |

+----+------+------+

| 0 | 0 | 0 |

| 5 | 5 | 10 |

| 6 | 5 | 6 |

| 10 | 10 | 10 |

| 15 | 15 | 15 |

| 20 | 20 | 20 |

| 25 | 25 | 25 |

+----+------+------+

 

窗口1

mysql> select * from t where c=5 and d=6 for update;

+----+------+------+

| id | c | d |

+----+------+------+

| 6 | 5 | 6 |

+----+------+------+

1 row in set (0.00 sec)

 

窗口2

mysql> select * from t where c=5 and d=7 for update;

^@ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

可见,再次证明索引时锁住的索引项,而非具体数据项.因为这俩语句都使用了c=5这个索引项,所以会阻塞