l 2,INNODB_LOCKS表
a) lock_id:锁的id以及被锁住的空间id编号、页数量、行数量
b) lock_trx_id:锁的事务id。
c) lock_mode:锁的模式。
d) lock_type:锁的类型,表锁还是行锁
e) lock_table:要加锁的表。
f) lock_index:锁的索引。
g) lock_space:innodb存储引擎表空间的id号码
h) lock_page:被锁住的页的数量,如果是表锁,则为null值。
i) lock_rec:被锁住的行的数量,如果表锁,则为null值。
j) lock_data:被锁住的行的主键值,如果表锁,则为null值。
MySQL> select * from INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 3015646:797:3:2
lock_trx_id: 3015646
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 797
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 3015645:797:3:2
lock_trx_id: 3015645
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 797
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set (0.00 sec)
mysql>
这里我们可以看到当前的锁信息了,2个事务都锁定了,看相同的数据lock_space: 797、lock_page: 3、lock_rec: 2可以得出事务t1和事务t2访问了相同的innodb数据块,再通过lock_data字段信息lock_data: 1,看到锁定的数据行都是主键为1的数据记录,可见2个事务t1和t2都申请了相同的资源,因此会被锁住,事务在等待。
通过lock_mode: X值也可以看出事务t1和t2申请的都是排它锁。
PS:当执行范围查询更新的时候,这个lock_data的值并非是完全准确。当我们运行一个范围更新时,lock_data只返回最先找到的第一行的主键值id;另外如果当前资源被锁住了,与此同时由于锁住的页因为InnoDB存储引擎缓冲池的容量,而导致替换缓冲池页面,再去查看INNODB_LOCKS表时,这个lock_data会显示未NULL值,意味着InnoDB存储引擎不会从磁盘进行再一次查找。
l 3,INNODB_LOCK_WAITS表
当事务量比较少,我们可以直观的查看,当事务量非常大,锁等待也时常发生的情况下,这个时候可以通过INNODB_LOCK_WAITS表来更加直观的反映出当前的锁等待情况:
INNODB_LOCK_WAITS表主要字段如下:
1) requesting_trx_id:申请锁资源的事务id。
2) requested_lock_id:申请的锁的id。
3) blocking_trx_id:阻塞的事务id。
4) blocking_lock_id:阻塞的锁的id。
去看下当前锁等待信息,如下所示:
mysql> select * from INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 3015646
requested_lock_id: 3015646:797:3:2
blocking_trx_id: 3015645
blocking_lock_id: 3015645:797:3:2
1 row in set (0.00 sec)
mysql>
这里我们可以看到事务t1(3015646)申请了锁资源,而事务t2(3015645)则阻塞了事务t1的申请。我们管理其他表,得到更直观的详细信息,如下所示:
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query, it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2 WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| 3015647 | 18 | update test.t1 set b='t2' where a>2 | 3015645 | 17 | NULL |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
mysql>