db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: [Derby-359]Skipping over user inserted values into GENERATED BY DEFAULT identity columns....
Date Thu, 22 Dec 2005 18:48:15 GMT
I looked at Derby code and in fact, Derby does implement the SQL standard by
doing the job of getting the generated value and updating the system table
SYSCOLUMNS for the next generated value in a transaction of its own. But
this does not happen in its own transaction when there are lock issues on
the system table.

If we run the problem script that Satheesh has provided in JIRA for this bug
with autocommit on, we will have the expected behavior of Derby consuming
the generated value even if the insert fails for duplicate key. But if the
same script is run with autocommit off, the create unique index sql puts a
table lock on SYSCOLUMNS table. Next when the insert is run with a request
for system to generate a value, Derby starts a new transaction in
InsertResultSet.getSetAutoincrementValue at line 777. At line 794, it calls
DataDictionary.getSetAutoincrementValue method to do the actual job of
generating the value and updating the system table. But because the
user(parent) transaction has table lock on SYSCOLUMNS, the openConglomerate
call on line 6697 fails and InsertResultSet at line 809 checks if there was
a lock timeout exception and if so, it goes ahead and uses the user
transaction to generate the value and update the system table. But later
when it tries to insert the generated value, there is an error thrown for
duplicate key with severity in 20000 range which causes the current
*statement* to rollback and hence the changes made to SYSCOLUMNS get lost
too. When the same insert is tried again in the transactio with create
index, it keeps running into this scenario. This is why Derby is unable to
consume the generated value.

So this behavior with system locks, one can try to run following sql in ij
autocommit off;
drop table t1;
create table t1(c11 int generated by default as identity (start with 1,
increment by 1), c12 int);
--following puts locks on system tables SYSCOLUMN and hence a nested
transaction later on can't update the system table because
--the parent user transaction has got locks on the table. So, it reverts to
using parent user transaction for updating the system table
create unique index t1i1 on t1(c11);
insert into t1 values(1,1);
-- you will notice that the next value for generated column is 1 at this
select * from sys.syscolumns where columnname like 'C11';
insert into t1(c12) values(3);
-- the insert above fails as expected because there is already a *1* in the
table. But the generated value doesn't get consumed and
-- following select will still show next value for generated column as 2. If
this sql script was run with autocommit off, you would see
-- the next generated value at this point to be 2.
select * from sys.syscolumns where columnname like 'C11';
insert into t1(c12) values(3);

Hope this explains what the problem is.
On 12/21/05, Daniel John Debrunner <djd@debrunners.com> wrote:
> Mamta Satoor wrote:
> > I looked at the SQL 2000 spec to see what it says about generated keys.
> > Following are some lines copied from the Identity columns section and
> > from Sequence generators section. The value generation for identity
> > column follows the gules of Sequence generator. And in the Sequence
> > generator section, the spec says that value generation is done in a
> > transaction of its own and is not associated with the outside user
> > transaction.
> Right, thinking about this more and looking at the SQL spec, I was wrong
> here. The generation of sequence numbers is independent of any unique
> index and so Derby needs to follow the SQL standard.
> The fetch the max value works for the *specific* case that the value is
> a duplicate in a unique index, but doesn't work for other failure cases,
> say a CHECK constraint failing. Thus the only rational thing to do is to
> follow the standard as Mamta indicates.
> Dan.

View raw message