T-SQL isolation levels

Isolation levels determines how data is read and written when the database is accessed by multiple processes. There are six isolation levels, listed below in rising order of locking.

  • READ UNCOMMITTED or NOLOCK
  • READ COMMITTED (default in SQL Server)
  • REPEATABLE READ or HOLDLOCK
  • SERIALIZABE
  • SNAPSHOT
  • READ COMMITED SNAPSHOT

The isolation level is set for the active session by using this command:

SET TRANSACTION ISOLATION LEVEL <level>

or you can use a table hint for a single query:

SELECT <columns> FROM <table> WITH (<level>)

You can only set the isolation level for queries, not for insert, update or delete. SQL server handles the locking for these operations by itself, but the isolation level of concurrently running queries can affect the kind of locks taken when running these commands.

Note that the isolation levels are written in one word when used with SELECT. So it is either

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or

SELECTFROM

WITH (READUNCOMMITED)

Below I describe the isolation levels.

READ UNCOMMITTED / NOLOCK

This is the lowest isolation level. The query doesn’t ask for a shared lock and thus can’t be in conflict with somebody else holding an exclusive lock. This means we will always be allowed to read, even uncommitted changes – this is also known as dirty reads, because what you read might be rolled back by another transaction.

I find READ UNCOMMITTED usefull when reading from tables where I know the data is never modified but only added to. A good example of this is if you use a table for logging some kind of events. Logging by its very nature only adds new data.

READ COMMITTED

This is the default isolation level in SQL Server. This guarantees that you can only read data that is committed. If somebody else is modifying og inserting data that match your query, your query will have to wait for that transaction to be committed before a query result can be returned. This is ensured by requesting a shared lock for the data.

The lock is released as soon as the query returns its data. This means that the data can be changed by somebody else if you request them again – even in the same transaction.

REPEATABLE READ / HOLDLOCK

This isolation level have the same behaviour as READ COMMITTED, but with the added bonus that the lock is held until the end of the transaction – so nobody can change the data under your feed. The downside is of corse that the processes wanting to modifying the data have to wait until your transaction is ended.

SERIALIZABLE

This is even stronger than REPEATABLE READ. REPEATBLE READ guarantees that data does no change under your feed, but it does not prevent new data to be inserted. SERIALIZABLE will prevent new data that match your query from being inserted, so an even higher level of consistency is achieved.

SNAPSHOT

Under the SNAPSHOT isolation level the last committed version of the data is returned. This is achieved by storing a copy of the data in tempdb. It is not needed to wait for a shared lock as the data is immediately available in the correct version. The downside is a performance penalty when writing to the affected tables.

In order to use SNAPSHOT isolation levels you need to activate it at the database level with this command:

ALTER DATABASE <database> SET ALLOW_SNAPSHOT_ISOLATION ON

In order to do this you need the right permissions to alter the database.

The SNAPSHOT isolation level behaves the same as the SERIALIZABLE isolation level in terms of data consistency.

READ COMMITTED SNAPSHOT

As above but with behavior like READ COMMITTED in terms of data consistency.

Advertisements

About Lund

Owner of iCodeIT, a software consulting company. I am primarily working the .NET development and architecture.
This entry was posted in MSSQL Server, SQL Server, T-SQL and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s