Dirty Read in SQL in DBMS

Before Going forward let us see some of the perquisites to better understand the topic:

Transaction Isolation Levels

As is common knowledge, a database must adhere to the ACID characteristics in order to preserve consistency. The degree to which transaction integrity is apparent to other users and systems depends on isolation, one of these four qualities (Atomicity, Consistency, Isolation, and Durability). It indicates that a transaction must occur in a system so that it is the sole transaction using the database system's resources.

The degree to which a transaction must be isolated from the data updates performed by any other transaction in the database system is determined by its isolation level. The phenomena listed below serve to determine a transaction isolation level:

A dirty read occurs when a transaction reads data that hasn't yet been committed, and is referred to as such. Let's take an example where Transaction 1 modifies a row but leaves it uncommitted, and Transaction 2 reads the changed row in the interim. Transaction 2 will have read data that is thought to have never existed if transaction 1 rolls back the modification.
When a transaction reads the same row twice and receives a different value each time, it is said to be performing a non-repeatable read. Consider the scenario where transaction T1 reads data. A different value will be returned if transaction T1 reads the same data again after another transaction T2 updated it and committed due to concurrency.
Phantom Read: Phantom Read happens when two identical queries are run, but the rows that each query returns are different. Assume, for instance, that transaction T1 obtains a set of rows that meet a set of search requirements. Now, Transaction T2 creates a few fresh rows that comply with Transaction T1's search criteria. Transaction T1 will get a different set of rows if it performs the statement that reads the rows again.

As the name indicates, a schedule is a procedure of setting up the transactions and carrying them out one at a time. Scheduling comes into play and the transactions are timed appropriately when there are numerous transactions running concurrently and the order of operation needs to be specified so that the operations do not overlap. Concurrency Control (Introduction) and Transaction Isolation Levels in DBMS articles cover the fundamentals of transactions and schedules, respectively. We'll talk about different timetables here.

Serial Schedules: A serial schedule is one in which no transaction begins until a running transaction has concluded. These schedules perform transactions without interleaving them.
Non-Serial Schedule: This style of scheduling involves the interleaving of the processes of various transactions. This might cause the concurrency problem to worsen. The transactions are carried out in a non-serial fashion while maintaining the accuracy and consistency of the serial timetable. In the non-serial schedule, in contrast to the serial schedule, where one transaction must wait for another to finish all of its operations, the subsequent transaction can start without waiting for the prior transaction to finish. Such a schedule does not allow for any concurrent transaction benefits. It comes in two varieties: the serializable schedule and the non-serializable schedule.

There are two categories of the Non-Serial Schedule: Serializable and Non-Serializable.

a. Serializable

To keep the database consistent, it must be serializable. It is mostly utilised in non-serial scheduling to determine whether or not the scheduling will cause any inconsistencies. A serial schedule, on the other hand, does not require serializability because it only follows a transaction once the one before it has finished. Only when the non-serial schedule is comparable to the serial schedules for a n number of transactions is it considered to be a serializable schedule. As concurrency is permitted in this situation, many transactions may run simultaneously. A serializable schedule aids in increasing CPU performance and resource usage. They come in two varieties:

i. Conflict Serializable:

If a schedule can be changed into a serial schedule by swapping out non-conflicting processes, it is said to be conflict serializable. If all requirements are met, two operations are said to be in conflict.

They are associated with certain transactions.
They work with the same piece of data.
One of them must be a write operation.

ii. View Serializable:

If a Schedule is view equivalent to a serial schedule, it is said to be view serializable (no overlapping transactions). A conflict schedule is view serializable, but it is not conflict serializable if the serializability incorporates blind writes.

b. Non- Serializable

Recoverable and Non-recoverable Schedules are the two categories inside the non-serializable schedule.

Recoverable Schedule: Transactions in recoverable schedules only commit when all transactions whose modifications they read have already committed. To put it another way, the commit of Tj must happen after the commit of Ti if some transaction Tj is reading a value that has been changed or written by some other transaction Ti.

Three different recoverable schedule kinds are possible:

i. Cascading schedule:

Cascading rollback or cascading abort scheduling is used when a failure in one transaction causes the rolling back or aborting of other dependent transactions.

ii. Cascadeless Schedule:

Transactions in cascadeless schedules read data only after all transactions whose modifications they intend to view have committed. prevents a single transaction abort from resulting in several transaction rollbacks. Preventing a transaction from reading uncommitted modifications from another transaction in the same schedule is one way to stop cascading aborts.

When a transaction reads data that has been updated but not yet committed by another transaction, it is known as a "dirty read" in SQL. In other words, reading uncommitted data from one transaction into another might produce inaccurate or inconsistent outcomes.

This can happen when a transaction updates a data item but fails to commit the changes as a result of a network fault, system malfunction, or other problem. A dirty read can occur if another transaction reads the updated data before the first transaction has a chance to commit.

Take the two transactions T1 and T2, for instance. T1 initiates a transaction and alters a row in a table, but it does not finish it. Before T1 commits its modifications, T2 tries to read the same row in the meanwhile. T2 will do a dirty read if the uncommitted data is made available to it, which might produce unreliable or inconsistent results.

SQL has many levels of transaction isolation, which define how transactions should be segregated from one another and help stop dirty reads. The levels of isolation include:

Read uncommitted: Transactions are permitted to access uncommitted data from other transactions at this level, which might result in dirty reads.

Read committed: Transactions are only permitted to read committed data at this level, preventing dirty reads.

Repeatable read: This level guarantees that a transaction always reads the same data for a particular query, even if other transactions alter the data in the meantime. It also prohibits dirty reads.

The highest level of isolation is provided by serializability, which guarantees that transactions are carried out serially and guards against anomalies like dirty reads.

Transaction isolation levels should be used to avoid dirty reads in SQL, which might provide erroneous or inconsistent results.

Common concurrency issues often fall into one of four categories: unclean reads, lost reads, non-repeatable reads, and phantom reads.

A dirty read occurs when a transaction is permitted to read a row that has been updated by another transaction but has not yet been committed. Several uncommitted transactions occurring at once are mostly to blame.

Example - Table - Record

ID	Cus_Name	Balance
1	S Adam	100
2	Zee Young	150

Transfer of $10 from the S Adam account to the Zee Young 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

Doing the aforementioned query will result in the response "Not committed" since there is a problem?there is no ID=C. Dirty Reads take place then if we wish to use that row in another transaction. There is no partial commitment if both the UPDATE query succeed only then the output will be "Committed". Before Execution: Table - Record

ID	Cus_Name	Balance
1	S Adam	100
2	Zee Young	150
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

Be aware that the first transaction result will appear as committed or one row affected if a valid ID is entered, but the second transaction result won't be impacted. Explanation: In the event that we have a ticket reservation system and one customer attempts to reserve a ticket at a time when there are 10 tickets available, this second transaction will inform the second consumer that there are 9 seats left to be reserved at that time. The surprise is that the first transactions will rollback if the first client does not have enough money on his debit card or in his wallet at that point, when there are 9 seats remaining, which is indicated by the second transaction.

Example: For the first client, available ticket

1st Step -

Input:
-- Transaction 1
Select *from Bus_ticket; 
Output:
ID	Bus_Name	Available_Seat
1	KA0017	10

2nd Step - 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

3rd Step -

Input:  
-- Transaction 1
set transaction isolation level read uncommitted
Select *from Bus_ticket where ID=1;
Output:
ID	Bus_Name	Available_Seat
1	KA0017	9

Be aware that nine seats are available if the first transaction fails for any reason during the payment of the first consumer. Rollback, then read dirty data from seat 9 that is accessible. After the first transaction's rollback, there are 10 seats available once again. Third and second steps are occurring simultaneously. After the reversal of transaction 1, the actual seat that is accessible is:

Next TopicER Diagram for Student Management System

← prev next →