以下复合查询是否保证是原子的? [英] Is the following compound query guaranteed to be atomic?

问题描述

我想在复杂的复制操作之前保留大量的对象 ID.我知道 setval 和 nextval 有原子保证，但是如果我在如下复合语句中使用它们，这些保证是否适用于多线程环境?我使用的是 postgresql 9.6.

SELECT setval('objects_id_seq', nextval('objects_id_seq') + 9999);-- 返回最后一个保留的 id

解决方案

我知道 setval 和 nextval 有原子保证

是的，但保证可能不是您认为的那样.请记住，序列不受正常事务边界的限制.您的 setval 将对其他并发事务立即生效.它是原子的 - 它要么全部发生，要么根本不发生 - 但这并不意味着它遵守所有 ACID 属性.

您的查询肯定会影响并发查询.具体来说，如果有来自其他查询的并发 nextval 调用，序列的值将在 nextval 和您后续的 setval 之间继续增加.因此，如果您的意图是保留 9999 个 ID"，它将不起作用，如果在 nextval 和 setval 调用之间的小窗口中有 10 个其他名为nextval"的会话，您实际上可能只保留 9989.

你也不能 LOCK 来自 SQL 的序列，所以你不能只对它进行 EXCLUSIVE 锁定.

您可以:

• 分配比您需要的更多的额外值，并希望您足够快；
• 在EXCLUSIVE模式下锁定使用序列的表，希望没有人直接在序列上同时调用nextval(...)；
• 让 COPY 像往常一样从 DEFAULT nextval(....) 分配生成的键；
• 以不同的方式分配生成的密钥，例如计数器表，您可以在其中应用更强的锁定.

我认为在这种情况下您真正想要的是一个 nextval 变体，它会根据您提供的值递增，例如9999，而不是 1.PostgresSQL 还没有这样的功能，但如果有一个真的很方便.欢迎补丁！

<小时>

您可能会想如果我 ALTER SEQUENCE ... INCREMENT 9999 然后调用 nextval 然后 ALTER SEQUENCE ... INCREMENT 1. 是的，不要那样做.不幸的是，ALTER SEQUENCE 的效果将也在事务边界之外变得可见，因为 nextval 工作.因此，调用 nextval 的并发查询也可能会跳转 9999 行.您可能不在乎，但值得了解.我不建议您依赖这种行为.>

I would like to reserve a large amount of object ids prior to a complex COPY operation. I know setval and nextval have atomic guarantees, but do these guarantees hold in a multithreaded environment if I'm using them in a compound statement such as the following? I'm using postgresql 9.6.

SELECT setval('objects_id_seq', nextval('objects_id_seq') + 9999); -- returns the last reserved id

解决方案

I know setval and nextval have atomic guarantees

Yes, but the guarantees may not be what you think they are. Remember that sequences are exempt from normal transaction boundaries. Your setval will take effect immediately on other concurrent transactions. It's atomic - it either happens in its entirety or not at all - but that doesn't mean it obeys all ACID properties.

Your query is definitely going to affect concurrent queries. Specifically, the value of the sequence will continue to increase between nextval and your subsequent setval, if there are concurrent nextval calls from other queries. So if your intent is "reserve 9999 IDs" it won't work, you might actually only reserve 9989 if 10 other sessions called 'nextval' in the small window between your nextval and setval calls.

You can't LOCK a sequence from SQL either, so you can't just take an EXCLUSIVE lock on it.

You can either:

• Assign way more extra values than you need and hope you're fast enough;
• Lock the table that uses the sequence in EXCLUSIVE mode and hope nobody concurrently calls nextval(...) on the sequence directly;
• let COPY assign generated keys from a DEFAULT nextval(....) like usual;
• Assign generated keys a different way, such as a counter table, where you can apply stronger locking.

What I think you really want in this case is a nextval variant that increments by your supplied value e.g. 9999, not by 1. PostgresSQL doesn't have such a function yet, but it'd be really handy to have one. Patches welcome!

---

You might be thinking "what if I ALTER SEQUENCE ... INCREMENT 9999 then call nextval then ALTER SEQUENCE ... INCREMENT 1. Yeah, don't do that. Unfortunately, ALTER SEQUENCE's effects will also become visible outside transaction boundaries due to the same properties that make nextval work. So concurrent queries calling nextval might get a 9999-row jump too. You might not care, but it's worth knowing about. I wouldn't recommend that you rely on this behaviour.

这篇关于以下复合查询是否保证是原子的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！