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 Fri, 23 Dec 2005 02:36:19 GMT
Considering what Derby does here, does anyone have any comments on the
current behavior? IMO, mixing of dml and ddl probably will not be seen in
real life application so much. The -ve thing is that the value is not
getting generated in its own transaction because of locking issues and is
getting generated in the user transaction. This is not in line with SQL spec
which says that value generation should be in a transaction of its own.

Mamta


On 12/22/05, Mamta Satoor <msatoor@gmail.com> wrote:
>
> Please read "So this behavior with system locks, one can try to run
> following sql in ij" as "To see this behavior with system locks, one can try
> to run following sql in ij"
>
> Also, please read "
> -- 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.
>
> "
> as
> " -- 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 *1*.
> If this sql script was run with autocommit off, you would see
> -- the next generated value at this point to be 2.
> "
>
> I accidentally hit send on earlier mail without first proofreading it.
> Mamta
>
>
>
> On 12/22/05, Mamta Satoor <msatoor@gmail.com> wrote:
> >
> > 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
> > point
> > 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.
> > Mamta
> >  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.
> > >
> > >
> > >
> >
>

Mime
View raw message