DBMS - Dirty Read Problem

Transaction management and concurrency control play a critical role in DBMS. Handling data storage and retrieval while supporting multiple transactions simultaneously can get complicated if we do not have efficient concurrency control mechanisms in place. One of the most significant problems is the "dirty read problem."

Read this chapter to learn what a "dirty read" is and its impact on data integrity. We will also learn how it occurs, and the methods to prevent it.

The Dirty Read Problem

The dirty read problem occurs when a transaction reads data that has been modified by another transaction and yet the change has not yet been committed. This means that the reading transaction might access temporary, and uncommitted data that could be rolled back, which may lead to inconsistencies and incorrect results. The dirty reads are a result of inadequate isolation between transactions.

How Dirty Reads Occur?

Dirty reads occur when two or more transactions operate on the same data concurrently. This is being done without sufficient isolation. If one transaction modifies data that is not committed the change, then another transaction may read this uncommitted data. If the first transaction is later rolled back, then the second transaction will have with the invalid data.

To understand the concept better, we must understand another type of problem called the lost update problem with example.

The Lost Update Problem

There will be a lost update problem when two transactions are updating the same record at the same time. The first transaction updates a record and then the second transaction updates it again with different value. So the update from the first transaction will be nullified. Let us see the following transactions to understand this better.

Here, we can see the updated result from transaction B is changed due to the write operation of transaction A. Now let us discuss the dirty read example.

Example of Dirty Read

Consider a practical example involving a banking system −

Initial State

• Data item DT in the database DB has a value of 1000.

Transaction A (T1)

• Reads DT (Value = 1000).
• Modifies DT to 1500 and stores it in a temporary buffer (not committed).

Transaction B (T2)

• Reads DT (Value = 1500, as modified by T1).
• Commits the change, making DT's value 1500 permanent.

Here before the T1 can commit, a server issue causes T1 to roll back to DT's original value of 1000. But, T2 has already committed 1500. And this is creating a data inconsistency. This leads to unreliable data in the database. This may affecting downstream processes.

Consequences of Dirty Reads

Dirty reads can have serious consequences, especially in critical applications −

• Data Inconsistency − Applications may read incomplete or incorrect data. And this may resulting in inaccurate reports or calculations.
• System Failures − In banking, incorrect balance calculations might cause overdrawn accounts or failed transactions.
• Business Logic Errors − The incorrect decisions might be made based on outdated or incorrect data.
• Operational Disruptions − Resolving data inconsistencies can disrupt normal operations and increase system downtime.

Preventing Dirty Reads

To prevent dirty reads, we can use a set of preventive approaches. The databases use transaction isolation levels that control how transactions interact with each other −

• Read Uncommitted − This allows dirty reads by permitting transactions to read uncommitted changes. It offers high performance but low reliability.
• Read Committed − This ensures only committed data is read. This is eliminating dirty reads while maintaining reasonable performance.
• Repeatable Read − This prevents dirty reads and unrepeatable reads by keeping data consistent throughout a transaction’s execution.
• Serializable − This is the highest isolation level. This is blocking all concurrent transactions to prevent dirty reads. Also for unrepeatable reads, and phantom reads.

Isolation Levels in Action

Let us see the previous example with "Read Committed" isolation level applied −

• T1 − Modifies DT to 1500 but hasn’t committed.
• T2 − Attempts to read DT, but the database blocks access until T1 commits or rolls back.

This gives the transaction T2 reads only committed data, thereby preventing dirty reads.

Techniques for Avoiding Dirty Reads

Consider applying the following techniques to avoid the dirty read problem −

• Locking Mechanisms − Use pessimistic or optimistic locks to control data access during transactions.
• Atomic Transactions − Ensure that operations either complete fully or have no effect at all.
• Application Design Best Practices − Structure applications to minimize concurrent data updates.
• Database Configuration − Properly configure the database system to align with business needs and reduce contention.

Optimistic Locking

The concept of optimistic locking is straightforward. It is the most popular technique for dealing with dirty read issues as well as lost update issues. This permits any record update as long as the value of the record remains unchanged since the last read. If the record's current value is the same as it was when it was last read, as determined by optimistic locking, an update is prohibited and the read-modify-write cycle must be restarted. Comparative-And-Update and Conditional Update are other names for optimistic locking.

Pessimistic Locking

Another technique to avoid lost update issues in DBMSs is to use pessimistic locking. This method uses the "PESSIMISTICWRITE" mode to explicitly lock the database objects that will be modified. Once the object has been locked, it is subjected to read-modify-write actions before being released. If another transaction attempts to read the same object during these operations, it must wait until the first transaction's read-modify-write cycle is finished.

Real-World Scenarios of Dirty Reads

Dirty reads can cause issues in various industries −

• Banking − Incorrect account balances due to pending transactions.
• E-commerce − Inventory mismatches during high-traffic sales events.
• Healthcare − Patient records showing incomplete updates.
• Logistics − Delivery statuses being updated incorrectly, causing tracking issues.

Best Practices for Developers

To prevent dirty reads, the developers should adopt several best practices as given below −

• Use Appropriate Isolation Levels − Match isolation levels to the application's requirements.
• Implement Robust Error Handling − Manage unexpected transaction failures to reduce data corruption risks.
• Apply Locking When Necessary − Use locking strategies properly and carefully to balance data integrity and performance.
• Monitor and Tune Performance − Continuously monitor transaction performance to detect and address potential issues early.

Conclusion

In this chapter, we covered the concept of the dirty read problem, how it arises through concurrent transactions, and its impact on database integrity. We explored a practical example for the dirty read and the lost update problems. We also highlighted the strategies to prevent dirty reads. We also discussed optimistic and pessimistic techniques of locking to handle the lost update and the dirty read problem in databases.