db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby : Lock time out in Read commited mode
Date Fri, 02 Mar 2007 08:10:49 GMT
Prashant <prashant@pramati.com> writes:

> Hello all,
> I am trying to debug a lock time out issue. After turning on debug as
> advised in the FAQ[1], I am trying to make sense of the lock table
> dump that derby wrote to the log file when the lock timed out
> occurred.
> The test case involves two threads one inserting to Database, while
> other thread reads from the same database.
> The isolation level is default which I think is Read Committed[2].
> Now what i do not understand is that the read query (XID 8520) is
> waiting to obtain a Shared (S) lock on a Row that the insert thread
> (XID 8519) has obtained exclusive lock for. How could this be possible
> in a Read committed mode ?

Since the row is locked exclusively, and possibly modified, by another
transaction, the reader needs to wait until the other transaction is
committed in order to be able to read committed data. If you on the
other hand ran the transaction in read uncommitted mode, it would not
try to obtain a shared lock (I think), and the reader would not be
blocked (but it could read uncommitted data).

> Also the same test case passes when run using Oracle database.

I think Oracle uses multi-version concurrency control in which case
this would work. The writer doesn't modify the row directly, but
copies the row and makes the changes to the copy. Other transactions
can still read the old (committed) version of the row and therefore
don't have to wait for the new version to be committed. Derby has only
one version of each row and must therefore wait until that version is
in a committed state before other transactions can read it in read
committed mode.

Knut Anders

View raw message