2

Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP? I just noticed some changes that a dev made in the SP recently.

4

If you have stored procedure that starts with:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

And then you have a SELECT query like this:

SELECT * FROM dbo.MyTable mt WITH (NOLOCK);

Then the SELECT query 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):

All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

As far as performance, using NOLOCK will 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

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.