GreatSQL 中为什么 Update 不会被锁等待

文摘科技 2024-09-20 08:01 福建

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

出乎意料的现象

我们有一张测试表 t1，表中有一些数据，当 session1 开启一个事务，并执行了 select for update 操作后仍未提交事务，在并发事务(如 session2)开启事务并行执行一些操作会有不同的锁现象，表现在：

select for update 会出现锁等待
delete 会出现锁等待
update 执行成功，不受影响

为什么 select for update、delete 会出现预期内的锁等待，而 update 不会出现锁等待呢？

问题复现

首先在 test 库中创建一张测试表 t1

greatsql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `type_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

在表中插入原始数据如下

greatsql> select * from t1;
+----+---------+
| id | type_id |
+----+---------+
|  1 |       3 |
|  2 |       3 |
|  3 |       4 |
|  4 |       3 |
|  5 |       4 |
|  6 |       4 |
|  7 |       5 |
|  8 |       5 |
|  9 |       4 |
| 12 |       4 |
| 15 |       4 |
+----+---------+
11 rows in set (0.00 sec)

现在 session1 对表 t1 开启一个事务，并执行select for update操作，保持事务，不执行 commit

greatsql> begin;
Query OK, 0 rows affected (0.02 sec)

greatsql> select * from t1 where type_id=4 for update;
+----+---------+
| id | type_id |
+----+---------+
|  3 |       4 |
|  5 |       4 |
|  6 |       4 |
|  9 |       4 |
| 12 |       4 |
| 15 |       4 |
+----+---------+
6 rows in set (0.02 sec)

模拟并发 session2 开启事务，并发执行操作

greatsql> select * from t1 where type_id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

greatsql> delete from t1 where type_id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

greatsql> update t1 set type_id=2 where type_id=3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

现象解答

其实这并非是一个问题，而是 GreatSQL READ COMMITTED 隔离级别下的一种正常现象，具体表现在

当我们使用的是 GreatSQL 的 READ COMMITTED 隔离级别

greatsql> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.03 sec)

在 session1 执行 select * from t1 where type=4 for update; 后，由于表 t1 在 type_id 字段上并没有索引，这会导致全表扫描。但是呢，在 GreatSQL 的 READ COMMITTED 隔离级别下会对所有的行进行加锁，找到对应的记录后加锁，会对不符合条件的行释放锁

因此 session1 最后获取了意向排他锁(IX)和 6 条行锁(X,REC_NOT_GAP)，仅仅锁住了 type_id=4 的 6 行记录。

greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| ENGINE_LOCK_ID                          | ENGINE_TRANSACTION_ID | LOCK_MODE     | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| 140547538324696:1073:140547436105584    |                  2719 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        93 |
| 140547538324696:11:4:6:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 3         | GRANTED     |        93 |
| 140547538324696:11:4:7:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 5         | GRANTED     |        93 |
| 140547538324696:11:4:8:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 6         | GRANTED     |        93 |
| 140547538324696:11:4:9:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 9         | GRANTED     |        93 |
| 140547538324696:11:4:10:140547436102528 |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 12        | GRANTED     |        93 |
| 140547538324696:11:4:11:140547436102528 |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 15        | GRANTED     |        93 |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
7 rows in set (0.00 sec)

select for update 会出现锁等待

session2 执行 select * from t1 where type_id=3 for update; 后，由于 type_id 字段没有索引，执行全表扫描，从第一行开始，但是此时 session1 获得的行锁中第一个为 id=3 的记录，因此 session2 获得了 id=1 和 id=2 的行锁，但是当扫描到第三行时（即 id=3 这条记录），这一行已经被 session1 持有了锁，因此 session2 被阻塞了，出现了锁等待。

因此 session2 最后获取了意向排他锁(IX) 和 3 条行锁(X,REC_NOT_GAP)，id=3 这一行的锁被阻塞获取不到。

greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| ENGINE_LOCK_ID                          | ENGINE_TRANSACTION_ID | LOCK_MODE     | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| 140547538325504:1073:140547436111664    |                  2726 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        94 |
| 140547538325504:11:4:2:140547436108560  |                  2726 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 1         | GRANTED     |        94 |
| 140547538325504:11:4:3:140547436108560  |                  2726 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 2         | GRANTED     |        94 |
| 140547538325504:11:4:6:140547436108904  |                  2726 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 3         | WAITING     |        94 |
| 140547538324696:1073:140547436105584    |                  2719 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        93 |
| 140547538324696:11:4:6:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 3         | GRANTED     |        93 |
| 140547538324696:11:4:7:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 5         | GRANTED     |        93 |
| 140547538324696:11:4:8:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 6         | GRANTED     |        93 |
| 140547538324696:11:4:9:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 9         | GRANTED     |        93 |
| 140547538324696:11:4:10:140547436102528 |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 12        | GRANTED     |        93 |
| 140547538324696:11:4:11:140547436102528 |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 15        | GRANTED     |        93 |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
11 rows in set (0.00 sec)

delete 会出现锁等待

session2 执行 delete from t1 where type_id=3; 后，由于 type_id 字段没有索引，执行全表扫描，从第一行开始，但是此时 session1 获得的行锁中第一个为 id=3 的记录，因此 session2 获得了 id=1 和 id=2 的行锁，但是当扫描到第三行时（即 id=3 这条记录），这一行已经被 session1 持有了锁，因此 session2 被阻塞了，出现了锁等待。

因此 session2 最后获取了意向排他锁(IX) 和 3 条行锁(X,REC_NOT_GAP)，id=3 这一行的锁被阻塞获取不到。

greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| ENGINE_LOCK_ID                          | ENGINE_TRANSACTION_ID | LOCK_MODE     | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| 140547538325504:1073:140547436111664    |                  2727 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        94 |
| 140547538325504:11:4:2:140547436108560  |                  2727 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 1         | GRANTED     |        94 |
| 140547538325504:11:4:3:140547436108560  |                  2727 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 2         | GRANTED     |        94 |
| 140547538325504:11:4:6:140547436108904  |                  2727 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 3         | WAITING     |        94 |
| 140547538324696:1073:140547436105584    |                  2719 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        93 |
| 140547538324696:11:4:6:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 3         | GRANTED     |        93 |
| 140547538324696:11:4:7:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 5         | GRANTED     |        93 |
| 140547538324696:11:4:8:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 6         | GRANTED     |        93 |
| 140547538324696:11:4:9:140547436102528  |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 9         | GRANTED     |        93 |
| 140547538324696:11:4:10:140547436102528 |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 12        | GRANTED     |        93 |
| 140547538324696:11:4:11:140547436102528 |                  2719 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 15        | GRANTED     |        93 |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
11 rows in set (0.00 sec)

讲到这里，不得不说一下手册权威解释了，在 GreatSQL READ COMMITTED 隔离级别下，手册是这样解释的

对于 update 和 delete 操作 innodb 仅仅会锁住更新和删除行，在 GreatSQL 中根据 where 条件搜索后，会将不满足条件行的行锁进行释放，这样可以很好的降低死锁发生的概率，但也仍然可能发生。

Using READ COMMITTED has additional effects:
For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.
For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

手册也说明了，对于 update 操作，如果某行被锁后，innodb 会执行 semi-consistent(半一致读)操作，将最新提交的版本返回给 GreatSQL ，以便 GreatSQL 来判断该行是否符合 update 的 where 条件。如果该行匹配（必须更新），GreatSQL 会再次读取该行，并且这一次 innodb 要么锁定它，要么等待它上的锁。

update 执行成功，不受影响

这也就将要说的 session2 执行 update t1 set tpye_id=2 where type=3; 后为什么成功了。session2 执行 update 后，innodb 会进行半一致读，获取每一行的最新提交版本，然后通过 where 条件过滤，判断该行是否能被锁定，如下图，对于 id=1,id=2 的记录符合 where 条件，可以获取到行锁，对于 id=3 的记录并不符合 where 条件记录，所以 session2 对 id=3 的行不加锁，也就说和 session1 持有的 id=3 的行锁并不冲突。继续后续扫描，对于 id=4 的记录符合 where 条件，可以获取到行锁。直到完成所有的扫描和加锁。

greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| ENGINE_LOCK_ID                          | ENGINE_TRANSACTION_ID | LOCK_MODE     | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
| 140547538325504:1073:140547436111664    |                  2749 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        94 |
| 140547538325504:11:4:2:140547436108560  |                  2749 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 1         | GRANTED     |        94 |
| 140547538325504:11:4:3:140547436108560  |                  2749 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 2         | GRANTED     |        94 |
| 140547538325504:11:4:4:140547436108560  |                  2749 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 4         | GRANTED     |        94 |
| 140547538324696:1073:140547436105584    |                  2748 | IX            | TABLE     | NULL       | test          | t1          | NULL      | GRANTED     |        93 |
| 140547538324696:11:4:6:140547436102528  |                  2748 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 3         | GRANTED     |        93 |
| 140547538324696:11:4:7:140547436102528  |                  2748 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 5         | GRANTED     |        93 |
| 140547538324696:11:4:8:140547436102528  |                  2748 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 6         | GRANTED     |        93 |
| 140547538324696:11:4:9:140547436102528  |                  2748 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 9         | GRANTED     |        93 |
| 140547538324696:11:4:10:140547436102528 |                  2748 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 12        | GRANTED     |        93 |
| 140547538324696:11:4:11:140547436102528 |                  2748 | X,REC_NOT_GAP | RECORD    | PRIMARY    | test          | t1          | 15        | GRANTED     |        93 |
+-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
11 rows in set (0.00 sec)

GreatSQL 就是利用 semi-consistent(半一致读)操作对 update 进行的优化，从而提高并发性。

Enjoy GreatSQL :)

Percona Toolkit 神器全攻略（性能类）

GreatSQL 异步复制及搭建

Percona Toolkit 神器全攻略（复制类）

GreatSQL执行Update失败案例分析

《GreatSQL运维实战》视频课程

<关于 GreatSQL>

GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网: https://greatsql.cn/

⏩Gitee : https://gitee.com/GreatSQL/GreatSQL
⏩GitHub : https://github.com/GreatSQL/

🆙BiliBili : https://space.bilibili.com/1363850082

（对文章有疑问或见解可去社区官网提出哦~）

加入微信交流群	加入QQ交流群

想看更多技术好文，点个"在看"吧！

http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247508955&idx=1&sn=1634a6a52e9cac2a16e9a75246705647

GreatSQL社区

专注GreatSQL数据库及相关产品