Postgresql函数锁错误

发布于 2024-12-22 00:32:06 字数 279 浏览 6 评论 0原文

我有一个更新缓存表的触发器。触发器执行的函数执行两个操作：删除旧的缓存行并根据 id 添加新的缓存行，总

缓存表列为： id |总计

由于服务器活动水平较高，我相信对该函数的两次并行调用将产生以下情况：

Delete 1 
Delete 2 
Insert 1
Insert 2 ( this will crash because of the primary key )

有什么方法可以防止这种情况吗？事务不应该阻止这种情况（postgresql 函数中的隐含事务）

原文

I have a trigger that updates a cache table.
The function executed by the trigger makes two operation: deletes the old cache row and adds a new cache row based on the id with a total

cache table columns: id | total

Because of the high level of server activity I believe that two paralles calls on the function will generate the following situation:

Delete 1 
Delete 2 
Insert 1
Insert 2 ( this will crash because of the primary key )

Is there any way I can prevent this ? Shouldn't the transaction prevent this ( the implied transaction in a postgresql function )

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晒暮凉 2024-12-29 00:32:06

您可以使用咨询锁。 http://www.postgresql.org/docs/9.0/interactive/functions -admin.html

BEGIN
  PERFORM pg_advisory_lock(old.id);
  DELETE FROM cache WHERE some_id = old.id;
  INSERT INTO cache SELECT ...;
  PERFORM pg_advisory_unlock(old.id);
  RETURN old.id;
END;
$ ...

帕维尔

you can use a advisory locks. http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

BEGIN
  PERFORM pg_advisory_lock(old.id);
  DELETE FROM cache WHERE some_id = old.id;
  INSERT INTO cache SELECT ...;
  PERFORM pg_advisory_unlock(old.id);
  RETURN old.id;
END;
$ ...

Pavel

回复收藏 0 原文

~没有更多了~