专栏首页立权的博客Mysql-Innodb 锁总结

Mysql-Innodb 锁总结

1.全局锁:(限制 DML , DDL[修改表结构])

  全局读锁: Flush tables with read lock

    Flush tables 做的是将缓存刷回硬盘,with read lock 给所有表加读锁,对于大部分 lock,当客户端连接断开的时候,锁一般会释放。

    如果在主库上使用此命令,则写业务停摆。在从库上使用此命令,则来自主库的 binlog 无法被执行,主从同步会延后

  全局只读属性:set global readonly = true

    此命令不等同于锁,而是设置数据库的全局可读性,客户端连接断开也不会使得此属性还原,慎用。

    readonly 可能用于从库,不适用于读锁

2.表级锁

  表锁:lock tables tableName read/write  /  unlock tables

    属于 lock,不是属性,也不是 latch(线程同步工具),所以客户端连接断开时,也会解锁

    并且同一线程(客户端连接),就算是自己拥有读锁,也不能进行写操作。

  MDL锁(metadata lock):

    1.增删查改时加 MDL 读锁

    2.改表结构(DDL)加写锁

  需要注意的是,MDL锁在 Mysql 的实现使用了一把锁,但是这把锁会记录两个链表

  一条链表是记录该Lock 已经被获取了的类型,称为 granted ,比如有 N 个连接请求了 MDL 读锁,那么 granted 链表就应该

  有 N 个状态为 MDL_SHARED_XXX 的节点

  还有一条链表是 waiting,表示因为和 granted 链表的任一锁类型冲突而导致需要等待加上的锁

官方给他们的定义:

 假设一下线程要去获取锁 A, 获取类型是 type,那么该线程需要做两件事,查看自己要获取的 type 是否和

 A的两条链表的锁类型都兼容。才能获得锁。否则阻塞,并且把自己的 type 加入 waitting 链表。

 所以有一种情况:三个客户端连接分别执行 A,B,C。且按A->B->C的顺序执行。

 Transaction A: select * from t;

 Transaction B: alter table t change clomun 'x' 'a' int null default null;

 Transaction C: select * from t;

 最终 B 和 C 会被堵住,原因就是 A 在 granted 链表中放入了 SHARED 类型的锁节点

 B获取失败,在 waitting 链表放入 EXCLUSIVE 类型的锁节点

 C检查链条链表,发现和自己的锁 waitting 链表中的 EXCLUSIVE 不兼容,所以C也被阻塞。

3.行锁:

  两阶段锁协议:连接在事务中获得的行锁,都在事务结束才会释放。而MDL写锁不会有类似现象(MDL读锁会)。

行锁调优:涉及 竞争度激烈的行 的语句应该尽量放在 事务的后面,这样的话占有这个行的锁的时间会尽可能短,因为离事务结束更近。

       占用这个锁的时间会相对短,如此一来,这个竞争激烈的行的锁就会更快释放。

       比如说,订单交易的时候,事务中会扣除客户账户余额,和商店营收。多个客户购买的话,就会同时去竞争商店营收这一行数据。

       所以在事务中可以先改用户账户余额,再去改商店营收。或者可以将商店营收改成多个行,然后把不同用户路由到不同行上去修改

       对应营收,求总营收的时候需要将所有营收行锁读锁,然后读取。

4.间隙锁 + 行锁 = net-key lock

  间隙锁 主要为了防止幻读,同时也表明了,加锁的对象不一定是行,也可以是间隙,比如一张表有两条记录 id = 1 和 id = 5

,并且id是主键,那么在主键上的范围(1, 5)就是一个间隙,锁住了这个间隙,其他客户端连接(线程)就不能对这个间隙插入内容,但是可以对这个间隙(不存在的值)做 update 和 delete。注意,间隙锁是不包含行记录的,锁行记录的是行锁。 间隙锁锁的是插入意图,不是更新和删除意图

只有在可重复读隔离级别的情况下,才可能出现幻读的情况,幻读指的是当前事务重复读取的情况下,下一次读取读取到了上一次读取不存在的行。如果是之前读取的了的某一行的内容变了,严格来说不算幻读。在可重复读的情况下,应该叫做脏读。

  间隙锁的加锁规则:

  1. 当使用 update,delete,或者带 for update 或者 lock in share mode 语句时会加锁, 且加锁的单位是 next-key lock

  2. 只有访问到的对象会加锁,此处对象可以是单单辅助索引或者带有数据行的聚簇索引,如果是访问不存在的行,也就是访问间隙的话,就只会加上间隙锁(辅助索引和聚簇索引都一样,就算是辅助索引,因为是访问两个存在行中的不存在行,所以访问到右边的存在行就会停下来,根据索引等值查询优化,不满足条件的右边行的行锁被排出 next-key lock ,所以只剩下间隙锁)

  3. 唯一索引(注意不只是聚簇索引)的等值查询会使原本要加的 next-key lock 退化 行锁

  4. 非唯一索引 的等值查询会扫描(只有非唯一索引上的等值查询会扫描,因为唯一索引是不能重复的)到最后一个不满足条件时停下,并且最后一个不满足条件的行造成的 next-key lock 会退化成 间隙锁

  5. next-key lock 以右值为标准 形成 做开右闭 区间,在innodb中有 suprenum 表示最大值,(x, suprenum] 表示最后一个next-key lock 区间

  6. 主键在范围查询时,本应该在遍历到最后一个满足条件的行后结束遍历(因为主键唯一),但是还是会遍历到不满足条件为止,这导致多加了一个 next-key lock,比如假设有 id(主键) = 10, 15, 20 的行,条件本来是 where id > 10 and id <= 15,那么按理说加的锁是

