原子更新以递增Postgresql中的整数 [英] Atomic UPDATE to increment integer in Postgresql

问题描述

我正在尝试确定以下查询在以下情况下是否可以安全使用:

I'm trying to figure out if the query below is safe to use for the following scenario:

我需要生成连续的数字，没有间隔.由于需要跟踪许多记录，因此我有一个表，其中包含序列记录和序列整数列.

I need to generate sequential numbers, without gaps. As I need to track many of them, I have a table holding sequence records, with a sequence integer column.

要获取下一个序列，我将触发下面的SQL语句.

To get the next sequence, I'm firing off the SQL statement below.

WITH updated AS (
  UPDATE sequences SET sequence = sequence + ? 
  WHERE sequence_id = ? RETURNING sequence
)
SELECT * FROM updated;

我的问题是:当多个用户在不显式启动事务的情况下同时在数据库中触发此查询时，下面的查询是否安全?

My question is: is the query below safe when multiple users fire this query at the database at the same time without explicitly starting a transaction?

注意:第一个参数通常是1，但也可以是10，例如，以获取10个新序列号的块.

Note: the first parameter is usually 1, but could also be 10 for example, to get a block of 10 new sequence numbers

推荐答案

是的，这很安全.

当一个这样的语句正在运行时，所有其他这样的语句都被锁定在一个锁上.交易完成后，该锁将被释放，因此请缩短交易时间.另一方面，您需要保持事务开放，直到完成所有工作为止，否则可能会导致序列空白.
这就是为什么通常要求无间隙序列是一个坏主意的原因.

While one such statement is running, all other such statements are blocked on a lock. The lock will be released when the transaction completes, so keep your transactions short. On the other hand, you need to keep your transaction open until all your work is done, otherwise you might end up with gaps in your sequence.
That is why it is usually considered a bad idea to ask for gapless sequences.

这篇关于原子更新以递增Postgresql中的整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！