1

In the book "Oracle Database Concepts", Oracle gives an example to explain Read Committed Isolation Level.

In the example, transaction 1 updates row 1, then transaction 2 updates the same row before transaction 1 commits. So, transaction 2 waits until transaction 1 commits. Then transaction 1 commits. Transaction 2 commits after that. Of course, after transaction 2 commits, update of row 1 by transaction 1 is overridden by transaction 2.

It consider this situation as a lost update. But in my view, it should not be a "lost update" since transaction 1 has committed. Lost update is considered in one single transaction. The schedule is even equal to a serializable schedule regardless of read operations.

The example is here, or more specifically, here.

In Oracle's glossary, lost update is -

A data integrity problem in which one writer of data overwrites the changes of a different writer modifying the same data.

So, what do you think of this? Is it a "lost update" and if so, could this kind of lost update be avoided by concurrency control of database?

Any comments and help would be appreciated.

Chait
  • 1,052
  • 2
  • 18
  • 30
Fang Zhen
  • 344
  • 3
  • 7

1 Answers1

3

It is considered a "lost update" because Session 2 overwrote the change that Session 1 had made without being aware that it was doing so. As far as Session 2 is concerned it read the 'Banda' row with a salary of 6200 and updated it to 6300 - it never saw Session 1's update to 7000.

As that documentation says: "devising a strategy to handle lost updates is an important part of application development" - i.e. it is not part of the in-built data concurrency functionality of the DBMS. Within a transaction this can be done by using select for update to attempt to lock the row. In this example, Session 2's select of the 'Banda' row would have been blocked if it had done this. Once session 1 had committed, session 2 would get the lock and would see the new salary. In a stateless environment like a web application optimistic locking is used to achieve this.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259