SET TRANSACTION ISOLATION LEVEL

This statement is used to set the isolation level for either a connection or a stored procedure. The most typical use I’ve seen is at the top of a stored procedure in order to avoid locking and deadlocks. This is a cleaner alternative to using WITH (NOLOCK) hints on tables. If you set the isolation level within a procedure, the isolation level will revert to its previous level once the procedure finishes.

The syntax is:

[cc lang=”sql”]
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
[/cc]

The available options are:

• READ UNCOMMITTED – Allows dirty reads, meaning data can be read from a transaction that is not yet complete.
• READ COMMITTED – Only reads data from transactions that have already been committed.  Current transactions must wait for transactions referencing common resources to finish.
• REPEATABLE READ – Data that is being read is exclusively locked until the transaction completes.
• SNAPSHOT – The default for Oracle.  This level allows all data to be read without using locks by maintaining a snapshot of all the data being modified in “versioning” tables.  This is the only isolation level not lock based.
• SERIALIZABLE – Data that is being read is exclusive locked and inserts are prevented within this range until the transaction completes.

Snapshot isolation is not available unless enabled at the database level. It’s recommended to know all implications prior to enabling.

Example

This statement needs to be placed before the transactions who isolation you want to modify.

[cc lang=”sql”]
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
firstname
,salary
FROM dbo.employee e
JOIN salary s
ON s.employeeid = s.employeeid;
[/cc]

The comparable method using hints would be:
[cc lang=”sql”]
SELECT
firstname
,salary
FROM dbo.employee e WITH (nolock)
JOIN salary s WITH (nolock)
ON s.employeeid = s.employeeid;
[/cc]

4 comments

Bpaul 26 Sep 2013 at 1:31 pm

Does the isolation level apply to subqueries too?

For example

select
name,
dob,
salary = (select sum(salary) + sum(allowances) from emp where emp_id = @emp_id)
from emp_base
where emp_id = @emp_id

Reply to this comment

Derek Dieter 29 Sep 2013 at 4:41 pm

Yes it does Bpaul

Reply to this comment

Yasser 20 Jul 2014 at 3:01 am

Excellent article!My SQL serervs recently acquired a new behavior that looks like some sort of deadlock on tempdb. It causes my application performance to come to a near standstill. When looking for deadlocks I see all my exec requests with a wait of page%latch_ex’ or page%latch_sh’ and the resource they’re waiting on is 2:1:103, which according to DBCC PAGE (2,1,103) is a system object on tempdb. Any idea what would cause this or how to look further into it?

Reply to this comment

Pradeepa 06 May 2013 at 10:21 am

Really good explanation………..!

Reply to this comment