I believe what you are describing is Oracle using versioning to provide
isolation level support rather than locks. Derby currently only uses
row locks to provide isolation support, there is no option to make it
return a previous version of a row rather than blocking when it
encounters a row being updated by another transaction.
There are other databases which do even more impressive things like
allowing concurrent updates to the same row, if the compiler has
determined that they are serializable (ie. both update are +1) - with
appropriate redo/undo/constraint support.
Adding this feature to Derby would be on the "hard" side of the scale.
Does anyone have public domain description of versioning algorithms?
I assume one would just keep a side pointer to the old version of the
row linked off the page in the buffer cache, but what do you do if
the page goes out of cache (maybe you provide versions for updates in
cache and locking for pages out of cache)? Do you keep both rows on
the page until end of transaction, currently Derby will overwrite the
row in place if possible?
Mehran Sowdaey wrote:
thanks Oystein and Mike for replying. Changing the question slightly,
with Oracle under the same circumstances session 2 can see the committed
data (default isolation level is read committed). The developers want to
see the same in derby, is that possible?
Øystein Grøvlen wrote On 11/24/05 05:43,:
You might get a table lock when scanning the whole table (as a select
without a predicate will do). However, this query would not succeed
even if row locking was used, since the scan would be blocked by the
lock on (c,d).
Except when using 'read uncommitted' isolation level, a query will be
blocked when it encounters a row with uncommitted changes.
