Re: Question about SERIALIZE transaction isolation

"Roy Hann" <specially_at_processed.almost.meat> wrote in message news:ef6dncCufqMTcqXXnZ2dnUVZ8k6dnZ2d_at_pipex.net...
> Christoph Rupp wrote:
>
>> Hi,
>>
>> i'm nearly ready for my first release of my new concurrent, multi-
>> threaded, ACID transactional, logical idempotent logging, lock-free
>> database engine (key/value storage).
>>
>> OK, enough buzzwords for today :)
>>
>> Some of you were kind enough to help me a couple of times with my
>> questions. This one is about the behaviour of a read-only transaction
>> in the SERIALIZE isolation level.
>>
>> TXN A:
>> BEGIN
>> INSERT (a, b)
>>
>> TXN B:
>> BEGIN
>> FIND (a)
>>
>> Does the lookup of an un-committed item return KEY_NOT_FOUND or does
>> it create a transaction conflict?
>
> The key shouldn't be found in transaction B, and to maintain
> serializable isolation it should never be found even by a future query
> in the same transaction. I have no idea what a "transaction conflict"
> is but it sounds like an error. The point of serializable isolation is
> to create a realistic illusion that you have the database for your
> sole use. Random errors caused by the actions of other users would
> spoil that illusion. If you don't want to wait, you'll have to keep
> track of key values that are added while transaction B is in progress so
> you can make sure you exclude them from the results of that transaction.
>
> --
> Roy
>
Cristoph,

Going back some 15 to 25 years, Rdb/VMS implemented an interesting variation on the traditional locking scheme.

In the default configuration, transactions that began ad READ-ONLY transactions were turned into SNAPSHOT transactions. Snaphot transactions could read everything that had been commited prior to the start of the snapshot transaction, but nothing else. That means that inserts committed after the snapshot are invisible to the snapshot, deletes commited after the snapshot are visible, and updates committed after the transaction have to deliver the before image of the data updated. Before images were stored in what I guess is multiversioning, in a special storage area for snapshot data.

In the snapshot scheme as implemented by Rdb/VMS Txn B is treated as BEFORE Txn A, even though Txn A started first. It therefore will not see the insert doen by Txn A before Txn A committs, and even after Txn A commits. If snapshotting has been disbled by the DBA or if the transaction is started as read write, even though it's not going to write anything, Txn B will block when it tried to read data altered by Txn A, and wait for A to complete.

The Rdb/VMS implementation may not be of any interest to you in terms of the engine you are building, but it's interesting to note that this question has been asked and anaswered before. Hopefully, some of the literature concerning building database systems has captured whatever insights of value, if any, were captured by the Rdb engineers way back when. Received on Wed Jun 17 2009 - 17:15:06 CEST