Can you change a transactions Isolation Level during a transaction? RRS feed

  • Question

  • Hi,

    The question, for those who don't want the details: How do you change a Transaction Isolation Level in the middle of a transaction?


    The details:

    I've got a ADO.NET connection and transaction on which I execute 3 stored procedures. Their purpose is to insert a parent record and n child records. We don't want the parent record committed if a single child record fails to insert. It's a pretty long amount of time for a transaction to remain open so if anyone has a better practice on that alone, please let me know. We were experiencing deadlocking because this is called from a web service. The procedures should run fine under the read uncommitted isolation level, but the dead lock occurs specifically in the last stored proc so I am wanting to change it to read uncommitted before executing the last proc just to be safe.

    • Edited by _xr280xr_ Monday, November 1, 2010 10:14 PM Typo in the title
    Monday, November 1, 2010 10:08 PM


All replies

  • Isolation level can be changed at any time (except for one case). At least for the SQL Server database. You can find some information here


    Val Mazur (MVP)
    Tuesday, November 2, 2010 10:13 AM
  • Thanks Val. I know it can be changed, but the question is how do you do it using a SqlTransaction? The link above shows how to do so using SQL, but this is an ADO.NET Managed Providers question. The IsolationLevel property doesn't have a setter. Do I execute a command with the "SET TRANSACTION ISOLATION LEVEL ..." text to change it? If so, will my transaction object update accordingly to reflect that change? Thanks.

    Tuesday, November 2, 2010 3:42 PM
  • Yes, you would need to execute SQL statement, but you need to be sure it executes against same database connection. I am not really sure how you would decide in your code when to change it, so why do not you want to set proper isolation level at the beginning?
    Val Mazur (MVP)
    Tuesday, November 2, 2010 10:12 PM