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. It's 100% free, no registration required.

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

Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock.

Does the nolock become invalid because the isolation level has already been set?

share|improve this question

The table hint in the query takes precedence, as I would have expected.

Here's my test script:

CREATE TABLE t1 (a int);

INSERT INTO t1(a) SELECT TOP 1000 message_id FROM sys.messages;



    SELECT * FROM t1 WITH(NOLOCK); /* Try it with and without the hint */

    EXEC sp_lock;


With the NOLOCK hint included, no locks are taken by the SELECT statement.

In other words, setting the isolation level on the transaction makes that isolation level the default within the transaction, but that default can still be overridden by table hints, as I demonstrated.

share|improve this answer

Your Answer


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.