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社群助手微信好友，傳送驗證資訊加群。

SQL語句為什麼不會共享（中）
2011-08-01
SQL
Jtti：MySQL 資料庫中update語句會不會發生死鎖
2023-12-20
JttiMySql資料庫
數倉的等待檢視中，為什麼會有Hashjoin-nestloop
2024-02-28
OOP
為什麼軟體會被稱為“軟體”
2012-03-13
QZ面試被問select......for update會鎖表還是鎖行lor
2022-03-01
面試
網站為什麼會打不開
2022-12-23
網站
為什麼新人不會程式設計
2011-06-18
程式設計
ViewGroup為什麼不會呼叫onDraw
2013-05-03
View
PHP為什麼會被認為是草根語言？
2013-12-31
PHP
GreatSQL 中 Insert 慢是什麼情況？
2024-07-05
SQL
鎖賦值給其他變數為什麼會死鎖呢？
2019-04-29
賦值變數
SQL語句為什麼不會共享（上）
2011-07-31
SQL
SQL語句為什麼不會共享（下）
2011-08-02
SQL
為什麼Facebook不會有“dislike”按鈕？
2011-04-27
這就是為什麼你學不會DDD
2024-08-13
為什麼程式設計師做外包會被瞧不起？
2020-03-24
程式設計師
你的爬蟲為什麼會被檢測到？
2022-05-05
爬蟲
為什麼我的.htaccess檔案會被忽略(轉)
2007-08-14
Python 的切片為什麼不會索引越界？
2021-12-20
Python索引
為什麼這麼多人學不會C語言呢？
2018-01-30
C語言
GreatSQL 死鎖案例分析
2024-04-19
SQL
GreatSQL執行Update失敗案例分析
2024-09-04
SQL
Android開發掌握什麼技術才不會被淘汰
2019-02-25
Android
fastjson到底做錯了什麼？為什麼會被頻繁爆出漏洞？
2020-07-06
ASTJSON
為什麼你還是學不會程式設計
2019-07-29
程式設計
為什麼RPG遊戲永遠不會過時？
2021-01-25
遊戲
你為什麼總是學不會設計模式？
2014-04-24
設計模式
GreatSQL 的重新整理鎖
2024-07-29
SQL
由select for update鎖等待問題引發的深入思考
2024-02-27
synchronized 作為悲觀鎖，鎖住了什麼？
2020-04-27
synchronized
為什麼dispatch_sync在主執行緒會死鎖
2018-12-05
執行緒
為什麼使用了高匿代理IP還是會被限制？
2022-07-15
為什麼 Android 中不建議使用 Enums
2015-08-31
Android
for update 和 for update of 有什麼區別
2010-08-11
我為什麼會從程式不喜歡加{}到加{}
2018-11-23
為什麼有的程式設計師不會程式設計？
2014-11-21
程式設計師
[Q]怎麼快速查詢鎖與鎖等待
2007-11-21
[譯] JavaScript 中為什麼會有 Symbol 型別？
2019-03-27
JavaScriptSymbol型別