I've recently started getting two deadlock errors:
deadlocked on lock | communication buffer resources
and
Transaction was deadlocked on lock resources with another process
There haven't been any recent changes to the system that offer a readily apparent explanation regarding why my users have started getting these errors in their browsers. I got a DBA to capture some data in Profiler and I'm posting the XML from one of the
deadlocks at the bottom of this.
The description of the scenario is that there is a ASP.NET 1.1 web app and a VC++7 Windows service both running against a SQL Server 2005 clustered database. The deadlocks are happening between a simple select statement that doesn't use any joins and a simple
update statement that is updating the same table. Neither process is using explicit SQL transactions (i.e. there is no explicit Begin Transaction) nor are they using any kind of hints (like NOLOCK or READUNCOMMITTED). But, I can't see why they should need
to.
I have captured staticts on the DB per another thread on this subject and the results were:
wait_type wait_time_s pct running_pct
------------------------------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------
SQLTRACE_BUFFER_FLUSH 1199
View Complete Post