none
Change IsolationLevel during middle of Transaction? RRS feed

  • Question

  •  

    http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx says the following:

     

    The IsolationLevel remains in effect until explicitly changed, but it can be changed at any time. The new value is used at execution time, not parse time. If changed during a transaction, the expected behavior of the server is to apply the new locking level to all statements remaining.

     

    How can I change the IsolationLevel during the middle of a transaction (e.g. after calling BeginTransaction())? The Isolation property is ReadOnly but the documentation leads me to believe this is possible. Is it?

     

    Thanks,

     

    Drew

    Wednesday, September 26, 2007 6:29 PM

Answers

  • That means you have to control the nesting of your transactions because I don't think ADO.NET will do that for you, I have covered the topic in the thread below and read the links I posted in the thread below because it can get complicated.  A database transaction is a unit of work changing isolation level during nested transaction means you could be passing a durable transaction to none durable transaction.  The reason it is the isolation level that guarantee data integrity.  Hope this helps.


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2118320&SiteID=1

    Wednesday, September 26, 2007 8:07 PM

All replies

  • That means you have to control the nesting of your transactions because I don't think ADO.NET will do that for you, I have covered the topic in the thread below and read the links I posted in the thread below because it can get complicated.  A database transaction is a unit of work changing isolation level during nested transaction means you could be passing a durable transaction to none durable transaction.  The reason it is the isolation level that guarantee data integrity.  Hope this helps.


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2118320&SiteID=1

    Wednesday, September 26, 2007 8:07 PM
  • A link should never be marked as an answer. Once the link is broken, the thread no longer has an answer. Can someone summarize the answer; caveats aside, what do you have to do to change the Isolation Level mid-transaction?
    Monday, November 1, 2010 9:59 PM
  • A link should never be marked as an answer. Once the link is broken, the thread no longer has an answer. Can someone summarize the answer; caveats aside, what do you have to do to change the Isolation Level mid-transaction?

    The link now works but if you are not using .Net System.TransactionScope in SQL Server 2008 and up the thread in that link may not relevant to you because Microsoft implemented nesting at the point of connection.  That means you can change ADO.NET and most RDBMS transaction isolation levels without issues because both are unit of work transactions that does not require resource managers.  I am assuming you know transaction SavePoints is required in unit of work transactions nesting.
    MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Wednesday, February 16, 2011 11:41 PM