Mysql锁

作者: fanyank | 来源:发表于2020-05-25 15:01 被阅读0次

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 先获取到写意向锁，然后对表中的行施加写锁。

通过上面的例子我们还得知：表级锁和行级锁是可以共存的。表级锁的兼容性见下表：

	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), 表级锁的兼容性如下表

	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     ;;
  ```

Mysql锁

Mysql锁及使用场景

问题提出

读完本文你将收获

Mysql中锁的分类(Lock Type)

MySQL中锁的模式(Lock Mode)

Mysql开启锁监控

实战

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读