如何在MariaDB Galera集群中实现行级排他锁确保流程唯一执行？

针对你在Galera集群下遇到的SELECT ... FOR UPDATE乐观锁导致流程重复执行的问题，以下是几种可靠的修改方案，确保同一流程仅被执行一次：

方案一：原子UPDATE抢占锁（推荐） #

利用MariaDB UPDATE语句的原子性和行级排他锁特性，直接在更新操作中完成锁抢占与状态判断，从根源避免并发冲突：

1. 去掉原有的SELECT ... FOR UPDATE步骤，直接执行带条件的UPDATE：

// 开启事务（若后续有其他数据库操作则保留）
$pdo->beginTransaction();

// 执行原子更新，仅当processed为0时修改为1
$stmt = $pdo->prepare("UPDATE mytable SET processed = 1 WHERE id = ? AND processed = 0");
$stmt->execute([$resourceId]);

// 获取受影响行数
$affectedRows = $stmt->rowCount();

if ($affectedRows === 1) {
    // 成功抢占锁，执行目标流程
    executeCriticalProcess();
    $pdo->commit();
} else {
    // 资源已被处理，跳过流程
    $pdo->rollBack();
}

原理：Galera集群中，UPDATE语句会立即对目标行施加排他锁，并发请求会被阻塞直到锁释放。只有第一个成功执行UPDATE的事务会得到affectedRows=1，后续请求会因processed=0条件不满足而返回0，从根本上避免了重复执行流程的问题。

方案二：调整事务隔离级别配合SELECT ... FOR UPDATE #

将事务隔离级别从READ COMMITTED改为REPEATABLE READ（Galera默认隔离级别），并保留原有的锁查询逻辑：

1. 修改事务隔离级别：

$pdo->exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ");

2. 保留原有的事务流程，此时SELECT ... FOR UPDATE会施加阻塞式排他锁：

$pdo->beginTransaction();

$stmt = $pdo->prepare("SELECT processed FROM mytable WHERE id = ? FOR UPDATE");
$stmt->execute([$resourceId]);
$row = $stmt->fetch();

if ($row['processed'] === 0) {
    executeCriticalProcess();
    $pdo->exec("UPDATE mytable SET processed = 1 WHERE id = ?", [$resourceId]);
    $pdo->commit();
} else {
    $pdo->rollBack();
}

原理：在REPEATABLE READ隔离级别下，Galera的SELECT ... FOR UPDATE会获取阻塞式行锁，并发请求会被阻塞直到第一个事务提交或回滚，确保只有一个事务能读到processed=0并执行流程。

方案三：使用数据库级分布式锁（GET_LOCK） #

利用MariaDB的GET_LOCK()函数实现分布式锁，与业务数据解耦：

1. 流程逻辑：

$lockName = "resource_lock_" . $resourceId;
// 尝试获取锁，超时时间10秒（可根据需求调整）
$stmt = $pdo->prepare("SELECT GET_LOCK(?, 10)");
$stmt->execute([$lockName]);
$lockAcquired = $stmt->fetchColumn();

if ($lockAcquired === 1) {
    // 拿到锁后，再次检查状态（避免锁等待期间资源已被处理）
    $stmt = $pdo->prepare("SELECT processed FROM mytable WHERE id = ?");
    $stmt->execute([$resourceId]);
    $row = $stmt->fetch();

    if ($row['processed'] === 0) {
        executeCriticalProcess();
        $pdo->exec("UPDATE mytable SET processed = 1 WHERE id = ?", [$resourceId]);
    }

    // 释放锁
    $pdo->exec("SELECT RELEASE_LOCK('$lockName')");
} else {
    // 未获取到锁，跳过流程
}

注意：GET_LOCK()是会话级锁，若进程意外崩溃，会话结束后锁会自动释放，但需注意锁超时时间设置，避免死锁。该方案适合不需要直接修改业务数据的锁场景，但需额外处理锁与数据状态的一致性。

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