MySQL行级锁初探

版本：8.0.29

最近研究MySQL行级锁发现一个有趣的东东。

正常情况下，对于表级锁来说，对于会话持有某个表的锁（无论什么类型），可以在performance_schema.metadata_locks中查到会话持有锁的信息。

而对于会话对表的DML操作涉及的每一行，竟然都能在数据字典performance_schema.data_locks中查询到持有行锁的会话信息，这在Oracle中是没有的。

防爬虫：https://www.cnblogs.com/PiscesCanon/p/17308101.html 

如下：

开启两个会话connID=85（观察者）和connID=86（操作会话）。

connID=86查看测试表的信息，connID=85查看持有行锁会话信息：

connID=86:
(root@localhost 21:39:16) [performance_schema](86)> select count(*) from zkm.test where id=1234;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.81 sec)

connID=85:
(root@localhost 21:39:22) [performance_schema](85)> SELECT * FROM performance_schema.data_locks WHERE LOCK_DATA is not null  \G
Empty set (0.00 sec)

其中，id=1234共两条数据，现在update这两条数据，再看看data_locks的信息：

connID=86:
(root@localhost 21:43:19) [performance_schema](86)> update zkm.test set id=1234 where id=1234;
Query OK, 0 rows affected (2.36 sec)
Rows matched: 2  Changed: 0  Warnings: 0

connID=85:
(root@localhost 21:44:32) [performance_schema](85)> SELECT * FROM performance_schema.data_locks WHERE LOCK_DATA is not null  \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139931197502248:409:1928:367:46912701287312
ENGINE_TRANSACTION_ID: 30127
            THREAD_ID: 318
             EVENT_ID: 123
        OBJECT_SCHEMA: zkm
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 46912701287312
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000001E93903
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139931197502248:409:1928:368:46912701287312
ENGINE_TRANSACTION_ID: 30127
            THREAD_ID: 318
             EVENT_ID: 123
        OBJECT_SCHEMA: zkm
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 46912701287312
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000001E93904
2 rows in set (0.00 sec)

可以看出，zkm.test的两行记录都有对应一条在performance_schema.data_locks的记录，可以看到持有行锁的会话的THREAD_ID之类的信息。

特别注意的是：LOCK_STATUS值为"GRANTED"才表示正持有锁。

保持上边会话update不commit，现在开多另外个会话connID=87，执行同一条update：

connID=87:
(root@localhost 21:46:29) [performance_schema](87)> update zkm.test set id=1234 where id=1234;
...等待...

connID=85:(排除connID=86会话的thread_id是318的信息)
(root@localhost 21:50:15) [performance_schema](85)> SELECT * FROM performance_schema.data_locks WHERE LOCK_DATA is not null and THREAD_ID!=318 \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139931197503056:409:1928:367:46912561630672
ENGINE_TRANSACTION_ID: 30128
            THREAD_ID: 319
             EVENT_ID: 120
        OBJECT_SCHEMA: zkm
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 46912561630672
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 0x000001E93903
1 row in set (0.01 sec)

LOCK_STATUS值为“WAITING”。

connID=87会话执行update被阻塞，单单从performance_schema.data_locks只知道想要持有某行的行锁而进入等待“WAITING”。

要查看查看等待的行锁阻塞和被阻塞这信息，需要查看：performance_schema.data_lock_waits(不推荐)或者sys.innodb_lock_waits。

--根据实际情况可以添加where过滤数据
(root@localhost 22:07:54) [performance_schema](85)> select * from performance_schema.data_lock_waits \G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 139931197503056:409:1928:367:46912561630672
REQUESTING_ENGINE_TRANSACTION_ID: 30128
            REQUESTING_THREAD_ID: 319
             REQUESTING_EVENT_ID: 120
REQUESTING_OBJECT_INSTANCE_BEGIN: 46912561630672
         BLOCKING_ENGINE_LOCK_ID: 139931197502248:409:1928:367:46912701287312
  BLOCKING_ENGINE_TRANSACTION_ID: 30127
              BLOCKING_THREAD_ID: 318
               BLOCKING_EVENT_ID: 123
  BLOCKING_OBJECT_INSTANCE_BEGIN: 46912701287312
1 row in set (0.00 sec)

(root@localhost 22:07:55) [performance_schema](85)> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2023-04-11 21:46:33
                    wait_age: 00:21:24
               wait_age_secs: 1284
                locked_table: `zkm`.`test`
         locked_table_schema: zkm
           locked_table_name: test
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 30128
         waiting_trx_started: 2023-04-11 21:45:22
             waiting_trx_age: 00:22:35
     waiting_trx_rows_locked: 4
   waiting_trx_rows_modified: 0
                 waiting_pid: 87
               waiting_query: update zkm.test set id=1234 where id=1234
             waiting_lock_id: 139931197503056:409:1928:367:46912561630672
           waiting_lock_mode: X,REC_NOT_GAP
             blocking_trx_id: 30127
                blocking_pid: 86
              blocking_query: NULL
            blocking_lock_id: 139931197502248:409:1928:367:46912701287312
          blocking_lock_mode: X,REC_NOT_GAP
        blocking_trx_started: 2023-04-11 21:39:19
            blocking_trx_age: 00:28:38
    blocking_trx_rows_locked: 2
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 86
sql_kill_blocking_connection: KILL 86
1 row in set (0.03 sec)

其中，对于sys.innodb_lock_waits来说，warting_pid=87&&blocking_pid=86表示会话87被86阻塞。

知道了86是始作俑者，就可以去对该会话做其他调查，看会话正在做什么，是否卡住或者被其他会话阻塞等。

对于元数据锁（其实就是表级锁）MDL，持有者信息可以查询performance_schema.metadata_locks，阻塞队列可以查询sys.schema_table_lock_waits。

而对于行级锁，持有者信息可以查询performance_schema.data_locks，阻塞队列可以查询performance_schema.data_lock_waits(不推荐)或者sys.innodb_lock_waits。

嗯，合理的雅痞。

参考文档：

How to Investigate InnoDB Lock Issues? (文档 ID 1531774.1)