How can I force a minimum isolation level in a procedure?

Question

How can I force a minimum isolation level in a procedure in mssql?

I can use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" inside my procedure to set the transaction-level. But this command will downgrade the isolation level if the caller/executor uses e.g. "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;" How can I force a minimum isolation level without downgrading a higher isolation level?

What happens if I commit the "nested" transaction in my procdure? Will the locks acquired by the nested transaction remain until the outer transaction (of the caller) is committed?

Answer 1

There are a couple of options for determining the transaction isolation level in SQL Server depending on whether you want to include read committed snapshot as separate from read committed. Once you know what isolation level your session is using, you can implement whatever logic you want. Something like

IF EXISTS( SELECT 1
             FROM sys.dm_exec_sessions 
            WHERE session_id = @@SPID
              AND transaction_isolation_level < 3 )
BEGIN
  set transaction isolation level repeatable read;
END;

Answer 2

How can I force a minimum isolation level without downgrading a higher isolation level?

You can indicate required TIL for every table directly in your query by using quary hints:

READCOMMITTED   
READUNCOMMITTED   
REPEATABLEREAD   
SERIALIZABLE  

More on query hints here: Hints (Transact-SQL) - Table

By using hints at quary level you don't alter TIL at the session level.

What happens if I commit the "nested" transaction in my procdure? Will the locks acquired by the nested transaction remain until the outer transaction (of the caller) is committed?

The only thing you'll get by doing commit of the nested transaction is decreasing @@trancount by one. Here you can find more on "nested" transactions: A SQL Server DBA myth a day: (26/30) nested transactions are real

No lock will be released.

But the locks will be different when using query hints. For example you'll have no range locks if you use other than serializable hint in your query.

You can even use nolock or readuncommitted in your query and no shared locks will be acquired even if session level TIL is still serializable