mysql中SQL优化中的事务与锁的平衡

应按业务场景选隔离级别：读多写少可选READ COMMITTED减少间隙锁，强一致性需REPEATABLE READ但必须走唯一索引；非DB操作须移出事务；批量更新务必加索引或主键分片。

事务隔离级别怎么选才不拖慢查询

MySQL 默认的 REPEATABLE READ 隔离级

别在多数业务场景下够用，但容易因间隙锁（gap lock）导致不必要的锁等待，尤其在 WHERE 条件未命中索引或范围查询时。比如执行 UPDATE users SET status = 1 WHERE created_at > '2025-01-01'，若 created_at 没有索引，InnoDB 会锁住整个聚簇索引，阻塞其他写操作。

实操建议：

• 读多写少且能接受幻读的后台报表类查询，可临时切到 READ COMMITTED：它禁用间隙锁，只锁匹配行，大幅减少锁冲突
• 高并发订单扣减等强一致性场景，保留 REPEATABLE READ，但必须确保 WHERE 条件走**唯一索引或主键**，避免升级为范围锁
• 绝对不要在生产环境用 SET SESSION TRANSACTION ISOLATION LEVEL ... 动态改级别——连接池复用下极易引发不可预期的行为

显式事务里哪些操作会意外延长锁持有时间

锁不是在 COMMIT 才释放，而是在语句执行完、下一条语句开始前就可能释放（取决于隔离级别和语句类型）。但开发者常忽略：事务中夹杂网络 I/O、循环计算、或调用外部 API，会让锁实际持有时间远超 SQL 执行本身。

常见错误现象：

• 事务内先 SELECT ... FOR UPDATE 锁一行，接着花 200ms 调微信支付接口，这 200ms 内该行一直被锁
• INSERT INTO log_table 日志写入放在事务末尾，但日志表没主键/没索引，导致 INSERT 变慢，连带前面所有锁延迟释放

实操建议：

• 把非数据库操作（如发消息、计算、调第三方）全部移出事务块，只保留在必要数据一致性边界内
• 事务内避免大结果集 SELECT，尤其是 SELECT ... FOR UPDATE 后还做内存遍历 —— 锁会持续到事务结束
• 用 SHOW ENGINE INNODB STATUS\G 查看 TRANSACTIONS 部分，重点关注 lock struct(s) 数量和 trx_wait_started 时间戳

死锁日志里怎么看谁该被回滚

MySQL 死锁检测后会选一个事务做 ROLLBACK，选择依据是“代价最小”：通常是修改行数少、事务已执行时间短的那个。但这个“代价”不透明，仅从 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 区域能看出线索。

关键字段解读：

• *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 表示事务 1 在等什么锁（例如等 PRIMARY 上某行的 X 锁）
• *** (2) HOLDS THE LOCK(S): 表示事务 2 当前持有哪些锁（注意它可能持有多把锁，其中一把恰好是事务 1 需要的）
• 最后一行 WE ROLL BACK TRANSACTION (1) 或 (2) 是 MySQL 的决策结果

实操建议：

• 如果发现总是同一个业务逻辑（如 pay_order）被回滚，说明它的事务粒度太粗或锁顺序不一致 —— 检查是否在不同接口中，对 order 和 user_wallet 表加锁顺序相反
• 避免在事务中用 SELECT ... FOR UPDATE 锁无关数据，比如为防超卖锁了商品行，却顺手查了用户地址（不加锁也行），这种冗余操作会扩大锁范围

批量更新时怎么避免锁表又保证一致性

直接 UPDATE t SET status = 2 WHERE status = 1 LIMIT 1000 看似安全，但如果 status 字段没有索引，InnoDB 仍要扫描全表，过程中会对所有扫到的行加 意向锁，并发高时等于半锁表。

实操建议：

• 给 WHERE 条件字段建索引，哪怕只是 INDEX(status) —— 这是批量更新不锁表的底线
• 用主键分片代替 LIMIT：比如先查出一批 id IN (1001,1002,...)，再按这些 id 更新，确保每次只锁确定的几行
• 对超大表（千万级+），考虑用 pt-archiver 工具做低影响批量更新，它内部自动按主键分块、控制休眠间隔、避免长事务

锁和事务从来不是非此即彼的权衡，而是根据具体语句的执行路径、索引覆盖、并发模式去抠每一毫秒的锁持有逻辑。最常被忽略的是：你以为的“小事务”，可能因为一个没走索引的 WHERE 或一次多余的 SELECT，变成锁瓶颈的源头。