MariaDB事务行读锁问题：ISOLATION级别与race condition排查

嘿，这个竞态条件的坑我之前在处理多进程任务分发的时候踩过！本质问题在于你原来的事务里用的是普通SELECT，这属于快照读（在默认的REPEATABLE READ隔离级别下），不会对读取的行加锁，导致多个进程能同时读到同一条status=1的记录，最后都去更新它，自然就出现了重复获取的情况。

下面给你几个靠谱的解决方案，根据你的并发场景选就行：

1. 用SELECT ... FOR UPDATE加排他锁 #

这是最经典的解决方案，通过FOR UPDATE把普通查询改成当前读，会对选中的行加上排他锁，其他事务必须等当前事务提交/回滚后才能读取或修改这行数据，从根源上避免竞态。

修改后的事务伪代码：

START TRANSACTION;
-- 关键：用FOR UPDATE锁定符合条件的最旧行
SELECT id, status FROM your_table WHERE status = 1 ORDER BY created_at ASC LIMIT 1 FOR UPDATE;

-- 这里判断是否拿到了有效行（比如id不为空），如果有就执行更新
IF 拿到了行 THEN
    UPDATE your_table SET status = 2 WHERE id = [刚才查询到的id];
END IF;

COMMIT;

注意：如果你的表没有针对status和created_at建立索引，FOR UPDATE可能会锁全表，严重影响性能。建议创建复合索引：

CREATE INDEX idx_status_created ON your_table(status, created_at);

2. 用SELECT ... FOR SKIP LOCKED实现无阻塞并发（MariaDB 10.6+支持） #

如果你的并发量很高，不想让进程因为等待锁而阻塞，可以用FOR SKIP LOCKED。这个语法会直接跳过已经被其他事务锁定的行，返回下一个符合条件的未锁定行，多个进程可以同时处理不同的记录，效率更高。

伪代码示例：

START TRANSACTION;
-- 跳过已锁定的行，直接取可用的最旧记录
SELECT id, status FROM your_table WHERE status = 1 ORDER BY created_at ASC LIMIT 1 FOR SKIP LOCKED;

IF 拿到了行 THEN
    UPDATE your_table SET status = 2 WHERE id = [查询到的id];
END IF;

COMMIT;

这个方案适合高并发的任务分发场景，不会出现进程排队等待的情况。

3. 合并读和更新为原子操作：UPDATE ... LIMIT #

如果你的业务不需要先读取记录内容再做判断，直接把“查找最旧行+更新状态”合并成一个UPDATE语句更简单，因为UPDATE本身是原子操作，数据库会自动锁定目标行，避免竞态。

示例代码：

-- 原子性更新：找到status=1的最旧行，直接改成2
UPDATE your_table SET status = 2 WHERE status = 1 ORDER BY created_at ASC LIMIT 1;

-- 如果需要获取刚才更新的记录信息，可以用ROW_COUNT()判断是否更新成功，再查询
IF ROW_COUNT() > 0 THEN
    SELECT id, status FROM your_table WHERE status = 2 ORDER BY updated_at DESC LIMIT 1;
END IF;

这个方案不需要显式开启事务（当然也可以放在事务里），代码更简洁，原子性也有保障。

最后再提个关键点 #

不管用哪种方案，都要确保你的隔离级别不是READ UNCOMMITTED（虽然一般不会用这个），另外，一定要测试并发场景，比如用多个进程同时执行，验证是否还会出现重复获取的问题。

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