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.

Example:

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.

Sources:

https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/

Last modified: July 17, 2020

Author

Comments

Write a Reply or Comment