Markus Junginger wrote:
Hi,
I am using Derby a couple of months now. Before, I usually worked with
Oracle (and some MySQL) DBs. For some reason, starting with Derby, I
became acquainted to DB locking issues. Is it just coincidence, or is
Derby actually trickier than Oracle when it comes to concurrency? I just
didn't have these issues before...
After some research, my speculation now is that Derby does never use the
rollback log for SELECTs. Let's say, we do read committed isolation and
transaction tx1 updates an entity (table or row) and stays open for some
time. Now, the entity is locked. When transaction tx2 wants SELECT this
particular entity, it has to wait until tx1 completes and the lock is
freed. Is this correct?
I think Oracle handles this differently and tx2 does not even need the
lock for a SELECT (UPDATE's is a different story). Instead, tx2 operates
on the state before tx1 started using the tx1's rollback log.
(http://www.broadh2o.net/docs/database/oracle/oracleLocks.html)
Markus,
This is partly correct; in Oracle, tx2 will read the state before tx1
started without waiting, but it can do so because Oracle does not
overwrite the old page with the update. Instead, Oracle stores both the
old page and the updated page in memory (as opposed to using the log).
tx2 will read the old version of the page.
Oracle has multi-version concurrency control (MVCC) with Snapshot
Isolation [1]. With MVCC, read operations do not need to acquire locks
at all. Still, the result of a select is what you would get if your
transaction had set a read lock at the beginning of the transaction
(assuming you run with REPEATABLE READ isolation [2]). Just like in your
example. Btw; MySQL has MVCC for the InnoDB and Falcon storage engines.
DBMSs with SI are not serializable [3], and Derby has serializable
concurrency control. There are pros and cons of both strategies. In
particular, there is a write anomaly that may happen in SI but not in
serializable histories (example in [1]).
Hope this helps.
[1] http://en.wikipedia.org/wiki/Snapshot_isolation
[2] http://en.wikipedia.org/wiki/Isolation_(database_systems)
[3] http://en.wikipedia.org/wiki/Serializability
--
Jørgen Løland
