HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Why is WITH NOLOCK running as Serializable

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whywithserializablerunningnolock

Problem

I ran the following query:

SELECT session_id,CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
Where transaction_isolation_level = 4


and then:

DBCC INPUTBUFFER(157)


where 157 was one of the prior session_ids, to see the statement of one of the results from query no. 1.

It showed the following:

(@ID uniqueidentifier) SELECT * FROM PS WITH (NOLOCK) WHERE ID = @ID

How can the statement using WITH (NOLOCK) run with isolation level Serializable? Is there anything "overriding" the With (NOLOCK)?

Solution

Lock hints are orthogonal to isolation level. While they address similar concerns, adding a lock hint does not change the isolation level. Your transaction will still be a 'serializable' transaction. Of course, the lock hint make the query operation itself violate the transaction serializability, but you are looking at a property of the transaction.

Context

StackExchange Database Administrators Q#126969, answer score: 9

Revisions (0)

No revisions yet.