MySQL 事务与锁查看

information_schema.innodb_trx

trx_id					事务ID
trx_state				事务状态
trx_started				事务执行开始时间
trx_requested_lock_id			事务等待锁ID号(等待其它事务释放锁)
trx_wait_started			事务等待锁开始时间
trx_weight						
trx_mysql_thread_id			事务线程ID
trx_query				具体的SQL
trx_operation_state			事务当前操作状态
trx_tables_in_use			事务中有多少个表被使用
trx_tables_locked				
trx_lock_structs					
trx_lock_memory_bytes			事务锁住的内存大小(B)
trx_rows_locked				事务锁住的行数
trx_rows_modified			事务更改的行数
trx_concurrency_tickets			事务并发数
trx_isolation_level			事务隔离级别
trx_unique_checks			是否唯一性检查
trx_foreign_key_checks			是否外键检查
trx_last_foreign_key_error		最后的外键错误
trx_adaptive_hash_latched		
trx_adaptive_hash_timeout		

注:trx_started记录了事务开始的时间,如果过去了很长时间,可能是异常事务。trx_wait_started记录了等待时间,如果等待了很长时间,可能是异常事务。对应等待锁的事务,trx_query记录了具体的SQL语句。trx_mysql_thread_id可以定位到具体的线程(回话ID)

information_schema.innodb_locks

lock_id							锁ID
lock_trx_id						拥有锁的事务ID
lock_mode						锁模式
lock_type						锁类型
lock_table						被锁的表
lock_index						被锁的索引(类型)
lock_space						被锁的表空间号
lock_page						被锁的页号
lock_rec						被锁的记录号
lock_data						被锁的数据(对应索引编号,一般是ID号)

事务可以持有多个锁。锁类型有S和X。根据索引类型不同,lock_rec和lock_data可以定位行号。当开启一个事务,对相关行上锁,这个时候的锁不会出现在innodb_locks表中,只有相关的锁被其它事务等待时,产生了锁等待,才会把锁与等待的锁插入此表(换个说法就是此表是用来存放有依赖关系的锁的)

information_schema.innodb_lock_waits

requesting_trx_id					请求锁的事务ID
requested_lock_id					请求锁的锁ID
blocking_trx_id						当前拥有锁的事务ID
blocking_lock_id					当前拥有锁的锁ID
requesting_thd_id					请求锁的线程ID
blocking_thd_id						当前拥有锁的线程ID

记录依赖关系。请求锁等待持有锁。

1 当要查看有哪些线程时,直接运行show full processlist即可,这个命令动态列出当前的线程状态
2 当要查看有哪些事务时(比如检查有哪些事务长时间未结束),可以直接查看innodb_trx表,这个表中的trx_started记录了开始事务的时间。
3 当要查看是否有锁等待时,可以查看innodb_locks,只要有记录,就说明产生了锁等待,具体是哪个依赖哪个,需要查看innodb_lock_waits的关系。

一般来说,产生了锁等待,如果超时,事务会自动释放,但是如果事务开启了,单长时间没有结束,就应该去innodb_trx查看确认(从线程基本无法查看到已经开启了事务)。

获得导致行锁等待和行锁等待超时的会话:

select l.* from ( select 'Blocker' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, lw.requesting_thd_id Blockee_id, lw.requesting_trx_id Blockee_trx from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id union select 'Blockee' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, null, null from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;

对于复杂的多个会话相互行锁等待情况,建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话;终止后再次检查,如果仍旧有行锁等待,再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。

对于标识为 Blocker 的会话(持有锁阻塞其他会话的 DML 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。比如,可以通过 Kill 命令来今后会话终止。