PostgreSQL:在恶劣的多用户环境中使用SELECT nextval生成器线程是否安全? [英] PostgreSQL: Is using SELECT nextval generator thread safe, in harsh multiuser environments?

问题描述

我的意思是像成千上万的用户及时更新数据库中的值吗?

I mean like thousands users in time updating values in database?

推荐答案

是的，可以从多个并发操作的事务中安全使用nextval.这是它的目的和存在的原因.

Yes, nextval is safe to use from multiple concurrently operating transactions. That is its purpose and its reason for existing.

也就是说，它实际上并不是线程安全的"，因为PostgreSQL使用的是多处理模型而不是多线程模型，并且因为大多数客户端驱动程序(例如libpq)不允许一个以上的线程一次与单个连接进行交互.

That said, it is not actually "thread safe" as such, because PostgreSQL uses a multi-processing model not a multi-threading model, and because most client drivers (libpq, for example) do not permit more than one thread at a time to interact with a single connection.

您还应该注意，虽然保证nextval返回不同且递增的值，但不能保证没有空洞"或空隙".当生成的值在没有提交的情况下被丢弃(例如，由ROLLBACK)并且PostgreSQL在服务器崩溃后恢复时，就会产生这样的差距.

You should also be aware that while nextval is guaranteed to return distinct and increasing values, it is not guaranteed to do so without "holes" or "gaps". Such gaps are created when a generated value is discarded without being committed (say, by a ROLLBACK) and when PostgreSQL recovers after a server crash.

尽管nextval总是返回递增的数字，但这并不意味着您的事务将按照它们从给定序列中获得ID的顺序进行提交.因此发生这种情况是完全正常的:

While nextval will always return increasing numbers, this does not mean that your transactions will commit in the order they got IDs from a given sequence in. It's thus perfectly normal to have something like this happen:

Start IDs in table: [1 2 3 4]
1st tx gets ID 5 from nextval()
2nd tx gets ID 6 from nextval()
2nd tx commits:     [1 2 3 4 6]
1st tx commits:     [1 2 3 4 5 6]

换句话说，漏洞可以出现和消失.

In other words, holes can appear and disappear.

这两个异常都是造成一个nextval呼叫不阻塞另一个呼叫的必然和不可避免的后果.

Both these anomalies are necessary and unavoidable consequences of making one nextval call not block another.

如果要使序列不具有此类排序和间隙异常，则需要使用无间隙序列设计，该设计一次只允许一个事务具有未提交的生成ID，从而有效地消除了该表中插入的所有并发性.通常使用计数器表上的SELECT FOR UPDATE或UPDATE ... RETURNING来实现.

If you want a sequence without such ordering and gap anomalies, you need to use a gapless sequence design that permits only one transaction at a time to have an uncommitted generated ID, effectively eliminating all concurrency for inserts in that table. This is usually implemented using SELECT FOR UPDATE or UPDATE ... RETURNING on a counter table.

搜索"PostgreSQL无缝序列"以获取更多信息.

Search for "PostgreSQL gapless sequence" for more information.

这篇关于PostgreSQL:在恶劣的多用户环境中使用SELECT nextval生成器线程是否安全?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！