PostgreSQL中SELECT FOR UPDATE并发执行为何未阻止重复插入？

你的理解是错误的，问题出在SELECT FOR UPDATE的作用范围上：

• 当前的SELECT FOR UPDATE仅锁定mst_user_team表中team_id=1的现有29行数据，但它不会阻止其他事务读取这些行的当前状态（仅阻止修改）。
• 两个并发事务都会先锁定这29行，执行COUNT后得到29——因为此时新用户行还未插入（插入操作在pg_sleep(10)之后）。所以两个事务都会进入插入分支，最终导致团队用户数突破30的上限。

正确的实现方式 #

要解决这个并发问题，需要锁定团队的容量控制资源，而非现有用户行。以下是两种可靠方案：

方案1：用专用团队配置表加锁 #

1. 创建一个团队配置表（如team_config），每个团队一行，包含team_id、max_users、current_users字段。
2. 在事务开始时先锁定对应团队的配置行，强制并发事务串行执行：

DO $$
declare
    team_user_count INT;
BEGIN
    RAISE NOTICE 'Insert start at %', timeofday();
    
    -- 锁定团队配置行，确保同一时间只有一个事务能修改该团队的用户数
    SELECT current_users INTO team_user_count 
    FROM team_config 
    WHERE team_id = 1 
    FOR UPDATE;

    if team_user_count >= 30 then
        RAISE NOTICE 'Not accept insert. team_user_count=%', team_user_count;
    else
        RAISE NOTICE 'Accept insert. team_user_count=%', team_user_count;    
        PERFORM pg_sleep(10);    
        -- 执行插入用户行的操作
        INSERT INTO mst_user_team (team_id, user_id) VALUES (1, ...);
        -- 更新配置表的当前用户数
        UPDATE team_config SET current_users = current_users + 1 WHERE team_id = 1;
    end if;

    RAISE NOTICE 'Insert done at %', timeofday();

END$$

方案2：插入时使用原子判断 #

利用PostgreSQL的原子操作特性，将计数判断与插入操作合并为一个原子步骤，无需显式加锁：

DO $$
declare
    insert_result INT;
BEGIN
    RAISE NOTICE 'Insert start at %', timeofday();
    
    -- 仅当当前用户数小于30时才执行插入，整个过程原子化
    WITH count_cte AS (
        SELECT COUNT(*) AS cnt FROM mst_user_team WHERE team_id = 1
    )
    INSERT INTO mst_user_team (team_id, user_id)
    SELECT 1, ...
    FROM count_cte
    WHERE cnt < 30
    RETURNING 1 INTO insert_result;

    IF insert_result IS NULL THEN
        RAISE NOTICE 'Not accept insert. team_user_count=%', (SELECT COUNT(*) FROM mst_user_team WHERE team_id = 1);
    ELSE
        RAISE NOTICE 'Accept insert. team_user_count=%', (SELECT COUNT(*) FROM mst_user_team WHERE team_id = 1);
        PERFORM pg_sleep(10);
    END IF;

    RAISE NOTICE 'Insert done at %', timeofday();

END$$

这种方式依赖PostgreSQL默认的READ COMMITTED事务隔离级别，确保计数判断和插入操作不会被并发事务打断，避免出现超量插入。

内容的提问来源于stack exchange，提问作者N.D.H.Vu