基于SELECT条件插入的最佳事务隔离级别及锁优化方案

用于存储用户优惠提案推送历史的offer_proposals表结构与示例数据如下：

当前使用SERIALIZABLE隔离级别处理请求，事务逻辑为判断用户近30天是否收到过提案，无有效记录则插入新记录并返回true，否则返回false，具体执行SQL：

1. 查询用户最新提案记录：

select proposed_on from offer_proposals where user_id = 'aaa' order by proposed_on limit 1

2. 符合插入条件时执行写入：

insert into offer_proposals (user_id, proposed_on) values('aaa', now())

线上频繁出现锁获取异常、事务回滚问题，需要在不修改表设计的前提下优化性能，初步考虑更换为read_committed隔离级别，确认是否需要配套锁机制。

查询语句的explain(analyze, buffers)执行结果：

Buffers: shared hit=6
-> Sort (cost 8.3..8.31 rows=1 width=48) (actual time=0.195..0.196 rows=1 loops=1)
  Sort key proposed_on
  Sort method: quicksort memory: 25kB
  Buffers shared hit=6
  -> Index Scan using offer_proposal_user_id_idx on offer_proposal (cost=0.28..8.29 rows=1 width=48) (actual time=0.182..0.183 rows=1 loops=1)
     Index Cond (user_id=1234)
     Buffers shared hit=3
Planning time 0.816 ms
Execution time  0.238 ms

• 先修正基础逻辑bug：现有查询order by proposed_on limit 1未加desc，实际取到的是用户最早的提案记录，完全不符合「取最新记录判断30天有效期」的业务要求，必须调整为order by proposed_on desc limit 1。
• 隔离级别直接更换为READ COMMITTED，不需要用SERIALIZABLE这么重的级别。SERIALIZABLE会强制全事务串行校验，并发场景下极易触发序列化失败回滚，对于这个业务场景完全是性能浪费。
• 查询时加行级排他锁解决并发冲突：把查询语句调整为带FOR UPDATE的版本，查询时直接锁定对应用户的相关记录，阻止其他并发事务同时操作同一用户的判断逻辑，避免出现两个并发请求同时判定「无30天内记录」导致重复插入的问题。调整后的查询SQL：

select proposed_on 
from offer_proposals 
where user_id = 'aaa' 
order by proposed_on desc 
limit 1
for update;

这个锁仅针对同一user_id的记录生效，不同用户的请求完全不会互相阻塞，并发性能远高于SERIALIZABLE级别。如果查询时该用户无任何历史记录，数据库会自动在对应索引位置加间隙锁，阻止其他事务同时插入同一用户的新记录，不会出现并发写入漏洞。

• 优化索引收窄锁范围：当前仅存在user_id字段的单列索引，查询时需要额外做内存排序，建议把索引替换为(user_id, proposed_on desc)的联合索引，既可以直接通过索引有序性拿到最新记录、省去排序步骤提升查询速度，还能进一步缩小锁的范围，减少不必要的行锁定，提升并发能力。
• 应用层加简单重试兜底：即使做了以上优化，极端场景下仍可能出现锁等待超时的偶发异常，只需要在应用层对这类异常加2-3次的重试逻辑即可，不需要为了覆盖极低概率的异常提升事务隔离级别牺牲整体性能。

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