讲透mysql锁退化和意向锁竞争

damncode
206 阅读6分钟

mysql锁退化

在 MySQL 中，锁退化指的是当查询无法利用索引来精确定位记录时，MySQL 将不得不锁定更多的行，甚至可能退化为锁定整个表。这种情况通常发生在以下几种场景中：

1. 没有索引或索引未生效

当查询中没有使用索引，或者 MySQL 优化器没有选择使用可用的索引时，MySQL 可能不得不对整个表进行扫描（全表扫描，即 TABLE SCAN），从而导致锁的粒度变得非常大，甚至可能退化为表锁。

示例：

UPDATE my_table SET column1 = 'value' WHERE column2 = 'value';

如果 column2 没有索引，MySQL 可能会扫描整个表，导致锁定所有行。这种情况下，行锁可能退化为表锁，影响整个表的并发操作。

2. 范围查询导致锁定更多行

即使查询使用了索引，如果查询条件是一个范围查询，MySQL 可能会锁定的行比预期的更多。这种行为在 InnoDB 存储引擎的默认隔离级别 REPEATABLE READ 下尤其明显，因为 InnoDB 会使用**Next-Key Lock（间隙锁和行锁的组合）**来避免幻读。

示例：

SELECT * FROM my_table WHERE column1 > 100 FOR UPDATE;

即使 column1 上有索引，MySQL 可能不仅会锁定匹配条件的行，还会锁定索引范围内的其他行，甚至包括那些超出查询范围的行和“间隙”，从而导致锁的范围扩大。

3. 锁升级

锁升级是指当事务持有大量行锁时，数据库系统为了减少管理大量行锁的开销，可能会将行锁升级为表锁。在 MySQL 中，虽然 InnoDB 默认是使用行级锁的，但在某些极端情况下，InnoDB 也可能会将行级锁升级为表锁，尤其是在没有索引的情况下。

4. 死锁检测与锁重试

在并发环境下，如果两个事务相互等待对方释放锁，可能会导致死锁。当 MySQL 监测到死锁时，会主动回滚其中一个事务，以解除死锁状态。在这种情况下，事务可能会重新尝试获取锁。在某些情况下，如果事务无法精准获取所需的行锁，MySQL 可能会选择退化锁定更多的行或表来避免再次进入死锁状态，从而影响并发性能。

如何避免锁退化？

  1. 确保查询使用索引：

    • 确保查询条件中的列有合适的索引，避免全表扫描。
    • 使用 EXPLAIN 分析查询的执行计划，检查索引是否被正确使用。

  2. 优化范围查询：

    • 尽量避免大范围的查询（如 >、<、BETWEEN 等），因为这些查询可能会锁定比预期更多的行。
    • 使用更精确的查询条件，缩小锁的范围。

  3. 减少长事务：

    • 长事务会持有锁的时间更长，增加锁竞争的机会。确保每个事务尽量简短且高效。

  4. 合理设计表结构和索引：

    • 索引的设计需要与查询模式匹配，确保常用的查询能够通过合适的索引快速定位到具体的行。
    • 定期检查和优化索引，避免不必要的全表扫描。

  5. 监控和调优：

    • 使用 MySQL 提供的工具（如 SHOW ENGINE INNODB STATUS）来监控锁的使用情况，尤其是长时间持有锁的事务。
    • 对出现锁退化的查询进行优化，必要时可以通过分区表、分表等手段减少锁的影响范围。

意向锁互斥问题

意向锁（Intention Lock)本身并不与其他事务的意向锁互斥，但它会与某些锁类型（例如表级别的锁）发生互斥。意向锁的主要目的是用来表明一个事务准备获取更细粒度的锁（如行锁），从而与表锁进行协调。下面是详细解释：

什么是意向锁？

意向锁是 InnoDB 存储引擎中用于实现多粒度锁的一种机制。它们的存在是为了帮助管理表级别的锁与行级别的锁之间的冲突。意向锁本质上是一种表级别的锁，但它的作用是声明事务打算在某些行上获取行锁。

意向锁主要有以下两种：

  1. 意向共享锁（Intention Shared, IS）：
    • 表示事务打算在表中的某些行上获取共享锁（S 锁）。
  2. 意向排他锁（Intention Exclusive, IX）：
    • 表示事务打算在表中的某些行上获取排他锁（X 锁）。

意向锁的特性：

  1. 意向锁与意向锁之间不互斥：

    • 例如，多个事务可以同时在同一张表上申请“意向共享锁（IS）”或“意向排他锁（IX）”。
    • 意向锁之间不会互相阻塞。

  2. 意向锁与表级别的锁互斥：

    • 意向锁与某些表级别的锁是互斥的，例如：
      • 如果一个事务想要获取某个表的表级别的共享锁（S 锁），那么它会阻塞其他事务对该表的意向排他锁（IX）。
      • 同样地，如果一个事务想要获取某个表的排他锁（X 锁），则其他事务的任何意向锁（IS 或 IX）都会被阻塞。

意向锁的互斥规则：

意向锁的互斥规则可以通过以下的锁兼容矩阵来理解：

表共享锁 (S)表排他锁 (X)意向共享锁 (IS)意向排他锁 (IX)
表共享锁 (S)兼容互斥兼容互斥
表排他锁 (X)互斥互斥互斥互斥
意向共享锁 (IS)兼容互斥兼容兼容
意向排他锁 (IX)互斥互斥兼容兼容

意向锁的作用：

意向锁的主要作用是提升性能并减少锁冲突，特别是在大并发的情况下，表级锁和行级锁一起使用时，意向锁可以帮助 MySQL 更快地判断是否存在潜在的锁冲突，而不需要检查每一行的锁状态。

举个例子：

  • 假设事务 A 在一个表上获取了意向排他锁（IX 锁），然后它在该表的某些行上获取行级别的排他锁（X 锁）。
  • 同时，事务 B 想要获取该表的表级别的共享锁（S 锁）。在这种情况下，事务 B 会被阻塞，因为事务 A 已经声明自己在这个表上打算获取排他锁（通过 IX 锁），这与 S 锁互斥。

总结：

  • 意向锁之间不互斥，即多个事务可以同时在同一张表上获取意向共享锁（IS）或意向排他锁（IX）。
  • 意向锁与表级别的锁（S 锁，X 锁）之间可能发生互斥。
avatar
文章
阅读
粉丝