Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dead Locks in SQL Server

Posted on 2013-01-02
5
Medium Priority
?
419 Views
Last Modified: 2013-01-11
Hi,
Please help me to resolve this issue.

We are using SQLSentry tool to monitor our DB server. We got below alert.

Environment: SQL Server 2005 Std With SP2 on Windows Server 2003 Ent

Please help why we are getting below deadlock

[Connection]:  abc.xyz.com
[Message]:  SQL Server Deadlock Detected
[Event Time]:  12/22/2012 3:52:10 PM
----------------------------------------------------------------------
[Timestamp (Local)]:  12/22/2012 3:52:10 PM
[Timestamp (UTC)]:  12/22/2012 11:52:10 PM
[Generated By]:  SQL Sentry 7.2 Server [SQLQADB02]
[Version]:  7.2.54.0
[Monitor Type]:  SqlServerProfiler
[Condition]:  SQL Server: Deadlock
[Response Ruleset]:  Notify Every Time (default)
----------------------------------------------------------------------
[Deadlock Victim Information]:
SPID [ecid]: 707 [0]
Host: host15
Application: .Net SqlClient Data Provider
Database: databse01
Login: WebServer
Log Used: 0
Deadlock Priority: 0
Wait Time: 1484
Transaction Start Time: 12/22/2012 3:52:09 PM
Last Batch Start Time: 12/22/2012 3:52:09 PM
Last Batch Completion Time: 12/22/2012 3:52:09 PM
Mode/Type: S
Status: suspended
Isolation Level: read committed (2)
Text Data:
    at databse01.dbo.up_Getads2 line 24
      SELECT
        H.ID as ID,
        H.ParentID as PID,
        RTRIM(REPLACE(REPLACE(REPLACE(H.Subject,'&amp;',  '&'), '&lt;', '<'), '&gt;', '>')) as T,
        dbo.fn_ConvertToXmlDate(H.PostDate) as D,
        RTRIM(REPLACE(REPLACE(REPLACE(H.PreviewText,'&amp;',  '&'), '&lt;', '<'), '&gt;', '>')) as PT,
        CASE
                WHEN Anonymous = 0 THEN RTRIM(U.Handle)
                ELSE 'Anonymous'
        END as A,

        CASE
                WHEN H.PollID = -1 THEN NULL
                ELSE 1
        END as P,

        CASE
                WHEN Status & 163
Client Options 2: 128056
Client Options 1: 538968096
Is Victim: True
Wait Resource: KEY: 5:72057594065518592 (2c00ac28262b)
Transaction Count: 0
Lock Timeout: 4294967295
SPID: 707
ECID: 0
Transaction ID: 423751167
Transaction Name: XML SELECT
Database ID: 5

-------------------------------------------------------
msghdrs-deadlock.JPG
0
Comment
Question by:spkvijay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 38736928
Make sure you have proper indexes for this query, can you post the execution plans of this query?
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 38738562
..can be many reasons:
the main one the code -- you need to include Catch -TRY for proc..
Isolation Level: read committed is contributing as well

more:
Minimizing Deadlocks
http://msdn.microsoft.com/en-us/library/ms191242(v=sql.90).aspx

Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:
• Rolled back, undoing all the work performed by the transaction.

• Resubmitted by applications because they were rolled back when deadlocked.


To help minimize deadlocks:
• Access objects in the same order.

• Avoid user interaction in transactions.

• Keep transactions short and in one batch.

• Use a lower isolation level.

• Use a row versioning-based isolation level.
¿ Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.

¿ Use snapshot isolation.


• Use bound connections.


Detecting and Ending Deadlocks
http://msdn.microsoft.com/en-us/library/ms178104(v=sql.90).aspx


SET DEADLOCK_PRIORITY
http://msdn.microsoft.com/en-us/library/ms186736(v=sql.90).aspx

Set READ_COMMITTED_SNAPSHOT

http://msdn.microsoft.com/en-us/library/ms173763(v=SQL.90).aspx
0
 

Author Comment

by:spkvijay
ID: 38739522
I've requested that this question be deleted for the following reason:

Posted wrong one
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38739523
<Posted wrong one>? :)

it looks like good one to me
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question