本篇内容介绍了“MySQL加锁机制的验证记录是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

开启 InnoDB Monitor

SETGLOBALinnodb_status_output=ON;--开启输出SETGLOBALinnodb_status_output_locks=ON;--开启锁信息输出

注意这些选项在 mysql 重启后会恢复默认值。接下来使用命令查看信息:

SHOWENGINEINNODBSTATUS\G

样例输出,我们只关心锁相关的内容:

---TRANSACTION929632,ACTIVE27sec2lockstruct(s),heapsize1136,1rowlock(s),undologentries1MySQLthreadid1309,OSthreadhandle123145430310912,queryid9179localhostrootTABLELOCKtable`test`.`id_pk_rc`trxid929632lockmodeIXRECORDLOCKSspaceid1813pageno3nbits72indexPRIMARYoftable`test`.`id_pk_rc`trxid929632lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields4;compactformat;infobits320:len4;hex80000005;asc;;1:len6;hex0000000e2f60;asc/`;;2:len7;hex4c000002222e83;ascL".;;3:len1;hex63;ascc;;

“page no 3 n bits 72” 代表在第 3 页的记录上,lock bitmap 共 72 位

“index PRIMARY of …” 代表锁在某个索引上,PRIMARY 代表锁在主键上

“lock_mode X” 锁模式,X 代表互斥,锁模式可以参数官方文档 InnoDB Locking

“locks rec but not gap” 代表记录锁,“locks gap before rec” 代表间隙锁,没有说明则代表 Next Key Lock

“heap no 4” 代表记录的序号,0 代表 infimum 记录、1 代表 supremum 记录,用户记录从 2 开始

PHYSICAL RECORD 后面的内容是索引记录的内存结构,通常没办法直接阅读

这个记录里没法直接看出锁住了哪些记录。一种方法是通过 select * from information_schema.innodb_locks \G; 查看抢锁没抢到的信息,为了查看记录,在测试时可以另开一个会话,用诸如 SELECT * FROM ... WHERE ... FOR UPDATE 来抢锁,这样就可以看出锁在哪个记录上了。样例输出:

lock_id|929771:1817:4:4lock_trx_id|929771lock_mode|Xlock_type|RECORDlock_table|`test`.`id_si_rc`lock_index|id_silock_space|1817lock_page|4lock_rec|4lock_data|5,3--注意这里是数据标识

还有一个工具好用的工具 innodb_ruby 可以用来解析 MySQL 的静态文件。Monitor 日志里我们知道是哪个页的哪条记录,可以使用innodb_ruby 来找到对应的记录。(不过不建议在生产上使用)

不同情形下加锁验证

我们会考查 DELETE FROM t1 WHERE id = 5 语句在不同情形下的加锁情况,通过构造数据、执行语句、查看 Monitor 日志来验证加锁的机制。

主键 + RC

结论:只对 ID = 5 这条记录加 Record Lock

首先建表准备数据:

--建表CREATETABLEid_pk_rc(idintprimarykey,namevarchar(32));--准备数据INSERTINTOid_pk_rcvalues(1,'a');INSERTINTOid_pk_rcvalues(3,'b');INSERTINTOid_pk_rcvalues(5,'c');INSERTINTOid_pk_rcvalues(7,'c');INSERTINTOid_pk_rcvalues(9,'b');

执行语句

--设置为RC隔离级别SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;BEGIN;--开启事务DELETEFROMid_pk_rcWHEREid=5;--先不结束事务,验证MonitorOutput再用ROLLBACK;回滚

Monitor 输出日志:

---TRANSACTION929632,ACTIVE27sec2lockstruct(s),heapsize1136,1rowlock(s),undologentries1MySQLthreadid1309,OSthreadhandle123145430310912,queryid9179localhostrootTABLELOCKtable`test`.`id_pk_rc`trxid929632lockmodeIXRECORDLOCKSspaceid1813pageno3nbits72indexPRIMARYoftable`test`.`id_pk_rc`trxid929632lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields4;compactformat;infobits320:len4;hex80000005;asc;;1:len6;hex0000000e2f60;asc/`;;2:len7;hex4c000002222e83;ascL".;;3:len1;hex63;ascc;;

