Transaction is an essential part of the database. No database design is complete without handing transaction behaviour correctly. Transaction defines that all the set of operations has to succeed or fail as a unit. Partial success or failure results in the unexpected output. Transaction is needed to maintain integrity in the database.
SQL server includes Isolation levels to control the concurrency within a database. Generally, each Isolation level is applied as per the database design needs and expected concurrency. Higher isolation level (Serializable) reduces concurrency effects. In addition to this, lower isolation level (Read Uncommitted) increases concurrency problem which could make database inconsistent.
Isolation levels come with limitation and problems which includes the following.
1. Dirty Read Problem
2. Non Repeatable Read
3. Phantom Read
In this tech-recipes post, we will understand and do a walk-through of Dirty Read problem in SQL Server.
Points To Ponder – Dirty Read Problem
1.When a transaction reads modified data that has not been committed by another transaction. Therefore, we read uncommitted or dirty data which might get rollback.
2.No shared locks are held to block other transactions from reading the modifying data.
3.Dirty read benefits multiple users to access data but it can cause concurrency issues. Database state could become inconsistent.
4.Dirty read problem is associated with the Read Uncommitted isolation level. This can be solved by upgrading the isolation level to Read Committed.
5.NOLOCK hint is similar to reading uncommitted data at the table level.
6.Dirty read problem arises only when a transaction tries to read uncommitted data. Otherwise, this approach speed-up queries avoiding the overhead of locks.
Difference Between NOLOCK & Read Uncommitted
1.Under the wrap, NOLOCK & Read Uncommitted are similar and both can read dirty data.
2.With NOLOCK, we apply this hint at table level whereas read uncommitted isolation level is applied to the entire session or transaction. If 3 tables joined together in a query, NOLOCK can be applied to individual tables. If all the tables need NOLOCK hint then we can include them under Read Uncommitted isolation level.
Dirty Read – Example
Following a classic example, Transaction (A) updates the salary for an employee from 100 to 200. However, transaction (A) is not yet committed. In the meantime, transaction (B) reads the uncommitted data, it reads salary as 200. Suddenly, something went wrong and transaction (A) has been rollback. Thus, salary has been set to 100, back to its original value.
In below example, on the left-hand side window, we have started Transaction A. Furthermore, updating salary to 200 for Id (1) from 100. Added WAITFOR DELAY option to wait for 5 seconds before doing a rollback.
On the right-hand side window. Requesting data from employee table for Id (1). Both the queries trying to read the uncommitted data (dirty data). Before rollback is issued, queries read salary as (200) dirty data. After the update statement issued but transaction is not committed. Once the transaction rollbacks, rerunning the queries on the right side window reads salary as (100).
As a result, this example shows how to read uncommitted or dirty data in SQL Server.
Transaction – In Progress – Dirty Data
Transaction – Rollback
Summary
As a result, in this post we have learned to understand the Dirty read problem in SQL Server. Read uncommitted isolation level is similar to dirty read problem. To overcome dirty read, we can use read committed isolation level. If you like this post, you may like to read through Tech-Recipes database archives to learn more good stuff.
