For years now I have been teaching people about changing code or components to use the proper transaction isolation level for data access. For years now, my words have fallen on deaf ears. For years now, the default for COM+ and ADO(.NET) have been SERIALIZABLE, which causes concurrency problems ad nauseum. But No More!
I'm currently embarking on a journey to learn C# and get back to my programming roots (as all DBAs should these days). While working with ADO.NET 2.0 connecting to my SQL Server 2005 database, I noticed one small change in the default SET options that are initialized on a new connection. I grabbed this from Profiler while I was troubleshooting some connectivity issues, and it put a smile on my face.
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
The improvement in performance due to reduction in blocking will be incredible with this small change in the behavior of ADO.NET. I can't wait to start telling people to upgrade, now (as if there weren't plenty of reasons already...). :)
Jon