2

My Application uses ADO.NET to make sql queries to a sql server database. The sql server default transaction level is set to read committed. The application is using the transaction isolation level repeatable read for all transactions. However, for some selects (and not all the time for the same selects) it appears the transaction isolation level is set to serializable. Is there any reason why this would happen and is there any way to enforce a given select to run at a given isolation level?

Tom Jones
  • 265
  • 2
  • 14
  • 1
    Could it be this issue? http://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections (SQL Server: Isolation level leaks across pooled connections) – usr Sep 21 '13 at 20:18
  • Possible - however nothing is explicitly setting the transaction level to serializable. – Tom Jones Sep 21 '13 at 22:38
  • Use the SQL Server DMVs to find out what level each connection is at right now. Are there mixed values? Is serializable among them? Filter to your app first. `SELECT sess.transaction_isolation_level, conn.* FROM sys.dm_exec_connections conn LEFT JOIN sys.dm_exec_sessions sess ON conn.session_id = sess.session_id` – usr Sep 22 '13 at 09:38
  • I ran that query I do see a mix of 2, 3, 4 so serializable is among them – Tom Jones Sep 22 '13 at 17:29
  • 1
    The default isolation level for TransactionScope is serializable. Maybe that is the issue. Or some library does it. I came to the conclusion that the only feasible way to deal with this feature/bug is to always use an explicit transaction. Only 2 lines of code more per use case. – usr Sep 22 '13 at 17:31

1 Answers1

1

Default isolation level for Transaction class in .NET is serializable. The isolation level is one of the properties of the connection that is not reset when the connection goes back to the pool after being disposed. This is why you're observing this.

Best practice for .NET applications using Transaction class is to set isolation level explicitly.

Endrju
  • 2,354
  • 16
  • 23