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?
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 only takes a minute to sign up.Sign up to join this community
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;
NOLOCK hint included, no locks are taken by the
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.