MySQL/MariaDB的锁超详细讲解(4)

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 14914:184:3:2
lock_trx_id: 14914
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tt`
 lock_index: GEN_CLUST_INDEX
 lock_space: 184
  lock_page: 3
  lock_rec: 2
  lock_data: 0x000000000601
*************************** 2. row ***************************
    lock_id: 14913:184:3:2
lock_trx_id: 14913
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tt`
 lock_index: GEN_CLUST_INDEX
 lock_space: 184
  lock_page: 3
  lock_rec: 2
  lock_data: 0x000000000601
2 rows in set (0.00 sec)

从上面的结果中看出,锁所在的事务ID为14914,并且锁模式为独占锁,类型为record即行锁,申请锁的表为tt表,而且锁定的页数为3页,锁定的行有2行,锁定行的主键值为0x000000000601。也许会奇怪,在前面实验过程中根本就没有建立主键,这里为什么会有主键值,这是因为MySQL在加锁的时候判断是否有索引,没有索引的时候会自动隐式的添加索引(聚集索引),从上面锁的索引为"GEN_CLUST_INDEX"可以看出。

所以我们可以知道,MariaDB/MySQL中的行锁是通过键锁(Key)来实现的(在SQL Server中有堆表的概念,SQL Server对于没有索引的表,其行锁通过rid锁来实现)。

并且从上面的两段结果也可以看到,它们的申请锁资源所处位置是相同的,正因为位置相同,所以才有了锁等待。

现在在会话1上创建索引,然后人为造成锁等待再来查看innodb_locks表。

在会话1和会话2执行:

rollback;

在会话1执行:

create index idx_tt on tt(a);
begin;
update tt set b='h' where a=1;

在会话2执行:

begin;
update tt set b='x' where a=1;

查看innodb_locks表。

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 14925:184:4:2
lock_trx_id: 14925
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tt`
 lock_index: ind_tt
 lock_space: 184
  lock_page: 4
  lock_rec: 2
  lock_data: 1, 0x000000000601
*************************** 2. row ***************************
    lock_id: 14924:184:4:2
lock_trx_id: 14924
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tt`
 lock_index: ind_tt
 lock_space: 184
  lock_page: 4
  lock_rec: 2
  lock_data: 1, 0x000000000601
2 rows in set (0.00 sec)

此处发现,锁的索引类型为ind_tt,而锁住行的主键值已经变为1个1了。

查出了锁的信息后,就可以人为的判断出锁等待信息。但是当事务比较大的时候,锁的信息非常繁杂,这时候通过上面的两张表无法轻易判断相关锁信息。由此要借助第三张表 innodb_lock_waits ,该表只有4列,且意义直观明了。

MySQL/MariaDB的锁超详细讲解

还是上面试验过程中造成的锁等待,查看那innodb_lock_waits表结果如下:

mysql> select * from information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 14914
requested_lock_id: 14914:184:3:2
  blocking_trx_id: 14913
 blocking_lock_id: 14913:184:3:2
1 row in set (0.00 sec)

可以看到,申请锁的事务ID为14914,阻塞在前方的事务ID为14913。

有了这3张表,还可以将它们联接起来更直观的显示想要的结果。如下:

SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM
    information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
 waiting_trx_id: 14925
 waiting_thread: 34
  waiting_query: update tt set b='x' where a=1
blocking_trx_id: 14924
blocking_thread: 1
 blocking_query: NULL

现在可以直观的看到14925事务被阻,语句为update,阻塞它的事务为14924。

还可以从以下联接语句中查看锁和事务的相关信息。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/3810f55420a83dd159a316202f796b19.html