mysql innodb锁查询_MySQL InnoDB锁信息阅读

下面主要分析一下 InnoDB 加锁的详细信息，主要用于分析一条语句的详细加锁结构以及加了什么锁。

记得先打开 InnoDB monitor 中的锁详细信息输出，只在 MySQL 下有用，在 MariaDB 下不起作用。

set GLOBAL innodb_status_output_locks=ON;

1

setGLOBALinnodb_status_output_locks=ON;

然后创建一个模拟表：

create table fd(id int primary key, b int, index(b));

insert into fd values(1,1),(3,1),(5,3),(7,6),(10,8);

1

2

createtablefd(idintprimarykey,bint,index(b));

insertintofdvalues(1,1),(3,1),(5,3),(7,6),(10,8);

RR隔离级别+普通索引

主要看一下这个语句的加锁信息。

begin;

select * from fd where b=3 for update;

1

2

begin;

select*fromfdwhereb=3forupdate;

执行 show engine innodb status 命令得到锁信息，如下：

---TRANSACTION 8067, ACTIVE 6 sec

4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

#-- 上面表示4个lock结构，包括1个表意向锁、1个二级索引锁、1个主键锁、以及1个Gap锁，下面有说明;

#-- 另外有3个行锁；

#-- 1条undo log，整个事务修改的行数，每行产生一条undo log;

MySQL thread id 2, OS thread handle 0x7f2090c3f700, query id 381 localhost root cleaning up

TABLE LOCK table `test`.`fd` trx id 8067 lock mode IX

#-- 表锁，fd表上面的IX意向锁;

RECORD LOCKS space id 14 page no 4 n bits 72 index `b` of table `test`.`fd` trx id 8067 lock_mode X

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000003; asc     ;; #-- 字段b的值是3(4字节十六进制0003转换为10进制为3)

1: len 4; hex 80000005; asc     ;; #-- 主键的值是5

#-- lock_mode X表示next-key锁，索引b加(1,3]、(3,6);

#-- space id 14 page no 4表示fd表的空间id为14，而锁住的数据在4号数据页上(MySQL就是根据space id加page no定位数据页);

#-- n_fields 2表示这一纪录有2列；

#-- heap no 4 PHYSICAL RECORD锁住的物理记录是那一条，heap no是每条记录标识;

#-- info bits 0表示这是一条未被删除的记录，如果是32则表示是已经被标记为删除的记录，但还没有被purge;

RECORD LOCKS space id 14 page no 3 n bits 72 index `PRIMARY` of table `test`.`fd` trx id 8067 lock_mode X locks rec but not gap

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000005; asc     ;; #-- 主键的数值是5

1: len 6; hex 000000001f7d; asc      };;  #-- TransactionID(事务ID号)

2: len 7; hex da0000017a0128; asc     z (;;  #-- Roll Pointer(回滚指针号)

3: len 4; hex 80000003; asc     ;; #-- 第二个字段的数据3

#-- lock_mode X locks rec but not gap表示这是一个记录锁，主键上锁住id=5的这条记录;

#-- n_fields 4表示字段数，其中包括InnoDB自动创建的事务ID、回滚指针列，其余就是表的字段；如果没有显式添加主键，就会有一个ROW_ID列;

RECORD LOCKS space id 14 page no 4 n bits 72 index `b` of table `test`.`fd` trx id 8067 lock_mode X locks gap before rec

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000006; asc     ;;

1: len 4; hex 80000007; asc     ;;

#-- lock_mode X locks gap before rec表示这是一个gap锁，加锁区间为(3,6);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

---TRANSACTION8067,ACTIVE6sec

4lockstruct(s),heapsize1184,3rowlock(s),undologentries1

#-- 上面表示4个lock结构，包括1个表意向锁、1个二级索引锁、1个主键锁、以及1个Gap锁，下面有说明;

#-- 另外有3个行锁；

#-- 1条undo log，整个事务修改的行数，每行产生一条undo log;

MySQLthreadid2,OSthreadhandle0x7f2090c3f700,queryid381localhostrootcleaningup

TABLELOCKtable`test`.`fd`trxid8067lockmodeIX

#-- 表锁，fd表上面的IX意向锁;

RECORDLOCKSspaceid14pageno4nbits72index`b`oftable`test`.`fd`trxid8067lock_modeX

Recordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits0

0:len4;hex80000003;asc    ;;#-- 字段b的值是3(4字节十六进制0003转换为10进制为3)

1:len4;hex80000005;asc    ;;#-- 主键的值是5

#-- lock_mode X表示next-key锁，索引b加(1,3]、(3,6);

#-- space id 14 page no 4表示fd表的空间id为14，而锁住的数据在4号数据页上(MySQL就是根据space id加page no定位数据页);

#-- n_fields 2表示这一纪录有2列；

#-- heap no 4 PHYSICAL RECORD锁住的物理记录是那一条，heap no是每条记录标识;

#-- info bits 0表示这是一条未被删除的记录，如果是32则表示是已经被标记为删除的记录，但还没有被purge;

RECORDLOCKSspaceid14pageno3nbits72index`PRIMARY`oftable`test`.`fd`trxid8067lock_modeXlocksrecbutnotgap

Recordlock,heapno4PHYSICALRECORD:n_fields4;compactformat;infobits0

0:len4;hex80000005;asc    ;;#-- 主键的数值是5

1:len6;hex000000001f7d;asc     };; #-- TransactionID(事务ID号)

