MySQL锁情况查看

本文介绍如何在MySQL数据库中分析锁的情况及处理思路。

MySQL版本

mysql> select version();
+------------+
| version()
|
+------------+
| 5.7.38-log |
+------------+
1 row in set (0.01 sec)

模拟锁产生

A会话加锁

mysql> show create table tG;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL,
`name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from t;
+----+------+
| id | name |
+----+------+
|
1 | a
|
|
2 | s
|
|
3 | c
|
|
4 | d
|
|
5 | e
|
+----+------+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id<5 for update;
+----+------+
| id | name |
+----+------+
|
1 | a
|
|
2 | s
|
|
3 | c
|
|
4 | d
|
+----+------+
4 rows in set (0.00 sec)

B会话插入数据，造成锁等待现象

mysql> insert into t values(0,'null');

这里介绍MySQL查看锁的3个数据字典表，分别是位于information_schema数据库下的innodb_trx、innodb_lock_waits、innodb_locks三张表，查看步骤如下：

先看innodb_trx表

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from innodb_trxG;
*************************** 1. row ***************************
trx_id: 8553
trx_state: LOCK WAIT
trx_started: 2022-12-14 16:52:29
trx_requested_lock_id: 8553:45:3:2
trx_wait_started: 2022-12-14 16:52:29
trx_weight: 2
trx_mysql_thread_id: 22
trx_query: insert into t values(0,'null')
trx_operation_state: inserting
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: 8552
trx_state: RUNNING
trx_started: 2022-12-14 16:51:39
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 20
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 5
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 rows in set (0.00 sec)
ERROR:
No query specified
mysql> show full processlist;
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| Id | User
| Host
| db
| Command | Time | State
| Info
|
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| 20 | root
| localhost | ray
| Sleep
|
132 |
| NULL
|
| 22 | raybak | localhost | ray
| Query
|
82 | update
| insert into t values(0,'null') |
| 24 | root
| localhost | information_schema | Query
|
0 | starting | show full processlist
|
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
3 rows in set (0.00 sec)

trx_id：唯一事务id号，本次测试中是8552和8553
trx_state：当前事务的状态，本次测试中8553是LOCK WAIT 锁等待状态
trx_wait_started：事务开始等待时间，本次测试中为2022-12-14 16:52:29
trx_mysql_thread_id：线程id，与show full processlist中的id对应，本次测试中为22
trx_query：事务运行的SQL语句，本次测试为insert into t values(0,‘null’)
trx_operation_state：事务运行的状态，本次测试为inserting

再看innodb_lock_waits表

mysql> select * from innodb_lock_waitsG;
*************************** 1. row ***************************
requesting_trx_id: 8553
requested_lock_id: 8553:45:3:2
blocking_trx_id: 8552
blocking_lock_id: 8552:45:3:2
1 row in set, 1 warning (0.00 sec)

requesting_trx_id：请求锁的事务id，本次测试为8553
blocking_trx_id：持有锁的事务id，也就是造成锁等待的事务id，本次测试为8552

再看innodb_locks表

mysql> select * from innodb_locksG;
*************************** 1. row ***************************
lock_id: 8553:45:3:2
lock_trx_id: 8553
lock_mode: X,GAP
lock_type: RECORD
lock_table: `ray`.`t`
lock_index: PRIMARY
lock_space: 45
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 8552:45:3:2
lock_trx_id: 8552
lock_mode: X
lock_type: RECORD
lock_table: `ray`.`t`
lock_index: PRIMARY
lock_space: 45
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified

综合三张表查询和show prcess fulllist得知，会话id 20（事务id 8552），锁住了ray.t表，锁模式是行级锁，会话id 22（事务id 8553）的insert操作需要等待会话20释放锁后才能执行，因此出现了会话id 22（事务id 8553）hang住现象。

解决方法，杀会话

mysql> kill 20;
Query OK, 0 rows affected (0.00 sec)

当然，杀会话也可以通过pt-kill工具更方便，在后续文章会对pt-kill工具做详细介绍

最后

以上就是粗暴蓝天为你收集整理的MySQL锁情况查看的全部内容，希望文章能够帮你解决MySQL锁情况查看所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。