MySQL锁机制——你想知道的都在这!

6,540 阅读24分钟

一、锁的类型

行锁

  • 共享锁(S Lock)允许事务读一行数据
  • 排它锁 (X Lock) 允许事务写一行数据

表锁(意向锁)

锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持一种额外的锁方式

  • 意向共享锁(IS Lock)事务想要获得一张表中某几行的共享锁

  • 意向排他锁(IX Lock)事务想要获得一张表中某几行的排他锁

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

若将上锁的对象看成一棵树,那么对最下层的对象上锁,也就是对最细粒度的对象进行上锁,那么首先需要对粗粒度的对象上锁。例上图,如果需要对页上的记录r进行上X锁,那么分别需要对数据库A、表、页上意向锁IX,最后对记录r上X锁。若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。举例来说,在对记录r加X锁之前,已经有事务对表1进行了S表锁,那么表1上已存在S锁,之后事务需要对记录r在表1上加上IX,由于不兼容,所以该事务需要等待表锁操作的完成。

意向锁到底有什么作用?

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁,如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响。为了解决这个问题,可以在表级上引入新的锁类型来表示其所属行的加锁情况,这就引出了“意向锁”的概念。

举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁,那么假如事务A在更新一条记录之前,先加意向锁,再加X锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务A释放,而无须逐条记录去检测。事务B更新表时,其实无须知道到底哪一行被锁了,它只要知道反正有一行被锁了就行了。

主要作用是处理行锁和表锁之间的矛盾,能够显示“某个事务正在某一行上持有了锁,或者准备去持有锁”

二、锁的算法

  • Record Lock:单个行记录上的锁

  • Gap Lock:间隙锁,锁定一个范围,但不包含记录本身

  • Next-Key Lock∶Gap Lock+Record Lock,锁定一个范围、索引之间的间隙,并且锁定记录本身;目的是为了防止幻读

三、mysql如何做到读写并行(多版本控制)?

多版本并发控制 MVCC,是行级锁的一个变种,通过保存数据在某个时间节点的快照(snapshot),类似实现了行级锁。由此不同事务对同一表,同一时刻看到的数据可能是不一样的。 实现上通过在不同的数据行后增加创建日期版本号和删除日期版本号,且版本号不断递增,进而实现了数据快照

读的类型

  • 一致性非锁定读(快照读)
    • 在事务隔离级别提交读(RC)和可重复读(RR)下,InnoDB存储引擎使用非锁定的一致性读
      • RC模式下,读取最新的快照
      • RR模式下,读取事务开始时的快照
  • 一致性锁定读 (当前读)
    • 隔离级别为未提交读(RN)时读取都是当前读
    • SELECT…FOR UPDATE (加写锁)
    • SELECT…LOCK IN SHARE MODE (加读锁)

四、加锁处理分析

下面两条简单的SQL,他们加什么锁?

select * from t1 where id = 10

delete from t1 where id = 10

如果要分析加锁情况,必须还要知道以下的一些前提,前提不同,加锁处理的方式也不同

  • 前提一:id列是不是主键?
  • 前提二:当前系统的隔离级别是什么?
  • 前提三:id列如果不是主键,那么id列上有索引吗?
  • 前提四:id列上如果有二级索引,那么这个索引是唯一索引吗?
  • 前提五:两个SQL的执行计划是什么?索引扫描?全表扫描?

根据上述情况,有以下几种组合

  • 组合一:id列是主键,RC隔离级别
  • 组合二:id列是二级唯一索引,RC隔离级别
  • 组合三:id列是二级非唯一索引,RC隔离级别
  • 组合四:id列上没有索引,RC隔离级别
  • 组合五:id列是主键,RR隔离级别
  • 组合六:id列是二级唯一索引,RR隔离级别
  • 组合七:id列是二级非唯一索引,RR隔离级别
  • 组合八:id列上没有索引,RR隔离级别
  • 组合九:Serializable隔离级别

排列组合还没有列举完全,但是看起来,已经很多了。真的有必要这么复杂吗?事实上,要分析加锁,就是需要这么复杂。但是从另一个角度来说,只要你选定了一种组合,SQL需要加哪些锁,其实也就确定了。接下来挑几个比较经典的组合

组合一:id主键+RC

  这个组合,是最简单,最容易分析的组合。id是主键,Read Committed隔离级别,给定SQL:delete from t1 where id = 10; 只需要将主键上,id = 10的记录加上X锁即可。如下图所示:            

      结论:id是主键时,此SQL只需要在id=10这条记录上加X锁即可。

