MySQL默认隔离级别下，带FOR UPDATE的事务处理请求计数是否安全？

我正在开发一款应用的后端，需要限制每个用户每月调用外部API的次数，因此需要记录每个用户的请求次数。我有丰富的并发编程经验，但几乎没接触过数据库管理或MySQL。

原本我想写这样的伪代码逻辑（混合SQL与应用层逻辑，lookups是对应的数据表）：

mutex mtx;

set @userid = 'usrid1';
set @date = CURDATE();
set @month = CONCAT_WS('-', YEAR(@date), MONTH(@date));

mtx.lock()

select counter from lookups where userid=@userid and month=@month;

if returned rows == 0:
    insert into lookups set month=@month, userid=@userid, counter=1;
else:
    update lookups set counter=counter+1;

mtx.unlock()

但没办法用这个mutex。一开始我以为用事务包裹整个逻辑就行，但查MySQL文档后发现这可能没法避免竞态条件（比如两个线程/进程读到相同的counter值）。那在默认配置的MySQL（隔离级别为REPEATABLE READ）里，执行下面的逻辑是否可行？

set @userid = 'usrid1';
set @date = CURDATE();
set @month = CONCAT_WS('-', YEAR(@date), MONTH(@date));

start transaction;

select counter from lookups where userid=@userid and month=@month for update;

if returned rows == 0:
    insert into lookups set month=@month, userid=@userid, counter=1;
else:
    update lookups set counter=counter+1;

commit;

我推测这个方案可行，不会有竞态条件或死锁，但文档内容太复杂，想确认这个方案是否可靠，性能不是重点。

你的方案是可靠的，能有效避免竞态条件，具体原因如下：

• SELECT ... FOR UPDATE的排他锁作用：在REPEATABLE READ隔离级别下，这条语句会对查询到的行加排他锁（X锁），其他事务要修改这些行必须等待当前事务提交或回滚，彻底杜绝了多个线程同时读取相同counter值后各自更新的竞态问题。
• 空结果集的安全处理：如果查询无返回行，说明该用户当月无记录，此时执行INSERT需注意：必须给userid和month添加联合唯一索引，否则可能出现并发插入重复记录的情况。加了唯一索引后，即使多个事务同时尝试插入同一条记录，只有一个能成功，其余会触发唯一键冲突报错，应用层捕获后重试即可。
• 极低的死锁风险：这个逻辑里每个事务仅操作一行（或插入一行），锁的范围极小，只要业务逻辑中没有其他交叉锁操作，死锁概率几乎为0。

另外，还有更简洁的优化方案，可以把INSERT和UPDATE合并为一条原子语句，减少应用层分支判断：

INSERT INTO lookups (userid, month, counter)
VALUES (@userid, @month, 1)
ON DUPLICATE KEY UPDATE counter = counter + 1;

这条语句依赖userid和month的联合唯一索引，MySQL会原子性完成“存在则更新，不存在则插入”的操作，无需手动开启事务或加SELECT ... FOR UPDATE，代码更简洁，并发安全同样有保障。

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