关于MySQL InnoDB存储引擎中的锁

  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>