Isolation Level for stored procedure SQL Server?

Question

I want to add Isolation level in my procedure and for that I wanted to confirm that which one is the correct format from below:

Attempt #1 - setting isolation level before calling the stored procedure:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

EXEC [sp_GetProductDetails] 'ABCD','2017-02-20T11:51:37.3178768'

Attempt #2 - setting isolation level inside the stored procedure:

CREATE PROCEDURE MySP AS
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   BEGIN TRAN 
       SELECT * FROM MyTable
       SELECT * FROM MyTable2
       SELECT * FROM MyTable3

       COMMIT TRAN  
END

Please suggest.

Illgotten

Answer 1

Both versions are "correct" - they just do different things:

• Your attempt #1 sets the isolation level for that database and connection - that means, the chosen isolation level will be used for any future statement - until you change the isolation level again

• Your attempt #2 sets the isolation level only INSIDE the stored procedure - so once the stored procedure is completed, the isolation level that existed on the database/connection level is restored again

So it really depends on what you want to do:

• set the isolation level to a different level in general for your current connection to this database? Any future statement will be run under this isolation level --> choose #1

• set the isolation level to a different setting for just a single stored procedure - regardless of what the connnection/database had before - then use #2

Ethnocentrism

Answer 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED This works at session level, you can set this up for your current session and run stored procedures. OR you can also use it inside stored procedure as the first statement to always set read uncommitted.

AT ISOLATION READ UNCOMMITTED This works at individual query levels. If there is a union operator then the it should be added at the end of last query.

Both of these cannot be used on below type of statemetns, the statements in proc should be SELECT or SELECT INTO or Searched DELETE, INSERT from full select, Searched Update.

• Subqueries
• INSERT statements

Semang

Answer 3

ISOLATION LEVEL READ COMMITTED is default ISOLATION for SQL database.

Attempt #2 is good practice to set ISOLATION LEVEL.

Reface