假设我有一些如下所示的PostgreSQL函数:
CREATE FUNCTION insertSth() RETURNS void AS $$
BEGIN
INSERT INTO ...;
END;
CREATE FUNCTION removeSthAfterSelect() RETURNS TABLE(...) AS $$
BEGIN
SELECT id INTO some_id ...;
RETURN QUERY SELECT * FROM ...;
DELETE FROM ... WHERE id = some_id;
END;
CREATE FUNCTION justDeleteSth() RETURNS void AS $$
BEGIN
DELETE FROM ...;
END;
CREATE FUNCTION justSelectSth() RETURNS TABLE(...) AS $$
BEGIN
RETURN SELECT * FROM ...;
END;
据我所知,PostgresSQL函数insertSth
、justDeleteSth
和justSelectSth
将自动执行(?)。因此,并行执行它们不会搞乱任何事情。
但是对于removeSthAfterSelect
来说,如果有并行执行,可能是SELECT id INTO some_id ..
找到了一些东西,然后同时另一个事务调用justDeleteSth
并用id = someId
删除了行,所以当事务继续时,它不会在这里删除任何东西:DELETE FROM ... WHERE id = some_id;
,这意味着它把事情搞乱了。
真的是这样吗?有没有办法避免这个问题?例如,通过说removeSthAfterSelect
应该被原子执行?
发布于 2019-06-10 19:32:13
通常可以使用锁定来实现所需的“原子”行为
例如:
BEGIN; -- transaction
SELECT pg_advisory_xact_lock(123); -- 123 is any big integer
-- do your "atomic" stuff here, other transactions
-- trying to acquire the same (123) lock will be waiting for it to be released
COMMIT; -- transaction has ended, the locks are released automatically
缺点是这样的锁定块不会并行执行。有关详细信息,请参阅文档https://www.postgresql.org/docs/11/explicit-locking.html。
https://stackoverflow.com/questions/26076416
复制相似问题