SQL Server: READ COMMITTED Isolation Level with the READ_COMMITTED_SNAPSHOT option

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 this READ COMMITTED isolation level:

First, create a table with sample data:

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

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

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

What is READ_COMMITTED_SNAPSHOT option?

This is very important and READ COMMITTED isolation level depends on this option ON / OFF setting.
It is by default OFF.

ALTER command to switch it ON / OFF:

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 old version of data and there is no any share 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 same version of the row can be updated by two different transactions.

Now test the READ_COMMITTED_SNAPSHOT ON:

ALTER command to ON a READ_COMMITTED_SNAPSHOT:

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

During this 15 second delay, 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 this 15 second delay, 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.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz