MySQL 锁的相关知识总结

MySQL中的锁

锁是为了解决并发环境下资源竞争的手段，其中乐观并发控制，悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段（具体可见我之前的文章），而MySQL中的锁就是其中的悲观并发控制。

MySQL中的锁有很多种类，我们可以按照下面方式来进行分类。

按读写

从数据库的读写的角度来分，数据库的锁可以分为分为以下几种：

独占锁：又称排它锁、X锁、写锁。X锁不能和其他锁兼容，只要有事务对数据上加了任何锁，其他事务就不能对这些数据再放置X了，同时某个事务放置了X锁之后，其他事务就不能再加其他任何锁了，只有获取排他锁的事务是可以对数据进行读取和修改。
共享锁：又称读锁、S锁。S锁与S锁兼容，可以同时放置。
更新锁：又称U锁。它允许再加S锁，但不允许其他事务再施加U锁或X锁，当被读取的数据要被更新时，则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务，并同时确保事务A自从上次读取数据后数据没有被更改，因此可以减少X锁和S锁的冲突，同时避免使用S锁后再升级为X锁造成的死锁现象。注意，MySQL并不支持U锁，SQLServer才支持U锁。

兼容性矩阵如下(+ 代表兼容， -代表不兼容)

右侧是已加的锁	X	S	U
X	-	-	-
S	-	+	+
U	-	+	-

按粒度

MySQL支持不同级别的锁，其锁定的数据的范围也不同，也即我们常说的锁的粒度。MySQL有三种锁级别：行级锁、页级锁、表级锁。不同的存储引擎支持不同的锁粒度，例如MyISAM和MEMORY存储引擎采用的是表级锁，页级锁仅被BDB存储引擎支持，InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。

特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。数据库引擎总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁从而避免死锁。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行锁总是逐步获得的，因此会出现死锁。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

下面详细介绍行锁和表锁，页锁由于使用得较少就不介绍了。

行锁

按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引上，通过非聚簇索引查询的时候，先锁非聚簇索引，然后再锁聚簇索引。如果一个where语句里面既有聚簇索引，又有二级索引，则会先锁聚簇索引，再锁二级索引。由于是分步加锁的，因此可能会有死锁发生。

MySQL的行锁对S、X锁上做了一些更精确的细分，使得行锁的粒度更细小，可以减少冲突，这就是被称为“precise mode”的兼容矩阵。（该矩阵没有出现在官方文档上，是有人通过Mysql lock0lock.c:lock_rec_has_to_wait源代码推测出来的。）

行锁兼容矩阵

间隙锁(Gap Lock)：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。
记录锁(Record Lock)：只锁记录，特定几行记录。
临键锁(Next-Key Lock)：同时锁住记录和间隙，前开后闭区间(a,b]。
插入意图锁(Insert Intention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。

右侧是已加的锁(+ 代表兼容， -代表不兼容)	G	R	N	I
G	+	+	+	+
R	+	–	–	+
N	+	–	–	+
I	–	+	–	+

S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。精确模式的检测，用在S、X和X、X之间。从这个矩阵可以看到几个特点：

INSERT操作之间不会有冲突：你插入你的，我插入我的。
GAP，Next-Key会阻止Insert：插入的数据正好在区间内，不允许插入。
GAP和Record，Next-Key不会冲突
Record和Record、Next-Key之间相互冲突。
已有的Insert锁不阻止任何准备加的锁。
间隙锁（无论是S还是X）只会阻塞insert操作。

注意点

对于记录锁，列必须是唯一索引列或者主键列，查询语句必须为精确匹配，如“=”，否则记录锁会退化为临键锁。
间隙锁和临键锁基于非唯一索引，在唯一索引列上不存在间隙锁和临键锁。

表锁与锁表的误区

只有正确通过索引条件检索数据（没有索引失效的情况），InnoDB才会使用行级锁，否则InnoDB对表中的所有记录加锁，也就是将锁住整个表。注意，这里说的是锁住整个表，但是Innodb并不是使用表锁来锁住表的，而是使用了下面介绍的Next-Key Lock来锁住整个表。网上很多的说法都是说用表锁，然而实际上并不是，我们可以通过下面的例子来看看。

假设我们有以下的数据(MySQL8)：

									mysql> select * from users;

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

									| id | name | age |

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

									| 1 | a  | 1  |

									| 2 | a  | 1  |

									| 3 | a  | 1  |

									| 4 | a  | 1  |

									| 5 | a  | 1  |

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

方法一：

我们使用表锁锁表，并查看引擎的状态

									mysql> begin;

									Query OK, 0 rows affected (0.00 sec)

									mysql> lock tables users write;

									Query OK, 0 rows affected (0.00 sec)

									mysql> show engine innodb status\G

									...

									------------

									TRANSACTIONS

									------------

									Trx id counter 4863

									Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle

									History list length 911

									LIST OF TRANSACTIONS FOR EACH SESSION:

									---TRANSACTION 281479760456232, not started

									mysql tables in use 1, locked 1  ###############注意这里

									0 lock struct(s), heap size 1136, 0 row lock(s)

									...

然后我们再通过非索引的字段查询来加锁，并查看引擎的状态

									## 先解锁上次的表锁

									mysql> unlock tables;

									Query OK, 0 rows affected (0.00 sec)

									mysql> commit;

									Query OK, 0 rows affected (0.00 sec)

									mysql> begin;

									Query OK, 0 rows affected (0.00 sec)

									mysql> select * from users where name = 'a' for update;

									mysql> show engine innodb status\G

									...

									------------

									TRANSACTIONS

									------------

									Trx id counter 4864

									Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle

									History list length 911

									LIST OF TRANSACTIONS FOR EACH SESSION:

									---TRANSACTION 4863, ACTIVE 37 sec

									2 lock struct(s), heap size 1136, 6 row lock(s)  ###############注意这里

									...

然后我们再删除id为2，3，4的数据，然后在通过非索引的字段查询来加锁，并查看引擎的状态

									mysql> delete from users where id in (2,3,4);

									Query OK, 3 rows affected (0.00 sec)

									mysql> commit;

									Query OK, 0 rows affected (0.00 sec)

									mysql> begin;

									Query OK, 0 rows affected (0.00 sec)

									mysql> select * from users where name = 'a' for update;

									mysql> show engine innodb status\G

									...

									------------

									TRANSACTIONS

									------------

									Trx id counter 4870

									Purge done for trx's n:o < 4869 undo n:o < 0 state: running but idle

									History list length 914

									LIST OF TRANSACTIONS FOR EACH SESSION:

									---TRANSACTION 4869, ACTIVE 9 sec

									2 lock struct(s), heap size 1136, 3 row lock(s)  ###############注意这里

									...

可以看到这里使用了表锁和因为没法用索引锁定特定行而转而锁住整个表是不一样的。从第二次和第三次的操作来看，lock住的row也是不同的，这是因为两者间隙的个数不同，所以可以看到使用的并不是表锁，而是Next-Key Lock。第一次锁住了(-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞]，第二次锁住了(-∞,1],(1,5],(5,∞]。

方法二：

也可以通过以下语句来查看锁的信息，也可以知道用的是行锁，且是锁住了区间（插入不了数据）和记录，所以是Next-Key Lock。

									mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (你的事务id);

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

									| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |

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

									|         4889 | TABLE   | IX    |

									|         4889 | RECORD  | X     |

									|         4889 | RECORD  | X     |

									|         4889 | RECORD  | X     |

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

									10 rows in set (0.00 sec)