文章 标签 分类 关于我

Contents

Mysql锁分析

Dison included in mysql
   3051 words   7 minutes 

一般处理并发问题会设计两种锁，

  • 共享锁(shared lock)，S锁，也叫读锁(read lock)。
  • 排他锁(exclusive lock)，X锁，也叫写锁(write lock)。 Mysql同样如此。 读锁互不阻塞（多个线程在同一时刻可以读取同一个资源，而不互相干扰）。而一个线程的写锁会会阻塞其他线程的写和读。

1. 全局锁

全局锁是对整个数据库实例加锁，加锁后整个实例就处于只读状态。典型的使用场景是做全库的逻辑备份，对所有的表进行锁 定，从而获取一致性视图，保证数据的完整性。 加全局锁的命令为：

flush tables with read lock;

释放全局锁的命令为：

unlock tables;

或者断开加锁session的连接，自动释放全局锁。 对于InnoDB存储引擎，使用mysqldump备份时可以使用–single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。

# MyISAM 提交请求锁定所有数据库中的所有表，以保证数据的一致性，全局读锁【LBCC】 
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables > /root/db.sql 
# InnoDB 一致性视图【MVCC】 
mysqldump -uroot -p --host=localhost --all-databases --single-transaction > /root/db.sql

2. 表锁

MySQL的表级锁有四种

  • 表读锁（Table Read Lock）
  • 表写锁（Table Write Lock）
  • 元数据锁（meta data lock，MDL)
    • 不需要显式指定
    • 当对一个表做增删改查操作的时候，加 元数据 读锁；当要对表做结构变更操作的时候，加 元数据 写锁。
  • 自增锁(AUTO-INC Locks)
    • 涉及AUTO_INCREMENT列的事务性插入操作时产生

表锁常用命令

# 查看表锁定状态 
mysql> show status like 'table%'; 

lock table t read; #为表t加读锁 
lock table t write; #为表t加写锁
show open tables; #查看表锁情况
unlock tables; #删除表锁
  • 【读阻塞写】
    • 添加读锁后，当前Session插入或更新锁定的表会提示错误，其他Session插入或更新锁定表会一直等待
  • 【写阻塞读】
    • 添加写锁后，当前session对锁定表的查询+更新+插入操作都可执行，其他session对锁定表的查询被阻塞，需要等待锁被释放

3. 行锁

MySQL的行级锁，是由存储引擎来实现的。InnoDB行锁是通过给索引上的索引项加锁来实现的

  • （这一点困扰了自己很长时间，一直以为是加在记录的那一行上）

因此只有通过索引条件检索的数据，InnoDB使用行级锁，否则将使用表锁。

InnoDB的行级锁，按锁定范围划分：

记录锁（Record Locks）

  • 锁定索引中一条记录。
  • 锁住的永远是索引，而非记录本身，即使该表上没有任何显示索引，那么innodb会在后台创建一个隐藏的聚簇索引索引，那么锁住的就是这个隐藏的聚簇索引索引。

间隙锁（Gap Locks）

  • 锁住一个索引区间**（开区间，不包括双端端点）**
  • 间隙锁可用于防止幻读，保证索引间隙不会被插入数据。在可重复读（RR）这个隔离级别下生效。

临键锁（Next-Key Locks）

  • 相当于记录锁 + 间隙锁【左开右闭区间】
  • 默认情况下，innodb使用临键锁来锁定记录，但在不同的场景中会退化
  • 当查询的索引含有唯一属性的时候，临键锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。

插入意向锁(Insert Intention Locks)

  • 在 INSERT 操作之前设置的一种特殊的间隙锁。
  • 插入意向锁不会阻止插入意向锁，但是插入意向锁会阻止其他间隙写锁（排他锁）、记录锁。
  • 举个栗子：有两个事务分别尝试插入值为 60 和 70 的记录，每个事务使用插入意向锁锁定 11 和 99之间的间隙，但是这两个事务不会相互阻塞，因为行是不冲突的！这就是插入意向锁。
  • 也就是说间隙锁锁住的范围太大，性能不好。而插入意向锁就是对间隙锁的优化。

