SET TRANSACTION ISOLATION LEVEL READ COMMITTED will cause deadlocks if the table is very large without indexes.
Having the query perform a table/index scan with the read committed option practically “locks” the table.
The impact can be minimized if the procedures access the data using the correct index so it does a table/index seek, therefore it only locks that row for a very short time, allowing other processes to keep reading with almost no impact.
CREATE PROCEDURE [dbo].[uspGetSomething] @value INT AS BEGIN SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT ON BEGIN TRY SELECT COUNT(*) FROM tbTable01 WHERE Col01 = @value END TRY
Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.