函数中的Postgres SELECT ... FOR UPDATE [英] Postgres SELECT ... FOR UPDATE in functions
问题描述
关于使用Post gres函数中的SELECT…FOR UPDATE行级锁定,我有两个问题:
I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function:
-
我选择哪几列?它们与我需要锁定然后更新的数据有什么关系吗?
Does it matter which columns I select? Do they have any relation to what data I need to lock and then update?
SELECT * FROM table WHERE x=y FOR UPDATE;
vs
SELECT 1 FROM table WHERE x=y FOR UPDATE;
在不将数据保存到某处的情况下,我无法在函数中进行选择,所以我保存到虚拟变量。这似乎很老套;
I can't do a select in a function without saving the data somewhere, so I save to a dummy variable. This seems hacky; is it the right way to do things?
这是我的功能:
CREATE OR REPLACE FUNCTION update_message(v_1 INTEGER, v_timestamp INTEGER, v_version INTEGER)
RETURNS void AS $$
DECLARE
v_timestamp_conv TIMESTAMP;
dummy INTEGER;
BEGIN
SELECT timestamp 'epoch' + v_timestamp * interval '1 second' INTO v_timestamp_conv;
SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
UPDATE my_table SET (timestamp) = (v_timestamp_conv) WHERE userid=v_1 AND version < v_version;
END;
$$ LANGUAGE plpgsql;
推荐答案
哪个重要我选择的列?
Does it matter which columns I select?
不,没关系。即使使用从表WHERE ... FOR UPDATE中选择1
,查询也会锁定满足where条件的所有行。
如果查询从联接中检索行,我们不想锁定联接中涉及的所有表中的行,而只锁定特定表中的行,即 SELECT ... FOR UPDATE OF-OF-TABLENAMES
语法可能有用:
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE
No, it doesn't matter. Even if SELECT 1 FROM table WHERE ... FOR UPDATE
is used, the query locks all rows that meet where conditions.
If the query retrieves rows from a join, and we don't want to lock rows from all tables involved in the join, but only rows from specific tables, a SELECT ... FOR UPDATE OF list-of-tablenames
syntax can be usefull:
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE
在不将数据保存到某处的情况下,我无法在函数中进行选择,因此我将其保存到虚拟变量。这似乎很老套;
I can't do a select in a function without saving the data somewhere, so I save to a dummy variable. This seems hacky; is it the right way to do things?
在Pl / PgSql中使用 PERFORM
放弃查询结果的命令:
http://www.postgresql.org/docs/9.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
代替:
In Pl/PgSql use a PERFORM
command to discard query result:
http://www.postgresql.org/docs/9.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
Instead of:
SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
使用:
PERFORM 1 FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
这篇关于函数中的Postgres SELECT ... FOR UPDATE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!