Sql-server – Difference in location of transaction isolation level declaration – inside/ouside a transaction

isolation-levelsql serverstored-procedurestransaction

I have been looking though some stored procedured on our system today when I saw the following structure:

(pseudocode indicative of actual SP, server is using the default isolation level):

CREATE PROCEDURE ...
AS
BEGIN
  IF NOT EXISTS(SELECT ...) 
  BEGIN
    RETURN; 
  END

  BEGIN TRAN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    DML...
    DML...
    DML...
  COMMIT
END

I have not seen the transaction isolation level being declared inside a transaction boundary and was wondering what the effect of it was?

Is the isolation level restored once the transaction is committed (so if there were additional statements after the commit – what would their isolation level be)?

In this case, I assume there wouldn't be a difference if the isolation level were to be declared just before the transaction started. Is that the case?

Mostly, I was somewhat startled to see this, as I have only ever seen transaction isolation declared outside of a transaction, not within in and was wondering – why? What are the benefits of doing so.

Best Answer

Below are relevant excerpts from the SET TRANSACTION ISOLATION LEVEL documentation.

Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

It goes on to say:

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Note that there is no mention of BEGIN TRANSACTION, COMMIT, etc. These transaction control statements do not affect the current session transaction isolation level.

I don't see a functional difference with specifying the transaction isolation level immediately after the BEGIN TRAN. Perhaps the developer wanted to make sure the level was obvious to the reader and thought the best place was at the transaction boundary rather than having to view the beginning of the proc code.

Related Question