mysql的行级锁与死锁的产生与处理方法

2次阅读

MySQL 行级锁仅在 InnoDB 引擎且 WHERE 条件命中索引（主键 / 唯一 / 普通索引）时生效；否则退化为表级锁或锁住全部索引记录及间隙，导致并发下降。

mysql 的行级锁与死锁的产生与处理方法

行级锁只在 InnoDB 引擎下有效，且必须走索引（主键或唯一 / 普通索引）才能锁定具体行；全表扫描或未命中索引时会退化为表级锁（或锁住所有索引记录，甚至间隙），导致并发性能骤降。

使用 SELECT …… FOR UPDATE 或 UPDATE/DELETE 语句时，若 WHERE 条件无索引，InnoDB 可能对聚簇索引的每条记录加锁，等效于锁表
即使有索引，若查询条件是范围（如 id > 100），InnoDB 会加间隙锁（Gap Lock）或临键锁（Next-Key Lock），锁住不存在的“间隙”，防止幻读——这也常被误认为“锁了不该锁的行”
注意 UPDATE t SET x=1 WHERE y=2：如果 y 列无索引，该语句可能锁住整张表的聚簇索引所有行

MySQL 通过 waits-for graph（等待图）实时检测循环等待关系。一旦发现两个及以上事务互相持有对方需要的锁、且都在等待对方释放，就立即选一个事务作为牺牲者（victim），回滚它并抛出错误：Deadlock found when trying to get lock; try restarting transaction。

被选为牺牲者的事务不一定是执行时间长的那个，而是代价最小的（例如修改行数少、undo log 小）
死锁检测本身有开销，默认开启（innodb_deadlock_detect=ON），高并发更新场景可考虑关闭，但需配合应用层重试逻辑
死锁日志不会自动写入 error log，需手动执行 SHOW ENGINE INNODB STATUSG 查看最近一次死锁详情，重点关注 TRANSACTIONS 部分中的 WAITING FOR THIS LOCK TO BE GRANTED 和 HOLDS THE LOCK(S)

死锁无法完全杜绝，但可通过约束访问模式大幅降低概率。核心是让多个事务以相同顺序获取锁。

按主键顺序更新 ：统一先查再按 ORDER BY primary_key 排序后批量更新，避免 A 更新 (1,3)、B 更新 (3,1) 这类交叉
缩小事务粒度 ：把大事务拆成多个小事务，减少锁持有时间；尤其避免在事务中做 RPC、文件读写、sleep 等外部耗时操作
一致的索引访问路径 ：确保不同业务模块更新同一张表时，WHERE 条件始终走同一个索引（例如都用 user_id 而非有时用 email 有时用 phone）
显式加锁 + 超时控制 ：用 SELECT …… FOR UPDATE WAIT 5（MySQL 8.0.1+）代替无超时的阻塞等待，避免长时间挂起

不能简单捕获异常后无脑重试，否则可能无限循环触发死锁；必须结合业务语义判断是否可重试，并限制次数。

try:     with connection.cursor() as cursor:         cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = %s", [uid])         cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = %s", [target_uid])         connection.commit() except pymysql.err.IntegrityError as e:     if "Deadlock found" in str(e):         # 记录日志，等待随机抖动后重试（避免重试洪峰）time.sleep(random.uniform(0.01, 0.1))         retry_count += 1         if retry_count <= 3:             continue         else:             raise  # 放弃重试，交由上层处理     else:         raise

注意：重试前最好重新 SELECT 当前值，确认业务状态未被其他事务改变（尤其是余额、库存等敏感字段），否则可能出现超扣或重复加款。

死锁最隐蔽的来源往往不是 SQL 写法，而是事务边界不清、ORM 自动生成的 N+1 查询、或者触发器隐式开启新子事务——这些地方最容易漏掉锁分析。

发表于：数据库

近一天内

# continue