一文详细讲解如何查看mysql里面的锁

通过查询表统计信息查看

information_schema库下相关事务表和锁相关信息表介绍

innodb_trx

存储了当前正在执行的事务信息

trx_id：事务ID。

trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。

trx_started：事务开始时间。

trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。

trx_wait_started：事务开始等待的时间。

trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。

trx_tables_locked：表示该事务目前加了多少个表级锁。

trx_lock_structs：表示该事务生成了多少个内存中的锁结构。

trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。

trx_rows_locked：表示该事务目前加了多少个行级锁。

innodb_locks

记录了锁信息

如果一个事务想要获取到某个锁但未获取到，则记录该锁信息

如果一个事务获取到了某个锁，但是这个锁阻塞了别的事务，则记录该锁信息

但是无法通过该表查询到谁被阻塞，谁持有未释放。

lock_id：锁 ID。

lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。

lock_mode：锁的模式。

lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。

lock_table：被锁定的或者包含锁定记录的表的名称。

innodb_lock_waits

表明每个阻塞的事务是因为获取不到哪个事务持有的锁而被阻塞

requesting_trx_id：–获取不到锁而被阻塞的事务id（等待方）

requested_lock_id：-- 请求锁ID ，事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。

blocking_trx_id： --获取到别的事务需要的锁而阻塞其事务的事务id（当前持有方，待释放）

blocking_lock_id： --这一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

processlist

id:标识ID。这与在SHOW PROCESSLIST语句的Id列、Performance Schema threads表的PROCESSLIST_ID列中显示的值类型相同，并由CONNECTION_ID()函数返回

user:发出该语句的mysql用户。

host：发出该语句的客户机的主机名(系统用户除外，没有主机)。

db：默认数据库。

command：线程正在执行的命令的类型。

time：线程处于当前状态的时间(以秒为单位)。

state：指示线程正在执行的操作、事件或状态。

info：线程正在执行的语句，如果没有执行任何语句，则为NULL。

如何借助这几张表来定位到有行锁等待

（1）查看当前有无锁等待

mysql> show status like ‘innodb_row_lock%’;

（2）查看哪个事务在等待(被阻塞了)

mysql> select * from information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'\G
trx_state 表示该事务处于锁等待状态。
trx_query : 当前被阻塞的操作是select * from actor where actor_id=1 for update。
从trx_mysql_thread_id和trx_id可以看到这里查到当前被阻塞的事务的：
线程ID是 971，注意说的是线程id

事务ID是3934

（3）查询该事务被哪个事务给阻塞了

从innodb_trx获取到被阻塞的trx_id是3934，阻塞该事务的事务id是3933

mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=970\G

（4）根据trx_id，从innodb_trx表可查询到trx_mysql_thread_id线程id为970

mysql> select * from information_schema.innodb_trx where trx_id=3933 \G

（5）根据线程id，查询表拿到thread_id为995

mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=970\G

（6）根据thread_id，查询当前锁源的sql

整个流程如下：

（1）首先查询是否有锁，根据锁查到被锁的trx_id

（2）根据被锁的trx_id可以查到锁源的trx_id

（3）根据锁源的trx_id查到trx_mysql_thread_id

（4）再根据trx_mysql_thread_id查到thread_id

（5）最后，用thread_id查找到锁源的sql

此外，第一步发现锁的方式，也可直接获取到锁源trx_id和被锁trx_id

但是这种方法在mysql8.0已经被移除，介绍另外一张表

sys.innodb_lock_waits 表

locked_table : 哪张表出现的等待
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID
blocking_pid : 锁源的线程号

mysql> select * from sys.innodb_lock_waits\G

获取到锁源的blocking_pid 976（=processlist表的id），根据此id找到thread_id，再根据thread_id找到对应的sql

总结：

两种找到锁源SQL步骤是一样的

锁源的事务trx_id -->pnformaction_schema.processlist表的线程id–>performance_schema.threads表的thread_id–>performance_schema.events_statements_current 或performance_schema.events_statements_history查看sql

注：下面所指的id含义相同

