在PostgreSQL中使用SELECT FOR UPDATE的SELECT INTO

Question

在PostgreSQL中使用SELECT FOR UPDATE的SELECT INTO

sqlpostgresqltransactionsplpgsqlisolation-level

3

假设我有一个名为 message 的关系，用于保存使用以下命令创建的消息：

CREATE TABLE message 
(
     id serial primary key, 
     foo1 integer, 
     foo2 integer, 
     foo3 text
)

我们有一个函数可以获取消息并从关系中删除它，像这样：

CREATE FUNCTION get_and_delete_message(p_foo1 integer)
RETURNS TABLE(r_id integer, r_foo1 integer, r_foo2 integer, r_foo3 text) AS $$
DECLARE
  message_id integer;
BEGIN
    SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1;
    RETURN QUERY SELECT * FROM message WHERE foo1 = p_foo1 LIMIT 1;
    DELETE FROM message where id = message_id;
END;
$$ LANGUAGE plpgsql;

假设使用隔离级别 READ_COMMITTED ，可能会发生两个并发事务从两个用户返回相同的消息，尽管显然只有一个删除/获取它。这不是我的应用程序所期望的行为，我希望一条消息只能被一个用户读取。

如果使用 REPEATABLE_READ ，则不会发生这种情况。

但在阅读了有关 FOR UPDATE 的内容后，我认为仍然可以使用 READ_COMMITTED 级别，并将 get_and_delete_message 函数更改如下：

   ...
    BEGIN
        SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1;
        RETURN QUERY SELECT * FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE;
        DELETE FROM message where id = message_id;
    END;
    ...

据我了解，在第二个SELECT语句中使用FOR UPDATE实际上会锁定返回的行，直到事务结束，因此如果我们有两个并发事务，只有一个事务会返回并删除消息。

这是真的吗？或者我还应该执行SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE吗？我找不到关于将SELECT INTO与FOR UPDATE结合使用的任何信息。对此有什么想法吗？

- insumity

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

你可以用一条语句来实现，不需要使用select语句：

CREATE FUNCTION get_and_delete_message(p_foo1 integer)
RETURNS TABLE(r_id integer, r_foo1 integer, r_foo2 integer, r_foo3 text) 
AS $$
  DELETE FROM message 
    where foo1 = p_foo1 
  returning *;
END;
$$ LANGUAGE sql;

这将删除行并返回所有已删除的行作为delete语句的结果。