mysql锁和阻塞排查

mysql锁和事务排查

SELECT 
    NOW(),
    (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,
    b.id,
    b.user,
    b.host,
    b.db,
    c.lock_type,
   
 c.lock_table,
    c.lock_index
FROM
    information_schema.innodb_trx a
       
 INNER JOIN
    information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id
   
 INNER JOIN
    information_schema.INNODB_LOCKS  c
  on a.trx_requested_lock_id=c.lock_id;

 SELECT
    r.trx_state wating_trx_state,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_Id waiting_thread,
    r.trx_query waiting_query,
    b.trx_state blocking_trx_state,
    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

INNODB_TRX表介绍

INNODB_TRX 表提供了信息关于在InnoDB中执行的当前的每个事务，包含是否事务是等待一个锁,当事务启动后，事务正在执行的SQL语句

     SELECT
      ifnull(trx_id, 'null'),
    ifnull(trx_state,'null'),
	  ifnull(trx_started,'null'),
    ifnull(trx_wait_started,'null'),
    ifnull(trx_weight,'null'),
    ifnull(trx_mysql_thread_id,'null'),
    ifnull(trx_query,'null'),
    ifnull(trx_operation_state,'null'),
    ifnull(TRX_TABLES_IN_USE,'null'),
    ifnull(trx_rows_locked,'null'),
    ifnull(trx_rows_modified,'null'),
	ifnull(trx_tables_locked,'null')
FROM
    information_schema.innodb_trx;

INNODB_TRX Columns 包含的内容:

TRX_ID：
唯一的事务ID号, 内部与InnoDB(从MySQL 5.6开始, 那些IDs 能用被创建用于只读和非锁定事务)

TRX_WEIGHT:
一个事务的权重,反映(但不一定全是确定的计数) 更改记录数和被事务锁定的记录数。

为了释放一个死锁, InnoDB 选择一个具有小权重的事务作为"受害者”回滚。

事务改变了非事务表是被认为是严重的相比其他, 无论是被改变的行或者锁定的行

TRX_STATE:
事务执行的状态, 允许的值为 RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.

TRX_STARTED：
事务开始时间

TRX_REQUESTED_LOCK_ID:
事务当前正在等待的 锁的id, 如果TRX_STATE 是LOCK WAIT 否则就是NULL

为了得到锁,关联这个列和INNODB_LOCKS table LOCK_ID列
TRX_WAIT_STARTED

事务开始等待锁的时间,如果 TRX_STATE 是lock wait,否则是NULL

TRX_MYSQL_THREAD_ID:
MySQL thread ID,得到细节关于thread, 使用这个列和NFORMATION_SCHEMA PROCESSLIST table的ID进行关联

TRX_QUERY：
事务执行的语句

TRX_OPERATION_STATE:
事务的当前操作 如果没有显示为NULL

TRX_TABLES_IN_USE:
InnoDB表的数据用于当处理当前的SQL语句

TRX_TABLES_LOCKED:
当前SQL语句有行锁在InnoDB表的数量上面的数量 ( 因为那些是行锁,不是表锁,表仍旧可以读取和写入通过多个事务m尽管一些记录被锁定)

TRX_LOCK_STRUCTS：
事务保留的锁的数量

TRX_LOCK_MEMORY_BYTES：
这个事务在内存中lock结构占据的大小

TRX_ROWS_LOCKED:
这个事务锁定的记录,这个值可能包含标记为删除的记录,但是物理存在的 但是对于事务不可见

TRX_ROWS_MODIFIED:
此事务中修改和插入记录的数目

TRX_ISOLATION_LEVEL:
当前事务的隔离级别

注意:
使用这个表帮助诊断性能问题 在有严重并发负载的时间段

使用DESCRIBE or SHOW COLUMNS 来查看额外的信息关于表的列 包含数据类型和默认值

参考：https://blog.csdn.net/zhaoyangjian724/article/details/53285925

参考：https://blog.csdn.net/zhaoyangjian724/article/details/52858519