[
https://issues.apache.org/jira/browse/DERBY-5494?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13238516#comment-13238516
]
Rick Hillegas commented on DERBY-5494:
--------------------------------------
To answer Kathey's question more exactly: Yes, a version of this problem also
affects identity columns. This is true regardless of whether you use the new
identity generators (currently implemented in 10.9) or the old-style identity
generators (in 10.8).
According to the SQL Standard, part 2, section 4.14.7, identity columns are
supposed to behave as though they are backed by internal sequence generators.
That means that commits and rollbacks are not supposed to affect identity
values.
The following scripts show that you can make an identity column produce the
same value in two successive invocations.
First run this script to generate a value and then rollback:
connect 'jdbc:derby:db;create=true';
create procedure systemExit( in exitCode int )
language java parameter style java no sql
external name 'java.lang.System.exit';
create table t( a int generated always as identity, b int );
autocommit off;
select count(*) from sys.syscolumns with rs;
insert into t( b ) values ( 0 );
-- here you see that the identity column has value 1
select * from t;
rollback;
select * from t;
call systemExit( 0 );
Now run this script to generate a new value (which turns out to be the original
value):
connect 'jdbc:derby:db';
insert into t( b ) values ( 1 );
-- here you see that the identity column again has value 1
select * from t;
> Same value returned by successive calls to a sequence generator flanking an
> unorderly shutdown.
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-5494
> URL: https://issues.apache.org/jira/browse/DERBY-5494
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2,
> 10.9.0.0
> Reporter: Rick Hillegas
> Labels: derby_triage10_9
>
> The following sequence of steps causes a sequence generator to return the
> same value on successive NEXT VALUE FOR calls.
> 1) Bring up ij and issue the following commands:
> connect 'jdbc:derby:db;create=true';
> create sequence s;
> values next value for s;
> 2) Control-c out of ij so that the engine comes down hard without an orderly
> shutdown.
> 3) Now bring up ij again and issue the following commands:
> connect 'jdbc:derby:db';
> values next value for s;
> Thanks to Knut for finding this one.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
