3

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)?

8

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.

8

Your query is reporting the session-level setting of transaction isolation level, which is set to serializable.

Using a NOLOCK hint (or its synonym READUNCOMMITTED) overrides the session isolation level for access to the specific object (table in this case) the hint is specified against.

So, the transaction is still running under serializable isolation, but access to the table PS will be performed with READUNCOMMITTED isolation semantics.

Documentation:

Related reading:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.