江苏龙网

  1. 主页 > 生活百科 > >

两万字详解InnoDB的锁( 九 )

Innodb


两万字详解InnoDB的锁

文章插图
 
即requesting_trx_id表示事务B的事务Id，blocking_trx_id表示事务A的事务Id 。
5.2 show engine innodb statusINNODB_LOCKS 和 INNODB_LOCK_WAITS 在MySQL 8.0已被移除，其实就是不鼓励我们用这两个表来获取表信息 。我们可以用show engine innodb status获取当前系统各个事务的加锁信息 。
在看死锁日志的时候，我们一般先把这个变量
innodb_status_output_locks打开哈，它是MySQL 5.6.16 引入的
set globalinnodb_status_output_locks =on;在RR隔离级别下，我们交替执行事务A和B：
两万字详解InnoDB的锁

文章插图
 
show engine innodb status查看日志，如下：
TRANSACTIONS------------Trx id counter 1644854Purge done for trx's n:o < 1644847 undo n:o < 0 state: running but idleHistory list length 32LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 283263895935640, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 1644853, ACTIVE 7 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1MySQL thread id 7, OS thread handle 11956, query id 563 localhost ::1 root updateinsert into t5 values(6,6,6)------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc;; 1: len 4; hex 8000000a; asc;;------------------TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IXRECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc;; 1: len 4; hex 8000000a; asc;;这结构锁的关键词记住哈：
  • lock_mode X locks gap before rec表示X型的gap锁
  • lock_mode X locks rec but not gap表示 X型的记录锁（Record Lock）
  • lock mode X 一般表示 X型临建锁（next-key 锁）
以上的锁日志，我们一般关注点是一下这几个地方：
  • TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED
表示它在等这个锁
  • RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
表示一个锁结构，这个锁结构的Space ID是267，page number是4，n_bits属性为80，对应的索引是c，这个锁结构中存放的锁类型是X型的插入意向Gap锁 。
  • 0: len 4; hex 8000000a; asc ;;
对应加锁记录的详细信息，8000000a代表的值就是10，
  • TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX 表示一个插入意向表锁
这个日志例子，其实理解起来，就是事务A持有了索引c的间隙锁（~，10），而事务B想获得这个gap锁，而获取不到，就一直在等待这个插入意向锁 。
6. 手把手死锁案例分析如果发生死锁了，我们应该如何分析呢？一般分为四个步骤：
  1. show engine innodb status，查看最近一次死锁日志 。
  2. 分析死锁日志，找到关键词TRANSACTION
  3. 分析死锁日志，查看正在执行的SQL
  4. 看它持有什么锁，等待什么锁 。
6.1 一个死锁的简单例子表结构和数据如下：
CREATE TABLE t6 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;insert into t6 values(5,5,5),(10,10,10);我们开启A、B事务，执行流程如下：
两万字详解InnoDB的锁

文章插图
 
6.2 分析死锁日志
  1. show engine innodb status，查看最近一次死锁日志 。如下：
------------------------LATEST DETECTED DEADLOCK------------------------2022-05-03 22:53:22 0x2eb4*** (1) TRANSACTION:TRANSACTION 1644867, ACTIVE 31 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)MySQL thread id 5, OS thread handle 7068, query id 607 localhost ::1 root statisticsSelect * from t6 where id=10 for update*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644867 lock_mode X locks rec but not gap waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 8000000a; asc;; 1: len 6; hex 00000019193c; asc<;; 2: len 7; hex dd00000191011d; asc;; 3: len 4; hex 8000000a; asc;; 4: len 4; hex 8000000a; asc;;*** (2) TRANSACTION:TRANSACTION 1644868, ACTIVE 17 sec starting index read, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s)MySQL thread id 7, OS thread handle 11956, query id 608 localhost ::1 root statisticsSelect * from t6 where id=5 for update*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gapRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 8000000a; asc;; 1: len 6; hex 00000019193c; asc<;; 2: len 7; hex dd00000191011d; asc;; 3: len 4; hex 8000000a; asc;; 4: len 4; hex 8000000a; asc;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc;; 1: len 6; hex 00000019193c; asc<;; 2: len 7; hex dd000001910110; asc;; 3: len 4; hex 80000005; asc;; 4: len 4; hex 80000005; asc;;


推荐阅读


上一篇：css常用属性之display属性

下一篇：C语言入门算法丨冒泡排序算法详解！绝不摆烂