05mysql的锁分析-锐单电子商城

写在前面

如果本文没有特别说明，使用的存储引擎都是InnoDB.

只要是共享资源，就会出现并发访问数据的一致性，数据库也是如此。数据库需要合理设置数据访问规则，我们称之为数据访问规则的数据结构锁。让我们一起来看看这篇文章MySQL提供了哪些锁机制？

1：MySQL提供了哪些锁？

根据锁的强度，分为全局锁、表锁、行锁，思维导图如下：

接下来，让我们按照锁的力度从大大小小的顺序一起学习。让我们开始吧！

考虑到这样的场景，我们正在执行数据查询，但其他线程执行ddl删除一列，此时已返回给我们的数据和数据库的表结构是不对的，所以肯定不对，MySQL在5.引入5版MDL，用于类似DDL加锁将改变表结构的操作。MDL分为MDL读锁和MDL写锁，且MDL无显式加锁和释放锁的方式是隐式自动加锁和释放锁。在执行增删改查操作时，会自动获取MDL读锁，事务结束后自动释放锁，执行DDL自动获取时间MDL写锁，DDL语句执行后自动释放锁，读锁为共享读锁，写锁为排他写锁，两者关系如下：

因为写锁是共享写锁，可以同时执行多个线程的读取操作。 读锁和写锁互斥，即有写锁时，不能加读锁，反之亦然。 写锁和写锁互斥，即当有写锁时，就不能加写锁。

大表执行时DDL需要特别注意，因为这个操作会获得MDL写锁，应用程序会因为无法获得表格中所有的添加、删除和修改而检查表格MDL读锁被堵塞，容易造成线上事故。尽量减少锁力，InnoDB存储引擎由行为单位提供锁实现，我们可以简单地称之为行锁。让我们看看。

h2> 4：行锁

当你发现某个sql执行被阻塞，且通过show processlist查看State=Updating时就说明是被行锁阻塞了，下面通过实例来验证下。

当执行如update t set c=x where id=1之类的语句时，就会获取id=1这一行的行写锁，此时行写锁(update 语句, select xxx for update)，行读锁(select xxx lock in share mode)注意select xx from t where id=1不获取任何锁都无法被获取，下面我们来一起看下。

启动会话A,B,C
在会话A启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

在会话A执行语句更新id=1的行

mysql> update t set c=c where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

在会话B启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

在会话B执行语句更新id=1的行

可以看到阻塞等待了。

在会话C执行show processlist;

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+-----------------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+
| 18 | root | localhost:57178 | test | Query   |   18 | Updating | update t set c=c where id=1 |
| 20 | root | localhost:58797 | test | Query   |    0 | NULL     | show processlist            |
| 21 | root | localhost:58801 | test | Sleep   |   55 |          | NULL                        |
| 22 | root | localhost:64840 | test | Sleep   | 1155 |          | NULL                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+

可以看到第一行线程ID为18的就是阻塞等待的线程，那么我们如何找到是谁占用了id=1的行锁呢？没有特别好的办法，但是我们知道行锁使用的是两阶段锁协议，即在执行语句时才会获取锁，但是语句执行完毕不立即释放锁，而是在事务提交时才释放锁，因此可以断定既然锁一直没有被释放，肯定是因为其所在的事务没有提交，所以我们可以通过过滤长事务的方式找到罪魁祸首,当然只是可能是哪个，但是必要时误杀也影响不大。可通过如下语句过滤：

mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G
*************************** 1. row ***************************
                    trx_id: AAF56
                 trx_state: RUNNING
               trx_started: 2022-07-15 14:18:56
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 320
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
1 row in set (0.03 sec)

这里我使用的时长是60秒，具体可以根据情况来设置时长，trx_mysql_thread_id: 26其中的25就是线程ID，kill 26即可，如下图：

5：间隙锁

当show processlist,state为update时就说明是被间隙锁阻塞了。

准备数据：

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;

insert into t values(0,10,100),(5,50,500),
(100,1000,10000),(150,1500,15000),(200,2000,2000),(250,2500,25000);

在了解间隙锁之前，我们必须先明确一个概念，幻读，即相同的查询条件，后一次查询比前一次查询多出了行。对于RR的隔离级别，如果是RR使用的快照度，则是没有幻读这个问题的，如下面的例子：

启动2个会话A，B
在会话A启动事务，并执行查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id in (5,500);
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

在会话B启动事务，并插入ID=500数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(500,500,500);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

在会话A,再次执行查询

mysql> select * from t where id in (5,500);
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

可以看到并没有查询到ID=500的行，也就证明了在RR快照读的时候是没有幻读这个问题的，但是如果是当前读呢！当前读读取的是当前的最新数据，是不是RR此时就有幻读的问题了呢？看上去是的，接下来我们再使用当前读来试验下(注意：将数据恢复到初始状态！！！)：

