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);
drop table t1;
create table t1(c11 int generated by default as identity (start with 1, increment by 1), c12 int);
commit;
-- 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);
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);
-- 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
-- on SYSCOLUMNS.
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
-- 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';
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,
Mamta
On 12/23/05, Daniel John Debrunner <[EMAIL PROTECTED]> 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
on SYSCOLUMNS?
Dan.
