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
- 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
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.
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.
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.
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.