功能"Insert or update" - 唯一密钥冲突 - 交易问题？

Question

我有一个PostgreSQL函数，用来计算用户使用"items"的次数。计数器值保存在表中:

users_items

user_id - integer (fk)
item_id - integer (fk)
counter - integer

有最大值。每个用户每件物品1个计数器(唯一键)。

下面是我的函数:

CREATE OR REPLACE FUNCTION increment_favorite_user_item (item integer, userid integer) RETURNS integer AS 
$BODY$
    DECLARE
        new_count  integer;   -- Usage counter
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM users_items WHERE user_id = userid AND item_id = itemid) 
        THEN
            INSERT INTO users_items ("user_id", "item_id", "counter") VALUES (userid, itemid, 1); -- First usage - create new counter
            new_amount = 1;
        ELSE
            UPDATE users_items SET count = count + 1 WHERE (user_id = userid AND item_id = itemid); -- Increment counter
            SELECT counter INTO new_count FROM users_items WHERE (user_id = userid AND item_id = itemid);
        END IF;
        RETURN new_count;
    END;
$BODY$
LANGUAGE 'plpgsql'
VOLATILE;

由应用程序使用，应用程序可以多次调用它。一切正常，直到我们对同一个用户和项依次调用函数，当项对于特定用户是新的(users_items表中不存在记录)。

对于第二个函数调用，我得到了唯一的冲突:"Key (user_id, item_id)=(1,7912)已经存在"。似乎"如果不存在"检查不能正常工作，第二个函数调用没有看到第一个插入的记录，并试图插入同一行，使uq检查失败。

我能做什么来解决这个问题?

每个函数调用在另一个事务中运行。

Answer 1

存在a)竞争条件，b)如果要确保INSERT

<>之前声明rc int;开始锁表用户在共享行独占模式;更新user_id = $1的用户获取诊断rc = ROW_COUNT;如果rc = 0，则INSERT INTO users(id, counter) VALUES($ 1,1)如果;结束;之前

或更复杂的代码，但使用更少的锁

<>之前声明rc int;开始——快速路径更新user_id = $1的用户获取诊断rc = ROW_COUNT;如果rc = 0，则锁表用户在共享行独占模式;更新user_id = $1的用户获取诊断rc = ROW_COUNT;如果rc = 0，则INSERT INTO users(id, counter) VALUES($ 1,1)如果;如果;结束;