9

I know that if I use System.Transactions.TransactionScope and don't specify an isolation level, it will default to Serializable. However, what if I'm not using transaction scope, and am just using an old-fashioned table adapter? What is the default isolation level then?

Many thanks in advance.

Neil Barnwell
  • 41,080
  • 29
  • 148
  • 220
  • 6
    It is more complex; isolation level does not get reset between uses of a pooled connection, so it also depends on "what the last use of the underlying connection was doing" – Marc Gravell Mar 23 '12 at 18:26
  • @MarcGravell Yes that's great, thanks. A colleague of mine just sent me a link that mentioned the same thing. Not exactly the pit of success, and how I've not noticed it in 7 years using .NET I have no idea... – Neil Barnwell Mar 23 '12 at 18:37
  • But "default default" is probably read committed – Marc Gravell Mar 23 '12 at 18:44
  • After learning about this disturbing fact about pooling, I posted a question on how to avoid that: http://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections – usr Mar 24 '12 at 12:04

1 Answers1

3

You may not want to rely on defaults if you need to be absolute about your application data ops. These things can change between framework versions.

Highly recommend being explicit about isolation levels and session settings on all data calls - either via TransactionScope (which may mean escalation to DTC dependant on circumstances) or explicitly within the target stored proc call (if thats a route taken).

More details on DTC / TransactionScope: https://stackoverflow.com/a/9075800/1568341

TL;DR

A: Read committed for SQLS but don't assume a default

Community
  • 1
  • 1
Steve
  • 96
  • 10