在PostgreSQL中使用SELECT FOR UPDATE选择进入

本文介绍了在PostgreSQL中使用SELECT FOR UPDATE选择进入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我有一个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.