InnoDB行锁和事务隔离级别下的MySQL查询锁定分析

lfsun666
80 阅读3分钟

InnoDB 行锁特点

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的。
  • 仅当通过索引条件检索数据时，InnoDB 才使用行级锁，否则将使用表锁。

事务隔离级别为可重复读（RR）和串行化（Serializable）

  • 条件列非索引：

    1. SELECT * FROM table WHERE stock = 100: 在聚簇索引上加 S 锁，同时在索引的所有间隙上加 gap lock。
    2. SELECT * FROM table WHERE stock > 100: 同上。
    3. SELECT * FROM table WHERE stock = 100 LOCK IN SHARE MODE: 同上。
    4. SELECT * FROM table WHERE stock > 100 LOCK IN SHARE MODE: 同上。
    5. SELECT * FROM table WHERE stock = 100 FOR UPDATE: 在聚簇索引上加 X 锁，同时在索引的所有间隙上加 gap lock。
    6. SELECT * FROM table WHERE stock > 100 FOR UPDATE: 同上。

  • 条件列是聚簇索引：

    1. SELECT * FROM table WHERE product_id = 1: 在聚簇索引上加 S 锁。
    2. SELECT * FROM table WHERE product_id > 1: 在聚簇索引上加 S 锁，同时在索引的间隙上加 gap lock。
    3. SELECT * FROM table WHERE product_id = 1 LOCK IN SHARE MODE: 在聚簇索引上加 S 锁。
    4. SELECT * FROM table WHERE product_id > 1 LOCK IN SHARE MODE: 在聚簇索引上加 S 锁，同时在索引的间隙上加 gap lock。
    5. SELECT * FROM table WHERE product_id = 3 FOR UPDATE: 在聚簇索引上加 X 锁。
    6. SELECT * FROM table WHERE product_id > 3 FOR UPDATE: 在聚簇索引上加 X 锁，同时在索引的间隙上加 gap lock。
    7. SELECT * FROM table WHERE product_id = 999 [LOCK IN SHARE MODE | FOR UPDATE]: 不存在的列，在间隙上加 gap lock。
    8. SELECT * FROM table WHERE product_id > 1000 [LOCK IN SHARE MODE | FOR UPDATE]: 查询结果为空，在间隙上加 gap lock。

  • 条件列是非聚簇索引：

    1. SELECT * FROM table WHERE price = 100: 在聚簇索引上加 S 锁，同时在非聚簇索引的间隙上加 gap lock。
    2. SELECT * FROM table WHERE price > 100: 在聚簇索引上加 S 锁，同时在非聚簇索引的间隙上加 gap lock。
    3. SELECT * FROM table WHERE price = 100 LOCK IN SHARE MODE: 在聚簇索引上加 S 锁，同时在非聚簇索引的间隙上加 gap lock。
    4. SELECT * FROM table WHERE price > 100 LOCK IN SHARE MODE: 在聚簇索引上加 S 锁，同时在非聚簇索引的间隙上加 gap lock。
    5. SELECT * FROM table WHERE price = 100 FOR UPDATE: 在聚簇索引上加 X 锁，同时在非聚簇索引的间隙上加 gap lock。
    6. SELECT * FROM table WHERE price > 100 FOR UPDATE: 在聚簇索引上加 X 锁，同时在非聚簇索引的间隙上加 gap lock。
    7. SELECT * FROM table WHERE price = 999 [LOCK IN SHARE MODE | FOR UPDATE]: 不存在的列，在间隙上加 gap lock。
    8. SELECT * FROM table WHERE price > 1000 [LOCK IN SHARE MODE | FOR UPDATE]: 查询结果为空，在间隙上加 gap lock。

总结

  • 文中对于不同的查询条件，使用不同的锁定方式，涉及到 S 锁、X 锁、以及 gap lock。
  • 文章提到了在不同的事务隔离级别下的行为，以及使用 LOCK IN SHARE MODE 和 FOR UPDATE 的情况。
avatar
文章
阅读
粉丝