Lost Upate Prevention with MVCC and IsoLevel Serializable

From: Jo Siffert <joDOTsiffertATgmxDOTnet_at_nospam.com>
Date: Sun, 23 Jul 2006 12:32:01 +0200
Message-ID: <e9vjaj$1ri6$1@ulysses.news.tiscali.de>

Hi,

I have a question regarding MVCC. If I have understood the concepts of MVCC correctly, lost updates would be possible even when isolation leven 'serializable' is used. Is this correct?

Example for 2 Transactions T1, T2 accessing the same data item x, using IsoLevel 'serializable':

  T1: begin
  T2: begin
  T1: read x, version 1

--> No read lock acquired

  T2: read x, version 1
--> No read lock acquired

  T1: write x
--> acquire write lock on x, hold

  T1: commit
--> yield version 2 for x
--> release write lock on x

  T2: write x
--> acquire write lock on x, hold

  T2: commit
--> yield version 3 for x
--> release write lock on x

--> Lost Update

In case of a SS2PL scheduler, the schedule would yield a deadlock. I guess Oracle would abort T2 with "Cannot serialize access for this transaction", but how does it notice?

Thanks,

   Jo Received on Sun Jul 23 2006 - 05:32:01 CDT