READ COMMITTED isolation level in SQL server

READ COMMITTED is the default isolation level in SQL Server. It ensures that a transaction reads only committed data and prevents Dirty Reads. SQL Server achieves this by acquiring Shared Locks during reads and respecting Exclusive Locks held by writers. If a row is being modified by another uncommitted transaction, the reader waits until that transaction commits or rolls back. However, READ COMMITTED does not hold Shared Locks for the entire transaction, so Non-Repeatable Reads and Phantom Reads can still occur. The main drawback is blocking between readers and writers. To reduce blocking while maintaining committed reads, many organizations enable READ_COMMITTED_SNAPSHOT (RCSI), which uses row versioning in TempDB instead of locking for read operations. This provides better concurrency without allowing Dirty Reads.

Enable Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This is enabled on session level. Other session uses the default isolation level which has been set on database. To check the current session isolation level we can use the DBCC command

DBCC USEROPTIONS;

Let’s see the demo

Dirty Read:

Session 1

Updating a record

BEGIN TRAN; UPDATE Employee SET Salary = 70000 WHERE EmployeeID= 1; --commit

Transaction is not yet committed.

Session 2

Selecting the record

Once we can commit the session 1 we will get the value.

After commit we are getting the value. READ COMMITTED prevents Dirty Reads but does not guarantee a consistent snapshot throughout the transaction.

Non-Repeatable Read:

Session 1

Reading the data

Trans not yet committed.

Session 2

Again, we running the session 1 select query.

Now we are getting the different result for the same query in the same transaction. This is called Non-Repeatable Read.

Phantom Read:

Session 1

It returning 2 rows. Transaction is still open. In the other session we are inserting a record into this table.

Again, we are running the session 1 select query.

Now we are getting 3 records. A new row appeared. This is called Phantom Read.

Advantages

Ø  Prevents Dirty Reads

Ø  Data is committed and trustworthy

Ø  Default SQL Server behavior

Ø  Suitable for most OLTP applications

Disadvantages

Ø  Readers can block writers

Ø  Writers can block readers

Ø  Blocking chains possible

Ø  Deadlocks possible

Ø  Non-Repeatable Reads possible

Ø  Phantom Reads possible