Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 8 down vote accepted

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.

share|improve this answer

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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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