MySQL加锁实践

    学习了林晓斌老师的《mysql实战45讲》后获益匪浅，将自己的一些学习心得以及动手实践以笔记的形式记录下来。

间隙锁（Gap Lock）

    首先谈谈间隙锁是什么。

    间隙锁是专门用于解决幻读这种问题的锁，它锁的了行与行之间的间隙，能够阻塞新插入的操作，同时间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。

    这里特别记录下读读不互斥，读写/写读/写写是互斥的，但是间隙锁之间是不冲突的，间隙锁会阻塞插入操作，另外，间隙锁在可重复读级别下才是有效的。

加锁的规则

    mysql加锁总结下来包括以下方面包括两个原则、两个优化、一个bug：

原则1:加锁的基本单位是next-key lock 。next-key lock 是前开后闭区间,next-key lock是行锁和间隙锁的组合。

原则2:查找过程中访问到的对象才会加锁--对于未命中索引的查询要走全表扫描，这种在扫描前就给全表加上了next-key lock。

优化1:索引上的等值查询，给唯一索引上加锁的时候，next-key lock会退化为行锁，是匹配上的时候，如果没有匹配上，自然也就不会退化为行锁了，此时会用到优化2，从而退化成间隙锁。

优化2:索引上的等值查询，像右遍历时且最后一个值不满足等值条件的时候，next-key lock会退化为间隙锁

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。（唯一索引范围锁的bug在最新的8.0.18已经修复）

    在后面的自己实践过程中牢记mysql加锁的规则才能更好的进行理解。

动手实践

准备

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>

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.00 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t;

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

| id | c    | d    |

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

|  0 |    0 |    0 |

|  5 |    5 |    5 |

| 10 |  10 |  10 |

| 15 |  15 |  15 |

| 20 |  20 |  20 |

| 25 |  25 |  25 |

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

6 rows in set (0.00 sec)

等值查询间隙锁

session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

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

session2

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

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

session3

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

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

分析:表t中无id=7的记录

1、根据next-key lock原则，左开右闭session1加锁范围（5，10];

2、索引上的等值查询，像右遍历时且最后一个值id=10时不满足等值条件的时候，next-key lock会退化为间隙锁，因此加锁范围为（5，10）

结论:session 2 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session 3 修改 id=10 这行是可以的。

非唯一索引等值锁

session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

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

+----+

| id |

+----+

|  5 |

+----+

1 row in set (0.00 sec)

session2

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

session3

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

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

分析：session 1 要给索引 c上c=5 的这一行加上读锁。

1、首先根据next-key lock 原则，左开右闭 给c索引为（0，5] 加锁。

2、因为c为普通索引（非唯一索引），索引上的等值查询，像右遍历时且最后一个值不满足c=10的等值条件的时候，next-key lock会退化为间隙锁，此时访问到的对象都加锁，此时给c为（5，10] 都要加next-key lock 锁

3、根据优化2，向右遍历最后一个不满足的值时，退化为间隙锁，（5，10）；注意前面分析的（0，5]间隙锁还是存在的，此时加锁的范围为c索引上（0，5] 和（5，10）的间隙锁。

4、根据原则2，访问到的对象（这里指c=5索引这一列）才会加锁。这个查询使用的是覆盖索引，因此并不需要访问主键索引，所以session2可以执行成功，这样也验证了访问到的对象才会加锁（访问到的对象才会加锁，这个“对象”指的是c列，不是 记录行。 补充一下： 加锁，是加在索引上的。 列上，有索引，就加在索引上； 列上，没有索引，就加在主键上）

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

    数据行加读锁，如果查询字段使用了覆盖索引，访问到的对象只有普通索引，并没有访问到主键索引，则不会锁主键索引。如果没有使用覆盖索引，则会回表查询，访问到主键索引，这样主键索引也会加锁。

主键索引范围锁

session1

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

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

| id | c    | d    |

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

| 10 |  10 |  11 |

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

1 row in set (0.00 sec)

session2

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

(blocked)

session3

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

(blocked)

分析：

1、开始执行 访问id=10 加锁范围id索引上 （5，10] next-key lock 由于id唯一索引退化为行锁 id=10

2、范围查询向后查找 第一个不满足条件的值id=15 加锁范围（10，15]

结论：session2、session3 阻塞

非唯一索引范围锁

session1

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 |  11 |

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

1 row in set (0.00 sec)

session2

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

(blocked)

session3

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

(blocked)

分析：对c字段加锁（非唯一索引）

在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

唯一索引范围锁 bug

session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

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

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

| id | c    | d    |

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

| 13 |  13 |  13 |

| 15 |  15 |  16 |

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

2 rows in set (0.00 sec)

session2

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

(blocked)

session3

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

(blocked)

分析：

1、session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

2、但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

所以你看到了，session B 要更新 id=20 这一行，是会被锁住的。同样地，session C 要插入 id=16 的一行，也会被锁住。

照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15，就可以确定不用往后再找了。但实现上还是这么做了。

非唯一索引上存在"等值"的例子

插入一行数据

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

session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from t where c=10;

Query OK, 2 rows affected (0.00 sec)

session2

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

(blocked)

session3

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

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

分析：

1、session1 在遍历的时候首先访问第一个c=10的记录 ，对(c=5,id=5) 到 (c=10,id=10)左开右闭 区域记录加 next-key lock。

2、session1 向右查询第二个c=10的记录，找到(c=10,id=30)记录之后继续向右查找直到找到 (c=15,id=15) 这一行，根据等值查询优化，找到第一个不等值的记录时退化为(c=10,id=30) 到 (c=15,id=15) 左开右开间隙锁， (c=15,id=15) 记录 不包括区域记录加 next-key lock。

加锁区域

limit 语句加锁

同样插入一行数据

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

session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from t where c=10;

Query OK, 2 rows affected (0.00 sec)

session2

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

Query OK, 1 rows affected (0.00 sec)

分析：

1、session 1 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session 2 的 insert 语句执行通过了。

2、delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间

    在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

一个死锁的例子

session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

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

session2

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

(blocked)

session1

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

Query OK, 1 rows affected (0.00 sec)

session2

ERROR 1213(40001)Deadlock found when trying to get lock;try restart trasaction

分析：

1、session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；

2、session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；

3、然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

    你可能会问，session B 的 next-key lock 不是还没申请成功吗？其实是这样的，session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

  总结下来，可重复读隔离级别遵守两阶段协议，所有的锁都在事务提交或者回滚才释放；read-commited 没有gap lock ，read-commited 语句执行完就释放“不满足条件的行”的行锁，而不是在事务结束的时候才释放。总结来说，读提交隔离级别下，锁的范围更小，锁的时间更短。