#### mysql sql语句的加锁分析 ####

目录

1、update/delete/加锁读/insert语句在不同隔离级别下的加锁分析

1.1、RC隔离级别

组合一：id为主键

组合二：id唯一索引

组合三：id不唯一索引

组合四：id无索引

1.2、RR隔离级别 ##

组合五：id主键

组合六：id唯一索引

组合七：id不唯一索引（日常实践多见）

组合八：id无索引

1.3、串行化级别​​​​​​​

2、各类语句的加锁分析

SELECT ... FROM

SELECT ... FROM ... LOCK IN SHARE MODE

SELECT ... FROM ... FOR UPDATE

UPDATE ... WHERE ...

DELETE FROM ... WHERE ...

INSERT

INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO T SELECT ... FROM S WHERE ...

CREATE TABLE ... SELECT ...

REPLACE

REPLACE INTO t SELECT ... FROM s WHERE ...

自增列

外键约束

lock tables

---

1、update/delete/加锁读/insert语句在不同隔离级别下的加锁分析

分析下面这条语句，在下面不用组合场景下的加锁情况

update name为'123' from T where id = 10; // 等同于delete、加锁读、insert的场景，区别于快照读的场景

1.1、RC隔离级别

组合一：id为主键

加1个X锁（也就是写锁或排他锁），加在在对应聚簇索引上。

组合二：id唯一索引

加2个X锁，一个在对应二级索引上，一个在对应聚簇索引上。

组合三：id不唯一索引

加多个X锁，一部分在对应二级索引上（例如表里共用10条id=10的记录，则这里要加10个锁），一部分在对应聚簇索引上。

组合四：id无索引

结论：会走聚簇索引进行全表扫描。

详细：每条记录无论是否满足条件都会加上X锁，但是为了效率考虑，MySQL在这方面进行了改进，在扫描过程中若记录不满足过滤条件，会进行解锁操作，但同时这个优化违背了业界的2PL原则。

2PL —— 两阶段锁：

传统的RDMS加锁的一个原则，就是2PL(Two-Phase Locking，二阶段锁)。也就是说锁操作分为两个阶段：加锁阶段和解锁阶段，并且保证加锁阶段和解锁阶段不想交。也就是说在一个事务中，不管有多少条增删改，都是在加锁阶段加锁，在 commit 后，进入解锁阶段，才会全部解锁。

1.2、RR隔离级别 ##

组合五：id主键

加1个X锁（也就是写锁或排他锁），加在在对应聚簇索引上。

组合六：id唯一索引

加2个X锁，一个在对应二级索引上，一个在对应聚簇索引上。

组合七：id不唯一索引（日常实践多见）

在where命中的记录上加[ X锁 + X锁 ]：一个在对应二级索引上，一个在对应聚簇索引上。

在Gap段记录上加[ Gap锁 + X锁 ]：再在二级索引上的Gap段加上Gap锁，再在Gap段对应的主键聚簇索引上加上X锁。

组合八：id无索引

在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表上的所有记录，并且所有的Gap加上Gap锁，杜绝所有的 delete/update/insert 操作。（当然在MySQL中，可以触发 semi-consistent read来缓解锁开销与并发影响，但是semi-consistent read本身也会带来其他的问题，不建议使用）

1.3、串行化级别

在Serializable隔离级别下，所有的操作都会加锁。

2、各类语句的加锁分析

包含2小节包含1小节，所以综合看吧。

SELECT ... FROM

是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。在 SERIALIZABLE 隔离级别下，如果索引是非唯一索引，那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

SELECT ... FROM ... LOCK IN SHARE MODE

在所有索引扫描范围的索引记录上加上共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

SELECT ... FROM ... FOR UPDATE

在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。这将堵塞其他会话利用SELECT ... FROM ... LOCK IN SHARE MODE 读取相同的记录，但是快照读将忽略记录上的锁。

UPDATE ... WHERE ...

在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。

DELETE FROM ... WHERE ...

在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

INSERT

将在插入的记录上加一把排他锁，这个锁是一个index-record lock，并不是next-key 锁，因此就没有gap 锁，他将不会阻止其他会话在该条记录之前的gap插入记录。

INSERT ... ON DUPLICATE KEY UPDATE

和普通的INSERT并不相同。如果碰到重复键值，INSERT ... ON DUPLICATE KEY UPDATE 将在记录上加排他的 next-key锁。

INSERT INTO T SELECT ... FROM S WHERE ...

在插入T表的每条记录上加上 index record lock 。如果隔离级别是 READ COMMITTED, 或者启用了 innodb_locks_unsafe_for_binlog 且事务隔离级别不是SERIALIZABLE，那么innodb将通过快照读取表S(no locks)。否则，innodb将在S的记录上加共享的next-key锁。

CREATE TABLE ... SELECT ...

和INSERT INTO T SELECT ... FROM S WHERE ... 一样，在S上加共享的next-key锁或者进行快照读取（(no locks）

REPLACE

在没有碰到重复键值的时候和普通的INSERT是一样的，如果碰到重复键，将在记录上加一个排他的 next-key锁。

REPLACE INTO t SELECT ... FROM s WHERE ...

和 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 中的select 部分将在表s上加共享的next-key锁。

自增列

当碰到有自增列的表的时候，innodb在自增列的索引最后面加上一个排他锁，叫AUTO-INC table lock 。AUTO-INC table lock会在语句执行完成后进行释放，而不是事务结束。如果AUTO-INC table lock被一个会话占有，那么其他会话将无法在该表中插入数据。innodb可以预先获取sql需要多少自增的大小，而不需要去申请锁，更多设置请参考参数innodb_autoinc_lock_mode.

外键约束

如果一张表的外键约束被启用了，任何在该表上的插入、更新、删除都将需要加共享的 record-level locks来检查是否满足约束。如果约束检查失败，innodb也会加上共享的 record-level locks。

lock tables

lock tables 是用来加表级锁，但是是MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及 autocommit = 0的时候，innodb能够感知表锁，同时server层了解到innodb已经加了row-level locks。否则，innodb将无法自动检测到死锁，同时server无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。