看到输出里有 lock_mode X locks rec but not gap ,可以确定持有的是记录锁。

唯一索引 + RC

结论:索引和聚簇索引/主键中都对 ID = 5 加 Record Lock

首先建表准备数据:

--建表CREATETABLEid_ui_rc(pkintprimarykey,idint,namevarchar(32));CREATEUNIQUEINDEXid_uiONid_ui_rc(id);--准备数据INSERTINTOid_ui_rcvalues(1,1,'a');INSERTINTOid_ui_rcvalues(2,3,'b');INSERTINTOid_ui_rcvalues(3,5,'c');INSERTINTOid_ui_rcvalues(4,7,'c');INSERTINTOid_ui_rcvalues(5,9,'b');

执行语句:

--设置为RC隔离级别SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;BEGIN;--开启事务DELETEFROMid_ui_rcWHEREid=5;--先不结束事务,验证MonitorOutput再用ROLLBACK;回滚

Monitor 输出日志:

---TRANSACTION929694,ACTIVE6sec3lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid1309,OSthreadhandle123145430310912,queryid9241localhostrootTABLELOCKtable`test`.`id_ui_rc`trxid929694lockmodeIXRECORDLOCKSspaceid1815pageno4nbits72indexid_uioftable`test`.`id_ui_rc`trxid929694lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000005;asc;;1:len4;hex80000003;asc;;RECORDLOCKSspaceid1815pageno3nbits72indexPRIMARYoftable`test`.`id_ui_rc`trxid929694lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits320:len4;hex80000003;asc;;1:len6;hex0000000e2f9e;asc/;;2:len7;hex7a0000059525c9;ascz%;;3:len4;hex80000005;asc;;4:len1;hex63;ascc;;

可以看到分别对 index id_ui 和 index PRIMARY 加了 Record Lock。

非唯一索引 + RC

结论:会对所有 ID = 5 的索引记录加 Record Lock,同时对主键加 Record Lock。

首先建表准备数据:

--建表CREATETABLEid_si_rc(pkintprimarykey,idint,namevarchar(32));CREATEINDEXid_siONid_si_rc(id);--准备数据INSERTINTOid_si_rcvalues(1,1,'a');INSERTINTOid_si_rcvalues(2,3,'b');INSERTINTOid_si_rcvalues(3,5,'c');INSERTINTOid_si_rcvalues(4,7,'c');INSERTINTOid_si_rcvalues(5,5,'b');

执行语句:

--设置为RC隔离级别SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;BEGIN;--开启事务DELETEFROMid_si_rcWHEREid=5;--先不结束事务,验证MonitorOutput再用ROLLBACK;回滚

Monitor 输出日志(省略了 PHYSICAL RECORD 的内容):

---TRANSACTION929779,ACTIVE3sec3lockstruct(s),heapsize1136,4rowlock(s),undologentries2MySQLthreadid1309,OSthreadhandle123145430310912,queryid9325localhostrootTABLELOCKtable`test`.`id_si_rc`trxid929779lockmodeIXRECORDLOCKSspaceid1817pageno4nbits72indexid_sioftable`test`.`id_si_rc`trxid929779lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits32...Recordlock,heapno6PHYSICALRECORD:n_fields2;compactformat;infobits32...RECORDLOCKSspaceid1817pageno3nbits72indexPRIMARYoftable`test`.`id_si_rc`trxid929779lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits32...Recordlock,heapno6PHYSICALRECORD:n_fields5;compactformat;infobits32...

