MySQL行级锁初探
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)
