学习小记-- Mysql事物锁监控

INNODB_TRX

在INFORMATION_SCHEMA在有三张表：INNODB_TRX、INNODB_LOCKS、INNODB_LOCKS_WAITS，通过这三张表，用户可以简单地监控当前事物并分析可能存在的锁问题

表INNODB_TRX的结构说明

字段名	说明
trx_id	Inodb存储引擎内部唯一的事物ID
trx_state	当前事务的状态
trx_tarted	事务的开始时间
trx_requested_lock_id	等待事务的锁id。如trx_state 的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state 的状态不是LOCK WAIT，则该值为NULL
trx_wati_started	事物等待开始的时间
trx_weight	事务的权重，反映了一个事务修改和锁住的行数。在Innodb存储引擎中，当发生死锁需要回滚时，Innodb存储引擎会选择该值最小的进行回滚
trx_mysql_thread_id	Mysql中的线程ID，SHOW PROCESSLIST现实的结果
trx_query	事务允许的SQL语句

看一个例子：

select * from information_schema.INNODB_TRX\G;
-- 结果如下
***************************1. rOW ***************************
trx id: 7311F4
trxstate: LOCKWAIT
trx started: 2010-01-04 10:49:33
trx_requested lock id: 7311F4:96:3:2
trx_wait started: 2010-01-04 10:49:33
trx weight: 2
trx mysql thread id:471719
七rx
query: select * from parent lock in share mode
***************************2. rOW ***************************
trx id: 730FEE trx state: RUNNING
tr started: 2010-01-04 10:18:37
trx requested lock id: NULL
trx wait started: NULL try weight: 2
trx mysgl thread id: 471718
trx query: NULL
2 rows in set (0.00 sec)

通过state可以观察到trx_id为730FEE的事务当前正在运行，trx_id为7311F4的事务目前处于LOCK WAIT状态，且运行的SQL语句是select*from parent lock in share mode。该表知识现实了当前运行的Innodb事务，并不能直接判断锁的一些情况。

如需查看锁，还需要访问INNODB_LOCKS

表INNODB_LOCKS的结构说明

字段名	说明
lock_id	锁的id
lock_trx_id	事务id
lock_mode	锁的模式
lock_type	锁的类型，表锁还是行锁
lock_table	要加锁的表
lock_index	锁住的索引
lock_space	锁对象的space id
lock_page	事务锁定页的数量。若是表锁，则该值为NULL
lock_rex	事务锁定行的数量。若是表锁，则该值为NULL
lock_date	事务锁定疾苦的主键值。若是表锁，则该值为NULL

紧接着上面例子，继续查看INNODB_LOCKS:

SELECT * FROM information_ schema. INNODB_IOCKS\G;
-- 运行结果如下
***************************1. rOW ***************************
lock_id: 7311F4:96:3:2
lock_trx_id: 7311F4 lock mode: S lock type: RECORD lock table: 'mytest'. 'parent' lock index: 'PRIMARY' lock space: 96
lock_page: 3
lock_rec: 2
lock_data: 1
***************************2. rOW ***************************
1ockid: 730FEE:96:3:2
lock_trx_id: 730FEE
lock_mode: X
lock_type: RECORD lock table:
'mytest'. 'parent'
lock_index: 'PRIMARY' lock space: 96
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set (0.00 sec)

可以清晰地看到当前锁的信息。trx_id 为730PER 的事务向表 parent 加了一个又的行锁，trx_id 为7311F4 的事务向表parent 申请了一个S的行锁。lock data 都是1，申请相同的资源，因此会有等待。这也可以解释INNODB TRX 申为什么一个事务的trx state 是“RUNNING”，另一个是“LOCK WAIT”了。

注意：lock_data这个值并非是“可信”值。例如当用户运行一个范围查找时，lock_data可能只返回第一行的主键。

表INNODB_LOCKS_WAITS能更直观地反映当前事务的等待

表INNODB_LOCKS_WAITS的结构说明

字段	说明	字段	说明
requesting_trx_id	申请锁资源的事务ID	blocking_trx_id	阻塞的事务ID
requesting_lock_id	申请的锁的ID	blocking_trx_id	阻塞的锁的ID

紧接着上面例子，继续查看INNODB_LOCKS_WAITS:

SELECT * FROM information_schema • INNODB_IOCK_WAITS\G;
-- 运行结果如下
***************************1. rOW ***************************
requesting_trx_id: 7311F4
requesting_lock_id: 7311F4:96:3:2
blocking_trx_id: 730FEE 
blocking_lock_id: 730FEE:96:3:2
1row in set (0.00 sec)

以清楚直观地看到哪个事务阻塞了另一个事务。

– 参考《MySQL技术内幕 InnoDB存储引擎》