2:len7;hexda0000017a0128;asc    z(;; #-- Roll Pointer(回滚指针号)

3:len4;hex80000003;asc    ;;#-- 第二个字段的数据3

#-- lock_mode X locks rec but not gap表示这是一个记录锁，主键上锁住id=5的这条记录;

#-- n_fields 4表示字段数，其中包括InnoDB自动创建的事务ID、回滚指针列，其余就是表的字段；如果没有显式添加主键，就会有一个ROW_ID列;

RECORDLOCKSspaceid14pageno4nbits72index`b`oftable`test`.`fd`trxid8067lock_modeXlocksgapbeforerec

Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits0

0:len4;hex80000006;asc    ;;

1:len4;hex80000007;asc    ;;

#-- lock_mode X locks gap before rec表示这是一个gap锁，加锁区间为(3,6);

可以看到，在 RR 隔离级别下，next-key 锁是分为 gap锁+记录锁 分别添加的。在我们这个例子中，加锁规则是 (1,3]、(3，6)，也就是说不光是对执行操作的记录本身(b=3) 加了x 锁，同时还对 (1,3)、(3,6) 这个区间加锁。所以在上面的情况下，在其他会话中进行如下操作以及对应的结果为：

INSERT INTO fd SELECT 4,2;

(—执行不成功，因为索引b的 (1,3]、(3,6) 这个区间被锁)

INSERT INTO fd SELECT 6,5;

(—执行不成功，因为索引b的 (1,3]、(3,6)这个区间被锁)

INSERT INTO fd SELECT 8,6;

(—执行成功，因为主键值8比主键值7大，所以落在 (7,6) 记录后面，无锁)

INSERT INTO fd SELECT 6,7;

(—执行成功，因为主键只锁了id=5这条记录，而b列的0也不在 (1,3]、(3,6) 锁范围内)

INSERT INTO fd SELECT 6,6;

(–执行不成功，因为主键6比主键7小，所以 (6,6) 这条记录需要插在 (7,6) 记录前，被索引b的 (1,3]、(3,6)这个区间锁了)

进制转换

从锁信息中可以看到，字段信息都是十六进制的，所以就牵扯到转换为列对应的各种数据类型。

十六进制转十进制

mysql> select CONV('0003',16,10);

+----------------------+

| CONV('000003',16,10) |

+----------------------+

| 3 |

+----------------------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

mysql>selectCONV('0003',16,10);

+----------------------+

|CONV('000003',16,10)|

+----------------------+

|3|

+----------------------+

1rowinset(0.00sec)

十六进制转字符串

mysql> select hex('中国');

+---------------+

| hex('中国') |

+---------------+

| E4B8ADE59BBD |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT CONVERT( unhex('E4B8ADE59BBD') USING utf8);

+--------------------------------------------+

| CONVERT( unhex('E4B8ADE59BBD') USING utf8) |

+--------------------------------------------+

| 中国 |

+--------------------------------------------+

1 row in set, 1 warning (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql>selecthex('中国');

+---------------+

|hex('中国')|

+---------------+

|E4B8ADE59BBD|

+---------------+

1rowinset(0.00sec)

mysql>SELECTCONVERT(unhex('E4B8ADE59BBD')USINGutf8);

+--------------------------------------------+

|CONVERT(unhex('E4B8ADE59BBD')USINGutf8)|

+--------------------------------------------+

|中国|

+--------------------------------------------+

1rowinset,1warning(0.00sec)

十六进制转日期

十六进制转日期这个转换比较。

0: len 3; hex 8fc72b; asc +;;

1

0:len3;hex8fc72b;asc+;;

实际的数值是 fc72b ；前 3 位 是年数 x 2 转换 16 进制，第 4 位是月数 x 2 转换 16 进制，超过 15 向上进位；第 5 位是 天，超过 15 向上进位； 因此 fc72b -> fc6 – 12 – b -> 4038 / 2 – 18 / 2 – 11 -> 2019 – 9 – 11。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。