数据库

MySQL语句加锁的实现分析
数据库 2024/11/5 佚名
3 2

摘要： MySQL两条SQL语句锁的分析

看一下下面的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 "htmlcode">



---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,加锁sql语句,sql语句加锁,数据库加锁的sql语句
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
白云城资源网 Copyright www.dyhadc.com

评论“MySQL语句加锁的实现分析”

暂无“MySQL语句加锁的实现分析”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源
一句话新闻
一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5

这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。

一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。

另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com