Please advise on why this deadlock and slowness problem happens?


We recently migrated to SQL Server 2005 with out client and our application is web-based using Java technology. The Client has two identical servers (One Production and other Disaster Recovery) with the following configuration; Intel Xeon 2.13GHz - Dual Core, 4 GB RAM, 400 GB RAID HDD. (S/w: JDK 1.4.2)

The installed applications are also identical at both the applications. Both the servers have both Application & Database on the same server.

The Problem: We have multi-threaded processes being hit on the database (Multiple Inserts) and there are simultaneous select operations performed on the table.

In the production server, the multi-threaded application console, shows Deadlock messages and asking user to re-run the transaction, whereas at Disaster site, there are no such issues and operation is fast.

Since the "Deadlock" is seen on the main server, the entire server slows down, dis-allowing any user operation on the server.

I checked through forums and a configuration, ISOLATION level has to be set explicitly in SQL 2005. Now this setting SET READ_COMMITTED_SNAPSHOT ON is set on our database.

Any other suggestions to help to check the slowness of the application/database during parallel inserts / updates?

Please help.

more ▼

asked Oct 12, 2009 at 08:10 AM in Default

avatar image

Hari 1
11 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Trouble shooting deadlock problems can be an involved process so I'll just point you to a couple of resources that could help.

One question I have is how do you know that the problem isn't happening on your "disaster recovery" system? Have you run it under the same concurrency load as your production system?

Here is a good starting point for resolving deadlocks - be sure to check our the additional reading links as well: http://support.microsoft.com/kb/832524

It should be said that implementing SNAPSHOT isolation has ramifications that the admins should be aware of. If you haven't read this already I would start here: http://technet.microsoft.com/en-us/library/ms189050%28SQL.90%29.aspx

In the meantime see if you may be guilty of these common reasons deadlocks occur:

  • long running statements inside explicit transactions.
  • unnecessary explicit transactions
  • transactions that are left uncommitted while it waits for other processes (or even worse - user actions)
  • logical sequence conflicts like one transaction that updates A then B while another transaction updates B then A.
more ▼

answered Oct 12, 2009 at 12:59 PM

avatar image

1.8k 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 12, 2009 at 08:10 AM

Seen: 1563 times

Last Updated: Oct 12, 2009 at 04:26 PM

Copyright 2018 Redgate Software. Privacy Policy