mysql锁信息查询
约 2787 字大约 9 分钟
2025-05-19
数据库版本
mysql> select version() as '数据库版本', @@transaction_isolation as '事务级别';
+-----------------+-----------------+
| 数据库版本 | 事务级别 |
+-----------------+-----------------+
| 8.4.5 | REPEATABLE-READ |
+-----------------+-----------------+
1 row in set (0.00 sec)Mysql所信息存储在系统库 information_schema 中, 注意不同版本的mysql所存储的表结构可能不同。但是万变不离其宗,都是通过事务的相关信息来查询锁信息。
数据锁分类
| 锁名称 | 英文全拼 | 简称 | 类别 |
|---|---|---|---|
| 共享锁 | Shared Locks | S 锁 | 属于行锁 |
| 排它锁 | Exclusive Locks | X 锁 | 属于行锁 |
| 意向锁 | Intension Locks | 意向共享锁+意向排它锁的组合 | |
| 意向共享锁 | Intension Shared Locks | IS 锁 | 属于表锁 |
| 意向排它锁 | Intension Exclusive Locks | IX 锁 | 属于表锁 |
| 自增锁 | AUTO-INC Locks | 在处理自增长列时的锁定行为 | |
| 临键锁 | Next-Key Locks | 记录锁+间隙锁的组合 | |
| 记录锁 | Record Locks | 仅仅把一条记录上锁 | |
| 间隙锁 | Gap Locks | GAP锁 | 对索引前后的间隙上锁，不对索引本身上锁 |
读锁定
- 共享锁: 对一条数据可以增加多条共享锁, 但是无法再增加排它锁
select * from people where id = 3 lock in share mode, 使用lock in share mode
- 排它锁: 一条数据有且只有一条排它锁
select * from people where id = 3 for update, 使用for updateupdate、delete会自动增加排它锁。
排它锁
排它锁不能与其他锁并存，若一个事务获取一个数据行的排它锁，其他事务就不能再次获取该行的其他锁，只有获取了该数据行的排它锁所在事务才能对数据进行读取、写入操作.update、delete 语句默认就是排它锁
意向锁
详细解析参见: MYSQL意向锁
提示
意向锁是隐式获取和释放的，并不需要显式的锁定语句来处理，它们是由 InnoDB 存储引擎自动管理的。它们用于表示事务在某个表或表分区上的意向，以避免不必要的冲突。
- 意向锁是一种粒度更粗的锁，用于协调并发事务对表和表中行的锁定。它们并不直接锁定行，而是指示事务在某个层次上有意向获取特定类型的锁。意向锁的引入可以减少冲突，提高并发性能.
- 意向锁的引入有助于优化锁定算法，避免了不必要的冲突，提高了并发性能。在事务操作过程中，当需要获取某个表的排它锁或共享锁时，先检查是否存在对应的意向锁，以减少对其他事务的干扰
意向共享锁
表示事务有意向在某个表或表分区上获取共享锁（Shared Lock）多个事务可以同时持有同一个表的意向共享锁，但在获取某个表的排它锁之前，必须先释放该表的意向共享锁
意向排它锁
表示事务有意向在某个表或表分区上获取排它锁（Exclusive Lock）一个事务在获取某个表的排它锁之前，必须先获取该表的意向排它锁，然后再获取该表的意向共享锁
自增锁
Auto-Increment：自增长列的特殊锁机制，通过 innodb_autoinc_lock_mode 参数配置自增长列的锁定模式，它决定了在插入数据时，如何对自增长序列进行锁定
innodb_autoinc_lock_mode 该参数有几个可选值，如下：
- 0（Traditional）： 表示使用传统的自增锁定方式，在插入数据时，会对整个表进行排它锁定，以防止并发插入导致自增值的冲突 插入语句在执行前不可以确定具体要插入多少条记录（无法预计即将插入记录的数量），比方说使用 INSERT … SELECT、REPLACE … SELECT 或者 LOAD DATA 这种插入语句，一般是使用 AUTO-INC 锁为 AUTO_INCREMENT 修饰的列生成对应的值
- 1（Consecutive）：表示使用连续模式的自增锁定，在插入数据时，只会对自增长索引的最后一个插入行进行排它锁定，而不是整个表
- 2（Interleaved）：表示使用交错模式的自增锁定，在插入数据时，会对自增长索引的最后一个插入行进行共享锁定，而不是排它锁定。这允许多个事务并发地插入数据，提高并发性能，使用交错模式时，会导致自增值的顺序会被打乱，虽然提高了事务的并发性，但自增列的值顺序可能会被打乱，因为插入行的锁定顺序可能不是它们实际插入时的顺序，在主从复制场景下时，当 binlog_format 配置为 statement 以语句的方式存储，会造成 slave 同步 master 节点数据回放时产生错乱
在 innodb_autoinc_lock_mode 参数中，传统模式（Traditional）使用排它锁（Exclusive Lock）对整个表进行锁定；连续模式（Consecutive）只对自增长索引的最后一个插入行进行排它锁定；交错模式（Interleaved）则使用共享锁（Shared Lock）对自增长索引的最后一个插入行进行锁定 一般该参数默认值为 1：连续模式，既保证了自增值的顺序性，在插入性能上面又高于 0 传统模式
查询当前数据库的innodb_autoinc_lock_mode配置:
mysql> show variables like 'innodb_autoinc_lock_mode' ;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
1 row in set (0.076 sec)记录锁
官方类型名称：LOCK_REC_NOT_GAP ，记录锁，只对一条记录进行锁定, delete/update 会隐式增加记录锁, 记录锁也是有 S、X 锁之分的，当一个事务获取了一条记录的 S 锁后，其他事务仍然可以继续获取该记录的 S 锁，但不可以获取该记录的 X 锁；当一个事务获取一条记录的 X 锁后，其他事务既不可以获取该记录的 S 锁，也不可以获取该记录的 X 锁
间隙锁
Gap Lock 为了防止其他事务在一个范围内插入新的记录而引入的一种锁机制。通过生成间隙锁，可以确保其他事务无法在已有记录之间插入新记录，从而维护数据的一致性和完整性。
详细解析参见: MYSQL间隙锁
数据锁互斥关系
| 锁类型 | IS(意向共享锁) | IX(意向排它锁) | S(共享锁) | X(排它锁) |
|---|---|---|---|---|
| IS(意向共享锁) | 不冲突 | 不冲突 | 不冲突 | 冲突 |
| IX(意向排它锁) | 不冲突 | 不冲突 | 冲突 | 冲突 |
| S(共享锁) | 不冲突 | 冲突 | 不冲突 | 冲突 |
| X(排它锁) | 冲突 | 冲突 | 冲突 | 冲突 |
总结: 只要预期获取的锁包含 排它属性 , 则和所有的锁冲突, 预期获取的锁包含 共享属性 , 则可以与所有共享锁共存, 但是需要注意一个特例: IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
核心信息存储
| 数据库 | 数据表 | 存储内容 |
|---|---|---|
| sys | innodb_lock_waits | 查询当前被锁SQL语句(等待获取锁的SQL语句) |
| performance_schema | events_statements_history | 数据锁的持有信息 |
| performance_schema | data_locks | 存储数据锁定信息 |
| performance_schema | data_locks_wait | 数据锁等待的相关信息 |
表数据说明
sys.innodb_lock_waits
sys.innodb_lock_waits 是一个视图, 本质是聚合了 performance_schema数据库 中相关表的信息。以一种更加方便有好的方式方便查询锁信息。
| 字段 | 含义 |
|---|---|
| wait_started | 开始等待锁释放的时间 |
| wait_age/wait_age_secs | 已经进行的锁等待时长。 |
| locked_table/locked_table_schema/locked_table_name | 锁定的数据库、数据表信息。 |
blocking_trx_id | 导致阻塞的事务ID，即持有锁的事务。 |
blocking_session_id | 持有锁的会话ID。 |
| locked_table_partition、locked_table_subpartition | 锁定的分区、子分区信息。 |
locked_index | 锁定的索引。 |
locked_type | 锁类型 TABLE：表锁 RECORD：行级锁 |
waiting_trx_id | 等待所释放的事务ID。 |
| waiting_trx_started | 开始等待的时间 |
| waiting_trx_age | 等待时长。 |
waiting_trx_rows_locked | 等待锁释放的事务锁定行数 |
waiting_trx_rows_modified | - |
waiting_pid | 等待锁的线程ID |
waiting_query | 等待锁的SQL语句 |
| waiting_lock_id | 等待锁释放的事务锁ID |
waiting_lock_mode | 预期要增加的锁类型 |
blocking_trx_id | 持有锁的事务ID |
| blocking_pid | 持有锁的线程ID |
| blocking_query | 持有锁的QUERY |
| blocking_lock_id | 当前持有锁的ID |
| blocking_lock_mode | 当前持有锁的类型: X：next-key 锁 X,REC_NOT_GAP：记录锁 X,GAP：间隙锁 |
| blocking_trx_started | 当前持有锁的事务开始时间 |
| blocking_trx_age | 持有锁的事务持续时长 |
| blocking_trx_rows_locked | 持有锁的事务锁定行数 |
| blocking_trx_rows_modified | 当前持有锁的事务修改行数 |
sql_kill_blocking_query | 杀死持有锁的事务需要执行的sql |
sql_kill_blocking_connection | 杀死持有锁的连接需要执行的sql |
示例数据如下:
mysql> SELECT * FROM sys.innodb_lock_waits \G
*************************** 1. row ***************************
wait_started: 2025-05-19 21:16:04
wait_age: 00:00:11
wait_age_secs: 11
locked_table: `blog_test`.`people`
locked_table_schema: blog_test
locked_table_name: people
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 98116
waiting_trx_started: 2025-05-19 21:16:04
waiting_trx_age: 00:00:11
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 71893
waiting_query: update people set name = '' where id = 3
waiting_lock_id: 140213280087968:147608:295:4:5:140213443760672
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 98112
blocking_pid: 71892
blocking_query: NULL
blocking_lock_id: 140213280087176:3360:295:4:5:140213443756064
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2025-05-19 21:07:52
blocking_trx_age: 00:08:23
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 71892
sql_kill_blocking_connection: KILL 71892
1 row in set (0.00 sec)performance_schema.events_statements_history
此表中字段与上述视图中的字段存在对应关系, 不做过多解释, 至终点说明如下几个字段:
| 字段 | 含义 |
|---|---|
THREAD_ID | 线程ID |
EVENT_ID | 事务ID |
END_EVENT_ID | 事务ID |
EVENT_NAME | 事务名称 |
SQL_TEXT | 生成当前锁的SQL语句 |
数据示例:
mysql> select * from performance_schema.events_statements_history limit 1 \G
*************************** 1. row ***************************
THREAD_ID: 71931
EVENT_ID: 31
END_EVENT_ID: 31
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:105
TIMER_START: 855674535531000000
TIMER_END: 855674535760000000
TIMER_WAIT: 229000000
LOCK_TIME: 2000000
SQL_TEXT: select * from people where id = 3 for update
DIGEST: b897d0b585aa8e1d4aeab470abdaf6cfa863ab2581c1c906356f5499925980d1
DIGEST_TEXT: SELECT * FROM `people` WHERE `id` = ? FOR UPDATE
CURRENT_SCHEMA: blog_test
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 1
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: 30
NESTING_EVENT_TYPE: TRANSACTION
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 293970
CPU_TIME: 0
MAX_CONTROLLED_MEMORY: 39056
MAX_TOTAL_MEMORY: 137661
EXECUTION_ENGINE: PRIMARY
1 row in set (0.00 sec)performance_schema.data_locks
此表中字段与上述视图中的字段存在对应关系, 不做过多解释, 至终点说明如下几个字段:
| 字段 | 含义 |
|---|---|
ENGINE | 存储引擎 |
THREAD_ID | 线程ID |
EVENT_ID | 事务ID |
LOCK_TYPE | 锁类型 |
LOCK_MODE | 锁模式 |
LOCK_STATUS | 锁状态 |
示例数据:
mysql> select * from performance_schema.data_locks \G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140213280087176:3362:1553:140213442601344
ENGINE_TRANSACTION_ID: 98117
THREAD_ID: 71931
EVENT_ID: 32
OBJECT_SCHEMA: blog_test
OBJECT_NAME: people
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140213442601344
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)performance_schema.data_lock_waits
此表中字段与上述视图中的字段存在对应关系, 不做过多解释, 至终点说明如下几个字段:
| 字段 | 含义 |
|---|---|
REQUESTING_ENGINE_LOCK_ID | 当前事务ID |
REQUESTING_THREAD_ID | 当前线程ID |
BLOCKING_ENGINE_LOCK_ID | 正在持有锁的事务ID |
BLOCKING_THREAD_ID | 正在持有锁的线程ID |
示例数据:
mysql> select * from performance_schema.data_lock_waits \G
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 140213280087968:147612:295:4:5:140213443760672
REQUESTING_ENGINE_TRANSACTION_ID: 98119
REQUESTING_THREAD_ID: 71932
REQUESTING_EVENT_ID: 42
REQUESTING_OBJECT_INSTANCE_BEGIN: 140213443760672
BLOCKING_ENGINE_LOCK_ID: 140213280087176:3362:295:4:5:140213443756064
BLOCKING_ENGINE_TRANSACTION_ID: 98117
BLOCKING_THREAD_ID: 71931
BLOCKING_EVENT_ID: 32
BLOCKING_OBJECT_INSTANCE_BEGIN: 140213443756064
1 row in set (0.00 sec)