MySql8.0锁表查询和处理

 ​

MySQL5.7版本中查看行锁命令：

select * from information_schema.innodb_lock_waits;

select * from information_schema.innodb_locks;

MySQL8.0版本查看锁的命令变为:

select * from performance_schema.data_lock_waits;

select * from performance_schema.data_locks;

因此针对MySQL8.0 提供相关的解锁步骤:

1、查看正在进行中的事务 SELECT * FROM information_schema.INNODB_TRX

2、查询是否锁表 SHOW OPEN TABLES where In_use > 0;

3、查看最近死锁的日志 SHOW ENGINE INNODB STATUS

4、 SELECT * from information_schema.processlist

5、 select * from information_schema.innodb_trx;

6、 select * from performance_schema.data_locks;

7、 select * from performance_schema.data_lock_waits;

解除死锁

查看当前正在进行中的进程

show processlist

也可以使用 SELECT * FROM information_schema.INNODB_TRX;

这两个命令找出来的进程id 是同一个。 杀掉进程对应的进程

也可以用一下语句解锁:

1、查询是否有锁表记录，找到锁表线程id

 select * from performance_schema.data_locks;

2、根据线程id 找到锁表进程id

SELECT b.processlist_id,a.thread_id,a.sql_text FROM

performance_schema.events_statements_current a, performance_schema.threads b

WHERE a.thread_id=b.thread_id

3、kill  相关进程id

4、也可以根据下边sql 查询线程id

 SELECT straight_join

 dl.THREAD_ID,------相关线程id

est.SQL_TEXT,

dl.OBJECT_SCHEMA,

dl.OBJECT_NAME,

dl.INDEX_NAME,

dl.LOCK_TYPE,

dl.LOCK_MODE,

dl.LOCK_STATUS,

dl.LOCK_DATA

FROM

performance_schema.data_locks dl

inner join performance_schema.events_statements_current est on dl.THREAD_ID = est.THREAD_ID

ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;