information_schema.innodb_trx(trx_mysql_thread_id)

information_schema.processlist(id)

sys.innodb_lock_waits(waiting_pid,blocking_pid)

sys.sys.innodb_lock_waits的应用

1）查看锁等待相关的（阻塞线程、被阻塞线程信息及相关用户、IP、PORT、locked_type锁类型）
SELECT locked_table,
       locked_index,
       locked_type,
       blocking_pid,
       concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)",
       blocking_lock_mode,
       blocking_trx_rows_modified,
       waiting_pid,
       concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)",
       waiting_lock_mode,
       waiting_trx_rows_modified,
       wait_age_secs,
       waiting_query
FROM sys.x$innodb_lock_waits T1
LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID
LEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;

2）等待的持续时间（单位秒>20s）

SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 20 ;
show engine innodb status
将锁信息打印出来
mysql> set global innodb_status_output_locks =ON;
执行如下sql，fisrt_name上有普通二级索引
begin;
select * from actor where first_name >'A' and first_name <'B' for update; 查询得到该事务ID，方面后面观察验证

show engine innodb status看到的事务信息如下

我将注释写在#后面

TRANSACTIONS

Trx id counter 3957 #下一个待分配的事务id
Purge done for trx’s n:o < 3930 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION: #各个事务信息
—TRANSACTION 421799341399664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421799341400576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421799341403312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421799341398752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 3956, ACTIVE 25 sec #事务id为3956的事务，活跃时间25秒
3 lock struct(s), heap size 1136, 27 row lock(s)
MySQL thread id 991, OS thread handle 140323910289152, query id 10636 localhost root
TABLE LOCK table sakila.actor trx id 3956 lock mode IX #事务id为3956的事务，对sakila.actor加了表级别意向独占锁 IX
RECORD LOCKS space id 45 page no 5 n bits 272 index idx_actor_first of table sakila.actor trx id 3956 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 #idx_actor_first 是二级索引，lock_mode X Record lock 表示X型的next_key 锁
0: len 4; hex 4144414d; asc ADAM;;
1: len 2; hex 0047; asc G;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 4144414d; asc ADAM;;
1: len 2; hex 0084; asc ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 2; hex 414c; asc AL;;
1: len 2; hex 00a5; asc ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 414c414e; asc ALAN;;
1: len 2; hex 00ad; asc ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 414c42455254; asc ALBERT;;
1: len 2; hex 007d; asc };;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 414c42455254; asc ALBERT;;
1: len 2; hex 0092; asc ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 414c4543; asc ALEC;;
1: len 2; hex 001d; asc ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 414e47454c41; asc ANGELA;;
1: len 2; hex 0041; asc A;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 414e47454c41; asc ANGELA;;
1: len 2; hex 0090; asc ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 414e47454c494e41; asc ANGELINA;;
1: len 2; hex 004c; asc L;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 414e4e45; asc ANNE;;
1: len 2; hex 0031; asc 1;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 415544524559; asc AUDREY;;
1: len 2; hex 0022; asc ";;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 6; hex 415544524559; asc AUDREY;;
1: len 2; hex 00be; asc ;;

Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 42454c41; asc BELA;;
1: len 2; hex 00c4; asc ;;

RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table sakila.actor trx id 3956 lock_mode X locks rec but not gap
Record lock, heap no 30 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 # trx id 3956 聚簇索引PRIMARY ，lock_mode X locks rec but not gap Record lock 表示X型记录锁
0: len 2; hex 001d; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0228; asc + (;;
3: len 4; hex 414c4543; asc ALEC;;
4: len 5; hex 5741594e45; asc WAYNE;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 35 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0022; asc ";;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b025a; asc + Z;;
3: len 6; hex 415544524559; asc AUDREY;;
4: len 7; hex 4f4c4956494552; asc OLIVIER;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 50 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0031; asc 1;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b02f0; asc + ;;
3: len 4; hex 414e4e45; asc ANNE;;
4: len 6; hex 43524f4e594e; asc CRONYN;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 66 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0041; asc A;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0390; asc + ;;
3: len 6; hex 414e47454c41; asc ANGELA;;
4: len 6; hex 485544534f4e; asc HUDSON;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 72 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0047; asc G;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b03cc; asc + ;;
3: len 4; hex 4144414d; asc ADAM;;
4: len 5; hex 4752414e54; asc GRANT;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 77 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 004c; asc L;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b03fe; asc + ;;
3: len 8; hex 414e47454c494e41; asc ANGELINA;;
4: len 7; hex 41535441495245; asc ASTAIRE;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 126 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 007d; asc };;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b05e8; asc + ;;
3: len 6; hex 414c42455254; asc ALBERT;;
4: len 5; hex 4e4f4c5445; asc NOLTE;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 133 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0084; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0631; asc + 1;;
3: len 4; hex 4144414d; asc ADAM;;
4: len 6; hex 484f50504552; asc HOPPER;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 145 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0090; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b06b5; asc + ;;
3: len 6; hex 414e47454c41; asc ANGELA;;
4: len 11; hex 57495448455253504f4f4e; asc WITHERSPOON;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 147 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0092; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b06cb; asc + ;;
3: len 6; hex 414c42455254; asc ALBERT;;
4: len 9; hex 4a4f48414e53534f4e; asc JOHANSSON;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 166 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 00a5; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b079c; asc + ;;
3: len 2; hex 414c; asc AL;;
4: len 7; hex 4741524c414e44; asc GARLAND;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 174 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 00ad; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b07f4; asc + ;;
3: len 4; hex 414c414e; asc ALAN;;
4: len 8; hex 4452455946555353; asc DREYFUSS;;
5: len 4; hex 43f23ed9; asc C > ;;

Record lock, heap no 191 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 00be; asc ;;

1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b08af; asc + ;;
3: len 6; hex 415544524559; asc AUDREY;;
4: len 6; hex 4241494c4559; asc BAILEY;;
5: len 4; hex 43f23ed9; asc C > ;;
从上我们可以看到此事务在表actor上，加上了

mysql> select * from actor where first_name >’A' and first_name <'B' for update;
+———-+————+————-+———————+
| actor_id | first_name | last_name | last_update |
+———-+————+————-+———————+
| 71 | ADAM | GRANT | 2006-02-15 04:34:33 |
| 132 | ADAM | HOPPER | 2006-02-15 04:34:33 |
| 165 | AL | GARLAND | 2006-02-15 04:34:33 |
| 173 | ALAN | DREYFUSS | 2006-02-15 04:34:33 |
| 125 | ALBERT | NOLTE | 2006-02-15 04:34:33 |
| 146 | ALBERT | JOHANSSON | 2006-02-15 04:34:33 |
| 29 | ALEC | WAYNE | 2006-02-15 04:34:33 |
| 65 | ANGELA | HUDSON | 2006-02-15 04:34:33 |
| 144 | ANGELA | WITHERSPOON | 2006-02-15 04:34:33 |
| 76 | ANGELINA | ASTAIRE | 2006-02-15 04:34:33 |
| 49 | ANNE | CRONYN | 2006-02-15 04:34:33 |
| 34 | AUDREY | OLIVIER | 2006-02-15 04:34:33 |
| 190 | AUDREY | BAILEY | 2006-02-15 04:34:33 |
+———-+————+————-+———————+
13 rows in set (0.00 sec)| actor |

CREATE TABLE actor (
  actor_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (actor_id),
  KEY idx_actor_last_name (last_name),
  KEY idx_actor_first (first_name)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 |
这些行对应的二级索引idx_actor_first加上了X型next_key锁，在对应的聚簇索引上加上了X型record锁
lock_mode X locks gap before rec  表示X型gap锁
lock mode X                       表示X型next_key 锁
lock_mode X locks rec but no gap  表示X型record锁

以上就是mysql查询锁信息的两种方法。

到此这篇关于如何查看mysql里面的锁的文章就介绍到这了,更多相关mysql查看锁内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

from:https://www.jb51.net/database/304757gwm.htm

November 2024
M	T	W	T	F	S	S
« Oct
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30