DeadLock

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » DeadLock

21 posts, Page 1 of 3

1 2 3 »»»

DeadLock

Rate Topic

Display Mode

Topic Options

Author

Message

Amit Pandey DeBugSQL

Posted Wednesday, October 06, 2010 11:52 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 11, Visits: 216

Hi All,

I am trying to fine tune an application. One of the issues I find with the application is deadlocks.

There have been six different instances of deadlocks in the last one week.
The Deadlock trace graphs shows the same object ID on the node2 which points to the below Stored proc.
My question is that the store procedure contains an outer Begin-end which can be removed (confirmed with the developers of the application). will it help in minimizing blocking if I remove the outer Begin-End block.

BEGIN -------- This one if removed, will it help
SET NOCOUNT ON;

-----------------------------------------------------------------------------------

CREATE PROCEDURE [sampleProc]
@IDD AS INT
AS
DECLARE @var1 AS INT
DECLARE @var2 AS INT
DECLARE @var3 AS INT
DECLARE @var4 AS INT

BEGIN -------- This one if removed, will it help
SET NOCOUNT ON;

CREATE TABLE #temp1 (IDD INT)

SELECT @var1 = PFK FROM TObjects WHERE IDD = @IDD

SELECT @var4 = COUNT(IDD) FROM TObjects

IF (@var4 = 1)
BEGIN
UPDATE TObjects
SET NLeft = 1, NRight = 2
END
ELSE
BEGIN
SELECT @var2 = NLeft, @var3 = NRight
FROM TObjects WHERE IDD = @var1

UPDATE TObjects
SET NRight = NRight + 2
WHERE NRight >= @var3

UPDATE TObjects
SET NLeft = NLeft + 2
WHERE NLeft > @var3

UPDATE TObjects
SET NLeft = @var3, NRight = @var3 + 1
WHERE IDD = @IDD
END

DROP TABLE #temp1
END

-----------------------------------------------------------------------------------

Post #999714

Evil Kraig F

Posted Wednesday, October 06, 2010 1:43 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100

Answer: No and no.

This code, as is, will not deadlock independently. There's no transaction calls.

What is the serverwide setting for isolation?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #999836

Amit Pandey DeBugSQL

Posted Wednesday, October 06, 2010 2:24 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 11, Visits: 216

thanks for the early reply
read committed is the server level isolation level.

Post #999885

Evil Kraig F

Posted Wednesday, October 06, 2010 2:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100

This query isn't the problem child, but it could be a contributor if default is READ COMMITTED.

EDIT: Whoops. Can you also find out if your READ_COMMITTED_SNAPSHOT is set to on/off please?

Run this, see if you get any hits:

select
o.name
from
sysobjects o
join syscomments c
on o.id = c.id
where
c.text like '%BEGIN TRAN%'
and o.type IN ( 'p', 't')

Post #999895

Amit Pandey DeBugSQL

Posted Wednesday, October 06, 2010 2:47 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 11, Visits: 216

Nope Snapshot is not turned on and turning it is not an available option (I have already recommended this).
Yes I ran the query given by you and it gives me as many as 37 records.
Please let me know if you need any further info.

Post #999914

Evil Kraig F

Posted Wednesday, October 06, 2010 2:58 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100

Amit Pandey DeBugSQL (10/6/2010)

One of these queries is your likely culprit, then, in the actual deadlock cause. Deadlocks in general (it seems you're relatively aware, but to make sure) are caused by two transactions fighting over the same resources. If you don't have multi-statement transactions, it's nearly impossible to get a deadlock. Timeouts waiting for lock, sure... but not deadlocks.

Look over your deadlock statistics again and check to see if one of those 37 are listed.

Post #999934

WayneS

Posted Wednesday, October 06, 2010 6:05 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227

Amit Pandey DeBugSQL (10/6/2010)

The Deadlock trace graphs shows the same object ID on the node2 which points to the below Stored proc.

I have a neat script that shreds a deadlock graph apart to show what all is happening. Can you post your deadlock graphs?

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1000062

WayneS

Posted Wednesday, October 06, 2010 6:08 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227

Craig Farrell (10/6/2010)

Answer: No and no.

This code, as is, will not deadlock independently. There's no transaction calls.

What is the serverwide setting for isolation?

Craig, did you consider the effect of parallelism on the deadlocks? I can see this potentially being an issue. Again, the deadlock graphs will help show what's really going on.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Post #1000063

Evil Kraig F

Posted Wednesday, October 06, 2010 6:15 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100

WayneS (10/6/2010)

Craig Farrell (10/6/2010)

Answer: No and no.

This code, as is, will not deadlock independently. There's no transaction calls.

What is the serverwide setting for isolation?

Craig, did you consider the effect of parallelism on the deadlocks? I can see this potentially being an issue. Again, the deadlock graphs will help show what's really going on.

Hm, I've seen parallelism cause timeouts, but I've never seen it cause a deadlock without an explicit two part transaction. You wouldn't happen to have some research links handy, would you?

Post #1000064

WayneS

Posted Wednesday, October 06, 2010 6:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227

Craig Farrell (10/6/2010)

WayneS (10/6/2010)

Craig Farrell (10/6/2010)

Answer: No and no.

This code, as is, will not deadlock independently. There's no transaction calls.

What is the serverwide setting for isolation?

Craig, did you consider the effect of parallelism on the deadlocks? I can see this potentially being an issue. Again, the deadlock graphs will help show what's really going on.

Hm, I've seen parallelism cause timeouts, but I've never seen it cause a deadlock without an explicit two part transaction. You wouldn't happen to have some research links handy, would you?

Research links, no. But I've seen deadlocks caused by parallelism before myself... so self-research - yes.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Post #1000072

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions