GreatSQL 中为什么 Update 不会被锁等待
  8MfSLxyzjZA4 3小时前 5 0

出乎意料的现象

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

  1. select for update 会出现锁等待
  2. delete 会出现锁等待
  3. 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)
  1. 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)
  1. 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 要么锁定它，要么等待它上的锁。

  1. 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 😃

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技术交流群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

image-20221030163217640

【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载，内容版权归原作者所有。本网站的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@moduyun.com
  1. 分享：
最后一次编辑于 3小时前 0

暂无评论

推荐阅读
  DvmQO3PQzZl2   14天前   31   0   0 MySQL
  8MfSLxyzjZA4   3小时前   5   0   0 MySQL
  C0Tz0A5KKSwy   4天前   8   0   0 MySQL
  C0Tz0A5KKSwy   3小时前   5   0   0 MySQL
  jTMfQq5cr55P   8天前   23   0   0 MySQL
  l3iKMwY9XZmO   9天前   24   0   0 MySQL
  8MfSLxyzjZA4   2024年08月21日   48   0   0 MySQL
  MbmRlDJhuJGZ   17天前   37   0   0 MySQL
  8MfSLxyzjZA4   17天前   33   0   0 MySQL
  dlDLn0A08La9   17天前   24   0   0 MySQL
  8MfSLxyzjZA4   2024年08月23日   39   0   0 MySQL
  8MfSLxyzjZA4   17天前   32   0   0 MySQL
  jTMfQq5cr55P   15天前   91   0   0 MySQL
  UuDQxJhIpKD4   26天前   42   0   0 MySQL
  DvmQO3PQzZl2   17天前   42   0   0 MySQL
  KHjRGEjcFzrV   17天前   33   0   0 MySQL
  8MfSLxyzjZA4   11天前   21   0   0 MySQL
  8MfSLxyzjZA4   13天前   28   0   0 MySQL
  RVb1WryTfP7X   28天前   28   0   0 MySQL
  MbmRlDJhuJGZ   10天前   30   0   0 MySQL
8MfSLxyzjZA4
8MfSLxyzjZA4
作者其他文章 更多
GreatSQL 中为什么 Update 不会被锁等待

2024-09-23

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

2024-09-13

GreatSQL 异步复制及搭建

2024-09-11

GreatSQL执行Update失败案例分析

2024-09-07

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

2024-09-07

Percona Toolkit 神器全攻略（开发类）

2024-08-23

独家揭秘丨GreatSQL 的MDL锁策略升级对执行的影响

2024-08-21

单条记录大小增长倍数和ibd文件大小的增长倍数不成正比

2024-08-16

GreatSQL 并行Load Data加快数据导入

2024-08-14

Percona Toolkit 神器全攻略（系统类）

2024-08-09

最新推荐 更多
Redis 内存突增时，如何定量分析其内存使用情况

2024-09-23

MySQL C连接与使用

2024-09-23

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

2024-09-23

[20240920]跟踪library cache lock library cache pin使用gdb.txt

2024-09-23

[INS-35180] Unable to check for available memory

2024-09-23

SQL Server的Descending Indexes降序索引

2024-09-23

Scala安装与环境配置详解教程

2024-09-21

如何自定义 CI/CD 配置文件中的输入参数？

2024-09-20

GitLab将会持续支持FluxCD

2024-09-20

VLDB来啦！企业上云“搭子”天翼云TeleDB数据库有话说

2024-09-20

QT绘图模块与数据库的结合

2024-09-20

中电信翼康基于Apache Dolphinscheduler重构“星海·济世医疗数据中台”实践经验分享

2024-09-20

MySQL linux下安装,配置,免密登录与基本认识

2024-09-20

Ruby-SAML CVE-2024-45409 漏洞解决方案

2024-09-19

使用Apache SeaTunnel高效集成和管理SftpFile数据源

2024-09-19

怎么办？用DolphinScheduler调度执行复杂的HiveSQL时无法正确识别符号

2024-09-19

Oracle静态监听中SID_NAME区分大小写吗？

2024-09-19

云上分布式SQL Server，你值得拥有

2024-09-19

快速创业之全栈技术栈

2024-09-18

mysql 常用知识点总结

2024-09-16

常用标签
数据数据数据数据linuxlinuxjavapythonredispythonJavaCentOS区块链技术信息系统区块链项目管理字符串MySQL资讯mysql教程MySQL