db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject RE: Derby, identity columns & locks on syscolumns
Date Fri, 29 Feb 2008 19:52:30 GMT

>     as I understand it, when you insert a row into a table with an
> identity column, it has to lock syscolumns to update the identity value -
> from the docs:
> "Derby keeps track of the last increment value for a column in a cache. It
> also stores the value of what the next increment value will be for the
> column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS
> system table. Rolling back a transaction does not undo this value, and
> thus rolled-back transactions can leave "gaps" in the values automatically
> inserted into an identity column. Derby behaves this way to avoid locking
> a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping
> concurrency high."

Ah-ha!  This answers something for me.  In our application, I just recently
started using dynamically created tables (created on demand as needed).
However, when table creation occurred, I would see a large number of
deadlocks for other, concurrent transactions which were inserting into
*unrelated* tables.  This essentially ground everything to a halt, similar
to Andrew's experience. 

My reaction was/is, "This is ridiculous", but I didn't have time to pursue
it further at the time.  My workaround was to use a global shared/exclusive
(read/write) lock for all database access, locking exclusively when creating
tables.  This was acceptable in our case because as it turns out, table
creation happens very seldom.  Nevertheless, this appears to be a weak point
for Derby... it shouldn't be necessary to do this type of global/external
locking for table creation.  

Comments?  Advice?  Is there a better way for Derby to manage this, so that
it doesn't produce lock contention across unrelated tables?


View raw message