组合二:id唯一索引+RC

  这个组合,id不是主键,而是一个Unique的二级索引键值。那么在RC隔离级别下,delete from t1 where id = 10; 需要加什么锁呢?见下图:        

  此组合中,id是unique索引,而主键是name列。此时,加锁的情况由于组合一有所不同。由于id是unique索引,因此delete语句会选择走id列的索引进行where条件的过滤,在找到id=10的记录后,首先会将unique索引上的id=10索引记录加上X锁,同时,会根据读取到的name列,回主键索引(聚簇索引),然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁?试想一下,如果并发的一个SQL,是通过主键索引来更新:update t1 set id = 100 where name = ‘d’; 此时,如果delete语句没有将主键索引上的记录加锁,那么并发的update就会感知不到delete语句的存在,违背了同一记录上的更新/删除需要串行执行的约束。

结论:若id列是unique列,其上有unique索引。那么SQL需要加两个X锁,一个对应于id unique索引上的id = 10的记录,另一把锁对应于聚簇索引上的[name='d',id=10]的记录。

组合三:id非唯一索引+RC

  相对于组合一、二,组合三又发生了变化,隔离级别仍旧是RC不变,但是id列上的约束又降低了,id列不再唯一,只有一个普通的索引。假设delete from t1 where id = 10; 语句,仍旧选择id列上的索引进行过滤where条件,那么此时会持有哪些锁?同样见下图:

  根据此图,可以看到,首先,id列索引上,满足id = 10查询条件的记录,均已加锁。同时,这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于,组合二最多只有一个满足等值查询的记录,而组合三会将所有满足查询条件的记录都加锁。

结论:若id列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁。同时,这些记录在主键索引上的记录,也会被加锁。

组合四:id无索引+RC

  相对于前面三个组合,这是一个比较特殊的情况。id列上没有索引,where id = 10;这个过滤条件,没法通过索引进行过滤,那么只能走全表扫描做过滤。对应于这个组合,SQL会加什么锁?或者是换句话说,全表扫描时,会加什么锁?这个答案也有很多:有人说会在表上加X锁;有人说会将聚簇索引上,选择出来的id = 10;的记录加上X锁。那么实际情况呢?请看下图:

  由于id列上没有索引,因此只能走聚簇索引,进行全部扫描。从图中可以看到,满足删除条件的记录有两条,但是,聚簇索引上所有的记录,都被加上了X锁。无论记录是否满足条件,全部被加上X锁。既不是加表锁,也不是在满足条件的记录上加行锁。

  有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。