(10, 15],因为已经找到 id = 15 的了,接下去不可能再有 id <= 15 的行,但是还是会继续遍历到 20,不满足条件,停止。所以最后加的锁是 (10, 15] 和 (15, 20]

  7.正序 加锁的范围:

    1. 对于 col >= x and col < y 是从 x 的等值查询 开始的,并且范围查询,直到遇到 col >= y 的行

    2. 对于 col > x and col < y 是从 x 这一行向右范围查询,直到遇到 col >= y

    3. 对于 col >=x and col <= y 和 7.1 一样,只不过遇到 col > y 才停止

    4. 对于 col > x and col <= y 和 7.2 一样,从x 这一行向右范围查询,直到遇到 col > y 等行停止

  8.倒序 加锁的范围

    1. 对于 col >= x and col < y by desc 是从 y 开始向左范围查询,直到遇到 col < x

    2. 对于 col > x and col < y by desc和 8.1 一样,从y开始向左范围查询,直到遇到 col <= x

    3. 对于 col >= x and col <= y by desc,从 y 的等值查询开始,向左范围查询,直到遇到 col < x

    4. 对于 col > x and col <= y by desc,从 y 的等值查询开始,向左范围查询,直到遇到 col <= x

  9. 多个线程对同一个间隙加锁不互斥,间隙锁本身是一种读锁

  10.next-key lock 是分阶段加上去的,先加间隙锁,再加行锁。所以如果有线程 A 先持有行锁,线程 B 再去持有间隙锁且要求A的行锁,线程A再去要求B持有的间隙锁,会造成死锁。

    假设只有 id = 1, c = 123 , a = 345 这一行,且c是索引

    A : update table set a = a + 1 where c = 123; (持有 (-max, 123] , (123, suprenum] 这两个next-key lock))

    B : update table set a = a + 2 where c = 123; (加上了 (-max,123) 这个间隙锁,并且堵在了 c = 123 这一行的行锁上)

    A : insert into tables values (2, 100, 666); (请求 B 持有的 (-max,123) 导致死锁)

  11.limit限制扫描行数,减少访问对象,减少锁的锁定对象 比如非唯一索引c上 有 10 12 15 三个值

    如果:

    A: update table set a = a + 1 where c > 10 limit 1(只会加(10,12]的next-key lock)

    B: insert into table values (123, 13, 123) 仍然能成功(c = 13)

  12.间隙锁拓展:间隙锁依赖于两条记录之间

    比如非唯一索引c有四条记录 A, B ,C, D

    那么存在三个间隙锁 (A, B) , (B , C),(C , D)

    倘若现已加 (C, D] next-key lock ,那么删除 C 这一行的时候,(C,D] 会变成 (B, D]

    倘若使用 update table set c = m where c = A     ( B < m < D)

    执行此语句的线程将被 block,因为上述语句相当于在 被间隙锁锁住的 (B,D) 中插入 c = m 的记录

    再删除 c = A 的记录

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Java:手写线程安全LRU缓存X探究影响命中率的因素

    最近遇到一个需求,需要频繁访问数据库,但是访问的内容只是 id + 名称 这样的简单键值对。

    执生
  • elasticsearch-基本概念

    先类比 elasticsearch 和 关系型数据库对应的某些概念,了解ES的结构:

    执生
  • Hadoop架构: 流水线(PipeLine)

    该系列总览: Hadoop3.1.1架构体系——设计原理阐述与Client源码图文详解 : 总览

    执生
  • Innodb锁机制探究(一)---自增锁(2)

    今儿做了很多的业务需求,对接了几个接口,算是比较充实吧。想起了同事说的那句话,越忙的时候,越要停下来好好思考,抽空整理整理,不然就会很快陷入一个死循环里...

    AsiaYe
  • 夯实根基,必知必会的 Python 基础知识

    Python 领域流传着一句话:人生苦短,我用 Python(Life is short,you need Python)。

    测试开发社区
  • 尝试解决 Xcode7.1 覆盖率测试 GCDA 文件损坏问题

    刘笑江
  • 项目从VS2010 升 VS2012 遇到的代表性问题及解决

    最近公司要把项目从使用VS2010 转成VS2012,开始以为是个很简单的事情,后来发现有点头疼。 现在问题解决了,发现主要有以下3个方面的问题 第三方组件,对...

    麦克-堂
  • 美团点评2018校园春季招聘后台开发工程师笔试经验

    Zoctopus
  • 美团点评2018校园春季招聘后台开发工程师笔试经验

    Zoctopus
  • 使用Python将图片转正方形的两种方法实例代码详解

    使用 nn.ZeroPad2d() 或者 nn.ConstantPad2d() 进行填充

    砸漏

扫码关注云+社区

领取腾讯云代金券