Mysql 会话锁查看

JIANGWANGZHEN_1

已于 2024-02-22 18:13:17 修改

阅读量913

点赞数 17

分类专栏： MYSQL 文章标签： mysql

于 2024-02-22 18:11:27 首次发布

本文链接：https://blog.csdn.net/JIANGWANGZHEN_1/article/details/136240644

版权

本文详细介绍了如何在MySQL中查看会话锁，包括全局读锁的执行过程，以及不同版本中查看锁信息的方法。通过示例展示了在8.0和5.7版本中使用`performance_schema`和`information_schema`来检查锁的状态，包括`metadata_locks`、`data_locks`、`innodb_trx`等表的内容，以及如何解析锁数据和类型。同时，文章讨论了锁的类型如全局锁、表锁、行锁，以及锁等待和事务状态。

摘要由CSDN通过智能技术生成

Mysql锁查看

一、全局读锁
会话1执行全局读锁
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 15 |
+-----------------+
1 row in set (0.00 sec)

mysql> flush table with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 15 |
+-----------------+
1 row in set (0.01 sec)

会话2执行普通DML 被堵塞
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 16 |
+-----------------+
1 row in set (0.00 sec)

mysql> insert into t values (0);

会话被堵塞

会话3

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 17 |
+-----------------+
1 row in set (0.00 sec)

8.0版本
mysql> select * from performance_schema.data_locks;
Empty set (0.00 sec)

mysql> select * from performance_schema.data_lock_waits;
Empty set (0.00 sec)

mysql> select * from information_schema.innodb_trx\G
Empty set (0.01 sec)

显示都为空。

mysql> show engine innodb status\G

------------
TRANSACTIONS
------------
Trx id counter 5003852
Purge done for trx's n:o < 5003846 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421858852885232, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852884424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852883616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852882808, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852882000, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852881192, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852880384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852879576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852878768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421858852877960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

TRANSACTIONS 没有信息

5.7版本开始提供了performance_schema.metadata_locks表，该表记录了各种Server层的锁信息，performance_schema.data_locks 查看行锁和表锁

show create table performance_schema.metadata_locks
表结构如下:
mysql> show create table performance_schema.metadata_locks\G
*************************** 1. row ***************************
Table: metadata_locks
Create Table: CREATE TABLE `metadata_locks` (
`OBJECT_TYPE` varchar(64) NOT NULL,
`OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
`OBJECT_NAME` varchar(64) DEFAULT NULL,
`COLUMN_NAME` varchar(64) DEFAULT NULL,
`OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL,
`LOCK_TYPE` varchar(32) NOT NULL,
`LOCK_DURATION` varchar(32) NOT NULL,
`LOCK_STATUS` varchar(32) NOT NULL,
`SOURCE` varchar(64) DEFAULT NULL,
`OWNER_THREAD_ID` bigint unsigned DEFAULT NULL,
`OWNER_EVENT_ID` bigint unsigned DEFAULT NULL,
PRIMARY KEY (`OBJECT_INSTANCE_BEGIN`),
KEY `OBJECT_TYPE` (`OBJECT_TYPE`,`OBJECT_SCHEMA`,`OBJECT_NAME`,`COLUMN_NAME`),
KEY `OWNER_THREAD_ID` (`OWNER_THREAD_ID`,`OWNER_EVENT_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

OBJECT_TYPE值有
GLOBAL, SCHEMA, TABLE, FUNCTION, PROCEDURE, TRIGGER (currently unused),
EVENT, COMMIT, USER LEVEL LOCK, TABLESPACE, or LOCKING SERVICE.

LOCK_STATUS解读
LOCK_STATUS   是否瞬时状态   备注
PENDING   否   请求锁但没有获得锁定
GRANTED   否   请求且已获得锁定
VICTIM   是   当死锁检测器取消挂起的锁定请求以打破死锁（ER_LOCK_DEADLOCK）时，其行状态从更新PENDING为VICTIM
TIMEOUT   是   超时
KILLED   是   被kill调
PRE_ACQUIRE_NOTIFY   是   表示该元数据锁定subsubsystem的通知感兴趣的存储引擎，而进入锁定获取操作或离开锁释放操作
POST_RELEASE_NOTIFY   是表示该元数据锁定subsubsystem的通知感兴趣的存储引擎，而进入锁定获取操作或离开锁释放操作

select * from performance_schema.metadata_locks