如何在多线程环境中安全地插入/更新 Postgres 中的值

发布于 2024-12-17 13:48:00 字数 521 浏览 3 评论 0原文

我在 PostgreSQL 中有一个如下所示的表，

 create table item_counts {
  item string,
  view_count int}

我想使用该表来跟踪 item 的出现，并根据需要增加计数。最初表未填充，因此当第一次观察到新值时插入新值，否则 view_count 会增加。速度和多任务处理都是问题。

我知道我可以做

rows_affected = execute("update item_counts set view_count = view_count + 1 
  where item = ?")
if rows_affected == 0:
   execute("insert into item_counts ...")

但是，这在多线程环境中是不安全的，所以我必须将其包装到事务中。这反过来会降低速度，因为每次插入/更新后都会发生提交。

有什么建议如何以干净有效的方式做到这一点？

原文

I have a table in PostgreSQL that looks like this

 create table item_counts {
  item string,
  view_count int}

I would like to use the table to keep track of occurrences of item, incrementing the counts as necessary. Initially the table is unpopulated, so a new value is inserted iff it is observed for the first time, otherwise the view_count is increased. Speed and multitasking are both concerns.

I know I can do

rows_affected = execute("update item_counts set view_count = view_count + 1 
  where item = ?")
if rows_affected == 0:
   execute("insert into item_counts ...")

However, this is unsafe in a multithreaded environment, so I would have to wrap it into a transaction. This would in turn decrease the speed, since a commit would occur after each insert/update.

Any suggestions how to do it in a clean and efficient way?

分享到QQ

分享到微博