主頁 > 資料庫 > MySQL的共享鎖阻塞會話案例淺析

MySQL的共享鎖阻塞會話案例淺析

2020-09-23 10:16:30 資料庫

 

 

這是問題是一個網友遇到的問題：一個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/










      
  

      轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/111057.html
      
標籤：其他 

      
上一篇：Oracle中關于to_date(),to_char(),to_number()函式的用法
下一篇：軟體測驗

     




  

    

      

        

      

    

    

      
標籤雲

      

        
    其他(157675)        Python(38076)        JavaScript(25376)        Java(17977)        C(15215)        區塊鏈(8255)        C＃(7972)        AI(7469)        爪哇(7425)        MySQL(7132)        html(6777)        基礎類(6313)        sql(6102)        熊猫(6058)        PHP(5869)        数组(5741)        R(5409)        Linux(5327)        反应(5209)        腳本語言(PerlPython)(5129)        非技術區(4971)        Android(4554)        数据框(4311)        css(4259)        节点.js(4032)        C語言(3288)        json(3245)        列表(3129)        扑(3119)        C++語言(3117)        安卓(2998)        打字稿(2995)        VBA(2789)        Java相關(2746)        疑難問題(2699)        细绳(2522)        單片機工控(2479)        iOS(2429)        ASP.NET(2402)        MongoDB(2323)        麻木的(2285)        正则表达式(2254)        字典(2211)        循环(2198)        迅速(2185)        擅长(2169)        镖(2155)        功能(1967)        .NET技术(1958)        Web開發(1951)        python-3.x(1918)        HtmlCss(1915)        弹簧靴(1913)        C++(1909)        xml(1889)        PostgreSQL(1872)        .NETCore(1853)        谷歌表格(1846)        Unity3D(1843)        for循环(1842)        

      

    

    

      
      

        









      

    

    

      
熱門瀏覽

      

        
    

  • 
        
     GPU虛擬機創建時間深度優化
          
    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......
    
           uj5u.com 2020-09-10 06:09:13 more
    
      
  • 
        
     可編程網卡芯片在滴滴云網路的應用實踐
          
    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......
    
           uj5u.com 2020-09-10 06:10:21 more
    
      
  • 
        
     滴滴資料通道服務演進之路
          
    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......
    
           uj5u.com 2020-09-10 06:11:05 more
    
      
  • 
        
     滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三
          
    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......
    
           uj5u.com 2020-09-10 06:11:29 more
    
      
  • 
        
     MPP (Massively Parallel Processing)大規模并行處理
          
    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......
    
           uj5u.com 2020-09-10 06:11:41 more
    
      
  • 
        
     滴滴資料倉庫指標體系建設實踐
          
    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......
    
           uj5u.com 2020-09-10 06:12:52 more
    
      
  • 
        
     單表千萬行資料庫 LIKE 搜索優化手記
          
    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......
    
           uj5u.com 2020-09-10 06:13:25 more
    
      
  • 
        
     滴滴Ceph分布式存盤系統優化之鎖優化
          
    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......
    
           uj5u.com 2020-09-10 06:14:51 more
    
      
  • 
        
     es~通過ElasticsearchTemplate進行聚合~嵌套聚合
          
    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......
    
           uj5u.com 2020-09-10 06:14:59 more
    
      
  • 
        
     爬蟲日志監控 -- Elastc Stack（ELK）部署
          
    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......
    
           uj5u.com 2020-09-10 06:15:05 more
    
      
    •         

      

    

    

      
      

        

      

    



      
最新发布

      

        
    

  • 
        
     day02-2-商鋪查詢快取
          
    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......
    
           uj5u.com 2023-04-20 08:33:24 more
    
      
  • 
        
     MySQL中binlog備份腳本分享
          
    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......
    
           uj5u.com 2023-04-20 08:28:06 more
    
      
  • 
        
     day02-短信登錄
          
    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......
    
           uj5u.com 2023-04-20 08:27:27 more
    
      
  • 
        
     快取與資料庫雙寫一致性幾種策略分析
          
    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......
    
           uj5u.com 2023-04-20 08:26:48 more
    
      
  • 
        
     sql陳述句優化
          
    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗  ......
    
           uj5u.com 2023-04-20 08:26:35 more
    
      
  • 
        
     云時代，MySQL到ClickHouse資料同步產品對比推薦
          
    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......
    
           uj5u.com 2023-04-20 08:26:29 more
    
      
  • 
        
     sql陳述句優化
          
    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗  ......
    
           uj5u.com 2023-04-20 08:25:13 more
    
      
  • 
        
     Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)
          
    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......
    
           uj5u.com 2023-04-20 08:24:54 more
    
      
  • 
        
     day02-2-商鋪查詢快取
          
    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......
    
           uj5u.com 2023-04-20 08:24:03 more
    
      
  • 
        
     day02-短信登錄
          
    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......
    
           uj5u.com 2023-04-20 08:23:11 more
    
      
    •         

      

    

    
    

      
      

        

      

    

  


  

    

      
Copyright © 2010-2020 有解無憂