提交事务时自动设置 SERIAL 值 [英] Atomically set SERIAL value when committing transaction
问题描述
假设我有一个表，我想在其中使用 serial 作为主键来请求客户端的更改.客户将询问在键 X 之后给我更改".如果不使用 SERIALIZABLE 隔离级别或锁定，这很容易出现竞争条件.
Say I have a table where I want to use a serial as primary key to ask for changes from the client. The client will ask "give me the changes after key X". Without using SERIALIZABLE isolation level or locking, this is prone to race conditions.
事务 A 可以先开始，然后进行写入，然后需要很长时间才能提交.同时事务 B 将在 A 提交之前启动并提交.来自 B 的写入将获得比来自 A 的写入更高的主键.如果客户端现在要求更改，它将错过来自 A 的仍未提交的写入，并记下最新的最高主键.所以即使在 A 提交之后，客户端也永远不会看到那个变化，因为它的键低于客户端已经得到的变化.
Transaction A can start first, and do its writes, then take a long time to commit. Meanwhile transaction B will start and commit, before A commits. The write from B will get a higher primary key than the write from A. If a client now asks for changes it will miss the still uncommitted write from A, and note the newest highest primary key. So even after A commits, the client will never see that change, because its key is lower than the change the client already got.
是否可以在提交时以原子方式确定 serial(或来自计数器的类似值)的值，以便我们保证在提交时它会高于所有其他值，并且低于所有之后会犯哪些呢?如果不是，解决此问题的最佳方法是什么?
Is it possible to make the value of a serial (or similar from a counter) be determined atomically at commit time so that we are guaranteed that it will be higher than all others when committed, and lower than all which will be committed after it? If not what is the best way to solve this problem?
推荐答案
Postgres 9.5 引入了与此问题相关的新功能:提交时间戳.
Postgres 9.5 introduced a new feature related to this problem: commit timestamps.
您只需要激活track_commit_timestamp 在 postgresql.conf 中(并重新启动！)开始跟踪提交时间戳.然后就可以查询了:
You just need to activate track_commit_timestamp in postgresql.conf (and restart!) to start tracking commit timestamps. Then you can query:
SELECT * FROM tbl
WHERE pg_xact_commit_timestamp(xmin) >= '2015-11-26 18:00:00+01';
阅读提交时间戳跟踪"一章Postgres 维基.
相关手册中的实用函数.
Read the chapter "Commit timestamp tracking" in the Postgres Wiki.
Related utility functions in the manual.
函数波动性只是VOLATILE，因为事务ID (xid) 可以根据定义环绕.因此，您无法在其上创建功能索引.
您可以在有限的时间范围内在应用程序的函数包装器中伪造 IMMUTABLE 波动性，但您需要了解其含义.相关案例有更多解释:
Function volatility is only VOLATILE because transaction IDs (xid) can wrap around per definition. So you cannot create a functional index on it.
You could fake IMMUTABLE volatility in a function wrapper for applications in a limited time frame, but you need to be aware of implications. Related case with more explanation:
对于许多只对提交顺序(而不是绝对时间)感兴趣的用例(比如你的?)，将 xmin 强制转换为 bigint 直接"(xmin::text::bigint) 而不是提交时间戳.(xid 在内部是一个无符号整数，上半部分不适合带符号的 integer.)同样，请注意由于可能的 xid 环绕而造成的限制.
For many use cases (like yours?) that are only interested in the sequence of commits (and not absolute time) it might be more efficient to work with xmin cast to bigint "directly" (xmin::text::bigint) instead of commit timestamps. (xid is an unsigned integer internally, the upper half that does not fit into a signed integer.) Again, be aware of limitations due to possible xid wraparound.
出于同样的原因，提交时间戳不会无限期地保留.对于中小型数据库，xid 环绕几乎不会发生 - 但如果集群存活时间足够长，它最终会发生.阅读防止事务ID环绕故障"一章 手册中的详细信息.
For the same reason, commit timestamps are not preserved indefinitely. For small to medium databases, xid wraparound hardly ever happens - but it will eventually if the cluster is live for long enough. Read the chapter "Preventing Transaction ID Wraparound Failures" in the manual for details.
这篇关于提交事务时自动设置 SERIAL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
