Transaction Isolation Level

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  April 1, 2013 at 4:12 pm

  #272298

  Hello Everyone

  I have a medium sized SSIS Package, with multiple steps, that call a single sproc each. I am working on a query that I am having to change the database Transaction Isolation Level approx 3/4 of the way thru the package steps.

  I alter the Transaction Isolation Level

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  Begin Tran BlahBlah

  Some Update Query is here

  Update Query Completes

  Commit Tran BlahBlah

  My question is: does the Isolation Level get set back to the database default after the commit, or after the package is complete?

  There are other processes that use some of the tables that the queries in the spocs have already used after I have changed the Isolation Level. I am thinking that I should set it back to the database default, so that the other processes can use the table that I just updated in the Update query that is between the Begin and Commit trans.

  Thank you in advance for all your assistance, comments and suggestions

  Andrew SQLDBA

• j.miner

  SSCrazy

  Points: 2264

  More actions

  April 2, 2013 at 9:53 am

  #1602869

  http://msdn.microsoft.com/en-us/library/ms173763.aspx

  It is SET at the connection level as stated in Books On line.

  By default SSIS tasks like [Execute SQL] or [Data Flow], open a connection upon start and close the connection upon completion.

  If you want to reuse the same connection for multiple tasks, set the RetainSameConnection property of connection manager to TRUE.

  Therefore, it up to you to define the scope of the SET when crafting your ETL solution.

  Good luck.

  🙂

  John Miner
  Crafty DBA
  www.craftydba.com