Thursday, January 17, 2013

Why the explicit declaration of the isolation level is a mandatory practice

Today I have got an unexpected error: 
System.Data.SqlClient.SqlException (0x80131904): Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.XXXtable' directly or indirectly in database 'YYYDb' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
The problem is that this error has occurred in simple code which just opens SQL Server connection and executes UPDATE command without any transaction stuff. Of course, the ReadCommited isolation level is expected by default, not Snapshot.

It appears that this this unexpected behavior is by (bad :-)) design:
When you use the System.Data.SqlClient.SqlConnection class, and you reuse a connection from the connection pool, commands are run with a previously specified transaction isolation level. The level is not reset as expected.
Read the KB 972915 article and MS Connect bug for details.

Update: SQL Server 2014 now resets the isolation level of pooled connections.

No comments:

Post a Comment