可以看到一共有 4 条记录,首先可以看到索引 id_si 和 PRIMARY 分别锁住了两条记录,加的锁都是 X Record Lock No Gap,也就是记录锁。我们通过 select * from information_schema.innodb_locks \G; 查看是锁住了 3, 5 这两条记录。

lock_id|929779:1817:4:4lock_trx_id|929779lock_mode|Xlock_type|RECORDlock_table|`test`.`id_si_rc`lock_index|id_silock_space|1817lock_page|4lock_rec|4lock_data|5,3<-注意这里

无索引 + RC

结论:对所有记录加 Record Lock 再释放不匹配的记录锁

这个情形比较特殊,涉及两个 知识点

鸿蒙官方战略合作共建——HarmonyOS技术社区

MySQL 加锁时是对处理过程中“扫描”到的记录加锁,不管这条记录最终是不是通过 WHERE 语句剔除了

对于 READ COMMITTED,MySQL 在扫描结束后,会违反 #1,释放 WHERE 条件不满足的记录锁

首先建表准备数据:

--建表CREATETABLEid_ni_rc(pkintprimarykey,idint,namevarchar(32));--准备数据INSERTINTOid_ni_rcvalues(1,1,'a');INSERTINTOid_ni_rcvalues(2,3,'b');INSERTINTOid_ni_rcvalues(3,5,'c');INSERTINTOid_ni_rcvalues(4,7,'c');INSERTINTOid_ni_rcvalues(5,5,'b');

执行语句:

--设置为RC隔离级别SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;BEGIN;--开启事务DELETEFROMid_ni_rcWHEREid=5;--先不结束事务,验证MonitorOutput再用ROLLBACK;回滚

Monitor 输出日志(省略了 PHYSICAL RECORD 的内容):

---TRANSACTION1446,ACTIVE17sec2lockstruct(s),heapsize1136,2rowlock(s),undologentries2MySQLthreadid7,OSthreadhandle123145446559744,queryid267localhostrootTABLELOCKtable`test`.`id_ni_rc`trxid1446lockmodeIXRECORDLOCKSspaceid27pageno3nbits72indexPRIMARYoftable`test`.`id_ni_rc`trxid1446lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits32...Recordlock,heapno6PHYSICALRECORD:n_fields5;compactformat;infobits32...

看到 TABLE LOCK 的状态是 IX 说明没有加表锁。同时看到最终锁住的只有heap_no = 4 和 6 的两条记录。

主键 + RR

当 ID 为主键时,在 RR 隔离级别下,加锁情况与一致,都是对主键记录加 Record Lock。

唯一索引 + RR

当 ID 为唯一索引时,在 RR 隔离级别下,加锁情况与一致,都是对索引记录和聚簇索引/主键 Record Lock。

非唯一索引 + RR

结论:对索引记录 Next Key Lock,末尾加 Gap Lock,同时对主键加 Record Lock

Repeatable Read 和 Read Committed 隔离级别的主要区别是 RR 要防止幻读。幻读指的是执行同一个 SQL 两次得到的结果不同。考虑下面的场景:

SELECTcount(*)FROMtWHEREid=5FORUPDATEid=5SELECTcount(*)FROMtWHEREid=5FORUPDATE

为了要避免这种情况,在 RR 隔离级别下,在 #1 执行时不仅要锁住现有的 ID=5 的索引,还需要阻止 ID = 5 的记录插入(即 #2)。而 Gap Lock 就是实现这个目的的一种手段。

考虑到索引是有序的,因此如果索引里有 [3, 5, 5, 7] 这几个元素,则可以通过锁住 (3, 5) 、 (5, 7) 这几个区间,加上 [5] 这几个已经存在的元素,就可以阻止 ID = 5 的记录插入。Gap Lock(间隙锁)的含义是锁住区间,而如果加上右边的闭区间,如 (3, 5] 就称为记录 5 的 Next-Key Lock。

