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);
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);
-- 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
-- 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
-- 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.
