Mysql锁及使用场景
问题提出
收到 DBA 发出的慢 SQL 报警邮件，如下：
UPDATE order_exception_042 SET yn = 0 WHERE order_id = 1050020097947451;
# Query_time: 0.101579 Lock_time: 0.000039 Rows_sent: 0 Rows_examined: 8420
SET timestamp=1590108934;
根据报警邮件可以看到，查询时间较长的原因是检索的行数过多导致的(order_id没有索引，因此全表查询)，其中的 lock_time 引起了我的注意，update 语句会锁表吗？是行级锁还是表级锁呢？
读完本文你将收获
- 常见的SQL会触发哪些锁
- 如何在本地练习具体执行的SQL触发了哪些锁(查看锁日志)
- 为什么 update 语句最好使用 主键来更新
- MySQL中的锁类型和锁模式
Mysql中锁的分类(Lock Type)
-
Shared and Exclusive Locks
行级锁。这两个分别是最为普通的读锁和写锁。读锁，又称为 S 锁，允许多个事务同时获取，因此也叫共享锁。写锁，又称为 X 锁，一次只能被同一个事务占有，因此也叫独占锁。
为什么要区分出读锁和写锁呢？因此要提高数据库的并发性，如果只有一个锁，数据库一次只能被一个事务读取，而对于数据库而言超过 80% 的使用场景都是读取数据，因此并发性会大大的降低。
-
Intention Locks
表级锁。翻译过来叫做 “意向锁”，意向锁也分为读意向锁（Intention Shared Lock，简写为 IS）和写意向锁（Intention Exclusive Lock，简写为 IX）。在 Mysql 中，为了实现多粒度锁控制，引入了意向锁。
当一个事务请求施加行级共享锁（Shared Locks）时，它必须先获取到表级读意向锁。如 select ... lock in share mode 先获取到了读意向锁，然后对行施加读锁。
当一个事务请求施加行级独占锁（Exclusive Locks）时，它必须先获取到表级写意向锁。如 select ... for update 先获取到写意向锁，然后对表中的行施加写锁。
通过上面的例子我们还得知：表级锁和行级锁是可以共存的。表级锁的兼容性见下表：
| X | S | IX | IS | |
|---|---|---|---|---|
| X | ||||
| S | ✅ | ✅ | ||
| IX | ✅ | ✅ | ||
| IS | ✅ | ✅ | ✅ |
-
Record Locks
行级锁。只针对索引有效。如 select field1 from t1 where id = 1 for update ，执行该语句时就会把 id=1 的所有行锁住，防止其他事务对 id=1 的行进行删除，增加，修改操作。
-
Gap Locks
行级锁。俗称 “间隙锁”。间隙锁施加的对象是两个 index records 之间的间隙。如 select filed1 from t1 where field1 between 10 and 20 for update，执行该语句时，Mysql 施加的间隙锁会防止 field1=15 的记录插入，因为 15 位于 10 和 20 的间隙。
Mysql 采用间隙锁的做法防止了 “幻影行” 的出现。关闭间隙锁的做法就是调整 Mysql 的事务隔离级别，从默认的 RR 调整为 RC
-
Next-Key Locks
行级锁。当 Mysql 事务的隔离级别为 RR(Repeatable Read)时，Next-Key Lock 使用的频率最为频繁，因为 Next-Key Lock 可以理解为 Record Lock + Gap Lock. MySQL 中引入 Next-Key Lock 锁的原因同样是为了解决幻影行的出现。
MySQL中锁的模式(Lock Mode)
Mysql 中总共只有四种锁的模式, 表级锁有 4 种(X , S, IX, IS)，行级锁只有两种(X , S), 表级锁的兼容性如下表
| X | S | IX | IS | |
|---|---|---|---|---|
| X | ||||
| S | ✅ | ✅ | ||
| IX | ✅ | ✅ | ||
| IS | ✅ | ✅ | ✅ |
- X： 与其他任何锁冲突
- S ： 与X锁，IX 锁冲突，允许并发读
- IX : 意向锁和意向锁之间不冲突，意味着 Mysql 支持单表的并发的读写
- IS： 意向锁和意向锁之间不冲突，意味着 Mysql 支持单标并发的读写
如何触发？
-
X
使用下列语句触发锁 locking 表，此时不允许任何请求读
lock tables locking write; -
S
使用下拉语句触发锁 locking 表，此时不允许任何请求写
lock tables locking read; -
IX
DML 语句，如 delete, update, insert 操作都会触发 Mysql 对表施加 IX 锁。
select ... for update
-
IS
select ... lock in share mode;
最为普通的 select 语句会加锁吗？
不会，select 语句使用读快照的方式，保证了 MySQL 的高并发特性。读快照不涉及到加锁和解锁。
Mysql开启锁监控
set GLOABL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
查看锁的状态
show engine innodb status\G
实战
特殊符号说明：
-
^用来表示间隙锁，如间隙锁的区间为 15-18, 则表示为 15^18 -
+用来表示Next-key锁，如Next-key锁由15-18的间隙锁和18的Record行锁构成，表示为15^18+18
如何根据日志辨别是哪种行锁？
--Record Lock
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71483 lock_mode X locks rec but not gap
--Gap Lock
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71486 lock_mode X locks gap before rec
--Next-key Lock
RECORD LOCKS space id 782 page no 5 n bits 80 index idx_name of table `ele`.`locking` trx id 71497 lock_mode X
使用ele库，隔离级别为RR（Repeatable Read），表结构如下
CREATE TABLE `locking` (
`id` int(11) NOT NULL,
`no` varchar(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_no` (`no`) USING BTREE,
KEY `idx_name` (`name`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中初始数据如下：
Screenshot 2020-05-24 at 12.18.35 PM.png
确保开启了 Mysql 的锁监控
set GLOABL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
下面我们开始测试各个场景下 Mysql 会施加哪种级别哪种类型的锁
- 聚簇索引，查询命中
| session1 | session2 | 备注 | |
|---|---|---|---|
| begin; | |||
| update locking set score = 99 where id = 15; | begin; | ||
| select * from locking where id =15 | |||
| commit; | |||
| select * from locking where id = 15 | 此时score=100，因为隔离级别为RR，确保在同一个事务中，同一个SQL查询的结果是一致的 |
此时我们查看锁的施加情况
show engine innodb status\G
展示如下
------------
TRANSACTIONS
------------
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 123145511911424, query id 150 localhost root starting
show engine innodb status
--ID为71483的事务对ele库的locking表施加了 IX 写意向锁
TABLE LOCK table `ele`.`locking` trx id 71483 lock mode IX
--ID为71483的事务对ele库的locking表中的主键索引施加了 X 写锁，特别指明没有 gap 间隙锁
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71483 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 00000001173b; asc ;;;
2: len 7; hex 30000001700d8a; asc 0 p ;;
3: len 5; hex 5330303031; asc S0001;;
4: len 3; hex 426f62; asc Bob;;
5: len 4; hex 80000019; asc ;;
6: len 4; hex 80000062; asc b;;
在 Session1 执行事务时，已经获取了 locking 表的 写意向锁，并且对 id=15 这个聚簇索引施加了 X 写锁，但是此时 Session2 执行另外一个事务中的 select 语句，仍然能读到 id=15 的数据，这一点证明普通的 select 语句是读快照不用加锁的。
- 聚簇索引，查询未命中
| session1 | session2 | 备注 | |
|---|---|---|---|
| begin; | |||
| update locking set score = 80 where id = 16; | begin; | ||
| select * from locking where id =16 | |||
| commit; | |||
| select * from locking where id = 16 | |||
| commit; |
------------
TRANSACTIONS
------------
show engine innodb status
--ID为71486的事务对ele库的locking表施加了 IX 写意向锁
TABLE LOCK table `ele`.`locking` trx id 71486 lock mode IX
--ID为71483的事务对ele库的locking表中的主键索引施加了 X 写锁，为间隙锁(15^18)
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71486 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000012; asc ;;
1: len 6; hex 000000011708; asc ;;
2: len 7; hex a8000001db0110; asc ;;
3: len 5; hex 5330303032; asc S0002;;
4: len 5; hex 416c696365; asc Alice;;
5: len 4; hex 80000018; asc ;;
6: len 4; hex 8000004d; asc M;;
--------
- 二级唯一索引，查询命中
| session1 | 备注 |
|---|---|
| begin; | |
| update locking set score = 80 where no = 'S0002'; | |
| commit; | |
------------
TRANSACTIONS
------------
show engine innodb status
--ID为71487的事务对ele库的locking表施加了 IX 写意向锁
TABLE LOCK table `ele`.`locking` trx id 71487 lock mode IX
--ID为71487的事务对ele库的locking表中的idx_no索引施加了 X 写锁，特别指明没有 gap 间隙锁
RECORD LOCKS space id 782 page no 4 n bits 80 index idx_no of table `ele`.`locking` trx id 71487 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 5; hex 5330303032; asc S0002;;
1: len 4; hex 80000012; asc ;;
--ID为71487的事务对ele库的locking表中的主键索引施加了 X 写锁，特别指明没有 gap 间隙锁
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71487 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000012; asc ;;
1: len 6; hex 00000001173f; asc ?;;
2: len 7; hex 33000001572461; asc 3 W$a;;
3: len 5; hex 5330303032; asc S0002;;
4: len 5; hex 416c696365; asc Alice;;
5: len 4; hex 80000018; asc ;;
6: len 4; hex 80000050; asc P;;
--------
- 二级唯一索引，查询未命中
| session1 | 备注 |
|---|---|
| begin; | |
| update locking set score = 80 where = 'S0008'; | |
| commit; | |
------------
TRANSACTIONS
------------
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 71496 lock mode IX
--从日志看，对idx_no索引施加的Next-Key Lock，而非 Gap Lock 与https://zhuanlan.zhihu.com/p/67793185 这篇文章写的有出入
RECORD LOCKS space id 782 page no 4 n bits 80 index idx_no of table `ele`.`locking` trx id 71496 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
--------
我们这里使用的唯一索引 idx_no 是类型为 varchar，字段名为 no 列，经过多次试验，varchar 类型的唯一如果没有命中施加的都是 Next-key Lock, 位置为 LastNode^Supremum+Supremum。
如果使用的唯一索引为非 varchar 类型，举例来说，我们在表中增加一个字段和唯一索引，SQL语句如下：
```sql
alter table locking add column no_id default null int(20)
alter table add unique idx_no_id (`no_id`)
```
此时，我们根据 no_id 唯一索引去查询，未命中的情况下，Mysql 施加的是 Gap Lock 或 Next-Key Lock，取决于 no_id 是否为索引的边界。
```sql
--no_id 落在索引边界
------------
TRANSACTIONS
------------
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 123145405616128, query id 117 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 72010 lock mode IX
--update locking set score where no_id = 12(12大于现有的所有no_id，为边界值，此时施加的是Next-Key Lock)
RECORD LOCKS space id 784 page no 7 n bits 80 index idx_no_id of table `ele`.`locking` trx id 72010 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
--------
--no_id 落在两个索引之间
------------
TRANSACTIONS
------------
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 123145405616128, query id 113 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 72009 lock mode IX
--update locking set score where no_id = 8(8位于6和10之间,此时施加的是Gap Lock)
RECORD LOCKS space id 784 page no 7 n bits 80 index idx_no_id of table `ele`.`locking` trx id 72009 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 80000032; asc 2;;
--------
```
- 二级非唯一索引，查询命中
| session1 | 备注 |
|---|---|
| begin; | |
| update locking set score = 80 where name = 'Tom'; | |
| commit; | |
------------
TRANSACTIONS
------------
--3种锁类型：Record Lock, Next-key Lock, Intention Lock
--5个行锁：三个Next-key锁 Rose^Tom1+Tom1,Tom1^Tom2+Tom2,Tom2^Supermum+Supremum（这三个在idx_name索引上）
-- 两个Record锁：Tom1,Tom2(这两个在主键索引上)
3 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2
MySQL thread id 6, OS thread handle 123145511911424, query id 227 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 71497 lock mode IX
RECORD LOCKS space id 782 page no 5 n bits 80 index idx_name of table `ele`.`locking` trx id 71497 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 546f6d; asc Tom;;
1: len 4; hex 80000025; asc %;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 546f6d; asc Tom;;
1: len 4; hex 80000031; asc 1;;
--在主键索引上施加的Record Lock，非Gap Lock
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71497 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000025; asc %;;
1: len 6; hex 000000011749; asc I;;
2: len 7; hex 390000016c0dda; asc 9 l ;;
3: len 5; hex 5330303035; asc S0005;;
4: len 3; hex 546f6d; asc Tom;;
5: len 4; hex 80000016; asc ;;
6: len 4; hex 8000005a; asc Z;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000031; asc 1;;
1: len 6; hex 000000011749; asc I;;
2: len 7; hex 390000016c0dfc; asc 9 l ;;
3: len 5; hex 5330303036; asc S0006;;
4: len 3; hex 546f6d; asc Tom;;
5: len 4; hex 80000019; asc ;;
6: len 4; hex 8000005a; asc Z;;
--------
- 二级非唯一索引，查询未命中
| session1 | 备注 |
|---|---|
| begin; | |
| update locking set score = 80 where name = 'John'; | |
| commit; | |
------------
TRANSACTIONS
------------
--两种锁类型：Intention Lock，Gap Lock
--一个行锁：Gap Lock
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 6, OS thread handle 123145511911424, query id 232 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 71499 lock mode IX
--Gap Lock info
RECORD LOCKS space id 782 page no 5 n bits 80 index idx_name of table `ele`.`locking` trx id 71499 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 526f7365; asc Rose;;
1: len 4; hex 80000032; asc 2;;
--------
- 无索引
| session1 | 备注 |
|---|---|
| begin; | |
| update locking set score = 80 where score = 90; | |
| commit; | |
------------
TRANSACTIONS
------------
--两种锁类型：Intention Lock, Next-key Lock
--8个行锁：infimum^15+15, 15^18+18, 18^20+20, 20^30+30
-- 30^37+37, 37^49+49, 49^50+50, 50^Supremum+Supremum
2 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 2
MySQL thread id 6, OS thread handle 123145511911424, query id 240 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 71500 lock mode IX
RECORD LOCKS space id 782 page no 3 n bits 80 index PRIMARY of table `ele`.`locking` trx id 71500 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000011740; asc @;;
2: len 7; hex 340000015b2c45; asc 4 [,E;;
3: len 5; hex 5330303031; asc S0001;;
4: len 3; hex 426f62; asc Bob;;
5: len 4; hex 80000019; asc ;;
6: len 4; hex 80000002; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000012; asc ;;
1: len 6; hex 00000001173f; asc ?;;
2: len 7; hex 33000001572461; asc 3 W$a;;
3: len 5; hex 5330303032; asc S0002;;
4: len 5; hex 416c696365; asc Alice;;
5: len 4; hex 80000018; asc ;;
6: len 4; hex 80000050; asc P;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 00000001170d; asc ;;
2: len 7; hex ab000001f10110; asc ;;
3: len 5; hex 5330303033; asc S0003;;
4: len 3; hex 4a696d; asc Jim;;
5: len 4; hex 80000018; asc ;;
6: len 4; hex 80000005; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 00000001170e; asc ;;
2: len 7; hex ac000001170110; asc ;;
3: len 5; hex 5330303034; asc S0004;;
4: len 4; hex 45726963; asc Eric;;
5: len 4; hex 80000017; asc ;;
6: len 4; hex 8000005b; asc [;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000025; asc %;;
1: len 6; hex 00000001174c; asc L;;
2: len 7; hex 3b0000017f0110; asc ; ;;
3: len 5; hex 5330303035; asc S0005;;
4: len 3; hex 546f6d; asc Tom;;
5: len 4; hex 80000016; asc ;;
6: len 4; hex 80000014; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000031; asc 1;;
1: len 6; hex 00000001174c; asc L;;
2: len 7; hex 3b0000017f0132; asc ; 2;;
3: len 5; hex 5330303036; asc S0006;;
4: len 3; hex 546f6d; asc Tom;;
5: len 4; hex 80000019; asc ;;
6: len 4; hex 80000014; asc ;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000032; asc 2;;
1: len 6; hex 000000011746; asc F;;
2: len 7; hex 370000017b0812; asc 7 { ;;
3: len 6; hex 533030303130; asc S00010;;
4: len 4; hex 526f7365; asc Rose;;
5: len 4; hex 80000017; asc ;;
6: len 4; hex 80000059; asc Y;;
--------
因此无索引情况下使用 update 语句会对全表中主索引施加 Next-Key 锁，相当于锁表。此时由于 X 锁和 X 锁互斥，会导致其他 Session 不能插入，删除和更新表。
因此更新语句一定要使用主键或者其他唯一索引限定锁定的行数，提供数据库的并发性，也可以避免产生死锁。
-
范围更新
现在 locking 表中的数据如下
Screenshot 2020-05-25 at 10.46.22 AM.png
如果我们执行如下语句，Mysql 会施加几种锁？每个种类对应几个锁呢?
update locking set score where id >= 50;
------------
TRANSACTIONS
------------
--三种类型锁：Intention Lock, Record Lock, Next-Key Lock
--Record Lock: 施加在 id=50 的索引上
--Next-Key Lock: 50^60+60, 60^Supremum+Supremum
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 123145405616128, query id 150 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 72019 lock mode IX
RECORD LOCKS space id 784 page no 3 n bits 88 index PRIMARY of table `ele`.`locking` trx id 72019 lock_mode X locks rec but not gap
Record lock, heap no 15 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000032; asc 2;;
1: len 6; hex 000000011953; asc S;;
2: len 7; hex 3f000001641bc7; asc ? d ;;
3: len 6; hex 533030303130; asc S00010;;
4: len 4; hex 526f7365; asc Rose;;
5: len 4; hex 80000017; asc ;;
6: len 4; hex 80000032; asc 2;;
7: len 4; hex 8000000a; asc ;;
RECORD LOCKS space id 784 page no 3 n bits 88 index PRIMARY of table `ele`.`locking` trx id 72019 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 16 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 8000003c; asc <;;
1: len 6; hex 000000011953; asc S;;
2: len 7; hex 3f000001641be9; asc ? d ;;
3: len 5; hex 5330303039; asc S0009;;
4: len 4; hex 5a61726b; asc Zark;;
5: len 4; hex 8000001c; asc ;;
6: len 4; hex 80000032; asc 2;;
7: len 4; hex 80000009; asc ;;
--------
```
9. 插入
执行的SQL如下：
```sql
insert into locking (id,no,name,age,no_id) values(70,'S00012','Jupiter',28,12);
```
加锁情况如下：
```sql
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 123145405616128, query id 211 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 72022 lock mode IX
```
10. 删除
执行的SQL如下：
```sql
delete from locking where id = 70;
```
加锁情况如下：
```sql
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 123145405616128, query id 217 localhost root starting
show engine innodb status
TABLE LOCK table `ele`.`locking` trx id 72027 lock mode IX
RECORD LOCKS space id 784 page no 3 n bits 88 index PRIMARY of table `ele`.`locking` trx id 72027 lock_mode X locks rec but not gap
Record lock, heap no 17 PHYSICAL RECORD: n_fields 8; compact format; info bits 32
0: len 4; hex 80000046; asc F;;
1: len 6; hex 00000001195b; asc [;;
2: len 7; hex 440000014312b6; asc D C ;;
3: len 6; hex 533030303132; asc S00012;;
4: len 7; hex 4a757069746572; asc Jupiter;;
5: len 4; hex 8000001c; asc ;;
6: SQL NULL;
7: len 4; hex 8000000c; asc ;;
```