意向锁（Intention Locks）

  • 相当于存储引擎级别的表锁
  • 意向锁和行锁可以共存，意向锁的主要作用是为了全表更新数据时的提升性能。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。那么将是一件非常繁琐且耗时操作。
  • 说白了，只需要判断一次即可知道某表有没数据行被锁定，提升性能

InnoDB的行级锁，按功能划分：

  • 读锁：阻止其他事务加写锁，但不阻止其他事务加读锁。
  • 写锁：阻止其他事务加读锁和写锁。

如何加行级锁？

  • 对于UPDATE*、*DELETE和INSERT语句，InnoDB会自动给涉及数据集加写锁；
  • 对于普通SELECT语句，InnoDB不会加任何锁
#添加读锁
select * from t1_simple where id = 4 lock in share mode;
#添加写锁
select * from t1_simple where id = 4 for update;

加锁规则

主键索引

  1. 等值条件，命中，加记录锁
  2. 等值条件，未命中，加间隙锁
  3. 范围条件，命中，包含where条件的临键区间，加临键锁
  4. 范围条件，没有命中，加间隙锁

辅助索引

  1. 等值条件，命中，命中记录的辅助索引项 + 主键索引项加记录锁，辅助索引项两侧加间隙锁
  2. 等值条件，未命中，加间隙锁
  3. 范围条件，命中，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁
  4. 范围条件，没有命中，加间隙锁

4. 讨论一条sql执行时加了什么锁。

delete from t1 where id = 10;  
# 假设数据表有两列，id和name
  • 首先再次明确一个概念，Mysql的行锁是加在索引上的。
  • 然后需要考虑一些前置条件
    1. 当前的数据库隔离级别
    2. id列是否是主键
    3. 若不是主键，id列是否有索引
    4. 如果id列有二级索引，是唯一索引吗
    5. 当前是否存在id=10的数据

分析

case 1 : id为主键

  • 只需要将主键上id = 10的记录加上写锁即可。

case 2：id非主键，但是一个Unique的二级索引键值

  • 假设name是主键
  • 首先在辅助索引中找到符合条件的记录（通过id查找name），加写锁
  • 然后拿着主键name去主键索引（聚簇索引）中找到记录，也加写锁
  • 一共加锁2个

case 3：id有二级索引，但是非唯一索引

case3-1: 隔离级别RC情况下
  • 由于id列非唯一，假设根据id能查到n条数据
  • 同case2，辅助索引和主键索引上符合条件的记录都加写锁
  • 一共加锁2*n个
case3-2: 隔离级别RR情况下
  • RR隔离级别，不允许存在幻读
    • 同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)
    • **如何保证两次当前读返回一致的记录？**那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。–>间隙锁
  • 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的写锁，加GAP上的间隙锁，然后加主键聚簇索引上的记录写锁，然后返回；
  • 然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录写锁，但是仍旧需要加间隙锁，最后返回结束。
  • 和RC隔离级别相比，增加了在辅助索引上的间隙锁，间隙锁的个数取决于数据位置。一共加锁2*n+x个

case 4: id无索引

case4-1: 隔离级别RC情况下
  • 由于id列上没有索引，因此只能走聚簇索引，进行全部扫描
  • 首先，聚簇索引上所有的记录，都被加上了写锁
  • 但是MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。
case4-2: 隔离级别RR情况下
  • RR隔离级别，不允许存在幻读
  • 首先，聚簇索引上的所有记录，都被加上了写锁。其次，聚簇索引每条记录间的间隙，也同时被加上了间隙锁。
    • 假设表中有1千万数据，那么就是加1千万记录锁和1千万零1条间隙锁。
    • 在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。（很严重了！）
  • 同RC一样，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。

case5: 串行化Serializable 隔离级别下

  • MVCC并发控制降级为LBCC
  • 同case4-2
Updated on 2023-03-25
 mysql
Back | Home