开发者

Mysql for update导致大量行锁的问题

目录
  • 一、引言
  • 二、分析
    • 1、Innodb不存在锁升级
    • 2、当for update一个不存在的where条件时
    • 3、Innodb如果在索引中找不到记录
    • 4、Innodb加锁的方式是从上到下的
    • 5、RR级别加锁情况
  • 三、总结

    一、引言

    最近同事的复盘会上提到自己for update一个不存在的where条件导致表锁,然后产生大量的事务失败和读写超时,这时博主非常奇怪,因为虽然网上许多博客写Innodb的表锁行锁与锁升级,但是事实上这都是错误的观点。

    二、分析

    首先博主的环境是mysql5.7,隔离级别是RC

    博主为什么说这些都是错误的观点呢?

    因为在《高性能Mysql》和《Innodb存储引擎当中》,非常明确的提出:

    1、Innodb不存在锁升级

    所以不存在因为锁的数据量大或者多表,导致行锁升级成表锁。

    Mysql for update导致大量行锁的问题

    2、当for update一个不存在的where条件时

    Innodb加的是Record级别锁

    这一点可以通过验证得到

    • 不存在的where:
    set autocommit = 0 ;
    begin;
    select * from t_aac_BATtery_compensate where gmt_create in ('2020-05-21 07:02:37') for PFTaquAhAupdate ;
    • 存在的where:
    begin;
    select *python from t_aac_***where gmt_create in ('2020-05-21 07:32:37') for update ;

    然后执行

    select * f编程rom information_schema.INNODB_LOCKS il

    可以看到锁

    Mysql for update导致大量行锁的问题

    可以看到两个事务加的都是行级别锁。

    可能有的同学会对锁住的行数量和数据有疑惑,这里python博主发现这两个数值统计的方式是不准的,包括在《Innodb存储引擎》作者明确提出lock_data是不准确的。

    也有的同学疑惑他加行锁为什么会阻塞其他读写,这里是innodb加了行锁之后最后一起释放,虽然不知道它这样的设计是出于什么考虑。

    3、Innodb如果在索引中找不到记录

    会在行数据进行搜索,锁住主键,而不是锁表

    Mysql for update导致大量行锁的问题

    所以一些博客说根据索引加不到锁,innodb就会锁全表,这是错误的理解

    只是可能在一些情况下他搜索行数据对主键加锁的数量过多,之前也说了innodb加行锁是最后一起释放的,所以阻塞了其他读写

    4、Innodb加锁的方式是从上到下的

    自动加锁只有表级别的意向锁和行级锁,表级别的意向锁只会阻塞全表扫描

    Mysql for update导致大量行锁的问题

    5、RR级别加锁情况

    上文都是基于博主线上环境配置,如果是RR隔离级别,还会有GapLock与行锁进行Next_keyLock算法加锁,其实简单说就是锁住当前B+树种当前索引到上一个索引之间(或当前行到上一行)的间隔,防止在这个过程中有插入数据,也就是防止幻读。

    Mysql for update导致大量行锁的问题

    但是这个情况不是绝对的,对于唯一索引,innodb会降低级别行级锁,不会锁住范围

    Mysql for update导致大量行锁的问题

    三、总结

    通过以上分析得到结论:

    1、RC级别下innodb都是行级锁,表级的意向锁只会阻塞全表扫描

    2、innodb不存在锁升级

    3、innodb加不到索引会搜索行,对主键加锁

    4、当for update一个不存在的where条件时,Innodb加的是Record级别锁

    以上分析除了实际操作验证和权威书籍理解之外,博主与DBA也经过深入探讨,如果有异议欢迎讨论。 另外希望各位同学,多实际操作、多看权威书籍和源码,对于网上的博客看一半信一半,要有自己的判断,书籍和源码的查看也要结合实际经验,因为每个人的脑回路是不一样的,一不小心理解方向就可能歪了。

    好了,这些编程仅为个人经验,希望能给大家一个参考,也希望大家多多支持编程客栈(www.devze.com)。

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