顺序不受交易影响吗? [英] Sequences not affected by transactions?

问题描述

我有一张桌子

create table testtable(
  testtable_rid serial not null,
  data integer not null,
  constraint pk_testtable primary key(testtable_rid)
);

所以可以说我执行此代码约20次:

So lets say I do this code about 20 times:

begin;
insert into testtable (data) values (0);
rollback;

然后我就

begin;
insert into testtable (data) values (0);
commit;

最后是

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?

推荐答案

回滚序列不是一个好主意.想象一下，同时发生两个事务，每个事务都使用序列作为唯一的ID.如果第二个事务提交并且第一个事务回滚，则第二个事务将插入带有"2"的行，而第一个事务将序列回滚到"1".

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".

如果再次使用该序列，则该序列的值将变为"2"，这可能会导致唯一约束问题.

If that sequence is then used again, the value of the sequence will become "2" which could lead to a unique constraint problem.

这篇关于顺序不受交易影响吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！