GreatSQL 中為什麼 Update 不會被鎖等待

GreatSQL發表於2024-09-23

出乎意料的現象

我們有一張測試表 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 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

Jtti：MySQL 資料庫中update語句會不會發生死鎖
2023-12-20
JttiMySql資料庫
數倉的等待檢視中，為什麼會有Hashjoin-nestloop
2024-02-28
OOP
QZ面試被問select......for update會鎖表還是鎖行lor
2022-03-01
面試
為什麼評論會被摺疊？
2022-07-14
GreatSQL 中 Insert 慢是什麼情況？
2024-07-05
SQL
由select for update鎖等待問題引發的深入思考
2024-02-27
網站為什麼會打不開
2022-12-23
網站
GreatSQL執行Update失敗案例分析
2024-09-04
SQL
鎖賦值給其他變數為什麼會死鎖呢？
2019-04-29
賦值變數
SAP:什麼熱什麼不熱為什麼你會關心?（轉）
2019-07-19
這就是為什麼你學不會DDD
2024-08-13
Python 的切片為什麼不會索引越界？
2021-12-20
Python索引
Android開發掌握什麼技術才不會被淘汰
2019-02-25
Android
什麼樣的文章一定不會被推薦？
2018-06-12
GreatSQL 死鎖案例分析
2024-04-19
SQL
fastjson到底做錯了什麼？為什麼會被頻繁爆出漏洞？
2020-07-06
ASTJSON
為什麼購買的小冊會被下架？
2018-11-03
為什麼程式設計師做外包會被瞧不起？
2020-03-24
程式設計師
你的爬蟲為什麼會被檢測到？
2022-05-05
爬蟲
為什麼螞蟻永遠不會堵車？
2020-04-06
為什麼特斯拉永遠不會做換電？
2022-03-03
GreatSQL 的重新整理鎖
2024-07-29
SQL
為什麼偽類的content不能被選中？
2024-12-07
《細胞研究》：癌細胞為什麼會被“餓死”？
2023-12-07
為什麼你還是學不會程式設計
2019-07-29
程式設計
為什麼RPG遊戲永遠不會過時？
2021-01-25
遊戲
synchronized 作為悲觀鎖，鎖住了什麼？
2020-04-27
synchronized
為什麼dispatch_sync在主執行緒會死鎖
2018-12-05
執行緒
華為數字化轉型，你為什麼就學不會？
2021-11-29
為什麼使用了高匿代理IP還是會被限制？
2022-07-15
什麼樣的文章會被修改？
2018-06-12
我為什麼會從程式不喜歡加{}到加{}
2018-11-23
為什麼react在setTimeOut中使用setSate不會批量更新
2018-11-18
React
MySQL 死鎖和鎖等待
2020-04-13
MySql
六星教育：Python為什麼這麼火？未來它將會被什麼語言取代？
2020-10-24
Python
For Update 加鎖分析
2022-01-24
java中死鎖是什麼
2021-09-11
Java
Valve 答：為什麼《半衰期》與《求生之路》系列不會「數 3」？
2020-04-02