实现MySQL语句加锁的方法

IcebergSteven

2019-01-05

关注关注

看一下下面的SQL语句加什么锁

SLQ1：select * from t1 where id = 10;

SQL2：delete from t1 where id = 10;

（1）id 是不是主键

（2）当前系统的隔离级别是什么

（3）id列如果不是主键，那么id列上有索引吗

（4）id列上如果有二级索引，那么这个索引是二级索引吗

（5）两个SQL的执行计划是什么？索引扫描还是全表扫描

实际的执行计划需要根据MySQL的输出为准

组合一：id列是主键，RC隔离级别

组合二：id列是二级唯一索引，RC隔离级别

组合三：id列是二级非唯一索引，RC隔离级别

组合四：id列没有索引，RC隔离级别

组合五：id列是主键，RR隔离级别

组合六：id列是二级唯一索引，RR隔离级别

组合七：id列是二级非唯一索引，RR隔离级别

组合八：id列上没有索引，RR隔离级别

Serializable隔离级别

在RR RC隔离级别下，SQL1：select 均不加锁，采用的是快照读；以下仅讨论SQL2：delete操作的加锁

Percona

组合一：id主键+RC

Percona

---TRANSACTION 1286310, ACTIVE 9 sec

2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up

TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX

RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap

MySQL

---TRANSACTION 5936, ACTIVE 171 sec

2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost root

TABLE LOCK table `test`.`t1` trx id 5936 lock mode IX

RECORD LOCKS space id 6 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 5936 lock_mode X locks rec but not gap

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

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 000000001730; asc 0;;

2: len 7; hex 26000001550110; asc & U ;;

3: len 1; hex 61; asc a;;

组合二：id唯一索引+RC

在唯一索引上的更新需要两个X锁，一个对应唯一索引id=10 记录，一个对应于聚簇索引name='d'的记录

Percona

---TRANSACTION 1286327, ACTIVE 3 sec

3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1

MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning up

TABLE LOCK table `test`.`t2` trx id 1286327 lock mode IX

RECORD LOCKS space id 30 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap

RECORD LOCKS space id 30 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap

MySQL

---TRANSACTION 5938, ACTIVE 3 sec

3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1

MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374 localhost root

TABLE LOCK table `test`.`t2` trx id 5938 lock mode IX

RECORD LOCKS space id 7 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 8000000a; asc ;;

1: len 1; hex 64; asc d;;

RECORD LOCKS space id 7 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap

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

0: len 1; hex 64; asc d;;

1: len 6; hex 000000001732; asc 2;;

2: len 7; hex 27000001560110; asc ' V ;;

3: len 4; hex 8000000a; asc ;;

组合三：id非唯一索引+RC

ID列为普通索引，那么对应的所有满足SQL查询条件的记录，都会被加锁；同时，这些记录在主键索引上的记录，也会被加锁

Percona

---TRANSACTION 1286339, ACTIVE 9 sec

3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2

MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning up

TABLE LOCK table `test`.`t3` trx id 1286339 lock mode IX

RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap

RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap

MySQL

---TRANSACTION 5940, ACTIVE 3 sec

3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2

MySQL thread id 2, OS thread handle 0x7f5677201700, query id 378 localhost root

TABLE LOCK table `test`.`t3` trx id 5940 lock mode IX

RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap

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

0: len 4; hex 8000000a; asc ;;

1: len 1; hex 62; asc b;;

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

0: len 4; hex 8000000a; asc ;;

1: len 1; hex 64; asc d;;

RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap

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

0: len 1; hex 62; asc b;;

1: len 6; hex 000000001734; asc 4;;