InnoDB 在扫描行时会为扫到的行加上 Next-Key Lock,对于上面的数据,扫到记录 5 时,会加上 (3, 5] 锁,同时,还会对下一个记录加上 Gap Lock,即 (5, 7) ,造成 (3, 7) 都无法插入的现象,验证 MySQL 实现如下:

首先建表准备数据:

--建表CREATETABLEid_si_rr(pkintprimarykey,idint,namevarchar(32));CREATEINDEXid_siONid_si_rr(id);--准备数据INSERTINTOid_si_rrvalues(1,1,'a');INSERTINTOid_si_rrvalues(2,3,'b');INSERTINTOid_si_rrvalues(3,5,'c');INSERTINTOid_si_rrvalues(4,7,'c');INSERTINTOid_si_rrvalues(5,5,'b');

执行语句:

--设置为RC隔离级别SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;BEGIN;--开启事务DELETEFROMid_si_rrWHEREid=5;--先不结束事务,验证MonitorOutput再用ROLLBACK;回滚

Monitor 输出日志(省略 PHYSICAL RECORD 的内容):

---TRANSACTION929891,ACTIVE6sec4lockstruct(s),heapsize1136,5rowlock(s),undologentries2MySQLthreadid1309,OSthreadhandle123145430310912,queryid9442localhostrootTABLELOCKtable`test`.`id_si_rr`trxid929891lockmodeIXRECORDLOCKSspaceid1820pageno4nbits72indexid_sioftable`test`.`id_si_rr`trxid929891lock_modeXRecordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits32...Recordlock,heapno6PHYSICALRECORD:n_fields2;compactformat;infobits32...RECORDLOCKSspaceid1820pageno3nbits72indexPRIMARYoftable`test`.`id_si_rr`trxid929891lock_modeXlocksrecbutnotgapRecordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits32...Recordlock,heapno6PHYSICALRECORD:n_fields5;compactformat;infobits32...RECORDLOCKSspaceid1820pageno4nbits72indexid_sioftable`test`.`id_si_rr`trxid929891lock_modeXlocksgapbeforerecRecordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits0...

首先我们看到:

id_siid_si

为什么唯一索引 + RR 就不需要 Gap Lock 呢?是因为我们的核心目的是不让其它事务插入 ID = 5 的记录,如果 ID 是唯一索引,锁住记录本身就能够满足要求了,不再需要 Gap Lock。

无索引 + RR

结论:对所有行都加记录锁,且索引前后都要加 Gap Lock

首先建表准备数据:

--建表CREATETABLEid_ni_rr(pkintprimarykey,idint,namevarchar(32));--准备数据INSERTINTOid_ni_rrvalues(1,1,'a');INSERTINTOid_ni_rrvalues(2,3,'b');INSERTINTOid_ni_rrvalues(3,5,'c');INSERTINTOid_ni_rrvalues(4,7,'c');INSERTINTOid_ni_rrvalues(5,5,'b');

执行语句:

--设置为RC隔离级别SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;BEGIN;--开启事务DELETEFROMid_ni_rrWHEREid=5;--先不结束事务,验证MonitorOutput再用ROLLBACK;回滚

Monitor 输出日志(省略了部分信息):

---TRANSACTION929980,ACTIVE5sec2lockstruct(s),heapsize1136,6rowlock(s),undologentries2MySQLthreadid1309,OSthreadhandle123145430310912,queryid9529localhostrootTABLELOCKtable`test`.`id_ni_rr`trxid929980lockmodeIXRECORDLOCKSspaceid1822pageno3nbits72indexPRIMARYoftable`test`.`id_ni_rr`trxid929980lock_modeXRecordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits00:len8;hex73757072656d756d;ascsupremum;;Recordlock,heapno2PHYSICALRECORD:n_fields5;compactformat;infobits0...Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0...Recordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits32...Recordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits0...Recordlock,heapno6PHYSICALRECORD:n_fields5;compactformat;infobits32...

