SQL Server Performance

Switching isolation level within stored procedure

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by anonymous2009, Nov 9, 2011.

  1. anonymous2009 New Member

    The database I'm working on is in SQL SERVER 2008 and has SNAPSHOT isolation ON (See below).

    This was set by executing the following:
    ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ON


    SELECT snapshot_isolation_state_desc from sys.databases where name='testdb'
    --Output:
    snapshot_isolation_state_desc
    ON

    My question is, for just one stored procedure I want the isolation level to be set to READ COMMITTED.
    Should I mention the following within that stored procedure?
    SET TRANSACTION ISOLATION LEVEL READ COMITTED


    Question 1:
    Will this overwrite the SNAPSHOT isolation level that was set at the whole db level?

    Question 2:
    Will the READ COMMITTED isolation level only apply to that stored procedure?

    Question 3:
    Once the stored procedure completes, will the isolation level revert back to SNAPSHOT isolation level?

    Thanks!
  2. anonymous2009 New Member

    1. Just because the database tempdb has "ALLOW_SNAPSHOT_ISOLATION" as ON, does it mean that for the whole database the isolation level SNAPSHOT will be set?
    (or)
    ALTER DATABASE tempdb SET ALLOW_SNAPSHOT_ISOLATION ON is just a prerequisite incase the isolation level must be set to SNAPSHOT for some transaction in tempdb database?

    2. With "ALLOW_SNAPSHOT_ISOLATION" as ON, when no isolation level is set explicitly within a stored procedure (or) a transaction, is it by default "READ COMMITTED"?

    Thanks!
  3. FrankKalis Moderator

    1. No, it doesn't affect the database setting.
    2+3. Yes, it is only valid within that procedure and will revert to the default database level once the procedure completes.
  4. FrankKalis Moderator

    Just to avoid confusion with my previous answer. That was geared towards your first 3 questions.
    1. ALLOW_SNAPSHOT_ISOLATION ON is just the prerequisite before you actually can use that level.
    2. Yes, the default is always READ COMMITTED.
  5. anonymous2009 New Member

    Great!
    Thanks FrankKalis.

Share This Page