提交事务时以原子方式设置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';
阅读本章提交时间戳记 。
相关手册中的实用程序功能。
函数波动性只有 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:
- Does PostgreSQL support "accent insensitive" collations?
- How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
对于许多只关注提交顺序（而不是绝对时间）的用例（如您的？），使用 xmin 强制转换为 bigint 直接（ xmin :: text :: bigint ），而不是提交时间戳。 （ 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 环绕操作-但是，如果群集的生存时间足够长，最终它将结束。阅读
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屋！