首先看到 TABLE LOCK 的状态是 IX 说明没有加表锁。同时看到锁住了 heap no 2~6的记录,对应数据库中的 5 条记录。另外这里的锁是 Next Key Lock,加上 heap no 为 1 的 “supremum” 记录的 gap lock,锁住了所有已经存在和不存在的行。因此如果执行 SELECT * FROM id_ni_rc WHERE id = 0 FOR UPDATE 也会阻塞,尽管 0 记录不在数据库中。

死锁验证

死锁与获取锁的顺序有关,一条语句(如 INSERT、DELETE)中对不同行、不同索引的加锁存在先后,因此不同事务内的语句执行时,有可能产生死锁。常见死锁原因(摘自 MySQL InnoDB锁和死锁 ):

同一索引上,两个session相反的顺序加锁多行记录

UPDATE/DELETE 通过不同的二级索引更新多条记录,可能造成在 Primary key 上不同的加锁顺序

Primary key 和 Secondary index,通过 primary key 找到记录,更新 Secondary index 字段与通过 Secondary index 更新记录

样例情形:

首先建表准备数据:

CREATETABLEdeadlock(idintprimarykey,namevarchar(32),regint);CREATEINDEXdeadlock_nameONdeadlock(name);CREATEINDEXdeadlock_regONdeadlock(reg);--准备数据INSERTINTOdeadlockvalues(1,'x',5);INSERTINTOdeadlockvalues(2,'b',4);INSERTINTOdeadlockvalues(3,'x',3);INSERTINTOdeadlockvalues(4,'d',2);INSERTINTOdeadlockvalues(5,'e',1);

两个事务分别“同时”执行:

--TransactionA|--TransactionBDELETEFROMdeadlockWHEREname='x';|DELETEFROMdeadlockWHEREreg>=2;

其中一个事务可能会检测到死锁而出错。Monitor 日志里找到 “LATEST DETECTED DEADLOCK” 可以看到记录的死锁原因(这个示例复现出的问题与上图不直接一致):

------------------------LATESTDETECTEDDEADLOCK------------------------2020-12-1315:59:400x700007a56000***(1)TRANSACTION:TRANSACTION930064,ACTIVE0secstartingindexreadmysqltablesinuse1,locked1LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s)MySQLthreadid1309,OSthreadhandle123145430310912,queryid9616localhostrootupdatingDELETEFROMdeadlockWHEREname='x'***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid1825pageno3nbits72indexPRIMARYoftable`test`.`deadlock`trxid930064lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno2PHYSICALRECORD:n_fields5;compactformat;infobits32...***(2)TRANSACTION:TRANSACTION930063,ACTIVE0secupdatingordeletingmysqltablesinuse1,locked13lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid1308,OSthreadhandle123145430589440,queryid9615localhostrootupdatingDELETEFROMdeadlockWHEREreg>=2***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid1825pageno3nbits72indexPRIMARYoftable`test`.`deadlock`trxid930063lock_modeXRecordlock,heapno2PHYSICALRECORD:n_fields5;compactformat;infobits32...***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid1825pageno4nbits72indexdeadlock_nameoftable`test`.`deadlock`trxid930063lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits0...***WEROLLBACKTRANSACTION(1)

我们看到:

第一个事务在等待 PRIMARY 索引上 heap_no = 2 的记录的 Record Lock

第二个事务已经取得 PRIMARY 索引上 heap_no = 2 的 Next Key Lock

同时第二个事务在等待 deadlock_name 索引上 heap_no = 2 的 Record Lock

MySQL 选择回滚第一个事务

更新操作如 UPDATE/DELETE 加锁的顺序为: 查询索引 > 主键索引 > 其它二级索引 。如上例中,第二个事务已经锁住了主键索引,准备锁住另一个二级索引 deadlock_name ,而第一个已经锁住了 deadlock_name ,准备锁主键索引,造成死锁。

“MySQL加锁机制的验证记录是什么”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!