深入浅出 MariaDB FOR UPDATE：告别锁等待与死锁的技巧和示例

2025-12-12

在 MariaDB 中，SELECT...FOR UPDATE 语句是用于行级锁定（Row-Level\ Locking）的关键工具，它能确保在事务处理过程中，您查询到的数据不会被其他并发事务修改，从而维护数据的一致性。

不过，在使用它时，确实会遇到一些常见的问题，以及在不同场景下的替代方案。

当一个事务对某些行使用了 FOR UPDATE 语句，但另一个事务也尝试获取相同的锁时，第二个事务就会进入等待状态。如果等待时间超过了数据库设定的锁等待超时时间（例如，innodb_lock_wait_timeout），它就会收到一个错误。

与其让事务无限期地等待，不如明确告诉数据库，如果锁不可用，应该立即返回，或者只等待一段合理的时间。

立即返回（NOWAIT） 如果锁不可用，事务立即失败，不等待。

有限等待（WAIT N） 如果锁不可用，最多等待 N 秒。

示例代码（MariaDB 10.3.3 及更高版本支持）

-- 替代方案：使用 NOWAIT

-- 如果指定的行已经被其他事务锁定，此语句会立即失败并报错。

START TRANSACTION;

SELECT balance FROM accounts WHERE user_id = 101 FOR UPDATE NOWAIT;

-- ... 后续操作

COMMIT;

-- 替代方案：使用 WAIT N（例如，等待 5 秒）

-- 如果指定的行已经被其他事务锁定，此语句会等待最多 5 秒。

START TRANSACTION;

SELECT balance FROM accounts WHERE user_id = 102 FOR UPDATE WAIT 5;

-- ... 后续操作

COMMIT;

死锁是并发控制中最棘手的问题之一。它发生在两个或多个事务互相等待对方释放锁时。例如

事务 A 锁定了行 1。

事务 B 锁定了行 2。

事务 A 尝试锁定行 2（需要等待 B 释放）。

事务 B 尝试锁定行 1（需要等待 A 释放）。

结果是A 在等 B，B 在等 A，谁也动不了。

虽然 FOR UPDATE 本身不会引起死锁，但它是死锁发生的常见原因。解决死锁主要依靠预防和检测

预防（按相同顺序获取锁） 确保所有事务都以相同的顺序访问和锁定资源（行）。例如，如果涉及多行，始终按主键 ID 升序锁定。

MariaDB/InnoDB 自动检测 InnoDB 存储引擎具备死锁检测机制。一旦检测到死锁，它会选择其中一个事务（通常是工作量较小的那个）作为“牺牲品”并回滚它，让其他事务继续执行。

示例代码（预防死锁 - 推荐做法）

假设需要同时更新 user_id 101 和 user_id 102 的记录。

-- 不好的做法（可能导致死锁，取决于并发情况）：

-- 事务 A 锁定 101，然后尝试锁定 102。

-- 事务 B 锁定 102，然后尝试锁定 101。

-- 推荐的做法（按 ID 升序）：

START TRANSACTION;

-- 始终按 user_id 升序排列并锁定

SELECT balance FROM accounts WHERE user_id IN (101, 102)

ORDER BY user_id ASC

FOR UPDATE;

-- ... 处理业务逻辑

COMMIT;

FOR UPDATE 属于悲观锁（Pessimistic\ Locking），它在读取数据时就将数据锁住，保证了数据的绝对安全，但会降低系统的并发性能，因为其他事务必须等待锁释放。

对于高并发、低冲突的场景，可以考虑使用乐观锁（Optimistic\ Locking）作为替代。乐观锁不锁定数据，而是在更新时检查数据是否被修改过。通常通过在表中增加一个版本号（version）字段或时间戳（timestamp）字段来实现。

示例代码（基于版本号的乐观锁）

表结构 在 accounts 表中增加一个 version 字段，默认为 1。

操作步骤

读取时 读取数据和当前版本号。

更新时 检查数据库中的版本号是否与读取时的版本号一致。如果一致，则更新数据，并将版本号 +1。如果不一致，说明数据已被其他事务修改，本次更新失败，需要回滚或重试。

-- 步骤 1: 读取数据和版本号

-- 假设读取到 balance = 1000, version = 1

SELECT balance, version FROM accounts WHERE user_id = 201;

-- 步骤 2: 计算新的 balance (例如，-100)

SET @new_balance = 900;

SET @old_version = 1; -- 读取到的版本号

-- 步骤 3: 更新数据（核心步骤）

-- 只有当 version 字段仍然等于我们读取时的 @old_version 时，才执行更新！

UPDATE accounts

SET

balance = @new_balance,

version = version + 1 -- 版本号自增

WHERE

user_id = 201 AND version = @old_version;

-- 检查更新影响的行数：

-- 如果影响行数 = 1，更新成功。

-- 如果影响行数 = 0，说明在读取数据后，其他事务已经修改了该行数据，本次操作失败，需要回滚或提示用户重试。

使用乐观锁，您不再需要 FOR UPDATE 语句和长时间的事务，从而提高了系统的并发能力！