SQL Server: READ COMMITTED Isolation Level with the READ_COMMITTED_SNAPSHOT option

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am going to share an important note about the READ COMMITTED isolation level and how it works with the READ_COMMITTED_SNAPSHOT option of SQL Server.

The READ COMMITTED is the default isolation level of SQL Server, and it prevents the dirty reads.
Your SELECT statements always returns committed data.

It issues shared lock against the data where data are updating or having an exclusive lock so for selecting those data you have to wait to complete that transaction.

Now test the READ COMMITTED isolation level:

First, create a table with sample data:

Open a new query window or session and executing this script:

During the delay of 15 seconds, Open a new query window or session and try to SELECT that table:

The result is, you can not SELECT your data until the UPDATE is not completed. As we have set delay of 15 seconds, so you have to wait for 15 seconds.

What is READ_COMMITTED_SNAPSHOT option?

READ COMMITTED isolation level depends on option ON / OFF setting.
It is by default OFF.

ALTER command to switch it to ON / OFF:

1 2 3 4 5

-- Switch to ON ALTER DATABASE Database_Name SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE   -- Switch to OFF ALTER DATABASE Database_Name SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

READ_COMMITTED_SNAPSHOT OFF:

This is the default setting and issues the shared locks to prevent other transaction when we are reading or updating a table data.
Above small demonstration represents the behaviour of this default setting.

READ_COMMITTED_SNAPSHOT ON:

When the READ_COMMITTED_SNAPSHOT database option is ON, read committed isolation uses row versioning.

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. When data is updating, you can still read the old version of data, and there is no any shared lock, but you cannot update that same data until the running update is not finished.

This is very different than Snapshot Isolation level.
It consumes less tempdb space than snapshot isolation.
In the Snapshot Isolation level, the same data causes an update conflict because two different transactions can update the same version of the row.

Now, test the READ_COMMITTED_SNAPSHOT ON:

ALTER command to ON a READ_COMMITTED_SNAPSHOT:

Open a new query window or session and executing below script:

During the delay of 15 seconds, Open a new query window or session and try to SELECT this table:

The result is, You can SELECT an old version of the data.

During the delay of 15 seconds, Open a new query window or session and try to UPDATE same row:

The result is, You cannot perform an UPDATE on the same data.