案例分析：MySQL 并行復制竟然比單線程慢？
最近碰到一個 case，發現在特定場景下，并行復制竟然比單線程復制要慢。
現象
從某個時間點開始，從庫的復制延遲持續增加，且沒有下降的趨勢。
數據庫版本：8.0.40，事務隔離級別 RC（Read Committed），并行重放線程數（replica_parallel_workers）為 8。
分析過程
通過show slave status\G查看，發現Relay_Master_Log_File和Exec_Master_Log_Pos都在變化，只不過變化得比較慢。
剛開始懷疑是主庫寫入量較大導致的，后來通過mysql-binlog-time-extractor（具體用法可參考：分享一個 MySQL binlog 分析小工具）分析，發現主庫的寫入量在剛開始出現延遲時（2025-09-01 09:30）并不大，反倒是寫入量大的時間段（2025-09-01 04:57:53 - 2025-09-01 05:02:42）沒有出現延遲。
+--------------------+------------------------+---------------------+---------------------+-----------+------------------------------------+ | Log_name | File_size | Start_time | End_time | Duration | GTID | +--------------------+------------------------+---------------------+---------------------+-----------+------------------------------------+ | binary-log.005565 | 1302499830 (1.21 GB) | 2025-09-01 04:57:53 | 2025-09-01 04:58:22 | 00:00:29 | 1284696693-1284699126 | | binary-log.005566 | 1105002721 (1.03 GB) | 2025-09-01 04:58:22 | 2025-09-01 04:58:23 | 00:00:01 | 1284699127-1284699312 | | binary-log.005567 | 1273545902 (1.19 GB) | 2025-09-01 04:58:23 | 2025-09-01 05:02:33 | 00:04:10 | 1284699313-1284728539 | | binary-log.005568 | 1287820910 (1.20 GB) | 2025-09-01 05:02:33 | 2025-09-01 05:02:42 | 00:00:09 | 1284728540-1284729282 | ... | binary-log.005633 | 58514304 (55.80 MB) | 2025-09-01 09:12:53 | 2025-09-01 09:17:53 | 00:05:00 | 1286735216-1286786118 | | binary-log.005634 | 58955596 (56.22 MB) | 2025-09-01 09:17:53 | 2025-09-01 09:22:53 | 00:05:00 | 1286786119-1286834568 | | binary-log.005635 | 71508778 (68.20 MB) | 2025-09-01 09:22:53 | 2025-09-01 09:27:53 | 00:05:00 | 1286834569-1286880281 | | binary-log.005636 | 107107179 (102.15 MB) | 2025-09-01 09:27:53 | 2025-09-01 09:32:53 | 00:05:00 | 1286880282-1286942223 | | binary-log.005637 | 530205055 (505.64 MB) | 2025-09-01 09:32:53 | 2025-09-01 09:37:53 | 00:05:00 | 1286942224-1287246612 | | binary-log.005638 | 546754562 (521.43 MB) | 2025-09-01 09:37:53 | 2025-09-01 09:42:53 | 00:05:00 | 1287246613-1287562930 | | binary-log.005639 | 528677634 (504.19 MB) | 2025-09-01 09:42:53 | 2025-09-01 09:47:53 | 00:05:00 | 1287562931-1287868985 | +--------------------+------------------------+---------------------+---------------------+-----------+------------------------------------+
查看該實例的錯誤日志，發現有大量的鎖等待超時報錯。
需要注意的是，這個實例的事務隔離級別是 RC。在該級別下，MySQL 通常只會加記錄鎖。此外，該實例啟用了 WRITESET 并行復制，MySQL 會根據事務修改的主鍵或唯一索引來判斷是否可并行執行。換句話說，如果兩個事務在主鍵或唯一索引上存在沖突，它們將無法并行重放。理論上，在這種機制組合下，從庫在重放過程不應發生鎖等待超時。
隨后使用binlog_summary.py（具體用法可參考：Binlog分析利器-binlog_summary.py）對延遲開始時段的四個 binlog 文件（ binary-log.005636 ~ binary-log.005639 ）進行了分析，發現這些 binlog 的操作模式十分相似：操作次數排名前兩位的均為同一張表biz_schema.tbl_product_service_mapping01的 DELETE 與 INSERT 操作。
# python3 binlog_summary.py -f binary-log.005636.txt -c opr --new TABLE_NAME DML_TYPE NUMS biz_schema.tbl_product_service_mapping01 INSERT 71271 biz_schema.tbl_product_service_mapping01 DELETE 67434 ...
寫了個簡單的腳本測試了下，發現對于相同的唯一索引值，INSERT操作總是出現在對應的DELETE操作之后，于是寫了個腳本將 DELETE操作涉及的記錄提取出來并插入到測試庫中，然后將相關 binlog 當作 relay log 進行重放。
為了排除其它表的干擾，在重放時設置了replicate-do-table = biz_schema.tbl_product_service_mapping01，只重放這一張表。
下面是具體的重放步驟：
1. 初始化 relay log：
CHANGE MASTER TO MASTER_HOST='dummy'; STOP SLAVE; RESET SLAVE ALL;
執行上述命令后，MySQL 會在當前數據目錄下生成兩個文件：
instance-20250903-0701-relay-bin.000001（第一個 relay log 文件）instance-20250903-0701-relay-bin.index（relay log 索引文件）
其中，instance-20250903-0701 是主機名。
2. 替換掉 relay log：
用 binary-log.005636 替換掉 instance-20250903-0701-relay-bin.000001，并修改該文件的屬主。
# cp binary-log.005636 /data/mysql/3306/data/instance-20250903-0701-relay-bin.000001 # chown mysql.mysql /data/mysql/3306/data/instance-20250903-0701-relay-bin.000001
3. 啟動 SQL 線程進行重放：
CHANGE MASTER TO RELAY_LOG_FILE='instance-20250903-0701-relay-bin.000001', RELAY_LOG_POS=1, MASTER_HOST='dummy'; START SLAVE SQL_THREAD;
結果發現能成功重放，且重放過程中未出現任何報錯。
測試了三次，重放時間分別為 362.74s、352.69s、361.75s，平均耗時 359.06 秒。
每次重放過程中，錯誤日志中都出現了多次鎖等待超時錯誤。
2025-09-21T07:53:45.257279-00:00 260 [Warning] [MY-010584] [Repl] Slave SQL for channel '': Worker 5 failed executing transaction '9206ff59-2d95-4a02-88cf-04d97adfdd65:1286917678' at master log , end_log_pos 63251784; Could not execute Write_rows event on table biz_schema.tbl_product_service_mapping01; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log FIRST, end_log_pos 63251784, Error_code: MY-00120
很顯然，鎖等待超時是并行重放導致的。
如果是單線程重放，就能規避這個問題，于是將replica_parallel_workers設置為 1，重新執行相同的測試，三次重放時間分別為 82.39s、83.40s、83.43s，平均僅 83.07 秒。
想不到，單線程重放竟然比多線程快了四倍多。
接下來，重點分析下鎖等待超時問題。
為什么會出現鎖等待？
以下是出現鎖等待時，sys.innodb_lock_waits的輸出：
mysql> select * from sys.innodb_lock_waits\G *************************** 1.row *************************** wait_started: 2025-10-1213:11:29 wait_age: 08:00:33 wait_age_secs: 28833 locked_table: `biz_schema`.`tbl_product_service_mapping01` locked_table_schema: biz_schema locked_table_name: tbl_product_service_mapping01 locked_table_partition: NULL locked_table_subpartition: NULL locked_index: tbl_product_service_pk locked_type: RECORD waiting_trx_id: 5221288 waiting_trx_started: 2025-10-1213:11:22 waiting_trx_age: 08:00:40 waiting_trx_rows_locked: 35 waiting_trx_rows_modified: 34 waiting_pid: 10 waiting_query: INSERT IGNORE INTO tbl_product ... (10512475, 1073743289) , waiting_lock_id: 140256432120808:10011:67:240:263:140256317861168 waiting_lock_mode: X,GAP,INSERT_INTENTION blocking_trx_id: 5221291 blocking_pid: 14 blocking_query: INSERT IGNORE INTO tbl_product ... (10512476, 1073743289) blocking_lock_id: 140256432125848:9282:67:240:263:140256317891856 blocking_lock_mode: S,GAP blocking_trx_started: 2025-10-1213:11:22 blocking_trx_age: 08:00:40 blocking_trx_rows_locked: 35 blocking_trx_rows_modified: 34 sql_kill_blocking_query: KILL QUERY 14 sql_kill_blocking_connection: KILL 14 *************************** 2.row *************************** wait_started: 2025-10-1213:11:29 wait_age: 08:00:33 wait_age_secs: 28833 locked_table: `biz_schema`.`tbl_product_service_mapping01` locked_table_schema: biz_schema locked_table_name: tbl_product_service_mapping01 locked_table_partition: NULL locked_table_subpartition: NULL locked_index: tbl_product_service_pk locked_type: RECORD waiting_trx_id: 5221291 waiting_trx_started: 2025-10-1213:11:22 waiting_trx_age: 08:00:40 waiting_trx_rows_locked: 35 waiting_trx_rows_modified: 34 waiting_pid: 14 waiting_query: INSERT IGNORE INTO tbl_product ... (10512476, 1073743289) waiting_lock_id: 140256432125848:9282:67:240:260:140256317892560 waiting_lock_mode: X,GAP,INSERT_INTENTION blocking_trx_id: 5221289 blocking_pid: 12 blocking_query: NULL blocking_lock_id: 140256432123832:9816:67:240:260:140256317879376 blocking_lock_mode: S,GAP blocking_trx_started: 2025-10-1213:11:22 blocking_trx_age: 08:00:40 blocking_trx_rows_locked: 34 blocking_trx_rows_modified: 33 sql_kill_blocking_query: KILL QUERY 12 sql_kill_blocking_connection: KILL 12 2rowsinset (0.01 sec)
可以看出：
- PID 10 的 INSERT 操作被 PID 14 持有的 S,GAP 鎖阻塞。
- PID 14的 INSERT 操作又被 PID 12 持有的 S,GAP 鎖阻塞。
使用performance_schema.data_locks可以獲取更詳細的鎖信息，包括被鎖定的數據行：
SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id, w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id, l1.LOCK_MODE AS waiting_lock_mode, l1.LOCK_DATA AS waiting_lock_data, l2.LOCK_MODE AS blocking_lock_mode, l2.LOCK_DATA AS blocking_lock_data FROM performance_schema.data_lock_waits AS w JOIN performance_schema.data_locks AS l1 ON w.REQUESTING_ENGINE_LOCK_ID = l1.ENGINE_LOCK_ID JOIN performance_schema.data_locks AS l2 ON w.BLOCKING_ENGINE_LOCK_ID = l2.ENGINE_LOCK_ID; +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ | waiting_trx_id | blocking_trx_id | waiting_lock_mode | waiting_lock_data | blocking_lock_mode | blocking_lock_data | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ | 5221288 | 5221291 | X,GAP,INSERT_INTENTION | 10512476, 1, 18158557178 | S,GAP | 10512476, 1, 18158557178 | | 5221291 | 5221289 | X,GAP,INSERT_INTENTION | 10512477, 1, 18158557146 | S,GAP | 10512477, 1, 18158557146 | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ 2 rows in set (0.00 sec)
接下來從show processlist的輸出中看看 PID 10、14、12 這三個線程的狀態。
mysql> show processlist; +----+-----------------+-----------+-----------+---------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+-----------+---------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1813 | Waiting on empty queue | NULL | | 8 | root | localhost | biz_schema | Query | 0 | init | show processlist | | 9 | system user | | NULL | Query | 1588 | Waiting for dependent transaction to commit | NULL | | 10 | system user | | biz_schema | Query | 3585949 | Applying batch of row changes (write) | INSERT IGNORE INTO tbl_product_service_mapping01 ( c1, c2 | | 11 | system user | | NULL | Query | 3585949 | Waiting for preceding transaction to commit | NULL | | 12 | system user | | NULL | Query | 3585949 | Waiting for preceding transaction to commit | NULL | | 13 | system user | | NULL | Query | 3585949 | Waiting for preceding transaction to commit | NULL | | 14 | system user | | biz_schema | Query | 3585949 | Applying batch of row changes (write) | INSERT IGNORE INTO tbl_product_service_mapping01 ( c1, c2 | | 15 | system user | | NULL | Query | 3585949 | Waiting for preceding transaction to commit | NULL | | 16 | system user | | NULL | Query | 3585949 | Waiting for an event from Coordinator | NULL | | 17 | system user | | NULL | Query | 3585949 | Waiting for an event from Coordinator | NULL | +----+-----------------+-----------+-----------+---------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+ 11rowsinset, 1warning (0.00 sec)
PID 12 的執行用戶是system user，說明它是并行重放的工作線程，其狀態為Waiting for preceding transaction to commit，表示該線程正在等待它前面的事務提交完成。
而 PID 10 和 PID 14 的狀態均為Applying batch of row changes (write)。從字面上看，似乎是在執行批量寫入操作，但實際上，這兩個線程正在等待鎖。
如果執行的是SHOW FULL PROCESSLIST，Info列的INSERT IGNORE操作中還可以看到具體要插入的唯一索引值。借助這些唯一索引值，可以在 binlog 中精確定位對應的執行位置，便于分析事務執行順序和鎖等待情況。
不過，對于 PID 12，由于Info列為NULL，無法直接看到具體的 DML 操作，因此難以定位其執行內容。
為了解決這個問題，我在Slave_worker::slave_worker_exec_event函數中，在調用ev->do_apply_event_worker(this)的前后分別添加了日志打印。這樣，就能清楚地看到每個工作線程正在執行的 event 的 binlog 位置點信息。
int Slave_worker::slave_worker_exec_event(Log_event *ev) { ... ulong thread_id = thd->thread_id(); ulong log_pos = static_cast<ulong>(ev->common_header->log_pos); std::string msg = "Executing event: worker_thread_id=" + std::to_string(thread_id) + ", master_log_pos=" + std::to_string(log_pos); LogErr(INFORMATION_LEVEL, ER_CONDITIONAL_DEBUG, msg.c_str()); ret = ev->do_apply_event_worker(this); msg = "Done executing event: worker_thread_id=" + std::to_string(thread_id) + ", master_log_pos=" + std::to_string(log_pos); LogErr(INFORMATION_LEVEL, ER_CONDITIONAL_DEBUG, msg.c_str()); return ret; }
下面是鎖等待發生時，PID 10、12、14 正在執行的 binlog event 位置點信息：
# PID 10 grep 'worker_thread_id=10' /data/mysql/3306/data/mysqld.err | tail -1 2025-10-12T13:11:22.639120-00:00 10 [Note] [MY-013935] [Repl] Executing event: worker_thread_id=10, master_log_pos=63245428 # PID 12 grep 'worker_thread_id=12' /data/mysql/3306/data/mysqld.err | tail -1 2025-10-12T13:11:22.725638-00:00 12 [Note] [MY-013935] [Repl] Executing event: worker_thread_id=12, master_log_pos=63248672 # PID 14 grep 'worker_thread_id=14' /data/mysql/3306/data/mysqld.err | tail -1 2025-10-12T13:11:22.646870-00:00 14 [Note] [MY-013935] [Repl] Executing event: worker_thread_id=14, master_log_pos=63251784
可以看到，PID 10 對應的事務在 binlog 中的位置早于 PID 12。
當參數replica_preserve_commit_order設置為 ON 時，從庫必須嚴格按照主庫的提交順序依次提交事務，因此 PID 12 必須等待 PID 10 提交完成才能繼續執行。
結合鎖依賴關系，就形成了一個循環等待的局面：
- PID 10 等待 PID 14 持有的 S,GAP 鎖；
- PID 14 等待 PID 12 持有的 S,GAP 鎖；
- PID 12 因提交順序限制，必須等待 PID 10 提交事務。
最終，這種環路導致三個線程相互阻塞，直到鎖等待超時，MySQL 才會重新執行這些事務。
模擬從庫的重放操作
根據獲取到的 PID 10、12、14 對應的 event 位置點信息，我們可以還原出鎖等待發生時這三個線程正在執行的具體操作：
worker_thread_id=10 master_log_pos=63245428: insert c1 = 10512475 的所有記錄，如(10512475,1),(10512475,20)... worker_thread_id=12 master_log_pos=63248672: insert c1 = 10512477 的所有記錄，如(10512477,1),(10512477,20)... worker_thread_id=14 master_log_pos=63251784: insert c1 = 10512476 的所有記錄，如(10512476,1),(10512476,20)...
這里的記錄值對應的是表的聯合唯一索引，其中c1是聯合索引的第一列。
值得注意的是，在這些INSERT操作之前，binlog 中還存在針對相同c1值的DELETE操作：
delete c1 = 10512475 的所有記錄，如(10512475,1),(10512475,20)... delete c1 = 10512477 的所有記錄，如(10512477,1),(10512477,20)... delete c1 = 10512476 的所有記錄，如(10512476,1),(10512476,20)...
也就是說，業務實際上是通過 DELETE + INSERT 的方式實現數據更新。
為了進一步分析鎖等待問題，我打印了重放過程中每個INSERT操作的具體內容。
2025-10-12T13:11:22.639267-00:00 10 [Note] [MY-013935] [Repl] Inserted row: (18158557112, 10512475, 1, ...) 2025-10-12T13:11:22.640729-00:00 10 [Note] [MY-013935] [Repl] Inserted row: (18158557113, 10512475, 20, ...) 2025-10-12T13:11:22.642004-00:00 10 [Note] [MY-013935] [Repl] Inserted row: (18158557114, 10512475, 26, ...) 2025-10-12T13:11:22.643344-00:00 10 [Note] [MY-013935] [Repl] Inserted row: (18158557115, 10512475, 123, ...) 2025-10-12T13:11:22.644262-00:00 12 [Note] [MY-013935] [Repl] Inserted row: (18158557146, 10512477, 1, ...) 2025-10-12T13:11:22.644663-00:00 10 [Note] [MY-013935] [Repl] Inserted row: (18158557116, 10512475, 131, ...) 2025-10-12T13:11:22.646250-00:00 12 [Note] [MY-013935] [Repl] Inserted row: (18158557147, 10512477, 20, ...) 2025-10-12T13:11:22.647020-00:00 14 [Note] [MY-013935] [Repl] Inserted row: (18158557178, 10512476, 1, ...) 2025-10-12T13:11:22.647192-00:00 10 [Note] [MY-013935] [Repl] Inserted row: (18158557117, 10512475, 133, ...)
其中，第一個值是自增主鍵，后兩個值是唯一索引列。
從輸出可以看到，這三個事務的插入操作是交叉執行的。
模擬從庫重放過程
下面通過一個實驗來模擬從庫的重放操作。
首先，在會話 1 中創建測試表并插入數據。
session1> create table test.t1(id bigint auto_increment primary key,c1 int,c2 int,unique key(c1,c2)); Query OK, 0 rows affected (0.06 sec) session1> insert into test.t1(c1,c2) values(10512475, 1),(10512475, 2),(10512476, 1),(10512476, 2),(10512477, 1),(10512477, 2); Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 session1> select * from test.t1; +----+----------+------+ | id | c1 | c2 | +----+----------+------+ | 1 | 10512475 | 1 | | 2 | 10512475 | 2 | | 3 | 10512476 | 1 | | 4 | 10512476 | 2 | | 5 | 10512477 | 1 | | 6 | 10512477 | 2 | +----+----------+------+ 6 rows in set (0.00 sec)
其次，在會話 2 中針對另外一張表執行FLUSH TABLES FOR EXPORT操作，至于為什么要執行這個操作，后續加鎖分析部分會解釋。
session2> flush tables test.t2 for export; Query OK, 0 rows affected (0.01 sec)
接著，在會話 1 中刪除表中數據。
session1> delete from test.t1; Query OK, 6 rows affected (0.02 sec)
接著分別創建三個新的會話，執行如下操作：
session3> begin; Query OK, 0 rows affected (0.00 sec) session3> insert into test.t1(c1,c2,id) values(10512475,1,100); Query OK, 1 row affected (0.01 sec) session4> begin; Query OK, 0 rows affected (0.00 sec) session4> insert into test.t1(c1,c2,id) values(10512476,1,18158557178); Query OK, 1 row affected (0.00 sec) session5> begin; Query OK, 0 rows affected (0.00 sec) session5> insert into test.t1(c1,c2,id) values(10512477,1,18158557146); Query OK, 1 row affected (0.01 sec)
繼續在會話 3 和 會話 4 中插入數據。
session3> set session innodb_lock_wait_timeout=5000; Query OK, 0 rows affected (0.00 sec) session3> insert into test.t1(c1,c2) values(10512475, 2); -- 阻塞中... session4> set session innodb_lock_wait_timeout=5000; Query OK, 0 rows affected (0.00 sec) session4> insert into test.t1(c1,c2) values(10512476, 2); -- 阻塞中...
接著在會話 2 中執行UNLOCK TABLES操作釋放表鎖。
session2> unlock tables; Query OK, 0 rows affected (0.00 sec)
在會話 5 中查看鎖等待信息。
+----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ | waiting_trx_id | blocking_trx_id | waiting_lock_mode | waiting_lock_data | blocking_lock_mode | blocking_lock_data | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ | 23228 | 23229 | X,GAP,INSERT_INTENTION | 10512477, 1, 18158557146 | S,GAP | 10512477, 1, 18158557146 | | 23225 | 23228 | X,GAP,INSERT_INTENTION | 10512476, 1, 18158557178 | S,GAP | 10512476, 1, 18158557178 | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ 2 rows in set (0.01 sec)
可以看到，該結果與重放過程中出現鎖等待時的輸出完全一致。
加鎖分析
接下來，我們重點分析一下：為什么在 RC（Read Committed）事務隔離級別下會產生 GAP 鎖？
畢竟，在大多數人的印象中，RC 隔離級別下只會存在記錄鎖，而不會出現間隙鎖。
事實上，這與 INSERT 操作之前執行的 DELETE 操作 有直接關系。
在前面的例子中，我們在 binlog 中發現，在執行 INSERT 操作之前，存在針對相同記錄的 DELETE 操作。
在 MySQL 中，DELETE 操作并不會立即物理刪除數據，而是將記錄標記為“已刪除”（delete-marked），等待后臺的 purge 線程異步清理。這意味著在邏輯刪除之后，這些記錄仍然可能暫時保留在索引頁中。
當隨后執行 INSERT 操作時，如果待插入的記錄在唯一索引上與某條“已標記刪除但尚未清除”的記錄鍵值相同，MySQL 會執行如下加鎖行為：
- 對該索引項加上 S 鎖；
- 同時，對該索引項的間隙（即該記錄與下一條記錄之間的范圍）加上 S,GAP 鎖。
下面我們通過一個簡化的實驗來驗證這一點。
實驗驗證
在前面的重放示例中，我們執行了FLUSH TABLES FOR EXPORT操作。
執行這個操作的目的，是為了暫停 purge 線程，從而保留 delete-marked 記錄，便于重現這種鎖行為。
# 會話 1：創建測試表并插入數據 session1> create table test.t1(id bigint auto_increment primary key,c1 int,c2 int,unique key(c1,c2)); Query OK, 0 rows affected (0.07 sec) session1> insert into test.t1(c1,c2) values(10512476, 1),(10512476, 2); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 session1> select * from test.t1; +----+----------+------+ | id | c1 | c2 | +----+----------+------+ | 1 | 10512476 | 1 | | 2 | 10512476 | 2 | +----+----------+------+ 2 rows in set (0.00 sec) # 會話 2：暫停 purge 線程 session2> flush tables test.t2 for export; Query OK, 0 rows affected (0.03 sec) # 會話 1：刪除數據 session1> delete from test.t1; Query OK, 2 rows affected (0.01 sec) # 會話 3：開啟事務并插入數據 session3> begin; Query OK, 0 rows affected (0.00 sec) session3> insert into test.t1(c1,c2,id) values(10512476,1,18158557178); Query OK, 1 row affected (0.01 sec) # 查看鎖信息 session3> select object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks; +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | test | t1 | NULL | TABLE | IX | GRANTED | NULL | | test | t1 | c1 | RECORD | S | GRANTED | 10512476, 1, 1 | | test | t1 | c1 | RECORD | S,GAP | GRANTED | 10512476, 2, 2 | | test | t1 | c1 | RECORD | S,GAP | GRANTED | 10512476, 1, 18158557178 | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ 4 rows in set (0.00 sec)
鎖行為解釋
在插入 (10512476, 1, 18158557178) 這條記錄時：
-
由于
(10512476, 1)仍存在于索引中（雖然被標記刪除），MySQL 會對該記錄加上 S 鎖； -
同時，對
(10512476, 1)的下一條記錄(10512476, 2)加上 S,GAP 鎖，防止該間隙范圍內被其他事務插入新記錄； -
此外，插入的新記錄
(10512476, 1, 18158557178)還會繼承下一條記錄(10512476, 2)的 GAP 鎖。
其中：
- 前兩種鎖的加鎖邏輯是在
row_ins_scan_sec_index_for_duplicate()中實現的； - 鎖繼承的邏輯是在
lock_rec_add_to_queue()中實現的。
當執行UNLOCK TABLES后，purge 線程恢復運行，會清理掉之前的 delete-marked 記錄，對應的鎖也會被釋放。但可以看到，新插入記錄自身的 GAP 鎖仍然保留：
session2> unlock tables; Query OK, 0 rows affected (0.05 sec) session3> select object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks; +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | test | t1 | NULL | TABLE | IX | GRANTED | NULL | | test | t1 | c1 | RECORD | S,GAP | GRANTED | 10512476, 1, 18158557178 | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ 2 rows in set (0.01 sec)
優化方案
針對上面分析的鎖等待案例，優化主要可以從應用側和數據庫側兩方面入手。
1. 應用側優化
應用層面的改進主要集中在索引設計與更新邏輯上：
- 將原本的唯一索引改為普通二級索引。
- 將自增主鍵去掉，直接用原來的唯一索引列作為主鍵。與普通唯一索引不同，主鍵在插入時，即使遇到已經刪除的記錄，也不會額外加 S,GAP 鎖。
- 優化更新邏輯。盡量避免通過
DELETE + INSERT的方式更新數據，可以考慮使用UPDATE或者其他業務邏輯調整，以減少對間隙鎖的觸發。
2. 數據庫側優化
將replica_preserve_commit_order設置為 OFF，允許從庫在遇到事務等待環路時，獨立提交事務，而無需等待其他事務完成。
不過需要注意的是，如果使用的 Group Replication，會要求該參數必須為 ON。
下表展示了不同方案下的從庫重放性能對比：
| 方案 | 三次平均執行時間（秒） |
|---|---|
| 唯一索引 + replica_parallel_workers = 8 | 359.06 |
| 唯一索引 + replica_parallel_workers = 1 | 83.07 |
| 普通索引 + replica_parallel_workers = 8 | 33.50 |
| 唯一索引 + replica_parallel_workers = 8 + replica_preserve_commit_order = OFF | 21.11 |
參考資料
- https://help.aliyun.com/zh/polardb/polardb-for-mysql/resolve-the-unique-key-check-problem-in-mysql
- http://mysql.taobao.org/monthly/2015/06/02/
- https://zhuanlan.zhihu.com/p/28797400192
- https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html
原文出處：https://mp.weixin.qq.com/s/7uP5_4Zhfz3gY5xIomC3cg?mpshare=1&scene=1&srcid=1020UolVUkMFTgUl7REy5KcH&sharer_shareinfo=db92b43b641fed358e2f2d36b4f173ac&sharer_shareinfo_first=db92b43b641fed358e2f2d36b4f173ac#rd
浙公網安備 33010602011771號