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:53:30 GMT
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