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)

 

 

 

 

 

   从上图的信息中，我们可以看出事务（trx_id=1261156958）处于等待状态，TRX_STATE是LOCK WAIT，表示当前事务事务正在等待锁资源的获取，通过lock_id，我们可以知道，事务在表空间ID为1678（即表tableB对应的表空间）,页码值为4，lock_rec=2（也就是id=100&bill_id=1）的记录上加了共享锁，而恰巧事务（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表呢？

 

 

 

那么现在在有个问题：共享锁的粒度是什么粒度？ 答案是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下是一致的），格式化对应的执行计划，你会有更形象、直观的认识。

 

 

 

下面我们再改变一下实验中的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优化器始终走索引扫描。

 

其实这个是MySQL长期以来的一个Bug（Bug #29508068），而这个Bug刚好在MySQL 8.0.18中被Fix掉了（连我都觉得太巧了，让人震惊的巧合！！！），具体参考官方文档https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

 

   InnoDB: An unnecessary next key lock was taken when performing a SELECT...FOR [SHARE|UPDATE] query with a WHERE condition that specifies a range, causing one too many rows to be locked. The most common occurrences of this issue have been addressed so that only rows and gaps that intersect the searched range are locked. (Bug #29508068)

 

    更多详细信息参考https://blog.csdn.net/n88Lpo/article/details/105721033

 

 

 

但是在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>

 

 

其实我们从performance_schema.data_locks中看到，bill_id='3'的记录即使没有被读取，但是也加了共享锁，而bill_id=‘4’的记录因为没有加上共享锁，所以会话2删除这行记录时，申请X锁时，就不会被阻塞。

 

 

 

如果继续上面的实验，将会话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

 

 

那么为什么在表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/