分析一条SQL语句加了什么锁

1. MySQL锁的划分标准

1.1 按照锁的粒度来说，MySQL主要包含三种级别的锁定机制：

全局锁：锁的是整个database。
表级锁：锁的是某个table。
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由存储引擎实现。

1.2 按照锁的功能来为:

共享锁Shared Locks（S锁）:

• 兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁
• 加锁方式：select…lock in share mode

排他锁Exclusive Locks（ X锁 、独占锁 ）

• 兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁
• 加锁方式：select…for update

2. 全局锁

• 对整个数据库实例加锁，
• 加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，
  已经更新操作的事务提交语句都将被阻塞。
• 其典型的使用场景是做 全库的逻辑备份 ，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

-- 加全局锁的命令
FLUSH TABLES WITH READ LOCK;
-- 释放全局锁的命令
UNLOCK TABLES;

3. 表级锁

3.1 表写锁

阻塞其它会话对同一表的读写，表写锁释放后，才会允许其它进程的读写

3.1 表读锁

会阻塞其它进程对同一表的写操作，不阻塞读操作。表读锁释放，才会允许其它进程写

3.2 元数据锁

MDL不需要显式使用，在访问一个表的时候会被自动加上。
当要对表做结构变更操作的时候，加 MDL 写锁
当对一个表做增删改查操作的时候，加 MDL 读锁

-- SESSION1
use test;
set autocommit = 0;
select * from mylock m ; -- 加mdl读锁


-- SESSION2 阻塞
alter table mylock add column age int(3);

-- SESSION1回滚事务
rollback;


-- SESSION2
Updated Rows	0
Query	alter table mylock add column age int(3)
Finish time	Tue Dec 13 20:24:28 CST 2022

3.3 自增锁 ( AUTO-INC锁 )

一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

4.行级锁

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

-- SESSION1 手动添加共享锁（S）
select * from mylock where id  = 1 lock in share mode;

-- SESSION2 手动添加排他锁
select * from mylock where id  = 1 for update;

4.1 记录锁

锁住索引记录的一行，在单条索引记录上加锁

4.2 间隙锁(Gap Locks)

• 锁住一个索引区间（开区间，不包括双端端点）。
• 在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
• 间隙锁可用于防止幻读，保证索引间的不会被插入数据

4.3 邻键锁(Next-Key Locks)

• 记录锁 + 间隙锁
• 邻键锁的退化场景
  
• 间隙锁、邻键锁、记录锁的示意图
  

4.4 插入意向锁

• 为了防止插入的数据冲突，在插入的数据未提交时会锁定新插入数据的id。A事务插入id为10的记录未提交时，B事务再插入id为10的记录时会阻塞。

5. SQL语句的加锁原理

SQL1 ： SELECT * FROM mylock WHERE id = 1;
SQL2 : DELETE FROM mylock WHERE id = 2;

SQL1 不加锁，MySQL是使用多版本并发控制的，读不加锁

5.1 RC隔离级别下的加锁原理

5.1.1 id是主键

主键索引，命中记录加记录锁

5.1.2 id是唯一索引

辅助索引，命中记录，辅助索引项+主键索引项加记录锁

5.1.3 id是非唯一索引

辅助索引，命中记录，辅助索引项+主键索引项加记录锁

5.1.4 id无索引

• 没有索引，因此只能走聚簇索引，进行全部扫描。
• 聚簇索引上所有的记录，都会被Innodb引擎加上X锁，返回给MySQL Server层进行过滤
• 为了效率考虑，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁
  

5.2 RR隔离级别下的加锁原理

5.2.1 id是主键

主键索引，命中记录加记录锁

5.2.2 id是唯一索引

辅助索引，命中记录，辅助索引项+主键索引项加记录锁

5.2.3 id是非唯一索引

• Repeatable Read隔离级别下，id列上有一个非唯一索引，
  对应SQL：delete from t1 where id= 10;
• 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，加主键聚簇索引上的记录X锁，然后返回；
• 读取下一条，重复进行。
• 直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁。
  

5.2.4 id无索引

全局扫描所有聚簇索引行，锁上所有索引行，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作