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