none
Default Isolation level for ADO.Net & SQL Server RRS feed

  • Question

  • Hi. Does anyone know what is the default isolation level if I access an SQL server DB from my ADO.Net app, with a simple connection , Command and DataAdapter.Fill()?

    without using any explicit transaction manager? no SystemTransaction or EnterpriseServices? simply sending a single SQL Command to the DB? is the Isolation level the Default I've set from within the SQL Server?

    thank you very much

    Thursday, April 5, 2012 1:59 PM

Answers

  • You're welcome, Eyal! If I were you, I'd use TransactionScope rather than the Command.Transaction ... it seems a lot cleaner to me.

    Coincidentally, I've been tweaking some of my code the last couple of days because we were having problems with Distributed Transactions that we just discovered this weekend. Well, we knew they were Distributed Transactions, but we missed catching exceptions in a few critical spots in the code ... ooops!

    Good luck with your project!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, April 11, 2012 4:32 AM

All replies

  • This looks like a informative article on the subject:

    http://blogs.u2u.be/diederik/post/2010/08/30/Getting-and-setting-the-Transaction-Isolation-Level-on-a-SQL-Entity-Connection.aspx

    Read the whole blog, but he states early on that:

    Nevertheless you should realize that every single T-SQL statement that you launch from your application will run in a transaction, hence will behave according to a transaction isolation level. 

    If you don't explicitely start a transaction or use a transaction scope, then SQL Server will run the statement as a transaction on its own.

    Which sounds to me like it will use the default on your SQL Server ... but I could be wrong.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, April 7, 2012 11:17 PM
  • Hi Bonnie, thanks for the reply!

    your theory that it will use the SQL Server's default Isolation level was mine also, but I needed some mechanism to make sure. tries to catch the SQL statements on the profiler with the Lock events, but it was to difficult to understand...

    However in the blog you sent there was a solution! there's an SQL statement there with how to read the isolation level from the current transaction. so I've executed that statement from the .Net application, and suprisingly altohugh the DB's default isolation level is read_committed (with the read_committed_snapshot on), the isolation level from the .Net was SERIALIZABLE!!!

    and now I'm back to square one. how can I change it? is there some reg key? some tag in the config files? I'm looking everywhere and can't find...

    Sunday, April 8, 2012 10:26 AM
  • My initial reaction was that it might be Serializable, because I always wrap up everything in a TransactionScope in all my DataAccess and it defaults to Serializable. But when I read that blog, it sounded like it might be different ... guess not. 

    What I ended up doing for my situtation was to put a static method I called GetTransactionScope() in a Utility class I have to instantiate a TransactionScope object, set the IsolationLevel properly and return the scope. You could do something similar and return a connection. In that blog post, he does show how to create an extension to the SqlConnection object ... so you could use that methodology too. All the code you need for both options is in the blog.

    Anyway, the answer you're looking for *is* in that blog post ... maybe you didn't read the whole thing carefully? Or maybe you don't understand Extension methods? After you try messing around with the code from that blog, let me know if you get stuck and need more help ...


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, April 8, 2012 1:58 PM
  • Hi Bonnie, I did read the blog well (and I'm familiar with extensions methods), but perhaps I didn't explain my question well. Our app architecture is using a transaction scope in some db queries, and don't in others. it depends on 2 flows: update (write) flow and select (read) flow. the update uses a transaction context (with Enterprise Services), and the Read flow does not. The reason I guess (wasn't here when it was written) was to use "softer" isolation level, like the DB's default isolation level.

    although rewriting the read flow to use a transaction context is possible (using your suggestion or any other), I was interested in finding out WHY exactly is the .Net choosing the serializable level for non transactional db access (which implicitly uses a  transaction context). Especially when the DB's default isolation level in our case is READ_COMMITTED. I assumed it's configurable somehow, somewhere. and that was the question - according to what is the ADO.Net choosing the SERIALIZABLE?

    BTW, the app was designed by top skilled engineers, all of which went on to work in MS, and without a doubt had a clear intention with leaving the read flow out of the DTC transaction boundries. I just don't know why, especially if the isolation level is the hardest by that plan. perhaps in the original design (.Net1.1 over SQL 2000) things worked differently then they do now (.Net 4.0 over SQL 2005)

    thanks,

    Eyal

    Sunday, April 8, 2012 2:34 PM
  • I was interested in finding out WHY exactly is the .Net choosing the serializable level for non transactional db access (which implicitly uses a  transaction context).

    Ahhh ... the "WHY" ... that's always the hard part to find out. ;0) I doubt if there's a registry setting you could use to change this behavior. I think you're stuck having to write an extension method or a static utility method.

     I just don't know why, especially if the isolation level is the hardest by that plan. perhaps in the original design (.Net1.1 over SQL 2000) things worked differently then they do now (.Net 4.0 over SQL 2005)

    That's always a possibility ... some stuff certainly has changed. I know that TransactionScope wasn't around in .NET 1.1, otherwise I would have been using it way back when myself.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, April 8, 2012 3:16 PM
  • hmm... ok 10x a lot Bonnie, guess I'll never get the WHY question answered (it might be possible to answer by stubborn debugging even via WinDbg, but it's just not worth the effort...).

    So inspite of my respect to the project's elders leaving the READ flow with implicit transactions, I see that the isolation level is always SERIALIZABLE without any consideration to the DB's Default isolation mode, and I will have to use explicit transactions there.

    thanks again!!

    Tuesday, April 10, 2012 3:26 PM
  • You're welcome, Eyal! If I were you, I'd use TransactionScope rather than the Command.Transaction ... it seems a lot cleaner to me.

    Coincidentally, I've been tweaking some of my code the last couple of days because we were having problems with Distributed Transactions that we just discovered this weekend. Well, we knew they were Distributed Transactions, but we missed catching exceptions in a few critical spots in the code ... ooops!

    Good luck with your project!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, April 11, 2012 4:32 AM