What is Lost Update Problem in DBMS?

The lost update is a type of concurrency control problem in a Database Management System(DBMS). Concurrency Control problems occur in DBMS when multiple transactions are being executed concurrently in an uncontrolled manner on a database. Let's understand some basic concepts of DBMS before going towards the lost update problem in DBMS.

Transaction in DBMS:

A transaction in DBMS is a group of tasks performed on a database. Tasks in these transactions are small units of operations that are collectively considered a transaction.

ACID Properties:

There is a set of rules to be followed while performing transactions on any database. A database should maintain ACID properties when a transaction is being performed. ACID properties are Atomicity, Consistency, Isolation and Durability.

• Atomicity: Atomicity states that either the transaction takes place at once or doesn't happen at all.
• Consistency: Consistency states that the database must be consistent before and after the transaction.
• Isolation: Isolation states that multiple transactions occur independently on a database without any interference with each other.
• Durability: Durability states that the changes of a transaction successfully occur in a database even if any system failure occurs.

Concurrency Problem: Concurrency problems are common problems faced while maintaining any database management systems. Concurrency problems occur when multiple transactions are being executed on the same database in unrestricted problems. Types of concurrency problems in DBMS are as follows:

• Lost Update Problem
• Temporary Update Problem
• Incorrect Summary Problem
• Unrepeatable Read Problem
• Phantom Read Problem

In this article, we will see what is lost update in DBMS and the conflicts due to which the lost update problem occurs in DBMS.

Lost Update Problem:

• A lost update problem occurs due to the update of the same record by two different transactions at the same time.
• In simple words, when two transactions are updating the same record at the same time in a DBMS then a lost update problem occurs. The first transaction updates a record and the second transaction updates the same record again, which nullifies the update of the first transaction. As the update by the first transaction is lost this concurrency problem is known as the lost update problem.
• Lost update problem is a write-write conflict.

Code Example

Now, let's see how one transaction nullifies the updates of another transaction resulting in a lost update problem using an example.

Here is a scenario, two transactions A and B are performing operations on the same record in a Database.

Let's understand the transactions being performed in the above image:

1. X = 100
2. Transaction A is reading record X = 100 and adding 15 to X, but this transaction is not yet reflected on DB. As write operation is not performed. (Result temp_X = 115)
3. Transaction B also reads X but here it is not getting updated value after transaction A. Hence, X = 100. Subtracting 25 from X. And write commit operation is performed on X. Hence, X = 75
4. Write commit operation for transaction A is performed. Hence X = 115
5. Here, the actual results of transactions A and B should be: X = 100, X after transaction A: X = 115, Transaction B begins: X = 115, X after transaction B: X = 95.
6. After performing transactions A and B, the value of X should have been X = 95.
7. But due to a lost update problem the result of Transaction B is lost and the final result is X = 115.

What Are the Different Ways to Prevent Lost Updates?

As we can see from the above example, the lost update problem occurs in DBMS due to concurrent transactions being performed on a DB and a lack of control over these transactions.

However, a lost update problem can prove fatal in the case of critical databases like banking databases. And we need to control and prevent lost update concurrency problems. In this section, we will see numerous ways to prevent lost update problems and ensure that updates from any transaction are not lost.

Increase Transaction Isolation Level

Increasing the isolation level of transactions on a database is one of the ways to prevent lost update problems. Isolation is I in ACID properties. This method ensures that the isolation level of transactions on a database is increased to 'Repeatable Read' so that the database can perform efficient checks in conjunction. The method to set the isolation level to 'Repeatable Read' is different in multiple database systems.

Optimistic Locking

Optimistic locking is the most common method used to prevent lost update problems. This method allows any update of a record to happen only when the value of that record has not changed after its last read. Optimistic Locking checks if the current value of the record is the same as it was when previously read then the update is not allowed, and the read-modify-write cycle has to be repeated. Optimistic Locking is also called Conditional Update or Compare-And-Update.

Pessimistic Locking

Pessimistic locking is also a way to prevent lost update problems in DBMS. In this approach, the DB objects that are going to be updated are explicitly locked using the 'PESSIMISTICWRITE' mode. After locking the object read-modify-write operations are performed on the objects and then the object is released. During these operations, if another transaction tries to read the same object it has to wait until the read-modify-write cycle of the first transaction is completed.

Atomic write operations

One of the many ways to prevent lost update problems is Atomic write operations. Atomic write operations read records at a memory location and write a new value into it simultaneously. Atomic write operations remove the need to implement read-modify-write cycles repeatedly. In this method a record is read and written at the same time, hence the lost update problem is avoided.

Learn More

To learn more about various Concurrency Control Problems in DBMS visit: Concurrency Control Problem