MySQL的共享鎖阻塞會話案例淺析輸入日誌標題

瀟湘隱者發表於2020-09-23
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     | <derived2> | 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     | <derived2> | 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：

 

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;

 

 

參考資料：

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

https://www.percona.com/blog/2017/09/25/avoid-shared-locks-from-subqueries-when-possible/

 

 

 

原因是這樣的，共享鎖（S）鎖一般是鎖定讀取的行，但是你這個測試案例裡面，為什麼出現了阻塞呢？其實還是因為這樣寫SQL，導致執行計劃裡面通過索引讀取了tableB中所有的行，所以導致會話2被阻塞。你可以看執行計劃（MySQL 8也是這種情況），

相關文章