postgresql 序列不受事务影响？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2095917/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sequences not affected by transactions?
提问by Earlz
I have a table
我有一张桌子
create table testtable(
testtable_rid serial not null,
data integer not null,
constraint pk_testtable primary key(testtable_rid)
);
So lets say I do this code about 20 times:
因此，假设我执行此代码大约 20 次：
begin;
insert into testtable (data) values (0);
rollback;
and then I do
然后我做
begin;
insert into testtable (data) values (0);
commit;
And finally a
最后一个
select * from testtable
Result: row0: testtable_rid=21 | data=0 Expected result: row0: testtable_rid=1 | data=0
As you can see, sequences do not appear to be affected by transaction rollbacks. They continue to increment as if the transaction was committed and then the row was deleted. Is there some way to prevent sequences from behaving in this way?
如您所见，序列似乎不受事务回滚的影响。它们继续递增，就好像事务已提交然后行被删除一样。有什么方法可以防止序列以这种方式运行吗？
回答by Aaron
It would not be a good idea to rollback sequences. Imagine two transactions happening at the same time, each of which uses the sequence for a unique id. If the second transaction commits and the first transaction rolls back, then the second inserted a row with "2" while the first rolls the sequence back to "1".
回滚序列不是一个好主意。想象一下同时发生的两个事务，每个事务都使用序列作为唯一的 id。如果第二个事务提交并且第一个事务回滚，则第二个事务插入带有“2”的行，而第一个事务将序列回滚到“1”。
If that sequence is then used again, the value of the sequence will become "2" which could lead to a unique constraint problem.
如果再次使用该序列，则该序列的值将变为“2”，这可能会导致唯一约束问题。
回答by Luká? Lalinsky
No, there isn't. See the note at the bottom of this page. It's a bad idea to do something like that anyway. If you have two transactions running at the same time, each inserting one row, you want them to insert rows with different IDs.
不，没有。请参阅本页底部的注释。无论如何，这样做是个坏主意。如果您同时运行两个事务，每个事务插入一行，您希望它们插入具有不同 ID 的行。
