Deadlocks on IIS Server

Question

Deadlocks on IIS Server

htt

SSCertifiable

Points: 5242
More actions
September 14, 2012 at 8:47 pm

#266753

We have an IIS server and a SQL 2005 server on 2 separate boxes. Sometime we got deadlocks recorded in event logs on the IIS server as below. However there is no deadlock show in SQL logs or event logs on the SQL server. Should this be an issue with IIS or SQL? Everyone just thinks deadlock is always a problem with SQL and wants me to dig in to find out what is wrong with SQL server. The CPU performance on SQL server is well under 5% all the time.
Friday, September 14, 2012
Transaction (Process ID 149) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Error Guid:fac6d438-0017-4c33-a9fa-30f0fe56d13f
SOURCE:.Net SqlClient Data Provider

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

matt.newman Hall of Fame Points: 3910 More actions · Answer 1

CPU does not really play into a deadlock. One operation starts next to another operation - which could both be small and perhaps both pull from disk. When they start, they both lock a resource that the other will eventually need. The first operation will complete what it needs to and then move on to the second resource, but can't use it because the first operation has that locked, so it will just wait. But, now the second operation will complete with the first resource, keeping it locked and now need the resource the first operation has locked. They will both forever wait for one another, until the back end components of SQL server come along, prioritize one of the operations, and choose a victim.

Deadlocks are easy to reproduce. There are many solutions out there, but mainly optimizations are the recommended path.

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.90).aspx

htt SSCertifiable Points: 5242 More actions · Answer 2

Thanks. More to learn for me.

I thought deadlocks would be recorded in SQL logs because I remember seeing deadlock errors in SQL logs before. I did not expect to see deadlock errors on client side.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

.. Does that mean SQL Server deadlocks are not logged in sql server errorlog always ?

htt SSCertifiable Points: 5242 More actions · Answer 4

Joy Smith San (9/16/2012)
.. Does that mean SQL Server deadlocks are not logged in sql server errorlog always ?

No. There is no deadlock in SQL logs at all. The deadlocks are shown in Application log on the separate IIS server.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

htt (9/16/2012)
Joy Smith San (9/16/2012)
.. Does that mean SQL Server deadlocks are not logged in sql server errorlog always ?
No. There is no deadlock in SQL logs at all. The deadlocks are shown in Application log on the separate IIS server.

If you mean that IIS deadlocks won't ever be reported in the SQL Server logs, I have no reason to disagree. I just want to make sure that folks understand that SQL Server Deadlocks will be recorded in the SQL Server logs if you turn on the correct trace flags.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)