MySQL锁

作者: 慧鑫coming | 来源:发表于2019-02-27 06:13 被阅读0次

    MySQL锁分类

    • 全局锁
    • 表级锁
    • 行锁
    • 间隙锁
    • next-key lock

    全局锁

    • 作用范围:对整个数据库实例加锁。(FTWRL)flush tables with read lock;,该命令会让整个库处于只读状态,之后其他线程的以下语句会被阻塞:
        1、数据更新语句(即对数据的增删改操作)。
        2、数据定义语句(建表,修改表结构,加索引等操作)
        3、更新类事务的提交语句
    • 使用场景:全库逻辑备份。
    • 备份加锁的必要性
        如果不加锁,备份得到的库整体不是一个逻辑时间点,视图是逻辑不一致的。
    • 缺点
        1、如果在主库上备份,整个备份期间都不能执行更新,业务基本上停摆
        2、如果在从库上备份,备份期间都不能执行从主库同步过来的binlog,会导致主从延迟
    • mysqldump优点
        官方自带的逻辑备份工具mysqldump,当使用参数-single-transaction时,导数据之前会启动一个事务,确保拿到一致性视图。由于MVCC的支持,在这个备份过程中数据是可以正常更新的。
    • FTWRL的必要性
        mysqldump固然好,但是前提要引擎支持可重复读隔离级别,-single-transaction方法只是用于所有的表使用事务引擎的库。
    • FTWRL做备份优于set global readonly=true
        1、某些系统中readonly值可能被用来做其他逻辑,比如判断一个库是master还是slave,修改global变量影响大。
        2、在异常处理机制上。执行FTWRL后若是由于客户端发生异常,断开连接,MySQL会自动释放这个全局锁;set global readonly=true方式遇到这种情况会一直保持readonly状态,会导致整个库长时间不可写,风险高。

    表级锁

    • 表级锁包括表锁元数据锁
    • 表锁
        lock tables t1 read, t2 write;,此时,其他线程写t1,读写t2的语句都被阻塞。同时,在执行unlock tables前,本线程也只能对t1执行读操作,对t2执行读写操作;写t1的操作时不允许的,本线程也不能访问其他表。
    • 使用场景:表锁通常用来处理并发。对于InnoDB这种支持行锁的引擎,一般不用表锁来控制并发。
    • 元数据锁MDL(Meta Data Lock)
        MDL不需要显示调用,在访问一个表时会自动加上,作用是保证读写的正确性。当对一个表做CRUD操作时,加MDL读锁;当对一个表结构变更操作时,加MDL写锁。
        读锁之间不互斥,因此可以多个线程同时对一张表做CRUD操作;写锁之间,读写锁之间互斥,用来保证变更表结构操作的安全性,如果有2个线程同时对一个表做结构变更操作,其中一个要等另一个执行完才能执行。
        MDL是系统默认加的,在一个事务中使用时,在语句执行开始时申请,但语句结束后并不会马上释放,要等整个事务提交后才释放。所以可能出现给一个小表加字段,导致整个库挂了的情况,如下:
    start transaction;  -- sessionA
    select * from t limit 1;  -- get MDL read and no release
    start transaction;  -- sessionB
    alter table t add f int;  -- waiting for session MDL read, blocked
    

    行锁

    • InnoDB行锁是通过给索引上的索引项加锁来实现的,意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
    • 行锁由MySQL各个引擎自己实现,并不是所有引擎都支持行锁,MyISAM就不支持行锁,InnoDB支持行锁,这里主要说InnoDB。
    • 行锁分为读锁(S锁,共享锁)写锁(X锁,排他锁)对同一行的操作,只有读锁与读锁间不冲突
    • 两阶段锁协议:在InnoDB中,行锁是在需要的时候加上,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。
    • 两阶段锁协议的影响如果一个事务中需要锁多个行,要把最可能造成锁冲突,最可能影响并发度的锁尽量往后放。同理于MDL锁的释放,也就是说一个事务中的某一行的锁的持续时间,根据两阶段锁协议,如果事务提交的时间点确定,那么越早获得这一行的行锁,它的持续时间越长,所以对并发度影响大的行要尽量往后放。
    • 死锁:两个事务互相等待对方的锁释放。
    • 死锁应对策略
        1、就是等,直到超时;超时时间可通过innodb_lock_wait_timeout设置,默认50s。(时间设置太长,很多在线服务无法接受;时间设置太短,可能产生很多非死锁的误伤)
        2、主动发起死锁检测,发现死锁后,主动回滚死锁链条中的某一事务,让其他事务得以继续执行;参数innodb_deadlock_detect=on表示开启死锁检测。
    • 主动检测死锁的过程
        每当一个事务要加行锁的时候,就要看看它所依赖的线程有没有被别人锁住(即需要看看自己需要的锁有没有在别人手里),如此循环,最后判断是否出现了循环等待,也就是死锁。

    间隙锁

    • 锁住索引树上相邻两个值之间的空隙。
    • 间隙锁使用场景是RR(可重复读隔离级别),也就是说它是InnoDB引擎所特有。

    next-key lock

    • 间隙锁和行锁合称next-key lock。以下是加锁规则。
    • 原则1:加锁的基本单位是next-key lock,它是( , ]左开右闭区间。(除了“唯一索引的等值查询”,其他“范围查询”、“唯一索引范围查询”、“非唯一索引的等值查询”均要找到不满足条件的“整个区间”)。
    • 原则2:查找过程中访问到的对象才会加锁。(若是涉及覆盖索引的查询,针对lock in share mode,若是所查询的字段都包含在覆盖索引中,那么不会锁主键索引;而for update 和 select 查找索引中没覆盖到的字段都会锁主键索引)。
    • 优化1唯一索引的等值查询,当找到符合条件的索引后,索引所在的间隙锁解除,该next-key lock退化为行锁
    • 优化2:索引上的等值查询(无论是否是唯一索引),当向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
    • 补充1:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
    • 补充2:使用limit可以减小加锁范围,取到满足条件的语句条数,就结束。delete操作建议加上limit,一是保证安全性,二是减小加锁范围。
    • 补充3:next-key lock加锁分为2步,先加间隙锁,再加行锁,间隙锁间不冲突(跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作操作。),行锁间冲突。这种锁的顺序可能产生死锁,A拿着B需要的行锁,B拿着A插入操作需要的间隙锁)。

    相关文章

      网友评论

          本文标题:MySQL锁

          本文链接:https://www.haomeiwen.com/subject/ntuieqtx.html