2021-06-05 11:30  阅读(1737)
文章分类:MySQL 技术内幕 文章标签:MySQLMySQL 技术内幕MySQL 高级
©  原文作者:一直不懂 原文地址:https://blog.csdn.net/shenchaohao12321/category_8075653.html

InnoDB存储引擎实现了如下两种标准的行级锁:

  • 共享锁( S Lock),允许事务读一行数据。
  • 排他锁( X LocK),允许事务删除或更新一行数据。

如果一个事务T1已经获得了行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,称这种情况为锁兼容( Lock Compatible)。
但若有其他的事务T3想获得行r的排他锁,则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容。下表显示了共享锁和排他锁的兼容性。

  X S
  X S
X 不兼容 不兼容
S 不兼容 兼容

从表中可以发现X锁与任何的锁都不兼容,而S锁仅和S锁兼容。需要特别注意的是,S和X锁都是行锁,兼容是指对同一记录(row)锁的兼容性情况。
此外, InnoDB存储引擎支持多粒度(granular)锁定,这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作, InnoDB存储引擎支持一种额外的锁方式,称之为意向锁(Intention lock)。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁,如下图所示。

202106051128397771.png
若将上锁的对象看成一棵树,那么对最下层的对象上锁,也就是对最细粒度的对象进行上锁,那么首先需要对粗粒度的对象上锁。例如图6-3,如果需要对页上的记录r进行上X锁,那么分别需要对数据库A、表、页上意向锁IX,最后对记录r上ⅹ锁。若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。举例来说,在对记录r加ⅹ锁之前,已经有事务对表1进行了S表锁,那么表1上已存在S锁,之后事务需要对记录r在表1上加上IX,由于不兼容,所以该事务需要等待表锁操作的完成。
InnoDB存储引擎支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:

  1. 意向共享锁( IS Lock),事务想要获得一张表中某几行的共享锁
  2. 意向排他锁( IX Lock),事务想要获得一张表中某几行的排他锁

由于 InnoDB存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下表所示。

  IS IX S X
  IS IX S X
IS 兼容 兼容 兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S 兼容 不兼容 兼容 不兼容
X 不兼容 不兼容 不兼容 不兼容

用户可以通过命令 SHOW ENGINE INNODB STATUS命令来查看当前锁请求的信息:
202106051128419752.png

可以看到SQL语句 select* from t where a<4 lock in share mode在等待, RECORD LOCKS space id 30 page no 3 n bits 72 index' PRIMARY 'of table'test''t trx id 48B89BD lock mode X locks rec but not gap表示锁住的资源。 locks rec but not gap代表锁住的是一个索引,不是一个范围。
在InnoDB1.0版本之前,用户只能通过命令 SHOW FULL PROCESSLIST,SHOW ENGINE INNODB STATUS等来查看当前数据库中锁的请求,然后再判断事务锁的情况。从 InnoDB1.0开始,在 INFORMATION SCHEMA架构下添加了表 INNODB_TRX、INNODB_LOCKS、 INNODB_LOCK_WAITS。通过这三张表,用户可以更简单地监控当前事务并分析可能存在的锁问题。我们将通过具体的示例来分析这三张表,在之前,首先了来看下表中表 INNODB_TRX的定义,其由8个字段组成。

字段名 说明
字段名 说明
trx_id InnoDB存储引擎内部唯一的事务ID
trx_state 当前事务的状态
trx_started 事务的开始时间
trx_requested_lock_id 等待事务的锁ID。如trx_state的状态为LOCKWAIT,那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state不是LOCKWAIT,则该值为NULL
trx_wait_started 事务等待开始的时间
trx_weight 事务的权重,反映了一个事务修改和锁住的行数。在InnoDB存储引擎中,当发生死锁需要回滚时,InnoDB存储引擎会选择该值最小的进行回滚
trx_mysql_thread_id MySQL中的线程ID,SHOWPROCESSLIST显示的结果
trx_query 事务运行的SQL语句

