patternsqlMinor
Why is WITH NOLOCK running as Serializable
Viewed 0 times
whywithserializablerunningnolock
Problem
I ran the following query:
and then:
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:
(
How can the statement using
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 = 4and 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.