MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

40 | insert语句的锁为什么这么多?

林晓斌 2019-02-13
在上一篇文章中,我提到 MySQL 对自增主键锁做了优化,尽量在申请到自增 id 以后,就释放自增锁。
因此,insert 语句是一个很轻量的操作。不过,这个结论对于“普通的 insert 语句”才有效。也就是说,还有些 insert 语句是属于“特殊情况”的,在执行过程中需要给其他资源加锁,或者无法在申请到自增 id 以后就立马释放自增锁。
那么,今天这篇文章,我们就一起来聊聊这个话题。

insert … select 语句

我们先从昨天的问题说起吧。表 t 和 t2 的表结构、初始化数据语句如下,今天的例子我们还是针对这两个表展开。
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t
现在,我们一起来看看为什么在可重复读隔离级别下,binlog_format=statement 时执行:
insert into t2(c,d) select c,d from t;
这个语句时,需要对表 t 的所有行和间隙加锁呢?
其实,这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列:
图 1 并发 insert 场景
实际的执行效果是,如果 session B 先执行,由于这个语句对表 t 主键索引加了 (-∞,1] 这个 next-key lock,会在语句执行完成后,才允许 session A 的 insert 语句执行。
但如果没有锁的话,就可能出现 session B 的 insert 语句先执行,但是后写入 binlog 的情况。于是,在 binlog_format=statement 的情况下,binlog 里面就记录了这样的语句序列:
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(51)

  • huolang
    老师,死锁的例子,关于sessionA拿到的c=5的记录锁,sessionB和sessionC发现唯一键冲突会加上读锁我有几个疑惑:
    1. sessionA拿到的c=5的记录锁是写锁吗?
    2. 为什么sessionB和sessionC发现唯一键冲突会加上读锁?
    3. 如果sessionA拿到c=5的记录所是写锁,那为什么sessionB和sessionC还能加c=5的读锁,写锁和读锁不应该是互斥的吗?
    4. sessionA还没有提交,为什么sessionB和sessionC能发现唯一键冲突?

    作者回复: 1. 是的
    2. 这个我觉得是为了防止这个记录再被删除(不过这个理由不是很硬,我还没有找到其他解释
    3. 互斥的,所以这两个语句都在等待。注意next-key lock是由间隙锁和记录锁组成的哦, 间隙锁加成功了的。好问题。
    4. 还没有提交,但是这个记录已经作为最新记录写进去了,复习一下08篇哈

    2019-02-13
    25
  • 轻松的鱼
    老师好,想请教一下死锁的例子中:
    1. 在 session A rollback 前,session B/C 都因为唯一性冲突申请了 S Next-key lock,但是被 session A 的 X but not gap lock 阻塞;
    2. 在 session A rollbak 后,session B/C 顺利获得 S Next-key lock,并且都要继续进行插入,这时候我认为是因为插入意向锁(LOCK_INSERT_INTENTION)导致的死锁,因为插入意向锁会被 gap lock 阻塞,造成了相互等待。还没有进入到记录 X lock。
    不知道我分析的对不对?

    作者回复: 对

    2019-03-06
    1
    12
  • sonic
    你好,
    我想问下文章中关于为什么需要创建临时表有这一句话:
    如果读出来的数据直接写回原表,就可能在遍历过程中,读到刚刚插入的记录,新插入的记录如果参与计算逻辑,就跟语义不符。

    我的疑问是:既然隔离级别是可重复读,照理来说新插入的的记录应该不会参与计算逻辑呀。

    作者回复: 可重复读隔离级别下,事务是可以看到自己刚刚修改的数据的 ,好问题

    2019-02-14
    10
  • 老杨同志
    课后问题:
          我用的最多还是insert into select 。如果数量比较大,会加上limit 100,000这种。并且看看后面的select条件是否走索引。缺点是会锁select的表。方法二:导出成excel,然后拼sql 成 insert into values(),(),()的形式。方法3,写类似淘宝调动的定时任务,任务的逻辑是查询100条记录,然后多个线程分到几个任务执行,比如是个线程,每个线程10条记录,插入后,在查询新的100条记录处理。
          

    作者回复: 👍

    2019-02-13
    9
  • 夹心面包

    1 关于insert造成死锁的情况,我之前做过测试,事务1并非只有insert,delete和update都可能造成死锁问题,核心还是插入唯一值冲突导致的.我们线上的处理办法是 1 去掉唯一值检测 2减少重复值的插入 3降低并发线程数量
    2 关于数据拷贝大表我建议采用pt-archiver,这个工具能自动控制频率和速度,效果很不错,提议在低峰期进行数据操作

    作者回复: 👍,这两点都是很有用的建议

    2019-02-13
    9
  • 王伯轩
    老师你好,去年双11碰到了dbcrash掉的情况.至今没有找到答案,心里渗得慌.老师帮忙分析下.
    我是一个开发,关于db的知识更多是在应用和基本原理上面,实在是找不到原因. 我也搜了一些资料 感觉像是mysql的bug,不过在其buglist中没有找到完全一致的,当然也可能是我们业务也许导致库的压力大的原因.
    应用端看到的现象是db没有响应,应用需要访问db的线程全部僵死.db表现是hang住 , 当时的诊断日志如下,表面表现为一直获取不到latch锁(被一个insert线程持有不释放) https://note.youdao.com/ynoteshare1/index.html?id=1771445db3ff1e08cbdd8328ea6765a7&type=note#/ 隔离级别是rr

    同样的crash双11当天后面又出现了一次(哭死),
    都是重启数据库解决的,

    后面应用层面做了一样优化,没有再crash过,优化主要如下:
    1.减小读压力,去除一些不必要的查询,
    2.优化前,有并发事务写和查询同一条数据记录,即事务a执行insert 尚未提交,事务b就来查询(快照读),优化后保证查询时insert事务已经提交

    作者回复: 这就是压力太大了。。 一般伴随着ioutil很大,语句执行特别慢,别的语句就被堵着等锁,等超时就自己crash

    2019-02-19
    4
  • Justin
    插入意向锁的gal lock和next key lock中的 gaplock互斥吗?

    作者回复: 额,
    这里我们要澄清一下哈
    只有一个gap lock,就是 next key lock = gap lock + record lock;

    我们说一个insert语句如果要插入一个间隙,而这个间隙上有gap lock的话,insert语句会被堵住,这个被堵住的效果,实现机制上是用插入意向锁和gap lock相互作用来实现的。
    gap lock并不属于插入意向锁的一部分 ,就没有“插入意向锁的gal lock”这个概念哈

    2019-02-15
    4
  • 常超
    对这个SQL 执行过程的解释,
    insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

    >由于实现上这个语句没有在子查询中就直接使用 limit 1,从而导致了这个语句的执行需要遍历整个表 t。
    没太看懂“由于”那句话,既然在子查询中有了limit 1,为什么不能只把最后一条记录插入临时表呢?
    老师能在说明一下吗?
    2019-03-31
    3
  • 一大只😴
    老师,我想问下:insert 语句出现唯一键冲突,会加next-key lock,而产生死锁的例子中,同样也是唯一键冲突却只加了记录锁,然后我按照唯一键冲突中的两个例子试了试
    1、比如t表中有两条记录(19,19,19),(22,22,22),这时候我再insert (22,22,22)造成了主键冲突,这时候加的就是(19,22]的next-key lock,这个insert为啥不是等值查询?
    2、根据死锁的例子,我又在t表中准备插入一行
          session A :begin; insert into t values (25,25,25)
          session B :insert into t values (25,25,25) 这时候sessionB锁等待
          session C:insert into t values (24,24,24) 锁等待,等B锁等待超时,session C插入成功
          那这里的session B应该是加了个(22,25]的next-key lock,并没有因为是唯一键退化成记录锁
    我想死锁的例子中t表已经有了(1,1,1),(2,2,2),(3,3,3),(4,4,4)4条记录,这时候insert (null,5,5),是不是加的(4,5]这个next-key lock,由于是整型并且间隙非常小,所以将他当成记录锁?

    作者回复: “那这里的session B应该是加了个(22,25]的next-key lock,并没有因为是唯一键退化成记录锁” 由于insert主键冲突导致的锁,是不会退化的。

    session B 加了next-key lock,
    这样session C插入也要等待,然后等session B超时,释放了这个next-key lock,session C就可以执行了。
    跟我们文中说的是一致的哦。


    你这个验证挺合理的呀,

    不会有因为“间隙非常小,所以将他当成记录锁”这种逻辑哈, a和a+1之间也是有间隙的😆。

    不过这个是个好的实验和好问题👍

    2019-02-13
    3
  • 滔滔
    老师,有个问题insert into … on duplicate key update语句在发生冲突的时候是先加next key读锁,然后在执行后面的update语句时再给冲突记录加上写锁,从而把之前加的next key读锁变成了写锁,是这样的吗?

    作者回复: 不是,发现冲突直接加的就是写锁

    2019-02-21
    2
  • 信信
    老师好,
    图6下方“发生主键冲突的时候”是不是应该改为“发生唯一键冲突的时候”?因为c不是主键。
    还有,图7下方:T2时刻session b 发现“唯一键冲突”,这里为啥不是锁冲突?因为如果没有锁冲突,仅有唯一键冲突,就对应图6的情况,这时加的是next-key lock,而不仅仅是记录锁了。

    作者回复: 1. 你说得对,👍细致,发起勘误了哈
    2. 图7 这里说的唯一键冲突,就是发现“已经有一个c=5的行存在”,所以转为加next-key lock,没有单独的加行锁的逻辑哈

    2019-02-14
    2
  • 滔滔
    老师,之前提到的一个有趣的问题"A、B两个用户,如果互相喜欢,则成为好友。设计上是有两张表,一个是like表,一个是friend表,like表有user_id、liker_id两个字段,我设置为复合唯一索引即uk_user_id_liker_id。语句执行顺序是这样的:
    以A喜欢B为例:
    1、先查询对方有没有喜欢自己(B有没有喜欢A)
    select * from like where user_id = B and liker_id = A
    2、如果有,则成为好友
    insert into friend
    3、没有,则只是喜欢关系
    insert into like",这个问题中如果把select语句改成"当前读",则当出现A,B两个人同时喜欢对方的情况下,是不是会出现由于"当前读"加的gap锁导致后面insert语句阻塞,从而发生死锁?

    作者回复: 好问题

    这种情况下一般是造成锁等待,不会造成死锁吧 😆

    2019-02-13
    2
  • mid_kingking
    想问一下老师,为何例子insert 唯一键冲突中的间隙锁是(5,10],c是唯一索引,不应该退化为行锁么,而且c值是定值10
    2019-10-10
    1
  • godtrue
    老师,也许问题有点傻,不过我还是想问一下。
    锁,在计算机世界中是非常重要的,只要有并发必然会有锁,这个锁不像这把🔒,看的见也常见很容易理解。
    1:锁,在计算机世界究竟是个什么东西?一种数据结构?一句计算机命令?一种同步机制?在JAVA中有许多锁的类,能产生锁的对象,实现锁的功能,但是它怎么锁的?我很好奇,没完全弄明白。
    2:锁,是怎么实现的?是打个标记嘛?新来的线程一看有标记就等着,自己也打一个。JAVA中看到过用一个变量当锁,进入锁变量加一解锁变量减一,这样根据变量值就知道是否加锁了,MySQL也是如此吗?
    3:锁,说加在索引上,这个加在索引上怎么加的呢?索引是一棵树,也是以一个对象的形式存在的吗?
    总是听到这个锁那个锁的,我觉得没有理解锁的本质是啥,或者锁这个概念是操作系统世界的词,在操作系统层面更容易理解一些,在各种应用软件中就是一段代码,只是功能和现实中锁门的锁的功能相似而已。
    4:有等待就有唤醒,唤醒就是唤醒线程嘛?这个唤醒就怎么唤醒呢?是从等待的队列中获取一个线程嘛?那他又怎么知道之前执行到哪?该怎么执行啦?
    2019-08-08
    1
  • inrtyx
    现在一般都用utf8mb4?

    作者回复: 要看需求,不过因为表情用的很多了,utf8mb4很常用了

    2019-04-07
    1
  • 伟仔_Hoo
    老师,看到您的回复,当select c+1, d from t force index(c) order by c desc limit 1;这条语句单独执行是会在c索引上加(4,sup] 这个next key lock, 于是我进行了尝试
    sessionA:
    begin;
    select c+1, d from t3 force index(c) order by c desc limit 1;
    sessionB:
    insert into t3 values(5, 5, 5);
    结果是,sessionB插入成功,是不是我哪里理解错了?我的版本是5.7.23

    作者回复: session A的select语句没有加 for update 或者 lock in share mode ?

    2019-03-15
    1
  • Lilian
    老师,能帮忙看下这个死锁记录吗?对于duplicate key插入有什么阻止的好方法?LATEST DETECTED DEADLOCK
    ------------------------
    190222 8:37:45
    *** (1) TRANSACTION:
    TRANSACTION 16FEC1AE, ACTIVE 0 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s)
    MySQL thread id 169973, OS thread handle 0x2ba0fa040700, query id 41915315 10.45.133.181 W59FFHKU
    INSERT INTO resource (
                            Id
                          , Name
                          , Date
                          , User
                        ) VALUES (99127, 'RS_2098185e367d11e9878202a98a7af318', '', 'JR')
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 78 page no 71 n bits 160 index `PRIMARY` of table `resource` trx id 16FEC1AE lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    *** (2) TRANSACTION:
    TRANSACTION 16FEC1AF, ACTIVE 0 sec inserting
    mysql tables in use 1, locked 1
    6 lock struct(s), heap size 1248, 3 row lock(s)
    MySQL thread id 169996, OS thread handle 0x2ba0ffec2700, query id 41915317 10.45.133.181 W59FFHKU
    INSERT INTO resource (
                            Id
                          , Name
                          , Date
                          , User
                        ) VALUES (99125, 'RS_2098b778367d11e9878202a98a7af318', '', 'JR')
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 78 page no 71 n bits 160 index `PRIMARY` of table `resource` trx id 16FEC1AF lock mode S
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 78 page no 71 n bits 160 index `PRIMARY` of table `resource` trx id 16FEC1AF lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    *** WE ROLL BACK TRANSACTION (2)
    2019-03-10
    1
  • Lilian
    老师,重复主键插入冲突是否推荐insert ignore方法?

    作者回复: 这个取决于业务需求,如果是明确会存在这样的情况,并且可以忽略,是可以这么用的

    2019-03-09
    1
  • Mr.Strive.Z.H.L
    老师您好:
    关于文中的锁描述有所疑惑。

    文中出现过 共享的next-key锁 和 排他的next-key锁。

    我们知道next-key是由 gap lock 和 行锁组成的。

    我一直以来的认知是 gap lock都是s锁,没有x锁。
    而行锁有s锁和x锁。
    比如 select………lock in share mode,行锁是s
    锁。
    比如select………for update,行锁就是x锁。
    但是gap lock 始终是s锁。

    文中直接描述next-key lock是排他的,总让我认为gap lock和行锁都是x锁。

    不知道我理解得对不对?

    作者回复: 是这样的,gap lock是无所谓S还是X的。
    但是record lock 有。

    Gap lock + 排他的record 就称作 排他的next-key lock 吧😄

    2019-02-27
    1
  • 发条橙子 。
    老师,年后过来狂补课程了哈哈 , 看到老师的bug留言已经被fix掉准备在最新版本发布了呢。

    这里我有一个疑问, 我之前以为只有更新的时候才会加锁, 参考前面的文章,innodb要先扫描表中数据,被扫描到的行要加锁 。

    或者我们执行 select 的时候手动加上 排他锁 或者 共享锁,也会锁住。

    这里老师讲到如果索引唯一键冲突, innodb为了做处理加了 next_key lock(S) 这个可以理解。

    insert .. select 也是因为有 select 索引会加锁 也可以理解

    问题 :

    图7那个死锁的案例, session A 的时候 只是执行了 insert 语句,执行 insert的时候也没有select之类的,为什么也会在索引c上加个锁, 是什么时候加的呢 ??? 是 insert 语句有索引的话都会给索引加锁么??

    作者回复: 不是都会,是在要写入的时候,发现有主键冲突,才会加上这个next-key lock的锁

    2019-02-23
    1
收起评论
51
返回
顶部