Please advise on why this deadlock and slowness problem happens?

Hi,

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.

asked Oct 12 '09 at 08:10 AM in Default

Hari 1
11 ● 1 ● 1 ● 1

(comments are locked)

1 answer:

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.