MySQL的共享鎖阻塞會話案例淺析
這是問題是一個網友遇到的問題：一個UPDATE語句產生的共享鎖阻塞了其他會話的案例，對於這個案例，我進一步分析、總結和衍化了相關問題。下面分析如有不對的地方，敬請指正。下面是初始化環境和資料的指令碼。
–
– Table structure for table tableA
DROP TABLE IF EXISTS tableA;
CREATE TABLE tableA (
id varchar(10) NOT NULL,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
–
– Dumping data for table tableA
LOCK TABLES tableA WRITE;
INSERT INTO tableA VALUES (‘1’,‘11’),(‘2’,‘22’);
UNLOCK TABLES;
–
– Table structure for table tableB
DROP TABLE IF EXISTS tableB;
CREATE TABLE tableB (
id varchar(10) NOT NULL,
bill_id varchar(10) DEFAULT NULL,
update_time bigint(12) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_bill_id (bill_id)
) ENGINE=InnoDB;
–
– Dumping data for table tableB
LOCK TABLES tableB WRITE;
/*!40000 ALTER TABLE tableB DISABLE KEYS /;
INSERT INTO tableB VALUES (‘100’,‘1’,1586880000000),(‘200’,‘2’,1586793600000),(‘300’,‘2’,1586880000000),(‘400’,‘2’,1586880000000),(‘500’,‘3’,1586990000000),(‘600’,‘4’ ,1586990000000);
/!40000 ALTER TABLE tableB ENABLE KEYS */;
UNLOCK TABLES;
下面我們先通過實驗模擬一下這個阻塞問題，事務的級別為預設的可重複讀級別（Repeatable Read），如下所示：
實驗環境： MySQL 5.6.25
會話1（連線ID=52）在autocommit=0下面，執行一個update語句
mysql> select connection_id() from dual;
±----------------+
| connection_id() |
±----------------+
| 52 |
±----------------+
1 row in set (0.00 sec)
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE tableA a
-> LEFT JOIN
-> (SELECT
-> bill_id,MAX(update_time)
-> FROM
-> tableB
-> GROUP BY bill_id) b ON a.id = b.bill_id
-> SET
-> a.name = ‘abcd’
-> WHERE
-> a.id = ‘2’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
會話2（連線ID=54）執行一個delete語句被阻塞
mysql> select connection_id() from dual;
±----------------+
| connection_id() |
±----------------+
| 54 |
±----------------+
1 row in set (0.00 sec)
mysql> delete from tableB where bill_id=‘1’;
會話3中進行分析、檢視這些阻塞、鎖等相關資訊，如下所示：
mysql> SELECT b.trx_mysql_thread_id AS ‘blocked_thread_id’
-> ,b.trx_query AS ‘blocked_sql_text’
-> ,c.trx_mysql_thread_id AS ‘blocker_thread_id’
-> ,c.trx_query AS ‘blocker_sql_text’
-> ,( Unix_timestamp() - Unix_timestamp(c.trx_started) )
-> AS ‘blocked_time’
-> FROM information_schema.innodb_lock_waits a
-> INNER JOIN information_schema.innodb_trx b
-> ON a.requesting_trx_id = b.trx_id
-> INNER JOIN information_schema.innodb_trx c
-> ON a.blocking_trx_id = c.trx_id
-> WHERE ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4;
±------------------±-------------------------------------±------------------±-----------------±-------------+
| blocked_thread_id | blocked_sql_text | blocker_thread_id | blocker_sql_text | blocked_time |
±------------------±-------------------------------------±------------------±-----------------±-------------+
| 54 | delete from tableB where bill_id=‘1’ | 52 | NULL | 39 |
±------------------±-------------------------------------±------------------±-----------------±-------------+
1 row in set (0.01 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: 1261156958
trx_state: LOCK WAIT
trx_started: 2020-09-21 07:05:36
trx_requested_lock_id: 1261156958:1678:4:2
trx_wait_started: 2020-09-21 07:05:36
trx_weight: 2
trx_mysql_thread_id: 54
trx_query: delete from tableB where bill_id=‘1’
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1261156943
trx_state: RUNNING
trx_started: 2020-09-21 07:05:28
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 6
trx_mysql_thread_id: 52
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 5
trx_lock_memory_bytes: 1184
trx_rows_locked: 14
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G;
*************************** 1. row ***************************
lock_id: 1261156958:1678:4:2
lock_trx_id: 1261156958
lock_mode: X
lock_type: RECORD
lock_table: test.tableB
lock_index: idx_bill_id
lock_space: 1678
lock_page: 4
lock_rec: 2
lock_data: ‘1’, ‘100’
*************************** 2. row ***************************
lock_id: 1261156943:1678:4:2
lock_trx_id: 1261156943
lock_mode: S
lock_type: RECORD
lock_table: test.tableB
lock_index: idx_bill_id
lock_space: 1678
lock_page: 4
lock_rec: 2
lock_data: ‘1’, ‘100’
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 1261156958
requested_lock_id: 1261156958:1678:4:2
blocking_trx_id: 1261156943
blocking_lock_id: 1261156943:1678:4:2
1 row in set (0.00 sec)
clip_image001[4]
從上圖的資訊中，我們可以看出事務（trx_id=1261156958）處於等待狀態，TRX_STATE是LOCK WAIT，表示當前事務事務正在等待鎖資源的獲取，通過lock_id，我們可以知道，事務在表空間ID為1678（即表tableB對應的表空間）,頁碼值為4，堆號2的記錄上加了共享鎖，而恰巧事務（trx_id=1261156943）在這些記錄上擁有共享鎖（S),導致事務事務（trx_id=1261156958）處於等待狀態。
我們知道共享鎖(S)跟排他鎖(X)是的相容關係如下圖所示，那麼為什麼會話1（執行緒ID=52）在表tableB的的bill_id='1’持有共享鎖呢？其實如果你修改一下實驗條件，你會發現delete任意記錄都會被阻塞（例如delete from tableB where bill_id=‘4’;），網友的問題是為什麼這裡共享鎖鎖定了整個tableB表呢？
clip_image002[4]
那麼現在在有個問題：共享鎖的粒度是什麼粒度？ 答案是InnoDB儲存引擎中，共享鎖的粒度是行級別的。如下資料所示：
Shared and Exclusive Locks
InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.
· A shared (S) lock permits the transaction that holds the lock to read a row.
· An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:
· A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
· A request by T2 for an X lock cannot be granted immediately.
If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.
那麼也就是說會話1的UPDATE語句對錶tableB中的所有行加了共享鎖，為什麼會這樣呢？ 其實共享鎖（S）鎖一般是鎖定讀取的行。那麼會話1中的SQL執行計劃，肯定讀取了tableB中所有的行，我們觀察執行計劃發現，優化器通過對索引idx_bill_id掃描，讀取了此表的6條記錄。這個也是業務邏輯使然。
mysql> explain
-> UPDATE tableA a
-> LEFT JOIN
-> (SELECT
-> bill_id,MAX(update_time)
-> FROM
-> tableB
-> GROUP BY bill_id) b ON a.id = b.bill_id
-> SET
-> a.name = ‘abcd’
-> WHERE
-> a.id = ‘2’;
±—±------------±-----------±------±--------------±------------±--------±------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-----------±------±--------------±------------±--------±------±-----±------------+
| 1 | PRIMARY | a | const | PRIMARY | PRIMARY | 12 | const | 1 | NULL |
| 1 | PRIMARY | | ref | <auto_key0> | <auto_key0> | 13 | const | 0 | Using where |
| 2 | DERIVED | tableB | index | idx_bill_id | idx_bill_id | 13 | NULL | 6 | NULL |
±—±------------±-----------±------±--------------±------------±--------±------±-----±------------+
3 rows in set (0.00 sec)
如果在MySQL 8下面（MySQL 8.0.18下的實驗結果跟MySQL 5.6.25下是一致的），格式化對應的執行計劃，你會有更形象、直觀的認識。
clip_image003[4]
下面我們再改變一下實驗中的SQL語句，修改業務邏輯，對比看看一下實驗效果。
會話1：
UPDATE tableA a
LEFT JOIN
(SELECT
bill_id,MAX(update_time)
FROM
tableB
WHERE bill_id <=‘2’
GROUP BY bill_id) b ON a.id = b.bill_id
SET
a.name = ‘abcd’
WHERE
a.id = ‘2’;
會話2：
delete from tableB where bill_id=‘4’;
照理來說，會話1中的SQL，在表tableB上，應該走索引區間掃描（rang），不會對bill_id=4的記錄加上共享鎖（S), 會話2不應該被會話1阻塞。然而實際情況：在MySQL 5.6.25中，我們實驗測試發現會話1還是會阻塞會話2，因為會話1的執行計劃還是走索引掃描，對錶tableB中的6行記錄加了共享鎖，如下截圖所示，即使更新統計資訊也好，重建索引也罷，MySQL優化器始終走索引掃描。不清楚為什麼會這樣。
clip_image004[4]
但是在MySQL 8.0.18中，就會發現會話1不會阻塞會話2，從執行計劃來看，在tableB上對索引idx_bill_id進行索引範圍掃描，讀取記錄有4行（bill_id<=2)。也就是說這4行上加上了共享鎖。
mysql> explain
-> UPDATE tableA a
-> LEFT JOIN
-> (SELECT
-> bill_id,MAX(update_time)
-> FROM
-> tableB
-> WHERE bill_id <=‘2’
-> GROUP BY bill_id) b ON a.id = b.bill_id
-> SET
-> a.name = ‘abcd’
-> WHERE
-> a.id = ‘2’;
±—±------------±-----------±-----------±------±--------------±------------±--------±------±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±-----------±-----------±------±--------------±------------±--------±------±-----±---------±------------+
| 1 | UPDATE | a | NULL | const | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | | NULL | ref | <auto_key0> | <auto_key0> | 13 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | tableB | NULL | range | idx_bill_id | idx_bill_id | 13 | NULL | 4 | 100.00 | Using where |
±—±------------±-----------±-----------±------±--------------±------------±--------±------±-----±---------±------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain format=tree
-> UPDATE tableA a
-> LEFT JOIN
-> (SELECT
-> bill_id,MAX(update_time)
-> FROM
-> tableB
-> WHERE bill_id <=‘2’
-> GROUP BY bill_id) b ON a.id = b.bill_id
-> SET
-> a.name = ‘abcd’
-> WHERE
-> a.id = ‘2’;
±-----------------------------------------------------------------------------------------------+
| EXPLAIN |
±-----------------------------------------------------------------------------------------------+
| -> Update a
-> Nested loop left join
-> Rows fetched before execution
-> Index lookup on b using <auto_key0> (bill_id=‘2’)
-> Materialize
-> Group aggregate: max(tableB.update_time)
-> Filter: (tableB.bill_id <= ‘2’) (cost=2.06 rows=4)
-> Index range scan on tableB using idx_bill_id (cost=2.06 rows=4)
|
±----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
clip_image005[4]
其實我們從performance_schema.data_locks中看到，bill_id='3’的記錄即使沒有被讀取，但是也加了共享鎖，而bill_id=‘4’的記錄因為沒有加上共享鎖，所以會話2刪除這行記錄時，申請X鎖時，就不會被阻塞。
clip_image006[4]
如果繼續上面的實驗，將會話2的SQL修改一下
delete from tableB where bill_id=‘3’;
然後我們按照下面的步驟測試實驗。
會話1：
mysql> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 41 |
±----------------+
1 row in set (0.00 sec)
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE tableA a
-> LEFT JOIN
-> (SELECT
-> bill_id,MAX(update_time)
-> FROM
-> tableB
-> WHERE bill_id <=‘2’
-> GROUP BY bill_id) b ON a.id = b.bill_id
-> SET
-> a.name = ‘abcd’
-> WHERE
-> a.id = ‘2’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
會話2：
mysql> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 42 |
±----------------+
1 row in set (0.00 sec)
mysql> select * from tableB;
±----±--------±--------------+
| id | bill_id | update_time |
±----±--------±--------------+
| 100 | 1 | 1586880000000 |
| 200 | 2 | 1586793600000 |
| 300 | 2 | 1586880000000 |
| 400 | 2 | 1586880000000 |
| 500 | 3 | 1586990000000 |
±----±--------±--------------+
5 rows in set (0.00 sec)
mysql> delete from tableB where bill_id=‘3’;
此時你會發現會話1阻塞了會話2. 那麼我來檢視一下事務相關的阻塞和鎖相關的資訊，如下所示：
會話3：
mysql> select thread_id, processlist_id from performance_schema.threads where PROCESSLIST_ID in(41,42);
±----------±---------------+
| THREAD_ID | PROCESSLIST_ID |
±----------±---------------+
| 80 | 41 |
| 81 | 42 |
±----------±---------------+
2 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: 7979252
trx_state: LOCK WAIT
trx_started: 2020-09-22 10:50:00
trx_requested_lock_id: 139958870846928:33:5:6:139958757162504
trx_wait_started: 2020-09-22 10:50:00
trx_weight: 2
trx_mysql_thread_id: 42
trx_query: delete from tableB where bill_id=‘3’
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 7979251
trx_state: RUNNING
trx_started: 2020-09-22 10:49:57
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 6
trx_mysql_thread_id: 41
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 2
trx_lock_structs: 5
trx_lock_memory_bytes: 1136
trx_rows_locked: 11
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT ENGINE
-> ,ENGINE_LOCK_ID
-> ,ENGINE_TRANSACTION_ID
-> ,THREAD_ID
-> ,EVENT_ID
-> ,OBJECT_NAME
-> ,INDEX_NAME
-> ,LOCK_TYPE
-> ,LOCK_MODE
-> ,LOCK_STATUS
-> ,LOCK_DATA
-> FROM performance_schema.data_locks;
±-------±---------------------------------------±----------------------±----------±---------±------------±------------±----------±--------------±------------±-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
±-------±---------------------------------------±----------------------±----------±---------±------------±------------±----------±--------------±------------±-----------+
| INNODB | 139958870846928:1090:139958757165432 | 7979252 | 81 | 34 | tableB | NULL | TABLE | IX | GRANTED | NULL |
| INNODB | 139958870846928:33:5:6:139958757162504 | 7979252 | 81 | 34 | tableB | idx_bill_id | RECORD | X | WAITING | ‘3’, ‘500’ |
| INNODB | 139958870846056:1088:139958757159480 | 7979251 | 80 | 42 | tableA | NULL | TABLE | IX | GRANTED | NULL |
| INNODB | 139958870846056:31:4:9:139958757156440 | 7979251 | 80 | 42 | tableA | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | ‘2’ |
| INNODB | 139958870846056:1090:139958757159568 | 7979251 | 80 | 42 | tableB | NULL | TABLE | IS | GRANTED | NULL |
| INNODB | 139958870846056:33:5:2:139958757156784 | 7979251 | 80 | 42 | tableB | idx_bill_id | RECORD | S | GRANTED | ‘1’, ‘100’ |
| INNODB | 139958870846056:33:5:3:139958757156784 | 7979251 | 80 | 42 | tableB | idx_bill_id | RECORD | S | GRANTED | ‘2’, ‘200’ |
| INNODB | 139958870846056:33:5:4:139958757156784 | 7979251 | 80 | 42 | tableB | idx_bill_id | RECORD | S | GRANTED | ‘2’, ‘300’ |
| INNODB | 139958870846056:33:5:5:139958757156784 | 7979251 | 80 | 42 | tableB | idx_bill_id | RECORD | S | GRANTED | ‘2’, ‘400’ |
| INNODB | 139958870846056:33:5:6:139958757156784 | 7979251 | 80 | 42 | tableB | idx_bill_id | RECORD | S | GRANTED | ‘3’, ‘500’ |
| INNODB | 139958870846056:33:4:2:139958757157128 | 7979251 | 80 | 42 | tableB | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | ‘100’ |
| INNODB | 139958870846056:33:4:3:139958757157128 | 7979251 | 80 | 42 | tableB | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | ‘200’ |
| INNODB | 139958870846056:33:4:4:139958757157128 | 7979251 | 80 | 42 | tableB | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | ‘300’ |
| INNODB | 139958870846056:33:4:5:139958757157128 | 7979251 | 80 | 42 | tableB | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | ‘400’ |
| INNODB | 139958870846056:33:4:6:139958757157128 | 7979251 | 80 | 42 | tableB | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | ‘500’ |
±-------±---------------------------------------±----------------------±----------±---------±------------±------------±----------±--------------±------------±-----------+
15 rows in set (0.00 sec)
mysql> SELECT * FROM performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 139958870846928:33:5:6:139958757162504
REQUESTING_ENGINE_TRANSACTION_ID: 7979252
REQUESTING_THREAD_ID: 81
REQUESTING_EVENT_ID: 34
REQUESTING_OBJECT_INSTANCE_BEGIN: 139958757162504
BLOCKING_ENGINE_LOCK_ID: 139958870846056:33:5:6:139958757156784
BLOCKING_ENGINE_TRANSACTION_ID: 7979251
BLOCKING_THREAD_ID: 80
BLOCKING_EVENT_ID: 42
BLOCKING_OBJECT_INSTANCE_BEGIN: 139958757156784
1 row in set (0.00 sec)
ERROR:
No query specifie
clip_image007[4]
那麼為什麼在表tableB的id=500或bill_id='3’的記錄上有共享鎖呢？ 我們來看看會話1中SQL的執行計劃，執行計劃會通過表tableB的索引idx_bill_id的區間索引掃描，讀取了4行記錄，對這4行記錄加上共享鎖。那麼為什麼id=500這條記錄上也加上了共享鎖呢？
mysql> explain format=tree
-> UPDATE tableA a
-> LEFT JOIN
-> (SELECT
-> bill_id,MAX(update_time)
-> FROM
-> tableB
-> WHERE bill_id <=‘2’
-> GROUP BY bill_id) b ON a.id = b.bill_id
-> SET
-> a.name = ‘abcd’
-> WHERE
-> a.id = ‘2’;
±-------------------------------------------------------------------------------------------------+
| EXPLAIN |
±-------------------------------------------------------------------------------------------------+
| -> Update a
-> Nested loop left join
-> Rows fetched before execution
-> Index lookup on b using <auto_key0> (bill_id=‘2’)
-> Materialize
-> Group aggregate: max(tableB.update_time)
-> Filter: (tableB.bill_id <= ‘2’) (cost=2.06 rows=4)
-> Index range scan on tableB using idx_bill_id (cost=2.06 rows=4)
|
±--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
說到這裡，就必須先簡單介紹一下Next-Key Lock，它是結合了Gap Lock和Record Lock的一種鎖定演算法，在Next-Key Lock演算法下，因為InnoDB對於行的查詢都是採用了Next-Key Lock的演算法，鎖定的不是單個值，而是一個範圍（GAP）。上面索引值有1，2，3，其記錄的GAP的區間如下：是一個左開右閉的空間：（-∞,1]，(1,2]，(2,3]，(3,+∞），該SQL語句鎖定的的記錄為bill_id <= '2’的行記錄，它還會對輔助索引下一個鍵值（bill_id=‘3’)加上Gap Lock,以及在在記錄bill_id='3’上加上共享鎖。所以在刪除bill_id='3’的記錄時，就會遇到阻塞了。
Next-Key Locks
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 15.7.4, “Phantom Rows”).
思考部分
從這個UPDATE語句中，我們可以看到其子查詢內tableB所有的行都會加上共享鎖。那麼要如何優化這個SQL呢？ 下面是一種方案，藉助臨時表，可以避免tableB上的所有記錄加上共享鎖，影響併發性。
CREATE TEMPORARY TABLE tmp_result
SELECT
bill_id,MAX(update_time)
FROM
tableB
GROUP BY bill_id;
UPDATE tableA a
LEFT JOIN
tmp_result b ON a.id = b.bill_id
SET
a.name = ‘abcd’
WHERE
a.id = ‘2’;
另外，我們還要特別留意UPDATE語句中使用子查詢的情況的，例如下面這種情況(下面是部落格Avoid Shared Locks from Subqueries When Possible中例子)
update ibreg set k=1 where id in (select id from ibcmp where id > 90000);
這樣的SQL會導致子查詢中的表，例如ibcmp，大範圍的加上共享鎖，導致DML操作被阻塞，嚴重的時候，可能產生大量的阻塞。所以可以通過下面方式優化：
方法1：
begin
select group_concat(id) into @ids from ibcmp where id > 90000;
update ibreg set k=1 where id in (@ids);
commit;
方法2：
https://mydramalist.com/list/1dOa6E94
https://mydramalist.com/list/45GnMjAL
https://mydramalist.com/list/3rWKrzw4
https://mydramalist.com/list/4a692ka1
https://mydramalist.com/list/1XzMypb3
https://mydramalist.com/list/1NkMl29L
https://mydramalist.com/list/1xr5Nqb3
https://mydramalist.com/list/45GnMjgL
https://mydramalist.com/list/3rWKrzm4
https://mydramalist.com/list/4a692km1
https://mydramalist.com/list/1XzMype3
https://mydramalist.com/list/1xr5NqD3
https://mydramalist.com/list/1zErxRQ4
https://mydramalist.com/list/10d5MPg1
https://mydramalist.com/list/LOPMNR03
https://mydramalist.com/list/4KoMaDb4
https://mydramalist.com/list/3DgDMXML
https://mydramalist.com/list/3bgrEJJ4
https://mydramalist.com/list/4q6yBe61
https://mydramalist.com/list/32ArDGXL
https://mydramalist.com/list/1R8MxqN3
https://mydramalist.com/list/LAlymkJ1
https://mydramalist.com/list/389N2GD4
https://mydramalist.com/list/47aloG8L
https://mydramalist.com/list/4YWMy8vL
https://mydramalist.com/list/LQJMPVw3
https://mydramalist.com/list/1VKM0QPL
https://mydramalist.com/list/1dOaRKA4
https://mydramalist.com/list/45GnJdgL
https://mydramalist.com/list/3rWK5lm4
https://mydramalist.com/list/4a69oDm1
https://mydramalist.com/list/1XzMODe3
https://mydramalist.com/list/1NkMXJqL
https://mydramalist.com/list/1xr5XlD3
https://mydramalist.com/list/10d5K0g1
https://mydramalist.com/list/LOPMlb03
https://mydramalist.com/list/4KoMpnb4
https://mydramalist.com/list/3DgDQZML
https://mydramalist.com/list/3bgr2jJ4
https://mydramalist.com/list/4q6yB661
https://mydramalist.com/list/1R8Mx8N3
https://mydramalist.com/list/389N29D4
https://mydramalist.com/list/47aloa8L
https://mydramalist.com/list/4YWMyWvL
https://mydramalist.com/list/LQJMPJw3
https://mydramalist.com/list/1VKM0KPL
https://mydramalist.com/list/1dOaROA4
https://mydramalist.com/list/45GnJGgL
https://mydramalist.com/list/3rWK5Wm4
https://mydramalist.com/list/4a69o6m1
https://mydramalist.com/list/1XzMOze3
https://mydramalist.com/list/1NkMXkqL
https://mydramalist.com/list/1xr5XrD3
https://mydramalist.com/list/4a69o681
https://mydramalist.com/list/1XzMOzm3
https://mydramalist.com/list/1NkMXklL
https://mydramalist.com/list/1xr5Xrq3
https://mydramalist.com/list/1zErNwE4
https://mydramalist.com/list/10d5Kpa1
https://mydramalist.com/list/LOPMlqe3
https://mydramalist.com/list/4KoMpzV4
https://mydramalist.com/list/3DgDQnkL
https://mydramalist.com/list/3bgr2874
https://mydramalist.com/list/4q6yBNo1
https://mydramalist.com/list/32ArDBEL
https://mydramalist.com/list/1R8Mxzn3
https://mydramalist.com/list/389N2ng4
https://mydramalist.com/list/47alo5nL
https://mydramalist.com/list/4YWMy6mL
https://mydramalist.com/list/LQJMPmj3
https://mydramalist.com/list/1VKM0xpL
https://mydramalist.com/list/1dOaR854
https://mydramalist.com/list/45GnJrEL
https://mydramalist.com/list/3rWK5BE4
https://mydramalist.com/list/4a69oK81
https://mydramalist.com/list/1XzMOvm3
https://mydramalist.com/list/1NkMXwlL
https://mydramalist.com/list/1xr5Xkq3
https://mydramalist.com/list/1zErNaE4
https://mydramalist.com/list/10d5Kaa1
https://mydramalist.com/list/LOPMlke3
https://mydramalist.com/list/4KoMpEV4
https://mydramalist.com/list/3bgr2A74
https://mydramalist.com/list/4q6yBKo1
https://mydramalist.com/list/32ArDXEL
https://mydramalist.com/list/1R8MxOn3
https://mydramalist.com/list/LAlymA21
https://mydramalist.com/list/389N2Pg4
https://mydramalist.com/list/47aloxnL
https://mydramalist.com/list/4YWMybmL
https://mydramalist.com/list/LQJMP2j3
https://mydramalist.com/list/1dOaRV54
https://mydramalist.com/list/45GnJXEL
https://mydramalist.com/list/3rWK5PE4
https://mydramalist.com/list/4a69o781
https://mydramalist.com/list/1XzMOKm3
https://mydramalist.com/list/1xr5Xnq3
https://mydramalist.com/list/3rWK5jP4
https://mydramalist.com/list/1XzMORq3
https://mydramalist.com/list/1xr5X6w3
https://mydramalist.com/list/3DgDQm9L
https://mydramalist.com/list/1R8MxKB3
https://mydramalist.com/list/47alojvL
https://mydramalist.com/list/1VKM0zwL
https://mydramalist.com/list/1XzMO7q3
https://mydramalist.com/list/1zErNlK4
https://mydramalist.com/list/4q6yB8g1
https://mydramalist.com/list/LAlymKE1
https://mydramalist.com/list/389N2qA4
https://mydramalist.com/list/LQJMPlR3
https://mydramalist.com/list/1dOaRpE4
https://mydramalist.com/list/3rWK5XP4
https://mydramalist.com/list/4a69oA51
https://mydramalist.com/list/1XzMOAq3
https://mydramalist.com/list/1NkMXQZL
https://mydramalist.com/list/3rWK5Xq4
https://mydramalist.com/list/4a69oAe1
https://mydramalist.com/list/1NkMXQGL
https://mydramalist.com/list/1xr5Xz93
https://mydramalist.com/list/1zErNkg4
https://mydramalist.com/list/10d5K9W1
https://mydramalist.com/list/LOPMlOo3
https://mydramalist.com/list/4KoMpeX4
https://mydramalist.com/list/3bgr20G4
https://mydramalist.com/list/4q6yBXG1
https://mydramalist.com/list/LAlym051
https://mydramalist.com/list/389N2Z94
https://mydramalist.com/list/4YWMyQ7L
https://mydramalist.com/list/1dOaR9B4
https://mydramalist.com/list/3rWK59q4
https://mydramalist.com/list/1xr5XW93
https://mydramalist.com/list/4KoMpVX4
https://mydramalist.com/list/32ArDOYL
https://mydramalist.com/list/389N2094
https://mydramalist.com/list/4YWMyx7L
https://mydramalist.com/list/1dOaRrB4
https://mydramalist.com/list/3rWK5kq4
https://mydramalist.com/list/1XzMOZk3
https://mydramalist.com/list/45GnJ02L
https://mydramalist.com/list/4a69oB01
https://mydramalist.com/list/1NkMXajL
https://mydramalist.com/list/10d5K871
https://mydramalist.com/list/4q6yBO81
https://mydramalist.com/list/1R8Mxmm3
https://mydramalist.com/list/LAlymeN1
https://mydramalist.com/list/389N2DO4
https://mydramalist.com/list/47alowAL
https://mydramalist.com/list/4YWMydzL
https://mydramalist.com/list/LQJMPZq3
https://mydramalist.com/list/1dOaRZJ4
https://mydramalist.com/list/45GnJA2L
https://mydramalist.com/list/3rWK5O74
https://mydramalist.com/list/4a69oq01
https://mydramalist.com/list/1XzMOaj3
https://mydramalist.com/list/1NkMXZjL
https://mydramalist.com/list/1zErNJD4
https://mydramalist.com/list/10d5KE71
https://mydramalist.com/list/LOPMlZN3
https://mydramalist.com/list/4KoMpZD4
https://mydramalist.com/list/3DgDQGgL
https://mydramalist.com/list/3bgr2qA4
https://mydramalist.com/list/4q6yBz81
https://mydramalist.com/list/32ArDkOL
https://mydramalist.com/list/1R8MxXm3
https://mydramalist.com/list/389N2eO4
https://mydramalist.com/list/47aloeAL
https://mydramalist.com/list/4YWMyJzL
https://mydramalist.com/list/LQJMPnq3
https://mydramalist.com/list/1VKM0DjL
https://mydramalist.com/list/1dOaR7J4
https://mydramalist.com/list/45GnJk2L
https://mydramalist.com/list/3rWK5Y74
https://mydramalist.com/list/4a69oW01
https://mydramalist.com/list/1XzMOVj3
https://mydramalist.com/list/1NkMXrjL
https://mydramalist.com/list/3rWK5Yv4
https://mydramalist.com/list/4a69oW21
https://mydramalist.com/list/1XzMOVp3
https://mydramalist.com/list/1zErNBl4
https://mydramalist.com/list/10d5Ko61
https://mydramalist.com/list/4KoMpKK4
https://mydramalist.com/list/3DgDQWJL
https://mydramalist.com/list/32ArDVlL
https://mydramalist.com/list/LAlymNw1
https://mydramalist.com/list/47alo8DL
https://mydramalist.com/list/LQJMPrz3
https://mydramalist.com/list/1dOaRBW4
https://mydramalist.com/list/45GnJ8oL
https://mydramalist.com/list/4a69oM21
https://mydramalist.com/list/1XzMOPp3
https://mydramalist.com/list/10d5KX61
https://mydramalist.com/list/4KoMpPK4
https://mydramalist.com/list/3DgDQrJL
https://mydramalist.com/list/4q6yBMr1
https://mydramalist.com/list/32ArDNlL
https://mydramalist.com/list/1R8Mxgw3
https://mydramalist.com/list/389N2zV4
https://mydramalist.com/list/47aloODL
https://mydramalist.com/list/4YWMyBXL
https://mydramalist.com/list/LQJMPgz3
https://mydramalist.com/list/1VKM0gEL
https://mydramalist.com/list/1dOaRxW4
https://mydramalist.com/list/45GnJZoL
https://mydramalist.com/list/3rWK5Mv4
https://mydramalist.com/list/4a69ol21
https://mydramalist.com/list/1XzMOgp3
https://mydramalist.com/list/1NkMXDkL
https://mydramalist.com/list/1xr5XYn3
https://mydramalist.com/list/3rWK5294
https://mydramalist.com/list/4a69oaq1
https://mydramalist.com/list/1XzMO0P3
https://mydramalist.com/list/1NkMXDDL
https://mydramalist.com/list/1xr5XYd3
https://mydramalist.com/list/1zErNKG4
https://mydramalist.com/list/LOPMlYz3
https://mydramalist.com/list/4KoMpdr4
https://mydramalist.com/list/3bgr2nV4
https://mydramalist.com/list/4q6yBoV1
https://mydramalist.com/list/LAlym7v1
https://mydramalist.com/list/47aloWrL
https://mydramalist.com/list/4YWMyvKL
https://mydramalist.com/list/LQJMPQ83
https://mydramalist.com/list/1dOaRqV4
https://mydramalist.com/list/45GnJY7L
https://mydramalist.com/list/4a69owq1
https://mydramalist.com/list/1XzMOXP3
https://mydramalist.com/list/1xr5Xod3
https://mydramalist.com/list/1zErN0G4
https://mydramalist.com/list/10d5KNn1
https://mydramalist.com/list/LOPMl7z3
https://mydramalist.com/list/4KoMpyr4
https://mydramalist.com/list/3DgDQyNL
https://mydramalist.com/list/3bgr2RV4
https://mydramalist.com/list/4q6yB0V1
https://mydramalist.com/list/32ArDxzL
https://mydramalist.com/list/1R8Mx5J3
https://mydramalist.com/list/389N26R4
https://mydramalist.com/list/47aloVrL
https://mydramalist.com/list/4YWMylKL
https://mydramalist.com/list/LQJMPB83
https://mydramalist.com/list/1VKM0VKL
https://mydramalist.com/list/1dOaRnV4
https://mydramalist.com/list/45GnJy7L
https://mydramalist.com/list/3rWK5q94
https://mydramalist.com/list/4a69orq1
https://mydramalist.com/list/1XzMObP3
https://mydramalist.com/list/1NkMXYDL
https://mydramalist.com/list/1xr5Xdd3
https://mydramalist.com/list/45GnJyqL
https://mydramalist.com/list/3rWK5qA4
https://mydramalist.com/list/1NkMXYKL
https://mydramalist.com/list/1zErNqV4
https://mydramalist.com/list/10d5Kqx1
https://mydramalist.com/list/LOPMlwD3
https://mydramalist.com/list/4KoMpYv4
https://mydramalist.com/list/3bgr2pv4
https://mydramalist.com/list/4q6yB791
https://mydramalist.com/list/32ArDEWL
https://mydramalist.com/list/LAlym9A1
https://mydramalist.com/list/389N2Eq4
https://mydramalist.com/list/47alodNL
https://mydramalist.com/list/4YWMyodL
https://mydramalist.com/list/1VKM08JL
https://mydramalist.com/list/1dOaRgn4
https://mydramalist.com/list/45GnJVqL
https://mydramalist.com/list/3rWK5RA4
https://mydramalist.com/list/4a69oZP1
https://mydramalist.com/list/1NkMX0KL
https://mydramalist.com/list/1xr5X8a3
https://mydramalist.com/list/1zErNeV4
https://mydramalist.com/list/10d5KRx1
https://mydramalist.com/list/LOPMldD3
https://mydramalist.com/list/4KoMpGv4
https://mydramalist.com/list/3DgDQapL
https://mydramalist.com/list/4q6yBm91
https://mydramalist.com/list/LAlymZA1
https://mydramalist.com/list/LQJMPar3
https://mydramalist.com/list/1VKM0PJL
https://mydramalist.com/list/3rWK5pA4
https://mydramalist.com/list/4a69oeP1
https://mydramalist.com/list/1XzMOGw3
https://mydramalist.com/list/1NkMX9KL
https://mydramalist.com/list/45GnJqBL
https://mydramalist.com/list/3rWK5p04
https://mydramalist.com/list/4a69oeQ1
https://mydramalist.com/list/1NkMX9WL
https://mydramalist.com/list/1xr5XPv3
https://mydramalist.com/list/10d5KVP1
https://mydramalist.com/list/4KoMplE4
https://mydramalist.com/list/3DgDQ5eL
https://mydramalist.com/list/4q6yBPO1
https://mydramalist.com/list/1R8Mx0Y3
https://mydramalist.com/list/LAlymbY1
https://mydramalist.com/list/4YWMyp9L
https://mydramalist.com/list/1VKM05mL
https://mydramalist.com/list/1dOaR0d4
https://mydramalist.com/list/45GnJ6BL
https://mydramalist.com/list/3rWK5x04
https://mydramalist.com/list/4a69oGQ1
https://mydramalist.com/list/1xr5XOv3
https://mydramalist.com/list/1zErN7x4
https://mydramalist.com/list/10d5KOP1
https://mydramalist.com/list/389N2W84
https://mydramalist.com/list/47alozzL
https://mydramalist.com/list/4YWMyk9L
https://mydramalist.com/list/LQJMPqN3
https://mydramalist.com/list/1VKM0YmL
https://mydramalist.com/list/1dOaRAd4
https://mydramalist.com/list/4a69odQ1
https://mydramalist.com/list/1xr5Xbv3
https://mydramalist.com/list/45GnJxML
https://mydramalist.com/list/1xr5XAW3
https://mydramalist.com/list/1zErNYJ4
https://mydramalist.com/list/4KoMpAY4
https://mydramalist.com/list/3DgDQAqL
https://mydramalist.com/list/3bgr2Mk4
https://mydramalist.com/list/4q6yBDZ1
https://mydramalist.com/list/32ArDm5L
https://mydramalist.com/list/LAlymVp1
https://mydramalist.com/list/389N2mr4
https://mydramalist.com/list/4YWMy2AL
https://mydramalist.com/list/1dOaR2v4
https://mydramalist.com/list/45GnJeML
https://mydramalist.com/list/3rWK5ez4
https://mydramalist.com/list/1XzMO5K3
https://mydramalist.com/list/1NkMXGeL
https://mydramalist.com/list/1xr5X7W3
https://mydramalist.com/list/1zErNZJ4
https://mydramalist.com/list/10d5KQo1
https://mydramalist.com/list/LOPMlzY3
https://mydramalist.com/list/4KoMpNY4
https://mydramalist.com/list/3DgDQ9qL
https://mydramalist.com/list/3bgr2xk4
https://mydramalist.com/list/4q6yBZZ1
https://mydramalist.com/list/32ArDg5L
https://mydramalist.com/list/1R8Mxwb3
https://mydramalist.com/list/389N2gr4
https://mydramalist.com/list/4YWMy9AL
https://mydramalist.com/list/1VKM0jkL
begin;
select id into outfile ‘/tmp/id.csv’ from ibcmp where id > 90000;
create temporary table t (id int unsigned not null) engine=innodb;
load data infile ‘/tmp/id.csv’ into table t;
update ibreg inner join t on ibreg.id = t.id;
commit;
相關文章
- MySQL的共享鎖阻塞會話案例淺析輸入日誌標題MySql會話
- mysql鎖與會話MySql會話
- Java偏向鎖淺析Java
- MySQL效能最佳化淺析及線上案例MySql
- MySQL的共享鎖和獨佔鎖MySql
- JAVA併發之阻塞佇列淺析Java佇列
- 淺析MySQL replace into 的用法MySql
- Oracle阻塞會話查詢Oracle會話
- 為什麼sleeping的會話會造成阻塞會話
- MySql(一) 淺析MySql索引MySql索引
- 淺析樂觀鎖與悲觀鎖
- 為什麼sleeping的會話會造成阻塞(2)會話
- SQLServer如何監控阻塞會話SQLServer會話
- 【MySQL】八、double write 淺析.MySql
- MySQL事務原理淺析MySql
- MySQL 共享鎖 (lock in share mode)，排他鎖 (for update)MySql
- MySQL鎖（四）行鎖的加鎖規則和案例MySql
- MySQL淺談（索引、鎖）MySql索引
- MySQL 由於MDL讀鎖select被阻塞MySql
- MySQL 的共享鎖和排它鎖以及自動提交MySql
- 【問答分享第一彈】MySQL鎖總結:MySQL行鎖、表鎖、排他鎖、共享鎖的特點MySql
- 《淺入淺出MySQL》表鎖 行鎖 併發插入MySql
- 淺析MySQL InnoDB的隔離級別MySql
- MySQL bit型別增加索引後查詢結果不正確案例淺析MySql型別索引
- MYSQL 對錶最大ID 搶加鎖時的阻塞分析MySql
- 關於 ReentrantLock 中鎖 lock() 和解鎖 unlock() 的底層原理淺析ReentrantLock
- MySql(四) InnoDB事務淺析MySql
- 【MySQL】死鎖案例之六MySql
- 【MySQL】死鎖案例之七MySql
- 【MySQL】死鎖案例之八MySql
- 對話 | 淺析NEO的dBFT共識演算法演算法
- 淺析MySQL事務中的redo與undoMySql
- SQLServer會話KILL不掉，一直處於KILLED/ROLLBACK狀態情形淺析SQLServer會話
- 淺析MySQL 8.0直方圖原理MySql直方圖
- MySQL死鎖案例一（回滾導致死鎖）MySql
- MySQL死鎖案例二（自增列導致死鎖）MySql
- MySQL:Innodb 一個死鎖案例MySql
- MySQL批量更新死鎖案例分析MySql