Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

DeadLock Expand / Collapse
Author
Message
Posted Wednesday, October 6, 2010 11:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 14, Visits: 342
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
Posted Wednesday, October 6, 2010 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
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
Posted Wednesday, October 6, 2010 2:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 14, Visits: 342
thanks for the early reply
read committed is the server level isolation level.
Post #999885
Posted Wednesday, October 6, 2010 2:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
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')



- 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 #999895
Posted Wednesday, October 6, 2010 2:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 14, Visits: 342
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
Posted Wednesday, October 6, 2010 2:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
Amit Pandey DeBugSQL (10/6/2010)
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.



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.



- 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 #999934
Posted Wednesday, October 6, 2010 6:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 5,366, Visits: 8,981
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
Posted Wednesday, October 6, 2010 6:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 5,366, Visits: 8,981
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!
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 #1000063
Posted Wednesday, October 6, 2010 6:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
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?



- 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 #1000064
Posted Wednesday, October 6, 2010 6:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 5,366, Visits: 8,981
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!
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 #1000072
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse