join left loke mysql_mysql 锁等待和死锁的查看方式

查看跟踪锁等待和死锁的方式

锁等待(LOCK WAIT)

与锁相关涉及到的表：

information_schema.innodb_lock_waits

information_schema.innodb_trx;

information_schema.innodb_locks;

information_schema. PROCESSLIST;

锁等待关联查询：SELECT

r.trx_id AS waiting_trx_id, *等待的事务id*/

r.trx_mysql_thread_id AS waiting_thread, *等待的线程号*/

TIMESTAMPDIFF(

SECOND,

r.trx_wait_started,

CURRENT_TIMESTAMP

) AS wait_time, *等待时间*/

r.trx_query AS waiting_query,   *锁等待的SQL*/

l.lock_table AS waiting_table_lock, *等待锁的表*/

b.trx_id AS blocking_trx_id,/*源头事务id*/

b.trx_mysql_thread_id AS blocking_thread, *源头线程号*/

SUBSTRING(

p. HOST,

1,

INSTR(p. HOST, ':') - 1

) AS blocking_host, *源头客户端*/

SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) AS block_port, *源头客户端port*/

IF (p.command = "Sleep", p.time, 0) AS idle_in_trx,

/*如果源头线程的状态是sleep，显示空闲时间，不为sleep,显示0*/

b.trx_query AS blcoking_query *源头SQL*/

FROM

information_schema.innodb_lock_waits AS w

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

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

INNER JOIN information_schema.innodb_locks AS l ON w.requested_lock_id = l.lock_id

LEFT JOIN information_schema. PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id

ORDER BY

wait_time DESC;

死锁(DEAD LOCK)

死锁相关参数介绍：

innodb_deadlock_detect：可以设置开启死锁检测，在5.7.15+版本出的，开启后可以检测并杀掉造成死锁的事务。(默认开启)

不过根据官方文档说法，该参数在大量线程等待相同的锁时，死锁检测速度会变慢，禁用死锁检测，并设置innodb_lock_wait_timeout参数或许更有效。

innodb_print_all_deadlocks：可以开启将死锁信息输出到mysql_error.log中。(默认开启)

查看死锁信息的方式：show engine innodb status\G

找到LATEST DETECTED DEADLOCK一栏

2.也可以通过mysql_error.log可以查看死锁相关信息。

(死锁信息只能查看到两条LOCK WAIT的事务，没办法查出源头事务的信息)