接着来看一个具体的例子:

202106051128431923.png

通过列state可以观察到trx_id为730FEE的事务当前正在运行,而trx_id为7311F4的事务目前处于“ LOCK WAIT”状态,且运行的SQL语句是 select * from parent lock in share mode。该表只是显示了当前运行的 InnoDB事务,并不能直接判断锁的一些情况。
如果需要查看锁,则还需要访问表 INNODB_LOCKS,该表的字段组成如下表所示。

字段名 说明
字段名 说明
lock_id 锁的ID
lock_trx_id 事务ID
lock_mode 锁的模式
lock_type 锁的类型,表锁还是行锁
lock_table 要加锁的表
lock_index 锁住的索引
lock_space 锁对象的spaceid
lock_page 事务锁定页的数量。若是表锁,则该值为NULL
lock_rec 事务锁定行的数量,若是表锁,则该值为NULL
lock_data 事务锁定记录的主键值,若是表锁,则该值为NULL

接着上面的例子,继续查看表 INNODB_LOCKS:

202106051128441434.png

这次用户可以清晰地看到当前锁的信息。 trx_id为730FEE的事务向表 parent加了一个X的行锁,ID为7311F4的事务向表parent申请了一个S的行锁。 lock_data都是1,申请相同的资源,因此会有等待。这也可以解释INNODB_TRX中为什么一个事务的trx_state是“ RUNNING”,另一个是“ LOCK WAIT”了。
另外需要特别注意的是,我发现 lock_data这个值并非是“可信”的值。例如当用户运行一个范围査找时, lock_data可能只返回第一行的主键值。与此同时,如果当前资源被锁住了,若锁住的页因为 InnoDB存储引擎缓冲池的容量,导致该页从缓冲池中被刷出,则査看 INNODB LOCKS表时,该值同样会显示为NUL,即 InnoDB存储引擎不会从磁盘进行再一次的查找。
在通过表 INNODB_LOCKS查看了每张表上锁的情况后,用户就可以来判断由此引发的等待情况了。当事务较小时,用户就可以人为地、直观地进行判断了。但是当事务量非常大,其中锁和等待也时常发生,这个时候就不这么容易判断。但是通过表INNODB_LOCK_WAITS,可以很直观地反映当前事务的等待。表 INNODB_LOCK_WAITS由4个字段组成,如下表所示。

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

接着上面的例子,运行如下查询:

        mysql> SELECT* FROM information_schema.INNODB_LOCK_WAITS\G;
        *****************************1.row*****************************************
        requesting_trx_id: 7311F4
        requested_lock_id: 7311F4:96:3:2
        blocking_trx_id: 730FEE
        blocking_lock_id: 730FEE:96:3:2
        1 row in set (0.00 sec)

通过上述的SQL语句,用户可以清楚直观地看到哪个事务阻塞了另一个事务。
当然,这里只给出了事务和锁的ID。如果需要,用户可以根据表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS得到更为直观的详细信息。例如,用户可以执行如下联合查询:

        SELECT
        r.trx_id waiting_trx_id,
        r.trx_mysql_thread_id waiting_thread,
        r.trx_query waiting_query,
        b.trx_id blocking_trx_id,
        b.trx_mysql_thread_id blocking thread,
        b.trx_query blocking_query
        FROM information_schema.innodb_lock_waits w
        INNER JOIN information_schema.innodb_trx b
        ON b.trx_id = w.blocking_trx_id
        INNER JOIN information_schema.innodb_trx r
        ON r.trx_id = w.requesting_trx_id\G;

202106051128480395.png


来源:https://blog.csdn.net/shenchaohao12321/category_8075653.html

点赞(1)
版权归原创作者所有,任何形式转载请联系作者; Java 技术驿站 >> 【MySQL技术内幕】35-锁的类型
上一篇
【MySQL技术内幕】34-lock与latch
下一篇
【MySQL技术内幕】36-一致性的非锁定读