序列不受事务影响? [英] Sequences not affected by transactions?

问题描述

我有一张桌子

创建表testtable(testtable_rid 序列号不为空，数据整数不为空，约束 pk_testtable 主键(testtable_rid));

所以假设我执行这段代码大约 20 次:

开始；插入测试表(数据)值(0)；回滚；

然后我做

开始；插入测试表(数据)值(0)；犯罪;

最后一个

select * from testtable

<前>结果:row0: testtable_rid=21 |数据=0预期结果:row0: testtable_rid=1 |数据=0

如您所见，序列似乎不受事务回滚的影响.它们继续递增，就好像事务已提交然后行被删除一样.有什么方法可以防止序列以这种方式运行吗?

解决方案

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

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

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:

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?

解决方案

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

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

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