5

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?

2

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;


SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

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

    EXEC sp_lock;

ROLLBACK;

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.

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.