db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5494) Same value returned by successive calls to a sequence generator flanking an unorderly shutdown.
Date Mon, 26 Mar 2012 16:10:37 GMT

    [ 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

        

Mime
View raw message