Prashant <[EMAIL PROTECTED]> 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
