4-Mysql锁

Mysql锁

1. update执行流程

2. Mysql锁的分类

1. 粒度

• 行级锁：锁数据行或者间隙
• 表级锁： 锁表
• 全局锁：锁整个database

2. 功能

• 共享锁 S锁

  加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁

• 排他锁 X锁

3. 全局锁

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

4. Mysql 表级锁

• 表读锁（S锁）、表写锁（X锁）。

  表锁：

  1. 表共享读锁（Table Read Lock）
  2. 表独占写锁（Table Write Lock）

  lock table 表名称 read(write)；

  show open tables;

  unlock tables;

• 元数据锁（meta data lock，MDL)。

• 意向锁 Intention Locks（InnoDB）

• 自增锁(AUTO-INC Locks)

5. Mysql行级锁

以InnoDB实现为例

1. 锁定范围分类

• 记录锁（Record Locks）:锁定索引中一条记录。
• 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
• 临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
• 插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

2. 功能分类

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

  对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。

  手动加锁：

  SELECT * FROM table_name WHERE … LOCK IN SHARE MODE;

  SELECT * FROM table_name WHERE … FOR UPDATE;

3. 意向锁

意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。

例：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

4. 记录锁

1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。

2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

5. 间隙锁

1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。

2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

3）间隙锁可用于防止幻读，保证索引间的不会被插入数据。

6. 临键锁

7. 行锁加锁规则

1）主键索引

1. 等值查询

   （1）命中记录，加记录锁。

   （2）未命中记录，加间隙锁。

2. 范围查询

   （1）没有命中任何一条记录时，加间隙锁。

   （2）命中1条或者多条，包含where条件的临键区间，加临键锁

2）辅助索引

1. 等值查询

   （1）命中记录，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁。

   （2）未命中记录，加间隙锁

2. 范围查询

   （1）没有命中任何一条记录时，加间隙锁。

   （2）命中1条或者多条，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁。

6. 行锁分析

delete from t1 where id = 10;

1. RC隔离级别 id为主键

​ 主键索引当前数据行加排它锁。

2. RC隔离级别 id唯一索引（非主键）

​ 次要索引当前记录加排它锁，取出主键去主键索引加排他锁。

3. RC隔离级别 id非唯一索引

​ 次要索引满足条件的记录加排它锁，取出主键去主键索引全部加排他锁。

4. RC隔离级别 id无索引

​ SQL会走聚簇索引的全扫描，遍历加排它锁，对于不满足条件的记录会释放锁。

5. RR隔离级别 id主键

​ 主键索引当前数据行加排它锁。

6. RR隔离级别 id唯一索引（非主键）

​ 次要索引当前记录加排它锁，取出主键去主键索引加排他锁。

7. RR隔离级别 id非唯一索引

​ 为了防止幻读,会加间隙锁。

​ 匹配到的记录加排它锁，记录之间加间隙锁。主键索引匹配到的数据加排它锁。

8. RR隔离级别 id无索引

全表扫描， 聚簇索引全部加排它锁，间隙锁。

​ 当然，也可以通过触发semi- consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问 题，不建议使用

9. 复杂sql分析

​ delete from t where pubtime > 1and pubtime < 20 and userid = ‘hdc’ and comment is not NULL;

​ Index：idx(pubtime, userid)

​ id 为主键

​ RR隔离级别

1）where 条件拆分

• Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。

• Index Filter：userid = ‘hdc’ 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。

• Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上

  过滤。

2）分析

• 组合索引范围条件符合要求的数据全部加排它锁，并且加间隙锁。
• 根据索引下推，直接在该组合索引上过滤userid = ‘hdc’，不符合条件的数据行取消排它锁。
• 根据所有主键id回表，并且在主键索引加排它锁。

7. 死锁

1. 注意程序的逻辑

   根本的原因是程序逻辑的顺序，最常见的是交差更新

   Transaction 1: 更新表A -> 更新表B

   Transaction 2: 更新表B -> 更新表A

   Transaction获得两个资源

2. 保持事务的轻量

   越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小

3. 提高运行的速度

   避免使用子查询，尽量使用主键等等

4. 尽量快提交事务，减少持有锁的时间

   越早提交事务，锁就越早释放