在PostgreSQL中使用SELECT FOR UPDATE选择进入 [英] SELECT INTO with SELECT FOR UPDATE in PostgreSQL
问题描述
假设我有一个message
关系,在其中保存了使用以下命令创建的消息:
Assume I have a message
relation where I save messages that was created with this command:
CREATE TABLE message
(
id serial primary key,
foo1 integer,
foo2 integer,
foo3 text
)
我们有一个获取消息并将其从关系中删除的函数,如下所示:
And we have a function that gets a message and deletes it from the relation, like this:
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
隔离级别,则可能是来自两个用户的两个并发事务返回相同的消息,尽管显然只有一个会删除/获取它.这不是我的应用程序所希望的行为,我希望仅由一个用户读取一条消息.
Assuming READ_COMMITTED
isolation level it could be that two concurrent transactions from two users return the same message, although obviously only ones deletes/gets it. This is not the desired behavior for my application, I want one message to be read by only one user.
假设 REPEATABLE_READ
不会发生.
Assuming REPEATABLE_READ
this wouldn't happen.
但是在阅读了 READ_COMMITTED
级别并按以下方式更改get_and_delete_message
函数:
But after reading about FOR UPDATE
I thought perhaps it's still possible to use READ_COMMITTED
level and change the get_and_delete_message
function as following:
...
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
实际上将锁定返回的行,直到事务结束,因此,如果我们有2个并发事务,则实际上只有一个将返回并删除该消息.
From my understanding, using FOR UPDATE
in the second SELECT
will actually lock the returned rows until the end of the transaction, so if we have 2 concurrent transactions only one will actually return and delete the message.
是这种情况吗?还是我也应该做SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE
?我找不到将 SELECT INTO
与FOR UPDATE
.有什么想法吗?
Is this the case? Or should I also do SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE
? I couldn't find any information on combining SELECT INTO
with FOR UPDATE
. Any ideas on that?
推荐答案
您可以在单个语句中完成此操作,无需选择:
You can do that in a single statement, no need for a 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
语句的结果返回所有已删除的行.
This will delete the rows and then return all deleted rows as a result of the delete
statement.
这篇关于在PostgreSQL中使用SELECT FOR UPDATE选择进入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!