启动2个会话A，B
在会话A启动事务，并使用当前读执行查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id in (5,500) for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

在会话B启动事务，并插入ID=500数据

从图中可以看出，此时插入操作阻塞等待了，其实就是被间隙锁阻塞了，所谓间隙锁，就是对数据的行与行之间的间隙增加的锁，不是很好理解，对于普通列，主键列，唯一索引，非唯一索引间隙锁的加锁行为也是不同的，因此我们分别来看下。

5.1：普通列

启动2个会话A，B
在会话A启动事务，并使用当前读执行查询

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where d=500 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

在会话B插入ID=9的数据

可以看到阻塞等待，实际上加的间隙锁如下图：

其中区间的就是间隙锁，即影响的行的ID值落到区间内都会被间隙锁所阻塞，另外上面的数字的代表是行锁，即(-∞，0)间隙锁，0行锁，(0，5)间隙锁，5行锁，(5,100)间隙锁，100行锁，(100,150)间隙锁，150行锁，(150,200)间隙锁，200行锁，(200,250)间隙锁，250行锁，(250,+∞)间隙锁，其中间隙锁和行锁的组合我们叫做next-key lock,使用左开右闭的格式来表示，即(-∞，0]next-key lock，(0，5]next-key lock，(5,10]next-key lock，(10,15]next-key lock，(15,20]next-key lock，(20,25]next-key lock，(25,supremum]next-key lock,也就是只要是落到了这些区间的就都会被阻塞，为什么要在所有这些间隙都加锁呢，因为所有间隙都有可能产生满足where=500条件的新数据！！！其实此时就是任何值都会阻塞。如下一些insert的操作（因为insert操作可能会破坏where d=500的条件，所以都会阻塞）：