注:在实际的实现中,MySQL有一些改进,在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁 (违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。

结论:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。

组合五:id主键+RR

  上面的四个组合,都是在Read Committed隔离级别下的加锁行为,接下来的四个组合,是在Repeatable Read隔离级别下的加锁行为。

  组合五,id列是主键列,Repeatable Read隔离级别,针对delete from t1 where id = 10; 这条SQL,加锁与组合一:[id主键,Read Committed]一致。

组合六:id唯一索引+RR

  与组合五类似,组合六的加锁,与组合二:[id唯一索引,Read Committed]一致。两个X锁,id唯一索引满足条件的记录上一个,对应的聚簇索引上的记录一个。

组合七:id非唯一索引+RR

  还记得前面提到的MySQL的四种隔离级别的区别吗?RC隔离级别允许幻读,而RR隔离级别,不允许存在幻读。但是在组合五、组合六中,加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下,

  组合七,Repeatable Read隔离级别,id上有一个非唯一索引,执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤,最后的加锁行为,是怎么样的呢?同样看下面这幅图:       

  此图,相对于组合三:[id列上非唯一锁,Read Committed]看似相同,其实却有很大的区别。最大的区别在于,这幅图中多了一个GAP锁,而且GAP锁看起来也不是加在记录上的,倒像是加载两条记录之间的位置,GAP锁有何用?

  其实这个多出来的GAP锁,就是RR隔离级别,相对于RC隔离级别,不会出现幻读的关键。确实,GAP锁锁住的位置,也不是记录本身,而是两条记录之间的GAP。

  如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生。

  如图中所示,有哪些位置可以插入新的满足条件的项 (id = 10),考虑到B+树索引的有序性,满足条件的项一定是连续存放的。记录[6,c]之前,不会插入id=10的记录;[6,c]与[10,b]间可以插入[10, aa];[10,b]与[10,d]间,可以插入新的[10,bb],[10,c]等;[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等;而[11,f]之后也不会插入满足条件的记录。因此,为了保证[6,c]与[10,b]间,[10,b]与[10,d]间,[10,d]与[11,f]不会插入新的满足条件的记录,MySQL选择了用GAP锁,将这三个GAP给锁起来。

  Insert操作,如insert [10,aa],首先会定位到[6,c]与[10,b]间,然后在插入前,会检查这个GAP是否已经被锁上,如果被锁上,则Insert不能插入记录。因此,通过第一遍的当前读,不仅将满足条件的记录锁上 (X锁),与组合三类似。同时还是增加3把GAP锁,将可能插入满足条件记录的3个GAP给锁上,保证后续的Insert不能插入新的id=10的记录,也就杜绝了同一事务的第二次当前读,出现幻象的情况。

  有心的朋友看到这儿,可以会问:既然防止幻读,需要靠GAP锁的保护,为什么组合五、组合六,也是RR隔离级别,却不需要加GAP锁呢?

  首先,这是一个好问题。其次,回答这个问题,也很简单。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。而组合五,id是主键;组合六,id是unique键,都能够保证唯一性。一个等值查询,最多只能返回一条记录,而且新的相同取值的记录,一定不会在新插入进来,因此也就避免了GAP锁的使用。其实,针对此问题,还有一个更深入的问题:如果组合五、组合六下,针对SQL:select * from t1 where id = 10 for update; 第一次查询,没有找到满足查询条件的记录,那么GAP锁是否还能够省略?此问题留给大家思考。

结论:Repeatable Read隔离级别下,id列上有一个非唯一索引,对应SQL:delete from t1 where id = 10; 首先,通过id索引定位到第一条满足查询条件的记录,加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录[11,f],此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。

什么时候会取得gap lock或nextkey lock 这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

组合八:id无索引+RR

  组合八,Repeatable Read隔离级别下的最后一种情况,id列上没有索引。此时SQL:delete from t1 where id = 10; 没有其他的路径可以选择,只能进行全表扫描。最终的加锁情况,如下图所示:   

  如图,这是一个很恐怖的现象。首先,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。这个示例表,只有6条记录,一共需要6个记录锁,7个GAP锁。试想,如果表上有1000万条记录呢?

  在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。

  当然,跟组合四:[id无索引, Read Committed]类似,这个情况下,MySQL也做了一些优化,就是所谓的semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁。针对上面的这个用例,就是除了记录[d,10],[g,10]之外,所有的记录锁都会被释放,同时不加GAP锁。semi-consistent read如何触发:要么是read committed隔离级别;要么是Repeatable Read隔离级别,同时设置了innodb_locks_unsafe_for_binlog 参数。更详细的关于semi-consistent read的介绍,可参考我之前的一篇博客:MySQL+InnoDB semi-consitent read原理及实现分析

结论:在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作。当然,也可以通过触发semi-consistent read,来缓解加锁开销与并发影响,但是semi-consistent read本身也会带来其他问题,不建议使用。

组合九:Serializable

  针对前面提到的简单的SQL,最后一个情况:Serializable隔离级别。对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致,因此不做介绍。

  Serializable隔离级别,影响的是SQL1:select * from t1 where id = 10; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。

结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。

五、死锁案例

1. 不同表相同记录行锁冲突

这种情况很好理解,事务A和事务B操作两张表,但出现循环等待锁情况。

2. 相同表记录行锁冲突

这种情况比较常见,之前遇到两个job在执行数据批量更新时,jobA处理的的id列表为[1,2,3,4],而job处理的id列表为[8,9,10,4,2],这样就造成了死锁。

3. 不同索引锁冲突

这种情况比较隐晦,事务A在执行时,除了在二级索引加锁外,还会在聚簇索引上加锁,在聚簇索引上加锁的顺序是[1,4,2,3,5],而事务B执行时,只在聚簇索引上加锁,加锁顺序是[1,2,3,4,5],这样就造成了死锁的可能性。

4. gap锁冲突

innodb在RR级别下,如下的情况也会产生死锁,比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。

六、如何尽可能避免死锁

  1. 以固定的顺序访问表和行。比如对第2节两个job批量更新的情形,简单方法是对id列表先排序,后执行,这样就避免了交叉等待锁的情形;又比如对于3.1节的情形,将两个事务的sql顺序调整为一致,也能避免死锁。
  2. 大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
  3. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
  4. 降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
  5. 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。=

七、如何查看锁

从InnoDB1.0开始,在INFORMATION_SCHEMA架构下添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。(详情见附录)通过这三张表,用户可以更简单地监控当前事务并分析可能存在的锁问题。

#全局分析系统上中行锁的争夺情况
show status like 'innodb_row_lock%';
#查看事务
SELECT * FROM information_schema.INNODB_TRX;
#查看锁
SELECT * FROM information_schema.INNODB_LOCKS;
#查看锁等待情况
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
#通过联合查询可以比较直观的查看哪个事务阻塞了哪个事务
SELECT r.trx_id              '等待事务ID',
       r.trx_mysql_thread_id '等待线程ID',
       r.trx_query           '等待事务运行语句',
       b.trx_id              '阻塞事务ID',
       b.trx_mysql_thread_id '阻塞线程ID',
       b.trx_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;

八、mysql是如何预防死锁的?

innodb_lock_wait_timeout 等待锁超时回滚事务

直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。

wait-for graph算法来主动进行死锁检测

每当加锁请求无法立即满足需要并进入等待时,wait-for graph算法都会被触发。

wait-for graph要求数据库保存以下两种信息:

  • 锁的信息链表

  • 事务等待链表

通过上述链表可以构造出一张图,而在这个图中若存在回路,就代表存在死锁,因此资源间相互发生等待。在wait-for graph中,事务为图中的节点。而在图中,事务T1指向T2边的定义为:

  • 事务T1等待事务T2所占用的资源

  • 事务T1最终等待T2所占用的资源,也就是事务之间在等待相同的资源,而事务T1发生在事务T2的后面

示例事务状态和锁的信息

在Transaction Wait Lists中可以看到共有4个事务t1、t2、t3、t4,故在wait-for graph中应有4个节点。而事务t2对row1占用x锁,事务t1对row2占用s锁。事务t1需要等待事务t2中row1的资源,因此在wait-for graph中有条边从节点t1指向节点t2。事务t2需要等待事务t1、t4所占用的row2对象,故而存在节点t2到节点t1、t4的边。同样,存在节点t3到节点t1、t2、t4的边,因此最终的wait-for graph如下图所示。

ps:若存在则有死锁,通常来说InnoDB存储引擎选择回滚undo量最小的事务并从新开始

附录

INNODB_ROW_LOCK

列名 描述
innodb_row_lock_current_waits 当前正在等待锁定的数量
innodb_row_lock_time 从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg 每次等待所花平均时间
innodb_row_lock_time_max 从系统启动到现在等待最常的一次所花的时间
innodb_row_lock_waits 系统启动后到现在总共等待的次数;直接决定优化的方向和策略

INNODB_TRX

提供有关当前正在内部执行的每个事务的信息 InnoDB,包括事务是否在等待锁定,事务何时启动以及事务正在执行的SQL语句(如果有)。详见dev.mysql.com/doc/refman/…

列名 描述
TRX_ID 事务Id
TRX_WEIGHT 事务的权重,反映(但不一定是确切的计数)更改的行数和事务锁定的行数。要解决死锁,请 InnoDB``选择权重最小的事务作为回滚的“ 受害者 ”。无论更改和锁定行的数量如何,已更改非事务表的事务都被认为比其他事务更重。
TRX_STATE 事务执行状态。允许值是 RUNNING,LOCK WAIT, ROLLING BACK,和 COMMITTING
TRX_STARTED 交易开始时间。
TRX_REQUESTED_LOCK_ID 事务当前正在等待的锁的ID,如果TRX_STATE是LOCK WAIT; 否则NULL``。
TRX_WAIT_STARTED 交易开始等待锁定的时间,如果 TRX_STATE是LOCK WAIT; 否则NULL``。
TRX_MYSQL_THREAD_ID MySQL线程ID,与show processlist中的ID值相对应
TRX_QUERY 事务正在执行的SQL语句
TRX_OPERATION_STATE 交易的当前操作,如果有的话; 否则 NULL``。
TRX_TABLES_IN_USE InnoDB``处理此事务的当前SQL语句时使用 的表数。
TRX_TABLES_LOCKED InnoDB``当前SQL语句具有行锁定 的表的数量。(因为这些是行锁,而不是表锁,所以通常仍可以通过多个事务读取和写入表,尽管某些行被锁定。)
TRX_LOCK_STRUCTS 事务保留的锁数。
TRX_LOCK_MEMORY_BYTES 内存中此事务的锁结构占用的总大小
TRX_ROWS_LOCKED 此交易锁定的大致数字或行数。该值可能包括实际存在但对事务不可见的删除标记行
TRX_ROWS_MODIFIED 此事务中已修改和插入的行数。
TRX_CONCURRENCY_TICKETS 一个值,指示当前事务在被换出之前可以执行多少工作
TRX_ISOLATION_LEVEL 当前事务的隔离级别。
TRX_UNIQUE_CHECKS 是否为当前事务打开或关闭唯一检查。例如,在批量数据加载期间可能会关闭它们
TRX_FOREIGN_KEY_CHECKS 是否为当前事务打开或关闭外键检查。例如,在批量数据加载期间可能会关闭它们
TRX_LAST_FOREIGN_KEY_ERROR 最后一个外键错误的详细错误消息(如果有); 否则NULL``
TRX_ADAPTIVE_HASH_LATCHED 自适应哈希索引是否被当前事务锁定。当自适应哈希索引搜索系统被分区时,单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由innodb_adaptive_hash_index_parts``,默认设置为8。
TRX_ADAPTIVE_HASH_TIMEOUT 是否立即为自适应哈希索引放弃搜索锁存器,或者在MySQL的调用之间保留它。当没有自适应哈希索引争用时,该值保持为零,语句保留锁存器直到它们完成。在争用期间,它倒计时到零,并且语句在每次行查找后立即释放锁存器。当自适应散列索引搜索系统被分区(受控制 innodb_adaptive_hash_index_parts``)时,该值保持为0。
TRX_IS_READ_ONLY 值为1表示事务是只读的。
TRX_AUTOCOMMIT_NON_LOCKING 值为1表示事务是 SELECT](https://dev.mysql.com/doc/refman/5.7/en/select.html)不使用FOR UPDATEor或 LOCK IN SHARED MODE子句的语句,并且正在执行, [autocommit因此事务将仅包含此一个语句。当此列和TRX_IS_READ_ONLY都为1时,InnoDB优化事务以减少与更改表数据的事务关联的开销

INNODB_LOCKS

提供有关InnoDB 事务已请求但尚未获取的每个锁的信息,以及事务持有的阻止另一个事务的每个锁。详见dev.mysql.com/doc/refman/…

列名 描述
LOCK_ID 一个唯一的锁ID号,内部为 InnoDB``。
LOCK_TRX_ID 持有锁的交易的ID
LOCK_MODE 如何请求锁定。允许锁定模式描述符 S,X, IS,IX, GAP,AUTO_INC,和 UNKNOWN``。锁定模式描述符可以组合使用以识别特定的锁定模式。
LOCK_TYPE 锁的类型
LOCK_TABLE 已锁定或包含锁定记录的表的名称
LOCK_INDEX 索引的名称,如果LOCK_TYPE是 RECORD; 否则NULL
LOCK_SPACE 锁定记录的表空间ID,如果 LOCK_TYPE是RECORD; 否则NULL``
LOCK_PAGE 锁定记录的页码,如果 LOCK_TYPE是RECORD; 否则NULL``。
LOCK_REC 页面内锁定记录的堆号,如果 LOCK_TYPE是RECORD; 否则NULL``。
LOCK_DATA 与锁相关的数据(如果有)。如果 LOCK_TYPE是RECORD,是锁定的记录的主键值,否则NULL。此列包含锁定行中主键列的值,格式为有效的SQL字符串。如果没有主键,LOCK_DATA则是唯一的InnoDB内部行ID号。如果对键值或范围高于索引中的最大值的间隙锁定,则LOCK_DATA 报告_supremum_ pseudo-record。当包含锁定记录的页面不在缓冲池中时(如果在保持锁定时将其分页到磁盘),InnoDB不从磁盘获取页面,以避免不必要的磁盘操作。相反, LOCK_DATA设置为 NULL``。

INNODB_LOCK_WAITS

包含每个被阻止InnoDB 事务的一个或多个行,指示它已请求的锁以及阻止该请求的任何锁。详见dev.mysql.com/doc/refman/…

列名 描述
REQUESTING_TRX_ID 请求(阻止)事务的ID。
REQUESTED_LOCK_ID 事务正在等待的锁的ID。
BLOCKING_TRX_ID 阻止事务的ID。
BLOCKING_LOCK_ID 由阻止另一个事务继续进行的事务所持有的锁的ID

引用文献

《MySQL技术内幕:InnoDB存储引擎》

何登成MySQL 加锁处理分析

Mysql加锁过程详解

数据库事务和锁(三)

针对MySQL死锁问题的思路分析