SET TRANSACTION ISOLATION LEVEL
-
Posted on January 29, 2012 by Derek Dieter
-
6
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.
![](https://sqlserverplanet.com/wp-content/themes/sqlserverplanet/images/ico9.gif)
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]
- Comments (RSS)
- Trackback
- Permalink
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
Yes it does Bpaul
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?