2: len 7; hex 28000001570110; asc ( W ;;

3: len 4; hex 8000000a; asc ;;

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

0: len 1; hex 64; asc d;;

1: len 6; hex 000000001734; asc 4;;

2: len 7; hex 28000001570132; asc ( W 2;;

3: len 4; hex 8000000a; asc ;;

组合四：id无索引+RC

Percona

---TRANSACTION 1286373, ACTIVE 5 sec

2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2

MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning up

TABLE LOCK table `test`.`t4` trx id 1286373 lock mode IX

RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1286373 lock_mode X locks rec but not gap

MySQL

---TRANSACTION 5946, ACTIVE 2 sec

2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2

MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost root

TABLE LOCK table `test`.`t4` trx id 5946 lock mode IX

RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 5946 lock_mode X locks rec but not gap

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

0: len 1; hex 62; asc b;;

1: len 6; hex 00000000173a; asc :;;

2: len 7; hex 2b0000015a0110; asc + Z ;;

3: len 4; hex 8000000a; asc ;;

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

0: len 1; hex 64; asc d;;

1: len 6; hex 00000000173a; asc :;;

2: len 7; hex 2b0000015a012c; asc + Z ,;;

3: len 4; hex 8000000a; asc ;;

组合五：id主键+RR

参考组合一

组合六：id唯一索引+RR

参考组合二

组合七：id非唯一索引+RR

Percona

---TRANSACTION 1592633, ACTIVE 24 sec

4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2

MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning up

Trx read view will not see trx with id >= 1592634, sees < 1592634

TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IX

RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X

RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1592633 lock_mode X locks rec but not gap

RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X locks gap before rec

MySQL

---TRANSACTION 5985, ACTIVE 7 sec

4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2

MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost root

TABLE LOCK table `test`.`t3` trx id 5985 lock mode IX

RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X

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

0: len 4; hex 8000000a; asc ;;

1: len 1; hex 64; asc d;;

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

0: len 4; hex 8000000a; asc ;;

1: len 1; hex 62; asc b;;

RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5985 lock_mode X locks rec but not gap

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

0: len 1; hex 64; asc d;;

1: len 6; hex 000000001761; asc a;;

2: len 7; hex 3f0000016d0132; asc ? m 2;;

3: len 4; hex 8000000a; asc ;;

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

0: len 1; hex 62; asc b;;

1: len 6; hex 000000001761; asc a;;

2: len 7; hex 3f0000016d0110; asc ? m ;;

3: len 4; hex 8000000a; asc ;;

RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 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 8000000b; asc ;;

1: len 1; hex 66; asc f;;

组合八：id无索引+RR

Percona

---TRANSACTION 1592639, ACTIVE 4 sec

2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2

MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning up

TABLE LOCK table `test`.`t4` trx id 1592639 lock mode IX

RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1592639 lock_mode X

MySQL

---TRANSACTION 6000, ACTIVE 3 sec

2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2

MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost root

TABLE LOCK table `test`.`t4` trx id 6000 lock mode IX

RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 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 4; compact format; info bits 0

0: len 1; hex 61; asc a;;

1: len 6; hex 000000001722; asc ";;

2: len 7; hex 9e0000014e0110; asc N ;;

3: len 4; hex 8000000f; asc ;;

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

0: len 1; hex 62; asc b;;

1: len 6; hex 000000001770; asc p;;

2: len 7; hex 47000001730110; asc G s ;;

3: len 4; hex 8000000a; asc ;;

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

0: len 1; hex 63; asc c;;

1: len 6; hex 000000001722; asc ";;

2: len 7; hex 9e0000014e0122; asc N ";;

3: len 4; hex 80000006; asc ;;

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

0: len 1; hex 64; asc d;;

1: len 6; hex 000000001770; asc p;;

2: len 7; hex 4700000173012c; asc G s ,;;

3: len 4; hex 8000000a; asc ;;

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

0: len 1; hex 66; asc f;;

1: len 6; hex 000000001722; asc ";;

2: len 7; hex 9e0000014e0134; asc N 4;;

3: len 4; hex 8000000b; asc ;;

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

0: len 2; hex 7a7a; asc zz;;

1: len 6; hex 000000001722; asc ";;

2: len 7; hex 9e0000014e013d; asc N =;;

3: len 4; hex 80000002; asc ;;

组合九：Serializable

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

Serializable隔离级别，影响的是SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

mysql mysql执行计划隔离级别

安科网

实现MySQL语句加锁的方法

IcebergSteven

IcebergSteven

相关推荐

Golang操作MySql数据库的完整步骤记录

MySQL主从复制原理以及需要注意的地方

Mysql联表update数据的示例详解

MySQL数据类型优化原则

专业级的MySQL开发设计规范及SQL编写规范

Mysql 查询JSON结果的相关函数汇总

Mysql 实现字段拼接的三个函数

浅谈MySQL中的自增主键用完了怎么办

MySql索引使用策略分析

mysql 如何动态修改复制过滤器

MySQL ddl语句的使用

MySQL中使用binlog时格式该如何选择

mysql 8.0.22 安装配置图文教程

解决Navicat Premium 连接 MySQL 8.0 报错\"1251\"的问题分析

MySQL数据操作-DML语句的使用

详解 MySQL中count函数的正确使用方法

MySQL 基于时间点的快速恢复方案

MySQL外键约束的实例讲解

MySQL用truncate命令快速清空一个数据库中的所有表

修改MySQL8.0 默认的数据目录(快捷操作无配置)

IcebergSteven