Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocks on IIS Server Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 8:47 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:36 PM
Points: 444, Visits: 559
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



Post #1359758
Posted Sunday, September 16, 2012 8:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:05 PM
Points: 175, Visits: 449
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

Post #1359883
Posted Sunday, September 16, 2012 10:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:36 PM
Points: 444, Visits: 559
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.




Post #1359899
Posted Sunday, September 16, 2012 12:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 1,500, Visits: 2,520
.. Does that mean SQL Server deadlocks are not logged in sql server errorlog always ?
Post #1359906
Posted Sunday, September 16, 2012 2:27 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:36 PM
Points: 444, Visits: 559
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.



Post #1359915
Posted Sunday, September 16, 2012 8:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359952
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse