db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [Derby-359]Skipping over user inserted values into GENERATED BY DEFAULT identity columns....
Date Sun, 25 Dec 2005 12:31:17 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
I wish I had recorded what autocommit was when I filed DERBY-359.
Likely it was OFF, based on your research. I was trying to
understand/test IDENTITY BY DEFAULT option, after being added by
Tomohito-san early this year. Looks like there is no functionality
problem here... though not sure about transaction/locking issue here.<br>
Mamta Satoor wrote:<br>
  <div>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.
  <div>autocommit off;<br>
drop table t1;<br>
create table t1(c11 int generated by default as identity (start with 1,
increment by 1), c12 int);</div>
  <div>-- following&nbsp;puts intent share lock on system table SYSCOLUMNS
and hence&nbsp;the nested transaction for generated keys&nbsp;can't get intent </div>
  <div>-- exclusive&nbsp;lock on it. Because of this, Derby ends up using
the&nbsp;parent user transaction for gnerating next value rather than a</div>
  <div>-- transaction of its own</div>
  <div>create unique index t1i1 on t1(c11); </div>
  <div>-- at this point, there is intent share lock IS on SYSCOLUMNS
and&nbsp;share locks on couple rows.</div>
  <div>select&nbsp; * from syscs_diag.lock_table l where tablename like
'SYSCOLUMNS' order by tablename, type;<br>
insert into t1 values(1,1);</div>
  <div>-- no changes in locks on SYSCOLUMNS after the insert above
because system did not generate a value for the identity column</div>
  <div>select&nbsp; * from syscs_diag.lock_table l where tablename like
'SYSCOLUMNS' order by tablename, type;<br>
-- you will notice that the next value for generated column is 1 at
this point as expected<br>
select * from sys.syscolumns where columnname like 'C11';<br>
insert into t1(c12) values(3);</div>
  <div>-- the insert above caused intent exclusive IX on SYSCOLUMNS and
exclusive lock on a row in the table because it was trying to</div>
  <div>-- generate the next value for identity column. But eventhough
the insert&nbsp;statement failed, the IX and X locks caused by it remain </div>
  <div>-- on SYSCOLUMNS. </div>
  <div>select&nbsp; * from syscs_diag.lock_table l where tablename like
'SYSCOLUMNS' order by tablename, type;<br>
-- 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 </div>
  <div>-- get consumed and following select will still show next value
for generated column as 1. If this sql script was run with autocommit </div>
  <div>-- off, you would see&nbsp;the next generated value at this point to
be 2.<br>
select * from sys.syscolumns where columnname like 'C11';<br>
  <div>So, the create index is getting intent share lock on SYSCOLUMNS
table and share locks on some of the rows.&nbsp;This prevents the nested
transaction for generated keys logic from&nbsp;getting an intent exclusive
on table and exclusive lock on the rows.
  <div>Hope this answers your question about locking,</div>
  <div><span class="gmail_quote">On 12/23/05, <b
 class="gmail_sendername">Daniel John Debrunner</b> &lt;<a
 href="mailto:djd@debrunners.com">djd@debrunners.com</a>&gt; wrote:</span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
Satoor wrote:<br>
&gt; I looked at Derby code and in fact, Derby does implement the SQL<br>
&gt; standard by doing the job of getting the generated value and
&gt; the system table SYSCOLUMNS for the next generated value in a<br>
&gt; transaction of its own. But this does not happen in its own
&gt; when there are lock issues on the system table.<br>
&gt; If we run the problem script that Satheesh has provided in JIRA
for this
&gt; bug with autocommit on, we will have the expected behavior of Derby<br>
&gt; consuming the generated value even if the insert fails for
&gt; key. But if the same script is run with autocommit off, the create
&gt; unique index sql puts a table lock on SYSCOLUMNS table. Next when
&gt; insert is run with a request for system to generate a value, Derby<br>
&gt; starts a new transaction in
InsertResultSet.getSetAutoincrementValue at<br>
&gt; line 777. At line 794, it calls
&gt; method to do the actual job of generating the value and updating
&gt; system table. But because the user(parent) transaction has table
lock on
&gt; SYSCOLUMNS, &lt;snip&gt;<br>
The troubling item here is that you say create index is getting a table<br>
lock on SYSCOLUMNS. Is a create index on another table by another<br>
transaction going to cause the same issue?
Is it the create index or the create table that is leading to the lock<br>

View raw message