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.
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);
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