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.

score 22 · Accepted Answer · answered Feb 21 '17 at 07:53

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

score 0 · Answer 2 · edited Aug 16 '17 at 08:09

0

ISOLATION LEVEL READ COMMITTED is default ISOLATION for SQL database.

Attempt #2 is good practice to set ISOLATION LEVEL.

edited Aug 16 '17 at 08:09

user2771704

5,994
6
37
38

answered Aug 07 '17 at 05:01

Tarun Kumar

26
4

Isolation Level for stored procedure SQL Server?

2 Answers2