Isolation Levels controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.The isolation level that your transaction runs in determines how sensitive your application is to changes other users' transactions make, and consequently, how long your transaction must hold locks to protect against these changes.
ANSI 92 standard isolation levels are:
- read uncommitted
- read commited
- repeatable read
Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, whether or not that data has been committed. For example, although another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data, your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back his or her transaction, so logically, those changes never occurred.
Eg. Use the SQL Server 7.0 Query Analyzer, and start two separate connections. Use the pubs database in each one. In the first connection, begin a transaction, but don't commit it:
SET col2 = 'New Value'
Now, use the second connection, and change your isolation level before trying to access the same table.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM ISOLATION_TEST
All the values in col1 are 0, even though the transaction in the first connection has not committed yet. In fact, the transaction might never commit. If you took some action based on the fact that all the values are 0, you could regret it if the changes turned out not to be permanent. Back in the first connection, roll back the transaction:
Now rerun the SELECT statement in the second connection to see that all the values are back to what they were before.
Committed Read is SQL Server's default isolation level. It ensures that an operation will never read data another application has changed but not yet committed. Because you can never read uncommitted data, if a transaction running with Committed Read isolation revisits data, that data might have changed, or new rows might appear that meet the criteria of the original query. Rows that appear in this way are called phantoms.
Eg: open two new connections in query analyzer using pubs again. In the first connection, run the following batch:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT AVG(col1) from ISOLATION_TEST
In the second connection, update the table:
SET col1 = 500 WHERE col1 = 50
Notice that the update is successful, even though the first connection is still inside a transaction.
Go back to the first connection and run the same SELECT statement:
SELECT AVG(col1) from ISOLATION_TEST
The average value is now different. The default isolation level does not prevent another connection from changing data you have read. Because you are not guaranteed to see the same data if you rerun the SELECT within the transaction, the read operations are not guaranteed to be repeatable.
The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won't pick up any changes to data values that another user's transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.
To see Repeatable Read behavior, close all the connections, and open two new ones in pubs. Issue the same two queries as above, but this time, have the first connection SET ISOLATION LEVEL REPEATABLE READ.
The second connection will have to use a slightly different update statement, because the value of 50 for col1 no longer exists:
SET col1 = 5000 WHERE col1 = 500
This update will block when it tries to update the ISOLATION_TEST table. And the first connection will get the same result when it reissues its original SELECT. Preventing nonrepeatable reads is a desirable safeguard, but it comes at a price. The cost of this extra safeguard is that all the shared locks in a transaction must be held until the completion (COMMIT or ROLLBACK) of the transaction.
The Serializable isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim. In other words, you won't see phantoms if the same query is issued twice within a transaction.
You pay a price to prevent phantoms. In addition to locking all the data you have read, enforcing the Serializable isolation level requires that SQL Server also lock data that doesn't exist! The Serializable level gets its name from the fact that running multiple serializable transactions at the same time is the equivalent of running them one at a time—that is, serially—regardless of sequence.
New Transaction Isolation Levels in SQL Server 2005
Serializable Isolation Level
The Snapshot Isolation Level works with Row Versioning technology. Whenever the transaction requires a modification for a record, SQL Server first stores the consistence version of the record in the tempdb. If another transaction that runs under Snapshot Isolation Level requires the same record, it can be taken from the version store. This Isolation Level prevents all concurrency related problems just like Serializable Isolation Level, in addition to that it allows multiple updates for same resource by different transactions concurrently.
Since there is a performance impact with Snapshot Isolation Level it has been turned off by default. The impact is explained below with the sample. You can enable it by altering the database
Read Committed Snapshot Isolation Level
This is the new implementation of the Read Committed Isolation Level. It has to be set not at session/connection level but database level. The only different between Read Committed and Read Committed Snapshot is, Read Committed Snapshot is Optimistic whereas Read Committed is Pessimistic. The Read Committed Snapshot differs from Snapshot in two ways; Unlike Snapshot, it always returns latest consistence version and no conflict detection.