Mysql一cookbook8 - Mysql一深入理解Mysql中的锁 - 《Mysql》

1. INNODB 默认情况下的加锁情况
2. 什么是快照读？什么是当前读？
- 快照读（snapshot read）
- 当前读（current read）
3. MySql InnoDB 中的三种行锁（记录锁、间隙锁、临键锁）

1. INNODB 默认情况下的加锁情况

在默认情况下（不去修改事务的隔离级别为 serializable），所有的 select … …. from 这样的SQL语句都是快照读，读取的是数据库的一个快照。是不会加任何锁的 serializable下，凡是通过beginl;start transaction 这样的语句开启了事务，就算是普通的select 语句，也会被隐式转换成 SELECT … LOCK IN SHARE MODE，所以还是加上了共享锁而针对于写的操作，update、insert into、delete 这样的SQL，会自动加上排他锁（行级别的），为这些行加上了行级别的排他锁，所以也是避免了数据的不安全性。

2. 什么是快照读？什么是当前读？

快照读（snapshot read）

语句：
- 简单的select语句（不包括）SELECT … LOCK IN SHARE MODE 和 SELECT …FOR UPDATE
实现：
- 基于MVVC 和 UNDO LOG 来实现
- 而MySQL中 MVVC的实现又是依靠 ReadView（事务视图）来实现的。多个ReadView组成回滚日志。每个普通的 select … from 查询的都是最新的 ReadView 的该条数据的值。
ReadView：ReadView是针对同一条数据生成的视图
- 读已提交：
  - 事务中每个sql语句执行都会生成一个ReadView，所以一个事务中的多条sql语句也就生成了多个ReadView，而每条sql执行时，都会查询最新的那个ReadView的值。
  - 事务A有2个sql语句，第一个查询的sql生成了一个 id = n 的 ReadView。如果事务B对该数据进行了操作，那么就会生成 id = n+1 的 readview ，下一次查询就会选择 id = n+1 的 readview。
- 可重复读：
  - 在事务开始的时候就会生成一个 readview ，所以同一个事务内的sql语句查询同一条数据时，每次读取到的都是同一个 readview，也就是这样保证了数据的一致性。
  - 举个栗子说明：
    - 当前事务A读取id=n的某一条记录，无论其他事务对其这条数据做任何修改，事务A未提交前，他读取的永远是没做任何修改的那条记录。
      当前读（current read）
语句：
- select … lock in share mode
- select … for update
- insert
- update
- delete
实现：
- 是基于临键锁（行锁 + 间歇锁）来实现的。
- 更新数据时，都是先读后写，而这个读就是当前读。说白了，当前读就是携带锁的读，目的就是为了防止读数据时，其他事务对数据进行了修改，就是为了保证数据的安全性。

3. MySql InnoDB 中的三种行锁（记录锁、间隙锁、临键锁）

参考行锁在InnoDB中通过索引实现，所以一旦某个加锁的操作没有使用索引，那么就会由行锁升级成为表锁。

记录锁（Record Locks）

就是给某行的记录加锁，它是基于一行或者说是表中的一条记录，那么就一定需要依赖主键或者唯一索引，来进行精准匹配，如：

SELECT * FROM `STUDENT` WHERE `ID` = 1 FOR UPDATE;

注意ID这个字段必须为主键或者是唯一索引，否则加的锁就会变成临键锁。而且查询语句必须为精准匹配（=），像是>、<、like等也会退化成临键锁。
我们都知道 INNODB 在写的情况下会自动加上排他锁来保证数据的安全性。为了确保这个锁是仅仅针对于某一行记录的锁，我们通常这样来写

-- id 是主键或者唯一索引
UPDATE SET AGE = 50 WHERE ID = 1;

间隙锁（Gap Locks）

间隙锁基于非唯一索引，它锁定的是一段范围性的索引记录。间隙锁基于 Next-Key Locking 算法，使用间隙锁锁住的是一个区间，而不仅仅是这个区间，而不仅仅是这个区间中的每一条数据。如：

SELECT * FROM table WHERE ID BETWEEN 1 AND 10 FOR UPDATE;

所有在（1， 10）区间内的记录行都会被锁住。除了像是这样手动加上一个间隙锁。在执行完某些 SQL 后，InnoDB 也会自动加间隙锁

临键锁（Next-Key Locks）

临键锁可以理解为一种特殊的间隙锁，它是一种特殊的算法。通过临键锁可以解决幻读的问题。没个数据行上的非唯一索引列上都会存在一把临键锁。当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。
也就是(a, b]. InnoDB中行级锁是基于索引实现的，临建锁只与非唯一索引列有关，所以在唯一索引列上（包括主键）不存在临建锁

-- 开启一个cmd
mysql> use cook;
Database changed

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from person;
+----+-----+-------+
| id | age | name  |
+----+-----+-------+
|  1 |  10 | test1 |
|  2 |  24 | test2 |
|  3 |  32 | test3 |
|  4 |  45 | test4 |
+----+-----+-------+

-- id 是主键,age 和name是普通字段
-- 现在分析age可能存在的临键锁区间
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

-- 现在开启事务A
mysql> begin;

mysql> update person set name = 'xs' where age = 24;
Query OK, 1 row affected (0.00 sec)

-- 然后开启事务B
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into person (id, age, name) values(5, 11, 'test5');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 我们可以看到，事务B的这条sql被阻塞了。
-- 因为事务A在执行上述sql 后会获得一个(10, 32) 的临届主键。

-- 哪怕age的范围也不行，因为... ...
insert into person (id, age, name) VALUES (10, 44, 'fly');

小结

InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

Mysql一深入理解Mysql中的锁