闲谈常见SQL语句加锁

在MySQL数据库中，锁机制是确保数据一致性和完整性的重要手段。MySQL中的锁可以分成两个粒度：表锁和行锁。以下是对常见SQL语句加锁的详细解析。

1. 表锁

   表级读锁（S锁）：允许其他事务读取，但不允许写入。

   表级写锁（X锁）：不允许其他事务读取和写入。

   意向锁：分为意向共享锁（IS锁）和意向排他锁（IX锁），用于协调行锁和表锁的关系。

   自增锁：在特定情况下（如innodb_autoinc_lock_mode = 0或Bulk inserts时）使用，确保自增列的唯一性。

2. 行锁

   记录锁（Record Lock）：加在索引记录上的锁，分为S锁和X锁。

   间隙锁（Gap Lock）：对索引记录之间的间隙加锁，防止新记录插入。

   Next-key Lock：记录锁和间隙锁的组合，用于防止幻读。

   插入意向锁（Insert Intention Lock）：表示插入意图，用于多个事务同时插入不同记录时避免锁冲突。

1. SELECT语句

   快照读：默认情况下，SELECT语句为快照读，不加锁，使用MVCC（多版本并发控制）机制。

   当前读：

   SELECT ... LOCK IN SHARE MODE：加S锁，允许其他事务读取，但不允许写入。

   SELECT ... FOR UPDATE：加X锁，不允许其他事务读取和写入。

   在Serializable隔离级别下，SELECT语句为当前读，加S锁。

2. DML语句（INSERT、DELETE、UPDATE）

   均为当前读，加X锁。

3. DDL语句（ALTER、CREATE等）

   加表级锁，且为隐式提交，不能回滚。

1. RC（读已提交）隔离级别

   SELECT语句为快照读，不加锁。

   DML语句加X锁。

   无间隙锁和Next-key锁（特殊情况下除外，如purge + unique key）。

2. RR（可重复读）隔离级别

   SELECT语句为快照读，但在Serializable隔离级别下为当前读，加S锁。

   DML语句加X锁，并可能产生间隙锁和Next-key锁。

以students表为例，分析UPDATE语句在不同情况下的加锁情况。

1. 聚簇索引，查询命中

   UPDATE students SET score = 100 WHERE id = 15

   在RC和RR隔离级别下，对id这个聚簇索引加X锁。

2. 聚簇索引，查询未命中

   UPDATE students SET score = 100 WHERE id = 16

   RC不加锁，RR有GAP锁，id15到18间隙锁。

3. 二级唯一索引，查询命中

   UPDATE students SET score = 100 WHERE no = 'S0003'

   命中二级唯一索引，加X锁，同时主键索引也会加X锁。

4. 二级非唯一索引，查询命中/未命中

   RC无锁，RR隔离级别会加GAP锁。

5. 无索引

   UPDATE students SET score = 100 WHERE score = 22

   聚簇索引上所有记录都被加上X锁，RC隔离级别下加行锁，RR隔离级别下还加GAP锁。

6. 聚簇索引，范围查询

   UPDATE students SET score = 100 WHERE id <= 20

   RC：锁住id = 20、18、15三条记录。

   RR：锁住id = 30以及(20, 30]之间的间隙。

7. 二级索引，范围查询

   UPDATE students SET score = 100 WHERE age <= 23

   和聚簇索引的范围查询一样，加锁范围包括WHERE条件范围内的记录和后续一条记录的Next-key锁。

1. 防止幻读

   如果记录之间加有GAP锁，则不能INSERT。

2. 唯一键冲突

   如果INSERT的记录与已有记录造成唯一键冲突，则不能INSERT。

3. 插入意向锁

   对插入的间隙加插入意向锁，如果该间隙已被加上了GAP锁或Next-Key锁，则加锁失败进入等待。

4. 唯一性约束检查

   如果不存在相同键值，则完成插入。

   如果存在相同键值，则进行进一步判断和处理（如加锁、等待、报错等）。

综上所述，MySQL中的锁机制是一个复杂而重要的系统，它确保了数据的一致性和完整性。在编写SQL语句时，了解并合理利用锁机制，可以显著提高数据库的并发性能和事务处理能力。