db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Lawrenson <andrew.lawren...@coppereye.com>
Subject Auto-increment values, nested transactions & locks...
Date Mon, 03 Mar 2008 15:40:29 GMT
Hi all,

    this is actually a follow-up to some post of mine on derby-users, as it's started getting
a bit technical & it was recommended that I ask here.

I've been having problems with locks on syscolumns when insert rows into tables with an auto-incremented
The Derby docs state:

"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."

When I've got lots of threads inserting at once, what I'm seeing is that every so often this
lock on SYSCOLUMNS gets held for a very long time, and other inserts are failing to insert
with lock-timeouts on this SYSCOLUMNS entry.

Now, I've done some experimentation & digging through the source-code, and I discovered
the following:

- when you try & do an insert with an auto-increment, derby creates a nested transaction
for the required updated of SYSCOLUMNS
- however, if this update using the nested transaction fails, it tries again using the parent
transaction - which will not be committed at the end of the update.

Normally, I'm seeing the SYSCOLUMNS update typically take 1ms or so.  However, under heavy
load, it's occasionally taking longer (I've seen up to about 1/3 second).

Now, when this updates takes longer, any other update (and there may be several in 1/3 second)
will fail immediately, as the the subtransaction deliberately does not wait if it comes across
any locks.

This means that the failed transactions will then get re-executed under the parent transaction,
which may not commit for several seconds (under heavy load).  Because these transactions are
now locking the SYSCOLUMNS row themselves, every other update starts failing & being re-executed
under the parent transaction as well.

The end result is that as soon as one update of the SYSCOLUMNS row fails due to a lock, I
get a massive cascade of locks that cause everything to come to a grinding halt - although
once most of the inserts have failed, it starts sorting itself out again.

My question is basically - why does the nested-transaction need to fail immediately if it
encounters a lock?
If it's to try & avoid blocking, for concurrency, it seems to have the opposite effect,
as executing under the parent transaction appears worse.  Or is there some subtle issue I'm
not aware of?  (I'm not a Derby Internals Guru!)

I've tried re-building derby, with the nested-transaction taking the default lock timeout,
and even with 100 concurrent clients, I see no lock timeouts in the clients.  I've run it
through the derbylang unit tests, and the autoincrement test still passes.

So - is this a viable change, or is it flawed in some way?

Many thanks for any advice,

        Andrew Lawrenson

View raw message