[ http://issues.apache.org/jira/browse/DERBY-359?page=all ]
Mamta A. Satoor resolved DERBY-359:
-----------------------------------
Resolution: Won't Fix
The sql script provided for this JIRA entry was run with autocommit off,
meaning the DDL and DML were in one single transaction. The DDL to create a
unique index on the generated column puts shared row locks on SYSCOLUMNS rows
involving the generated column. After the index creation, the next insert
statement has the system generate values for the identity column (system
generates values 1 and 2). (Note - As per SQL standards, the new value
generation for identity columns should happen in a transaction of its own.
Based on this SQL specification, Derby attempts to generate values in a
transaction of its own but since the parent user transaction has shared row
locks on SYSCOLUMNS, the nested transaction can't get locks on those rows and
hence Derby has to discard the nested transaction and Derby asks the parent
user transaction to generate the values. If the DDL to create unique index was
not in the same user transaction, nested transaction would have succedded).
After this, user inserts manual value 4 into the generated column. Next insert
has the system generate the value (3 is generated) for identity column. But the
subsequent attempt to generate value 4 for identity column will fail because
user has manually inserted value 4 into the identity column which has been
defined to have a unique index on it. The unique index failure causes the
insert statement to rollback which in turn causes the the code related to
generated
value 4 to rollback too. And hence, when next time, system tries to generate
value, it will try to generate 4 again which will again run into unique index
failure and so on and so forth.
So, in short, the issue here is that the parent user transaction has shared
locks on some of the SYSCOLUMNS's rows which prevents a nested transaction to
get shared locks on those rows to generate the next value. If the DDL to
generate unique index can be moved out of the transaction, there will not be
locking issues for nested transaction.
I am going to mark this JIRA entry as Won't fix (don't if invalid is more
appropriate Resolution). If anyone disagrees with marking this issue as not a
bug, we can come back to it to discuss it further.
> GENERATED BY DEFAULT identity option should consume identity values even if
> the transaction aborts. This would allow skipping over user inserted values
> during system generation.
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-359
> URL: http://issues.apache.org/jira/browse/DERBY-359
> Project: Derby
> Type: Improvement
> Components: SQL
> Versions: 10.1.1.0
> Environment: Generic
> Reporter: Satheesh Bandaram
> Assignee: Mamta A. Satoor
>
> Using GENERATED BY DEFAULT identity column, user can specify a value to be
> inserted into identity column. When the system generated value and this user
> specified value match, if an unique index is present, an error is correctly
> generated. However, it is desirable to skip this value for next identity
> generation, so that next INSERT would pass. Currently, all subsequent insert
> statements fail.
> ij(CONNECTION0)> create table tauto(i int generated by default as identity, k
> int);
> 0 rows inserted/updated/deleted
> ij(CONNECTION0)> create unique index tautoInd on tauto(i);
> 0 rows inserted/updated/deleted
> ij(CONNECTION0)> insert into tauto(k) values 1,2;
> 2 rows inserted/updated/deleted
> ij(CONNECTION0)> select * from tauto;
> I |K
> -----------------------
> 1 |1
> 2 |2
> 2 rows selected
> ij(CONNECTION0)> insert into tauto values (4,4);
> 1 row inserted/updated/deleted
> ij(CONNECTION0)> insert into tauto(k) values 3;
> 1 row inserted/updated/deleted
> ij(CONNECTION0)> insert into tauto(k) values 4; <=== Expected
> error.
> ERROR 23505: The statement was aborted because it would have caused a
> duplicate
> key value in a unique or primary key constraint or unique index identified by
> 'T
> AUTOIND' defined on 'TAUTO'.
> ij(CONNECTION0)> insert into tauto(k) values 5; <=== Would be
> preferable to skip over identity value of '4' and use '5'
> ERROR 23505: The statement was aborted because it would have caused a
> duplicate
> key value in a unique or primary key constraint or unique index identified by
> 'T
> AUTOIND' defined on 'TAUTO'.
> ij(CONNECTION0)> insert into tauto(k) values 6;
> ERROR 23505: The statement was aborted because it would have caused a
> duplicate
> key value in a unique or primary key constraint or unique index identified by
> 'T
> AUTOIND' defined on 'TAUTO'.
> ij(CONNECTION0)>
> At this point, all system generated identity value based inserts would
> continue to fail.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
