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 20:06:02 GMT
To narrow down the locking issue, I have moved the create table outside the
create unique index transaction as follows. Also, I have added sql to dump
lock table for syscolumns to see what kind of locks exist on SYSCOLUMNS at
various points.

 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 intent share lock on system table SYSCOLUMNS and hence the
nested transaction for generated keys can't get intent
-- exclusive lock on it. Because of this, Derby ends up using the parent
user transaction for gnerating next value rather than a
-- transaction of its own
create unique index t1i1 on t1(c11);
-- at this point, there is intent share lock IS on SYSCOLUMNS and share
locks on couple rows.
select  * from syscs_diag.lock_table l where tablename like 'SYSCOLUMNS'
order by tablename, type;
insert into t1 values(1,1);
-- no changes in locks on SYSCOLUMNS after the insert above because system
did not generate a value for the identity column
select  * from syscs_diag.lock_table l where tablename like 'SYSCOLUMNS'
order by tablename, type;
-- you will notice that the next value for generated column is 1 at this
point as expected
select * from sys.syscolumns where columnname like 'C11';
insert into t1(c12) values(3);
-- the insert above caused intent exclusive IX on SYSCOLUMNS and exclusive
lock on a row in the table because it was trying to
-- generate the next value for identity column. But eventhough the
insert statement failed, the IX and X locks caused by it remain
select  * from syscs_diag.lock_table l where tablename like 'SYSCOLUMNS'
order by tablename, type;
-- the insert above fails as expected because there is already a *1* in
unique key c11 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.
select * from sys.syscolumns where columnname like 'C11';

So, the create index is getting intent share lock on SYSCOLUMNS table and
share locks on some of the rows. This prevents the nested transaction for
generated keys logic from getting an intent exclusive on table and exclusive
lock on the rows.

Hope this answers your question about locking,
On 12/23/05, Daniel John Debrunner <djd@debrunners.com> wrote:
> Mamta Satoor 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, <snip>
> The troubling item here is that you say create index is getting a table
> lock on SYSCOLUMNS. Is a create index on another table by another
> transaction going to cause the same issue?
> Is it the create index or the create table that is leading to the lock
> Dan.

View raw message