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.
The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:
CREATE TABLE table_to_be_updated (
id serial PRIMARY KEY,
other_column text,
column_changing text
);
INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');
Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):
| <-- BEGIN;
|
|
| UPDATE table_to_be_updated
BEGIN; -----------------------> | SET column_changing = 'new_value'
| WHERE
| other_column = 'value' AND
| column_changing = 'old_value';
|
|
SELECT column_changing -------> | -- update not yet committed
FROM table_to_be_updated |
WHERE other_column = 'value'; | <-- COMMIT;
|
|
SELECT column_changing -------> |
FROM table_to_be_updated |
WHERE other_column = 'value'; |
|
|
COMMIT; ----------------------> |
Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:
SET transaction ISOLATION LEVEL REPEATABLE READ;
(The command must be the first statement in a transaction.)
Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.
Best Answer
If you have stored procedure that starts with:
And then you have a
SELECTquery like this:Then the
SELECTquery will use the READ UNCOMMITED isolation level* - in other words, the NOLOCK will override the SET statement. This is documented in SET TRANSACTION ISOLATION LEVEL (Transact-SQL):As far as performance, using
NOLOCKwill ignore locks taken by other queries, and take / release locks more quickly, so you will likely experience less blocking. However, this is at the expense of the correctness of the results.* Since this is a table-level hint, the hint would need to be applied to each table involved in the SELECT query in order to be functionally equivalent to using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED