Lost update and dirty read problem both are issues that appear due to uncontrolled execution of the concurrent transaction.
Lost Update Problem
A lost update problem appears when at least two transactions access the same transaction data and performs their operation. As we know, the data of transaction if the first read by the system and operation is performed and then write operation comes in the action which permanently saves the change in the actual database. Similarly, two or more transaction read the data from the database and performs their own operations but during the write operation sometimes the updated data from one
transaction is overwritten by another transaction due to which the new data from the first transaction got lost and this situation is called lost update problem.
Lost Update Problem example
T1 | T2 |
Read(X) | |
Read(X) | |
Update X=X+1000 | |
Update X=X-500 | |
Write(X) | |
Write(X) |
Let two transactions T1 and T2. The transaction first T1 reads the data X from the database, and then transaction T2 reads the same data from the database. After that T1 perform their operation to add 1000 to X, and then transaction T2 performs the operation to subtract 500 from the data which is read by T2 i.e. X, that T1 performs write operation which stores the value of X according to the changes made by T1 and then T2 runs write operation which again updates the value of X in database. This situation causes the loss of change of X which is performed by T1 as the T2 overwrites the X again after it gets updated by T1. We can also say that the update made by T1 is lost.
Dirty read problem
This is one of the problems that occur due to concurrent execution in an uncontrolled manner. In this problem, when two or more transactions run and one transaction reads the data value from the database and updates it with some operation but not completed, another transaction reads that updated value of data and completes its execution by the ‘commit’ statement’. After completing of other transaction, the first transaction reads the same data or another data or performs another operation and get failed.
According to the atomicity property of the transaction, the transaction rolled back and started from the begging i.e. the old or raw value of that data. But the other transaction which is already get completed by reading the old updated value of data is not considered an incorrect transaction as its data is considered as wrong data or dirty read. This whole scenario is called the dirty read problem.
Dirty read problem example
T1 | T2 |
Read(Y) | |
Update Y=Y+1000 | |
Write(Y) | |
Read(Y) | |
Update Y=Y+500 | |
Write(Y) | |
commit | |
Read(Z) // Failed | |
Write(Z) |
Let two transactions T1 is execution and read a data Y from the database and then update the data by adding 1000 to data and then write statement is executed (but the transaction is not finished). Then another transaction T2 comes in the action and it read the updated value of Y and performs subtracting 500 from Y and then executes the ‘write’ statement and with commit command, it completes its execution.
Then transaction T1 again performs read operation on some data Z and get failed. This failure will abort the transaction T1 and then the transaction T2 which has already been completed by reading the value of updated X which was the part of an aborted transaction, is now not a stable database transaction. Hence the data returned or written by transaction T2 is considered to be wrong.
Similar Reads
-
Aggregate function in SQL with Examples
In a Database Management System (DBMS), an aggregate function is a special tool that performs calculations on a set of… -
SQL and Parts of SQL language
SQL is a popular programming language, which is used to manage the relational databases and to perform various operations on… -
Views in Relational Algebra
In SQL, virtual table are considered as views. Like Tables, views also contain rows and columns. We can select the… -
Syntax of following SQL commands: sysdate, to date(), dual table ,to_number, substr() and initcap()
1. SYSDATE Purpose: SYSDATE is a function that returns the current date and time of the database server. Syntax SELECT… -
What is Super key, Candidate key and Primary key?
1. Super Key A super key is an attribute or a set of attributes within a table that uniquely identifies… -
What is Cursor? Difference between Implicit and Explicit Cursor
A Cursor is essentially a temporary work area created in the system memory that comes into play during the execution… -
What is Relational Algebra?
Relational algebra is a formal language used to query and manipulate data stored in relational database systems. It provides a… -
What do you mean by Relational Model? Explain with Example
The relational model is a type of model, which represents how the data are stored in a relational database. In… -
Functions of DBA in DBMS
The role of a Database Administrator (DBA) is to manage and maintain the performance, security, and integrity of a database.… -
What is Data Abstraction and Data Redundancy in DBMS
In the world of data management, two terms often come up: data abstraction and data redundancy. Both play crucial roles…