MySQL 8.4.0 查询锁常见问题：如何解决高并发场景下的查询锁竞争导致的性能瓶颈？

Question

**问题：如何在MySQL 8.4.0高并发场景下减少查询锁竞争？** 在高并发场景中，MySQL 8.4.0的查询锁竞争可能导致性能瓶颈。例如，频繁的SELECT ... FOR UPDATE操作会阻塞其他事务的读写请求，导致队列等待时间增加。此外，全局锁（如FLUSH TABLES WITH READ LOCK）或元数据锁（MDL）也可能加剧问题。 为解决此问题，可采用以下策略：1) 使用覆盖索引优化查询，减少行锁范围；2) 将长事务拆分为多个短事务，降低锁持有时间；3) 利用MVCC特性，尽量使用一致性非锁定读；4) 调整innodb_lock_wait_timeout参数，优化锁等待逻辑；5) 在合适场景下，用乐观锁替代悲观锁。通过以上方法，可有效缓解查询锁竞争，提升系统并发性能。

Answer 1

如何在MySQL 8.4.0高并发场景下减少查询锁竞争

1. 理解问题背景

在高并发场景中，MySQL 8.4.0的查询锁竞争可能导致性能瓶颈。例如，频繁的SELECT ... FOR UPDATE操作会阻塞其他事务的读写请求，导致队列等待时间增加。此外，全局锁（如FLUSH TABLES WITH READ LOCK）或元数据锁（MDL）也可能加剧问题。

• SELECT ... FOR UPDATE：该语句会在行上加排他锁，阻止其他事务对同一行进行修改。
• 全局锁：通过FLUSH TABLES WITH READ LOCK命令，会对整个数据库加只读锁。
• 元数据锁（MDL）：在执行DDL或DML时，可能会持有较长时间的锁，影响其他查询。

理解这些锁机制是优化的基础，接下来我们将探讨具体的优化策略。

2. 使用覆盖索引优化查询

覆盖索引是指查询所需的字段全部包含在索引中，避免回表操作，从而减少行锁范围。

场景	优化前SQL	优化后SQL
订单查询	SELECT id, amount FROM orders WHERE status = 'processed';	在(status, amount)上创建复合索引
用户信息查询	SELECT name, email FROM users WHERE active = 1;	在(active, name, email)上创建覆盖索引

通过合理设计覆盖索引，可以显著减少锁的竞争范围。

3. 将长事务拆分为多个短事务

长事务会持有锁较长时间，容易引发锁竞争。将事务拆分为多个短事务，能够有效降低锁持有时间。

# 长事务示例
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 123;
INSERT INTO order_details (order_id, product_id, quantity) VALUES (456, 123, 1);
COMMIT;

# 拆分后的短事务
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 123;
COMMIT;

BEGIN;
INSERT INTO order_details (order_id, product_id, quantity) VALUES (456, 123, 1);
COMMIT;

这种拆分方式减少了事务持有锁的时间，降低了锁冲突的可能性。

4. 利用MVCC特性，尽量使用一致性非锁定读

MVCC（多版本并发控制）允许事务读取数据的快照版本，而不需要加锁。通过设置合适的隔离级别（如READ COMMITTED），可以减少锁的使用。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM products WHERE category = 'electronics';

一致性非锁定读避免了因读操作引发的锁竞争，适合于以读为主的场景。

5. 调整innodb_lock_wait_timeout参数

通过调整innodb_lock_wait_timeout参数，可以优化锁等待逻辑，减少死锁和超时的发生。

# 默认值为50秒，可根据业务需求调整
SET GLOBAL innodb_lock_wait_timeout = 10;

缩短超时时间有助于快速释放资源，但需要结合业务特点权衡。

6. 在合适场景下，用乐观锁替代悲观锁

悲观锁（如SELECT ... FOR UPDATE）适合高并发写场景，而乐观锁则适用于读多写少的场景。通过版本号或时间戳实现乐观锁，可以减少锁竞争。

# 乐观锁示例
UPDATE products SET stock = stock - 1, version = version + 1 WHERE product_id = 123 AND version = 1;

如果更新失败（即没有受影响的行），则重试或提示用户。

7. 流程图总结

以下是优化锁竞争的整体流程：

graph TD;
    A[分析锁竞争原因] --> B[使用覆盖索引];
    B --> C[拆分长事务];
    C --> D[利用MVCC];
    D --> E[调整超时参数];
    E --> F[采用乐观锁];