db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jørgen Løland <Jorgen.Lol...@Sun.COM>
Subject Re: Derby Locking vs. Oracle's and MySQL's
Date Wed, 15 Oct 2008 13:41:55 GMT
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)


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

View raw message