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.

Wednesday, January 16, 2013

XmlSerializer class may result in a memory leak and poor performance

We have recently found an unpleasant memory leak related with XmlSerializer class. This issue is described in MSDN:  
To increase performance, the XML serialization infrastructure dynamically generates assemblies to serialize and deserialize specified types. The infrastructure finds and reuses those assemblies. This behavior occurs only when using the following constructors: 
If you use any of the other constructors, multiple versions of the same assembly are generated and never unloaded, which results in a memory leak and poor performance. The easiest solution is to use one of the previously mentioned two constructors. Otherwise, you must cache the assemblies.
So be careful with XmlSerializer class.