mysql分析表锁_mysql 锁的一些分析方法

数据库在出现锁或者死锁的时候，处理起来应该综合分析，应该配合数据库监控系统、结合业务同时分析数据库寻找问题，下面是一些常用的手动供参考：

参考参数：

innodb_lock_wait_timeout

查看是否有表锁住：

show OPEN TABLES where In_use > 0;

show processlist ；

select * from information_schema.`PROCESSLIST` where info is not null order by time desc ;

show engine innodb status;

或者查询

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT * from INFORMATION_SCHEMA.INNODB_TRX;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

应该熟悉常用的表INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS的一些字段含义，这样可以快速的分析问题。

innodb_trx #当前运行的所有事务

innodb_locks #当前出现的锁

innodb_lock_waits #锁等待的对应关系

其中show OPEN TABLES where In_use > 0;只能看到那些表被锁住；

show processlist 看不到锁的信息，判读起来比较麻烦；

show engine innodb status; 可以查看到很多信息，特别是一些死锁信息，但是分析起来比较麻烦。

下面为常用的sql语句供参考：

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_Id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM

information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

生成kill：

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_Id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

CONCAT('kill query ',b.trx_mysql_thread_id , ';'),

b.trx_query blocking_query

FROM

information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

根据查询结果杀掉相应的thread：

kill blocking_thread；

表对应的字段含义参考：

SELECT

trx_id AS  `事务ID`,

trx_state AS `事务状态`,

trx_requested_lock_id  AS  `事务需要等待的资源`,

trx_wait_started    AS  `事务开始等待时间`,

trx_tables_in_use AS `事务使用表`,

trx_tables_locked AS `事务拥有锁`,

trx_rows_locked  AS `事务锁定行`,

trx_rows_modified  AS `事务更改行`

FROM

information_schema.innodb_trx ;

SELECT

lock_id         AS  `锁ID`,

lock_trx_id    AS  `拥有锁的事务ID`,

lock_mode      AS  `锁模式    `,

lock_type      AS  `锁类型`,

lock_table         AS  `被锁的表`,

lock_index         AS  `被锁的索引`,

lock_space         AS  `被锁的表空间号`,

lock_page      AS  `被锁的页号`,

lock_rec       AS  `被锁的记录号`,

lock_data      AS  `被锁的数据`

FROM

information_schema.innodb_locks;

SELECT

requesting_trx_id   AS  `请求锁的事务ID`,

requested_lock_id   AS  `请求锁的锁ID`,

blocking_trx_id     AS  `当前拥有锁的事务ID`,

blocking_lock_id    AS  `当前拥有锁的锁ID`

FROM

innodb_lock_waits;