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讲
登录|注册

07 | 行锁功过:怎么减少行锁对性能的影响?

林晓斌 2018-11-28
在上一篇文章中,我跟你介绍了 MySQL 的全局锁和表级锁,今天我们就来讲讲 MySQL 的行锁。
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。
我们今天就主要来聊聊 InnoDB 的行锁,以及如何通过减少锁冲突来提升业务并发度。
顾名思义,行锁就是针对数据表中行记录的锁。这很好理解,比如事务 A 更新了一行,而这时候事务 B 也要更新同一行,则必须等事务 A 的操作完成后才能进行更新。
当然,数据库中还有一些没那么一目了然的概念和设计,这些概念如果理解和使用不当,容易导致程序出现非预期行为,比如两阶段锁。

从两阶段锁说起

我先给你举个例子。在下面的操作序列中,事务 B 的 update 语句执行时会是什么现象呢?假设字段 id 是表 t 的主键。
这个问题的结论取决于事务 A 在执行完两条 update 语句后,持有哪些锁,以及在什么时候释放。你可以验证一下:实际上事务 B 的 update 语句会被阻塞,直到事务 A 执行 commit 之后,事务 B 才能继续执行。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(227)

  • 木木北月生 置顶
    老师,关于死锁检测innodb_deadlock_detect我想请教一下,是每条事务执行前都会进行检测吗?如果是这样,即使简单的更新单个表的语句,当每秒的并发量达到上千的话,岂不是也会消耗大量资源用于死锁检测吗?

    作者回复: 是个好问题
    如果他要加锁访问的行上有锁,他才要检测。

    这里面我担心你有两个误解,说明下:
    1. 一致性读不会加锁,就不需要做死锁检测;

    2. 并不是每次死锁检测都都要扫所有事务。比如某个时刻,事务等待状态是这样的:

       B在等A,
       D在等C,
       现在来了一个E,发现E需要等D,那么E就判断跟D、C是否会形成死锁,这个检测不用管B和A

    2018-12-03
    87
  • bluefantasy3 置顶
    请教老师一个问题:
    innodb行级锁是通过锁索引记录实现的。如果update的列没建索引,即使只update一条记录也会锁定整张表吗?比如update t set t.name='abc' where t.name='cde'; name字段无索引。为何innodb不优化一下,只锁定name='cde'的列?

    作者回复: 第一个问题是好问题,我加到答疑文章中。简单的回答:是的。但是你可以再往前考虑一下,如果是 你的update 语句后面加个limit 1, 会怎么锁?


    Innodb支持行锁,没有支持“列锁” 哈😄


    2018-11-28
    5
    63
  • 荒漠甘泉 置顶
    老师,本节课讲的不支持行锁的引擎,只能使用表锁,而表锁同一张表在同一时刻只能有一个更新。但是上节课讲的表级锁中的MDL锁,dml语句会产生MDL读锁,而MDL读锁不是互斥的,也就是说一张表可以同时有多个dml语句操作。感觉这两种说法有点矛盾,请老师解惑!

    作者回复: 不矛盾,MDL锁和表锁是两个不同的结构。

    比如:
    你要在myisam 表上更新一行,那么会加MDL读锁和表的写锁;
    然后同时另外一个线程要更新这个表上另外一行,也要加MDL读锁和表写锁。

    第二个线程的*MDL读锁是能成功加上*的,但是被表写锁堵住了。从语句现象上看,就是第二个线程要等第一个线程执行完成。

    2018-11-28
    4
    49
  • 蓝天 置顶
    老师:上一节讲的dml时会产生读MDL锁(表锁),也就是update会持有读MDL。读和读不互斥。但是对于行锁来说。两个update同时更新一条数据是互斥的。这个是因为多种锁同时存在时,以粒度最小的锁为准的原因么?

    作者回复: 不是“以粒度最小为准”
    而是如果有多种锁,必须得“全部不互斥”才能并行,只要有一个互斥,就得等。

    好问题

    2019-01-09
    21
  • 三木禾 置顶
    老师,如果开启事务,然后进行死锁检测,如果发现有其它线程因为这个线程的加入,导致其它线程的死锁,这个流程能帮着分析一下么

    作者回复: 好问题

    理论上说,之前没死锁,现在A加进来,出现了死锁,那么死锁的环里面肯定包含A,
    因此只要从A出发去扫就好了

    2018-12-14
    15
  • 某、人 置顶
    老师,针对我的第一个问题。我就是想问怎么能拿到比较全得死锁信息以及把这些信息保存到文件里。
    第二个问题,如果reset以后,是不是就失去了长连接的意义了呢?相当于再次进行连接。

    作者回复: 1. 就是持续监控,发现新的就存起来

    2. 不会,reset_connection只是复位状态,恢复到连接和权限验证之后的状态,没有重连

    2018-11-28
    13
  • 武者 置顶
    老师 你好
    有以下情况 帮忙分析下会锁表不

    update a,b set a.name = b.name where a.uid=b.uid and b.group=1;
    update c,b set c.age=b.age where c.uid=b.uid and b.group = 1;

    如果两个语句同时执行期间 是不是有个执行不了 要等b解锁。还是说没有更新b的字段b不会锁,两个可并行执行
    请老师指导下

    作者回复: 这个你得同时贴表结构。

    还有,会不会锁,不是验证一下就可以吗,两个都用begin + 语句,

    两阶段锁协议会帮助你😄

    2018-11-29
    2
    7
  • Tony Du
    方案一,事务相对较长,则占用锁的时间较长,会导致其他客户端等待资源时间较长。
    方案二,串行化执行,将相对长的事务分成多次相对短的事务,则每次事务占用锁的时间相对较短,其他客户端在等待相应资源的时间也较短。这样的操作,同时也意味着将资源分片使用(每次执行使用不同片段的资源),可以提高并发性。
    方案三,人为自己制造锁竞争,加剧并发量。
    方案二相对比较好,具体还要结合实际业务场景。

    另,对于innodb的行锁,我觉得可以增加一讲,如何加锁(依赖于具体的隔离级别,是否有索引,是否是唯一索引,SQL的执行计划),特别是在RR隔离级别下的GAP锁,对于innodb,RR级别是可以防止幻读的。

    作者回复: 分析得很好。

    嗯嗯索引和锁的内容很多,也是需要慢慢安排😄
    突然上概念怕大家看得不开心😓

    2018-11-28
    61
  • 我选第二种。
    第一种,需要锁资源多,事务较大,持有锁时间最长。
    第三种,多个事务会对同一行产生锁竞争,消耗cpu资源。
    请指正。
    2018-11-28
    55
  • WL
    继续把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
    1.
    两阶段锁的概念是什么? 对事务使用有什么帮助?
    2.
    死锁的概念是什么? 举例说明出现死锁的情况.
    3.
    死锁的处理策略有哪两种?
    4.
    等待超时处理死锁的机制什么?有什么局限?
    5.
    死锁检测处理死锁的机制是什么? 有什么局限?
    6.
    有哪些思路可以解决热点更新导致的并发问题?

    作者回复: 继续手动👍🏿

    2018-12-01
    36
  • 岁月安然
    总结:
    两阶段锁:在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放, 而是要等到事务结束时才释放。
    建议:如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
    死锁:当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态。
    解决方案:
    1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间,InnoDB引擎默认值是50s。
    2、发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑(默认是开启状态)。
    如何解决热点行更新导致的性能问题?
    1、如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关闭掉。一般不建议采用
    2、控制并发度,对应相同行的更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
    3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突,但是业务复杂度可能会大大提高。

    innodb行级锁是通过锁索引记录实现的,如果更新的列没建索引是会锁住整个表的。

    作者回复: 👍🏿这个总结

    2018-11-29
    32
  • suynan
    系列课程看到现在,我能说,这是我看过的最好的mysql课程吗。网上的文章要么漏洞百出小学生水平,要么浅尝辄止并赠送一堆废话,要么千篇一律copy加转载。
    说实话,真的被误导过,可怕的不是误导我一人,而是千千万万的菜鸟开发者。
    文章很用心,感谢作者,超值!

    作者回复: 感谢鼓励🤝

    2019-03-06
    21
  • 锅子
    老师好,关于上一期的问题我有2疑问:
    1.Q2,WITH CONSISTENT SNAPSHOT语句执行完可以确保得到一个一致性视图,为什么还会备份到Q2时间点之后更改的表结构啊?如果这样那是不是意味着如果有一个数据库一直有数据在写入的话,备份会一直都无法完成。
    2.Q3设置了保存点,之后读到主库的DDL语句,那Q6又回滚到了Q3设置的保存点,那是不是就主从不一致了啊?
    2018-11-28
    4
    15
  • Aurora
    针对第一层楼主提到的问题,我记得是,如果update 没有走索引,innodb内部是全表根据主键索引逐行扫描 逐行加锁,释放锁。

    作者回复: 逐行加锁,

    事务提交的时候统一释放。— 记得两阶段锁哈

    2018-11-28
    2
    12
  • bing
    在开发时一般都是按照顺序加锁来避免死锁。比如都是按照先拿t1,再拿t2.

    作者回复: 是个好的实践经验👍🏿

    2018-11-28
    11
  • 某、人
    老师我有几个问题:
    1.如何在死锁发生时,就把发生的sql语句抓出来?
    2.在使用连接池的情况下,连接会复用.比如一个业务使用连接set sql_select_limit=1,释放掉以后.其他业务复用该连接时,这个参数也生效.请问怎么避免这种情况,或者怎么禁止业务set session?
    3.很好奇双11的成交额,是通过redis累加的嘛?
    4.不会改源码能成为专家嘛?😄

    作者回复: 1. show engine innodb status 里面有信息,不过不是很全…
    2. 5.7的reset_connection接口可以考虑一下
    3. 用redis的话,为了避免超卖需要增加了很多机制来保证。修改都在数据库里执行就方便点。前提是要解决热点问题
    4. 我认识几位处理问题和分析问题经验非常丰富的专家,不用懂源码,但是原理还是要很清楚的

    2018-11-28
    11
  • 肉山
    不考虑数据表的访问并发量,单纯从这个三个方案来对比的话。
    第一个方案,一次占用的锁时间较长,可能会导致其他客户端一直在等待资源。
    第二个方案,分成多次占用锁,串行执行,不占有锁的间隙其他客户端可以工作,类似于现在多任务操作系统的时间分片调度,大家分片使用资源,不直接影响使用。
    第三个方案,自己制造了锁竞争,加剧并发。
    至于选哪一种方案要结合实际场景,综合考虑各个因素吧,比如表的大小,并发量,业务对此表的依赖程度等。
    2018-11-28
    10
  • 杰之7
    昨天和今天学习了锁相关的知识,对锁有了一定的认识。锁分为全局锁,表锁,行锁三类。全局锁在有事物支持的情况下,使用Mysqldump的single - transaction的方法进行备份时的更新。昨天晚上也问了老师一个关于表锁的疑问,通过老师的解答,理解了write比read的权限高。在今天的学习中,学习了行锁,顾名思义,行锁就是在进行行字段操作时,其他操作不能同时对行进行操作。根据这一问题,提出了两阶段锁协议,并发的锁尽量往后排,这样可以提升并发度。在锁中,还有两种情况,一是死锁,就是事物相互等待,直到等待结束可以通过设置innodb_lock_wait_timed的等待时间来控制。另一种情况是死锁检测,正常情况下是开的,可以对锁进行检测,但老师举了一个1000个并发请求,这样相互等待就是1000的平方量级,导致CPU消耗100%的性能,秒执行不到100行的例子。针对这种问题,老师给予了三个解决方法,把锁关了,不一定是最好,,第二是控制并发度,但有可能有几百个客户端并发并发进行,第三是在逻辑上的多行,当然也需要在程序上对特殊情况下的处理。学习到今天,数据库的核心有难度,但我相信自己可以搞定它,这就是我坚持它的理由。

    作者回复: 手动点赞

    2018-12-07
    9
  • shawn
    话说留言系统打不出大于小于号是为了防止xss攻击吧,推荐下,能不能用作转义的方式解决呢,留言里小于now()整个没有之后语句不通了
    技术向的服务自己的技术得过关吧(手动滑稽)
    2018-11-29
    8
  • ~嗡嗡
    个人理解,选择第二种
    1.直接delete 10000可能使得执行事务时间过长
    2.效率慢点每次循环都是新的短事务,并且不会锁同一条记录
    3.效率虽高,但容易锁住同一条记录,发生死锁的可能性比较高

    作者回复: 对的

    2018-12-26
    2
    6
收起评论
99+
返回
顶部