PostgreSQL中SELECT FOR UPDATE查询含义及receipt_id年度重置方案咨询

嘿，我来给你详细拆解这两个问题，帮你彻底搞清楚～

一、SELECT col1, col2 FROM table WHERE col1=123 FOR UPDATE; 到底是什么意思？ #

这条语句是PostgreSQL里的行级排他锁查询，核心是在查询数据的同时，给符合col1=123条件的行加上排他锁（Exclusive Lock），具体细节如下：

• 锁的作用：加锁之后，其他事务没办法对这些行做UPDATE、DELETE操作，也不能给这些行再加FOR UPDATE/FOR NO KEY UPDATE这类排他锁，直到当前事务提交（COMMIT）或者回滚（ROLLBACK）。不过要注意，其他事务还是能正常读取这些行的快照数据（PostgreSQL默认的读已提交隔离级别下），不会被完全阻塞。
• 适用场景：它主要用来解决并发修改时的一致性问题。比如你要先查某行数据，再基于这个结果修改它——用FOR UPDATE就能避免在“查询→修改”的间隙里，其他事务偷偷改了这行数据，导致你基于旧数据做出错误的修改。举个例子，电商扣库存时，先查库存数（加锁）再扣减，就能防止超卖。
• 小细节：如果你的查询没匹配到任何行，那不会加任何锁；而且锁是行级的，不会锁整个表，所以对性能的影响相对可控。

二、按年重置receipt_id的方案：当前做法有坑，怎么优化？ #

当前方案的问题 #

你现在用SELECT MAX(receipt_id) FROM table WHERE receipt_year=$current_year;然后加1的方式，在单事务、低并发的场景下确实能跑，但一旦碰到高并发，肯定会出问题：
比如两个事务同时执行这个查询，都拿到当前年份的最大receipt_id是2，然后各自把新数据的receipt_id设为3，最后就会出现两条同一年份、receipt_id都是3的数据，完全不符合你的需求。

靠谱的实现方式 #

要解决这个问题，核心是要让“获取当前最大值+生成新ID”变成一个原子操作，这里给你推荐两种实用的方案：

方案1：用专属序列记录表+行级锁

先建一张专门记录每年receipt_id序列的表：

CREATE TABLE receipt_year_seq (
    receipt_year INT PRIMARY KEY,
    current_max_id INT DEFAULT 1
);

每次生成新receipt_id的时候，用下面的原子操作：

-- 先确保当前年份的记录存在（不存在就插入）
INSERT INTO receipt_year_seq (receipt_year)
VALUES ($current_year)
ON CONFLICT (receipt_year) DO NOTHING;

-- 锁住该行，更新并返回新的ID（这一步是原子的）
UPDATE receipt_year_seq
SET current_max_id = current_max_id + 1
WHERE receipt_year = $current_year
RETURNING current_max_id;

这样一来，并发情况下只有一个事务能拿到对应年份行的锁，其他事务会等着锁释放后再执行，绝对不会出现重复的receipt_id。

方案2：按年创建独立序列

如果你的PostgreSQL版本支持动态操作对象，可以每年创建一个专属的序列，比如receipt_seq_2018、receipt_seq_2019。插入数据时，用nextval('receipt_seq_' || $current_year::TEXT)来获取下一个ID。这种方式性能不错，但需要每年手动或自动创建序列，维护成本稍高一点。

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