SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocks on IIS Server


Deadlocks on IIS Server

Author
Message
htt
htt
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 793
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




matt.newman
matt.newman
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 473
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
htt
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 793
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
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3240 Visits: 3200
.. Does that mean SQL Server deadlocks are not logged in sql server errorlog always ?
htt
htt
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 793
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86980 Visits: 41107
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search