x

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.

more ▼

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

Hari 1 gravatar image

Hari 1
11 1 1 1

(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

    TG gravatar image

    TG
    1.8k 1 3

    (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.

    New code box

    There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

    Follow this question

    By Email:

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

    By RSS:

    Answers

    Answers and Comments

    SQL Server Central

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

    Topics:

    x1949
    x39

    asked: Oct 12, 2009 at 08:10 AM

    Seen: 1358 times

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