Dirty Read in SQL

Pre-Requisite - Types of Schedules, Transaction Isolation Levels in DBMS

A Dirty Read in SQL occurs when a transaction reads data that has been modified by another transaction, but not yet committed. In other words, a transaction reads uncommitted data from another transaction, which can lead to incorrect or inconsistent results.

This situation can occur when a transaction modifies a data item and then fails to commit the changes due to a system failure, network error, or other issue. If another transaction reads the modified data before the first transaction has a chance to commit, it can lead to a dirty read.

For example, consider two transactions T1 and T2. T1 modifies a row in a table and starts a transaction, but does not commit it. Meanwhile, T2 tries to read the same row before T1 commits its changes. If T2 is allowed to read the uncommitted data, it will result in a dirty read, potentially leading to incorrect or inconsistent results.

To prevent dirty reads, SQL provides transaction isolation levels, which specify how transactions should be isolated from one another. The isolation levels include:

• Read uncommitted: This level allows transactions to read uncommitted data from other transactions, leading to potential dirty reads.
• Read committed: This level allows transactions to read only committed data, preventing dirty reads.
• Repeatable read: This level prevents dirty reads and also ensures that a transaction always reads the same data for a given query, even if other transactions modify the data in the meantime.
• Serializable: This level provides the highest level of isolation and ensures that transactions are executed serially, preventing dirty reads and other anomalies.
• Dirty reads in SQL can lead to incorrect or inconsistent results and should be prevented through the use of transaction isolation levels. There are mainly four types of common concurrency problems: dirty read, lost read, non-repeatable read and phantom reads. 
• Dirty Reads - When a transaction is allowed to read a row that has been modified by another transaction that is not been committed yet that time Dirty Reads occurred. It is mainly occurred because of multiple transactions at a time which is not committed. 

Example: Table Record

Transaction: Transfer 10 from S Adam's account to Zee Young's account.

Input:
BEGIN TRY
  BEGIN TRANSACTION
    UPDATE Table SET Balance = Balance - 10 WHERE ID=1;
    UPDATE Table SET Balance = Balance + 10 WHERE ID='C';
  COMMIT TRANSACTION
  PRINT 'Committed'
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  PRINT 'Not Committed'
END CATCH

Output:
Not committed

By executing the above query the output will be "Not committed" because there is an error, there is no ID=C. So at that time if we want to execute another transaction with that row time Dirty Reads occur. There is no partial commitment if both the UPDATE query succeeds only then the output will be "Committed". Before Execution.

Table Record

After Execution:

Input:
BEGIN TRY
BEGIN TRANSACTION
UPDATE Table SET Balance = Balance - 10 WHERE ID=1;
UPDATE Table SET Balance = Balance + 10 WHERE ID='C';
COMMIT TRANSACTION
PRINT 'Committed'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Not Committed'
END CATCH
Output: 
(1row affected)
(0row affected)
Not Committed 

Note that if we put a valid ID first transaction result will come out as committed or 1 row affected but the 2nd one will not be affected.

Explanation: If we have a ticket booking system and One Customer is trying to book a ticket at that time an available number of the ticket is 10, before completing the payment, the Second Customer wants to book a ticket at that time this 2nd transaction will show the second customer that the number of the available tickets is 9. The twist is here if the first customer does not have sufficient funds in his debit card or in his wallet then the 1st transaction will Rollback, At that time 9 seats are available which is read by the 2nd transaction is Dirty Read. 

Example: Available ticket: For 1st customer

Step 1:

Input: Transaction 1
Select *from Bus_ticket; 

Output:

Step 2: Booking time for 1st customer

Input:  
--Transaction 1
        BEING Transaction
        UPDATE Bus_Ticket set Available_Seat=9
        WHERE ID=1
        
        --Payment for Transaction 1
        Waitfor Delay '00.00.30'
        Rollback transaction 

Available ticket: For the 2nd customer while the 1st customer is paying for the ticket.

Step 3: Input

Transaction 1

set transaction isolation level read uncommitted
Select *from Bus_ticket where ID=1; 

Output:

Note that during the payment of the 1st customer's 2nd transaction read 9 seat is available if somehow the 1st transaction Rollback then available seat 9 is Dirty read data. After the rollback of the 1st transaction available seat is 10 again. 2nd and 3rd steps happening at the same time. Actually available seat after rollback of transaction 1.

Advantages of Dirty Read

• Increased Concurrency: Dirty reads can increase the degree of concurrency in the database system, as transactions are allowed to read uncommitted data. This can lead to improved performance and throughput.
• Reduced Locking Overhead: Since dirty reads do not require locks, the overhead associated with acquiring and releasing locks can be reduced.
• Faster Response Time: Dirty reads can improve the response time of database queries, as transactions can read data without waiting for other transactions to commit.

Disadvantages of Dirty Read

• Inconsistent Data: Dirty Reads can lead to inconsistent and incorrect data, as transactions may be reading data that has been modified by other transactions that have not yet been committed. This can lead to errors and inaccuracies in the database.
• Unreliable Results: Dirty reads can lead to unreliable query results, as the data being read may change before the transaction is completed.
• Data Integrity Issues: Dirty reads can cause data integrity issues, as transactions may be reading data that is in an inconsistent or incomplete state.
• Hard to Debug: Dirty reads can be difficult to debug, as the data being read may be inconsistent or incomplete, making it hard to trace the source of errors.