MySQL 事务与锁

Updated on with 0 views and 0 comments

10cf649eaf1f494ba6e30cff340d8bc4.png

多版本并发控制 MVCC

  • 实现非锁定读，读不加锁，读写不冲突
  • 如何实现？
    • 两个隐含的列
      1. 更新版本号
      2. 删除版本号
    • 每个事务开始时会被分配一个递增的事务号
  • 下面分别以 SELECT、INSERT、 DELETE、 UPDATE 语句来说明：
    • SELECT，对于 select 语句，只有同时满足了下面两个条件的行，才能被返回：
      1. 行的被修改版本号小于或者等于该事务号
      2. 行的被删除版本号要么没有被定义，要么大于事务的版本号
        • 行的删除版本号如果没有被定义，说明该行没有被删除过
        • 如果删除版本号大于当前事务的事务号，说明该行是被该事务后面启动的事务删除的
        • 如果是 Repeatable Read 隔离等级，后开始的事务对数据的影响不应该被先开始的事务看见，所以该行应该被返回
    • INSERT，对新插入的行，行的更新版本被修改为该事务的事务号
    • DELETE，对于删除，innodb 直接把该行的被删除版本号设置为当前的事务号，相当于标记为删除，而不是实际删除
    • UPDATE，在更新行的时候，innodb 会把原来的行复制一份到回滚段中，并把当前的事务号作为该行的更新版本

快照读 (Snapshot Read) 与 当前读 (Current Read)

  • 快照读
    • 简单的 select 操作，没有 lock in share mode 或 for update
    • 快照读一般不会加任何的锁，而且由于 MySQL 的一致性非锁定读的机制存在，任何快照读也不会被阻塞，但是如果事务的隔离级别是 SERIALIZABLE 的话，那么快照读也会被加上共享的 next-key 锁
  • 当前读
    • 官方文档的术语叫 locking read，也就是 insert、update、delete、select ... in share mode 和 select ... for update
    • 当前读会在所有扫描到的索引记录上加锁，不管它后面的 where 条件到底有没有命中对应的行记录
    • 当前读可能会引起死锁
  • 典型 SQL
    • select * from table where ?;
    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert into table values (…);
      • Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读
    • update table set ? where ?;
    • delete from table where ?;

聚簇索引

2PL（Two-Phase Locking，二阶段锁）

  • 将 加锁 / 解锁 分为两个完全不相交的阶段
  • 加锁阶段：只加锁，不放锁
  • 解锁阶段：只放锁，不加锁

事务隔离级别 Isolation Level

  • ACID 和 事务隔离级别 是什么关系？
    • ACID 是事务的基本属性，并不解决事务并发问题
  • 读一致性问题：脏读、不可重复读 和 幻读
    f1aaaf848313793ef438f0a96d9d3aa8.png
  • 更新丢失（Lost Update）
    1032bc103f568cc86551fad4862a6d42.png

锁

10cf649eaf1f494ba6e30cff340d8bc4.png

  • 三锁
    • 表锁
    • 行锁
    • 页锁
  • 悲观锁 和 乐观锁
  • 共享锁 和 排他锁
  • Gap 锁 和 Next-Key 锁
    • Gap 锁：在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身
    • Next-Key 锁：就是 Record Lock 和 Gap Lock 的结合，即除了锁住记录本身，还要再锁住索引之间的间隙
  • 意向锁
    • 主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”
  • 死锁
    1. 互斥条件
    2. 请求和保持条件
    3. 不可抢占条件
    4. 循环等待条件

事务隔离级别、MVCC 和 锁 是什么关系？

  • 事务隔离级别的目的是做事务并发控制，解决读一致性的问题
  • MVCC 和 锁 是实现事务并发控制的手段

加锁分析

  • 用例
    • SQL1：select * from t where id = 10;
    • SQL2：delete from t where id = 10;
  • 前提条件假设
    1. id列是主键，RC隔离级别
    2. id列是二级唯一索引，RC隔离级别
    3. id列是二级非唯一索引，RC隔离级别
    4. id列上没有索引，RC隔离级别
      • 说明：在实际的实现中，MySQL有一些改进，在 MySQL Server 过滤条件，发现不满足后，会调用 unlock_row 方法，把不满足条件的记录放锁 (违背了 2PL 的约束)，这样做保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的
    5. id列是主键，RR隔离级别
    6. id列是二级唯一索引，RR隔离级别
    7. id列是二级非唯一索引，RR隔离级别
    8. id列上没有索引，RR隔离级别
      • semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁
      • semi-consistent read如何触发：要么是read committed隔离级别，要么是Repeatable Read隔离级别，同时设置了 innodb_locks_unsafe_for_binlog 参数
    9. Serializable隔离级别

复杂 SQL 加锁分析

  • Where 条件提取
    • Index Key：用于确定SQL查询在索引中的连续范围的查询条件。pubtime > 1 and pubtime < 20，此条件，用于确定SQL在idx_t1_pu索引上的查询范围，Index Key确定的范围，需要加上GAP锁
    • Index Filter：用于过滤索引查询范围中不满足查询条件的记录。userid = 'hdc' ，此条件可以在idx_t1_pu索引上进行过滤（Index Condition Pushdown，索引下推），但不属于Index Key。不满足Index Filter的记录，不加X锁，否则需要X锁
    • Table Filter：所有不属于索引列的查询条件，均归为Table Filter之中。comment is not NULL，此条件在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤，Table Filter过滤条件，无论是否满足，都需要加X锁
  • RR 条件下的加锁分析
标题：MySQL 事务与锁
作者：yanghao
地址：http://solo.fancydigital.com.cn/articles/2021/09/23/1632329114155.html

↑ ↓