mysql中使用事务与锁定机制优化查询效率

事务不提升查询效率，反而可能因锁竞争和长事务拖慢性能；真正影响查询效率的是索引、执行计划与是否全表扫描。

事务本身不提升查询效率，反而可能降低

MySQL 中开启 START TRANSACTION 并不会让 SELECT 变快。事务的核心作用是保证数据一致性（ACID），不是加速读操作。相反，长事务会持有锁、阻塞其他会话、拖慢整体吞吐，尤其在高并发写场景下。

真正影响查询效率的是：索引是否合理、执行计划是否走索引、是否避免全表扫描——这些和事务无关。但如果你在事务中执行了大量未加索引的 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE，反而会因锁竞争导致查询排队、响应延迟升高。

什么时候该用 SELECT ... FOR UPDATE？

仅当你要“读取后立刻更新”，且必须防止其他事务在这之间修改同一行时才需要。典型场景如扣减库存、抢购、账户余额变更。

• 必须在 BEGIN 和 COMMIT 之间使用，否则锁会在语句结束立即释放（InnoDB 行级锁只在事务内持续）
• 必须有可用索引支撑，否则会升级为表锁——例如 SELECT * FROM order WHERE user_id = 123 FOR UPDATE，若 user_id 无索引，InnoDB 将锁定整张表
• 避免在事务中混合读写逻辑：先查再判断再更新，不如直接用带条件的 UPDATE 一行解决，减少锁持有时间

UPDATE inventory SET stock = stock - 1 WHERE id = 1001 AND stock >= 1;

这条语句自带原子性，成功返回影响行数为 1 即表示扣减成功，无需先 SELECT ... FOR UPDATE 再 UPDATE。

READ COMMITTED 与 REPEATABLE READ 对查询性能的影响

InnoDB 默认隔离级别是 REPEATABLE READ，它通过多版本并发控制（MVCC）实现一致性读，但会为事务中首次 SELECT 创建一个快照，后续相同查询都复用该快照。这在长事务中可能导致读到明显过期的数据，同时占用更多 undo log 空间。

READ COMMITTED 每次 SELECT 都读取最新已提交版本，MVCC 快照更轻量，undo log 回收更快，对高并发只读+短事务场景更友好。

• 如果业务能接受每次读都是最新提交结果（比如报表类查询），显式设置 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
• 不要全局改 transaction_isolation，除非确认所有应用逻辑兼容——某些依赖可重复读的应用（如基于两次读差值做判断）会出错
• 注意：即使在 READ COMMITTED 下，SELECT ... FOR UPDATE 仍会加当前读锁，不受隔离级别影响

锁等待超时与死锁是性能恶化的关键信号

当看到 Lock wait timeout exceeded; try restarting transaction 或 Deadlock found when trying to get lock，说明锁已成瓶颈，此时优化方向不是调事务，而是收缩锁范围：

• 确保 WHERE 条件走索引，避免锁住不该锁的行（甚至整表）
• 按固定顺序访问多张表或多个主键，降低死锁概率（例如总是先更新 user 再更新 order）
• 把事务粒度拆小：不要在一个事务里处理 100 个订单，改为批量 10 个一组提交
• 监控 INFORMATION_SCHEMA.INNODB_TRX 和 INNODB_LOCK_WAITS，定位长时间持有锁的事务

锁问题往往藏在看似简单的查询背后，比如一个没走索引的 UPDATE 会意外锁住几千行，进而让下游所有依赖这些行的 SELECT ... FOR UPDATE 全部卡住。