迁移至MariaDB Galera集群后，为何事务触发死锁而非等待？

场景与问题 #

基础表结构 #

create table rooms ( id int not null, owner int null, PRIMARY KEY (`id`));
insert into rooms values (1, null);

核心需求：并发操作时仅当owner为NULL时设置所有者ID，避免覆盖已存在的所有者信息。

单节点数据库（预期正常行为） #

• 事务TRX1（服务器A）：

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  START TRANSACTION;
  SELECT * FROM rooms WHERE id=1 FOR UPDATE;
  

• 事务TRX2（服务器A）：

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  START TRANSACTION;
  SELECT * FROM rooms WHERE id=1 FOR UPDATE;
  

  -> TRX2进入等待状态，直到TRX1提交或回滚。
• TRX1完成更新并提交：

  UPDATE rooms SET owner=1 WHERE id=1;
  COMMIT;
  

  -> TRX2被释放，能获取到TRX1更新后的最新数据，并发处理符合预期。

MariaDB Galera集群（异常行为） #

• 事务TRX1（服务器A）：

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  START TRANSACTION;
  SELECT * FROM rooms WHERE id=1 FOR UPDATE;
  

• 事务TRX2（服务器B）：

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  START TRANSACTION;
  SELECT * FROM rooms WHERE id=1 FOR UPDATE;
  

  -> TRX2未进入等待状态，直接获取本地行锁。
• TRX1提交更新后，TRX2执行更新并提交：

  -- TRX1操作
  UPDATE rooms SET owner=1 WHERE id=1;
  COMMIT;
  
  -- TRX2操作
  UPDATE rooms SET owner=2 WHERE id=1;
  COMMIT;
  

  -> TRX2因死锁错误执行失败。

可行解决方案 #

1. 改用原子更新语句（推荐） #

放弃前置的SELECT ... FOR UPDATE，直接使用带条件的原子UPDATE操作，利用数据库本身的原子性保证只有当owner为NULL时才执行更新：

START TRANSACTION;
UPDATE rooms SET owner=? WHERE id=1 AND owner IS NULL;
-- 检查SQL执行后的受影响行数：
-- 若为1，说明更新成功；若为0，说明已被其他事务抢占，应用层可提示用户或重试
COMMIT;

这种方式无需依赖跨节点的行锁同步，从根本上避免锁竞争导致的死锁，是集群环境下最可靠的方案。

2. 启用wsrep_sync_wait参数同步锁状态 #

通过设置会话级或全局级的wsrep_sync_wait参数，让事务在执行前等待集群同步最新的锁状态，确保本地锁能感知其他节点的锁情况：

-- 会话级设置，仅对当前事务生效
SET SESSION wsrep_sync_wait = 1;
START TRANSACTION;
SELECT * FROM rooms WHERE id=1 FOR UPDATE;

注意：该参数会增加事务等待时间，带来一定性能开销，需根据业务并发量权衡使用。

3. 使用FOR UPDATE NOWAIT/SKIP LOCKED控制锁行为 #

利用MariaDB的锁选项，让无法获取锁的事务立即返回，避免后续死锁：

• NOWAIT：无法获取锁时直接抛出错误，应用层捕获后可重试

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  START TRANSACTION;
  SELECT * FROM rooms WHERE id=1 FOR UPDATE NOWAIT;
  

• SKIP LOCKED：直接跳过被锁定的行，返回空结果，应用层可提示用户稍后操作

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  START TRANSACTION;
  SELECT * FROM rooms WHERE id=1 FOR UPDATE SKIP LOCKED;
  

4. 应用层实现分布式锁 #

如果数据库层面的方案无法满足需求，可在应用层引入分布式锁（如基于Redis的锁），操作房间数据前先获取锁，只有拿到锁的事务才能执行更新，从业务层面保证跨节点的互斥性。

内容的提问来源于stack exchange，提问作者Tobia