mysql> insert into t values(9,9,9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=5 where id=0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (7,7,7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (23,23,23);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (9999,9999,9999);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如下是一些update的操作，分为两种情况，第一种是update有匹配的行，此时就会被阻塞，因为可能会破坏where d=500的条件，而当没有匹配的行时，因为不可能会破坏where d=500的条件，所以不会被阻塞，如下分别测试有匹配行和没有匹配行的情况(是否有匹配行已经给出了注释，另外set部分不重要，重要的是where部分)：

mysql> update t set c=c where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=9090 where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=8 where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set c=c where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=8 where id=2; /*无匹配行*/
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set d=8 where id=2222; /*无匹配行*/
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set d=8 where id=250; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

看到这里，不知道你有没有一个疑问，比如insert操作insert into t values (7,7,7)生成的新数据，并不满足where=500的条件，比如update操作update t set c=c where id=0; /*有匹配行*/也不会产生满足where=500的数据，但是为什么就阻塞了呢？MySQL就这么笨不会加上这个判断吗？我认为不这样做的原因是，代价太大！！！我们的例子肯定是很好判断的，但是实际的情况可就是千变万化了，比如insert into t values (7,7,select dVal from xxx where xxx in(selet ...)),判断的成本必定不可预估。

5.2：主键列

主键列分为两种情况，如果是目标ID对应的行是存在的，则直接给该行上行锁，如果是不存在，则会将其所在的间隙上间隙锁，这里我们先看这个要查询的ID值不存在对应行的情况：

启动2个会话A，B
在会话A启动事务，并使用当前读执行查询

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=13 for update;
Empty set (0.00 sec)

在会话B启动事务，并插入ID=14的行

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(14,140,1400);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到被阻塞了，阻塞的锁正是(5,100)间隙锁，也是唯一的间隙锁，那么为什么只给这一个间隙加间隙锁呢？是因为对于查询条件where id=13可能满足的ID范围一定在这个范围内，此时其他的行间隙都是可以操作的，如下验证：

mysql> insert into t values(3,30,300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(130,1300,13000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(180,1800,18000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(230,2300,23000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(251,2510,25100);
Query OK, 1 row affected (0.00 sec)

5.3：唯一索引

注意：先将数据恢复到初始状态！！！

添加唯一索引

mysql> alter table t add unique index idx_d(`d`);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

在存在对应的值和不存在对应的值是行为是不同的，我们分开来看。

5.3.1：值存在

启动2个会话A，B，C
在会话A启动事务，并使用当前读执行查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where d=500 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

在会话B对ID的间隙执行插入测试

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(3,30,300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(53,530,5300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(123,1230,12300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(230,2230,22300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(23000000,2230,2230000);
Query OK, 1 row affected (0.00 sec)

可以看到所有的ID间隙都是可以正常插入数据的，说明此时并没有间隙锁，为什么不需要加呢？因为是唯一索引，所以不可能通过insert d=500的信新数据(唯一索引冲突)，或者是update其他数据，即将其他数据的d值改成500(唯一索引冲突)，唯一加的锁是d=50改行对应的行写锁，可以通过如下方式验证:

在会话B执行如下查询

mysql> update t set c=c where d=500;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在会话C查看连接状态

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                         |
+----+------+-----------------+------+---------+------+----------+------------------------------+
| 15 | root | localhost:49423 | test | Sleep   |  776 |          | NULL                         |
| 16 | root | localhost:49434 | test | Query   |    4 | Updating | update t set c=c where d=500 |
| 17 | root | localhost:9513  | test | Query   |    0 | NULL     | show processlist             |
+----+------+-----------------+------+---------+------+----------+------------------------------+

5.3.2：值不存在

此时间隙锁是基于d对应的二级索引树来加的，简单来说就是加在d上的，不同于主键，普通列，加在主键上。

启动2个会话A，B，C
在会话A启动事务，并使用当前读执行查询

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where d=89 for update;
Empty set (0.00 sec)

在会话B测试所有d间隙

mysql> insert into t values (9900,990,99);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (111100,11110,1111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (300000,30000,3000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (1300000,130000,13000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (1800000,180000,18000);
Query OK, 1 row affected (0.00 sec)

可以看到，只有当d的范围在(负无穷,100)时才会被阻塞，也就是说，此时只有(负无穷,100)的间隙锁。其实这个行为和主键列是一样的。

5.4：非唯一索引

注意：先将数据恢复到初始状态！！！

分为值存在和值不存在两种情况来看。

5.4.1：值不存在

启动会话A,B
在会话A启动事务，并查询c=11的数据

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+-----+------+-------+
| id  | c    | d     |
+-----+------+-------+
|   0 |   10 |   100 |
|   5 |   50 |   500 |
| 100 | 1000 | 10000 |
| 150 | 1500 | 15000 |
| 200 | 2000 |  2000 |
| 250 | 2500 | 25000 |
+-----+------+-------+
6 rows in set (0.00 sec)

mysql> select * from t where c=11 for update;
Empty set (0.00 sec)

在会话B启动事务，并试验c的不同间隙加锁情况

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 3, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 13, RAND() * 900 + 100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(RAND() * 900 + 100, 913, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 1113, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 1713, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 2412, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 2501, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

可以看到只有间隙c(10,50)加了间隙锁，其他的正常。

5.4.2：值存在

启动会话A,B
在会话A启动事务，并查询c=50的数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where c=50 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

在会话B执行如下测试

mysql> insert into t values(RAND() * 900 + 100, 6, RAND() * 900 + 100); /*间隙（负无穷,10）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 36, RAND() * 900 + 100); /*间隙（10,50）*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(RAND() * 900 + 100, 999, RAND() * 900 + 100); /*间隙（50,1000）*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(RAND() * 900 + 100, 1333, RAND() * 900 + 100); /*间隙（1000,1500）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 1833, RAND() * 900 + 100); /*间隙（1500,2000）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 2433, RAND() * 900 + 100); /*间隙（2000,2500）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 99999, RAND() * 900 + 100); /*间隙（2500,正无穷）*/
Query OK, 1 row affected (0.00 sec)

可以看到，只有间隙（10,50），（50,1000）会加上间隙锁，其他的正常！

6：其他锁

6.1：意向锁

当我们执行语句lock tables t write语句获取表X锁时，只有满足如下的条件才能获取成功：

1：当前表t不存在其他事务持有表X锁
2：当前表t的每一行不存在其他事务持有行X锁

其中的1很好判断，但是2则需要遍历索引树的每一个叶子节点，判断是否存在锁，这显然是一个全表扫描的过程，性能可想而知好不到那里去，那么为了解决这个问题，就引入了意向锁，具体如下：

select * from t where xxx lock in share mode：先获取满足条件行的S锁，然后获取意向共享锁，即IS锁。
select * from t where xxx for udpate：先获取满足条件行的X锁，然后获取意向排它锁，即IX锁。

这样，执行语句lock tables t write时的2，就只需要判断当前表是否存在IX锁即可(注意：意向锁是表锁！！！)。IS，IX之间互斥关系如下：

意向锁和行锁之间的互斥关系如下：

意向锁和表锁之间的互斥关系如下：

写在后面

一文搞懂Undo Log版本链与ReadView机制如何让事务读取到该读的数据

MySQL进阶系列：多版本并发控制mvcc的实现。

详解 MySql InnoDB 中意向锁的作用。

资讯详情

05mysql的锁分析

写在前面

1：MySQL提供了哪些锁？

2：全局锁

3：表级锁

3.1：表锁

3.2：MDL