October 6, 2010 at 11:52 am
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
-----------------------------------------------------------------------------------
October 6, 2010 at 1:43 pm
Answer: No and no.
This code, as is, will not deadlock independently. There's no transaction calls.
What is the serverwide setting for isolation?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2010 at 2:24 pm
thanks for the early reply
read committed is the server level isolation level.
October 6, 2010 at 2:32 pm
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')
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2010 at 2:47 pm
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.
October 6, 2010 at 2:58 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2010 at 6:05 pm
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
Author - SQL Server T-SQL Recipes
October 6, 2010 at 6:08 pm
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
Author - SQL Server T-SQL Recipes
October 6, 2010 at 6:16 pm
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2010 at 6:32 pm
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
Author - SQL Server T-SQL Recipes
October 6, 2010 at 6:34 pm
WayneS (10/6/2010)
Research links, no. But I've seen deadlocks caused by parallelism before myself... so self-research - yes.
Well, in that case, Amit, try a MAXDOP 1 and see if that helps your deadlocking problems.
Thanks Wayne, learned something new today. 🙂
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2010 at 6:45 pm
Craig Farrell (10/6/2010)
WayneS (10/6/2010)
Research links, no. But I've seen deadlocks caused by parallelism before myself... so self-research - yes.Well, in that case, Amit, try a MAXDOP 1 and see if that helps your deadlocking problems.
Thanks Wayne, learned something new today. 🙂
Before you do that... can we see the deadlock graphs to see if this is a factor.
(What I saw was an update that was updating data and indexes in separate threads... and they deadlocked on each other.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 7, 2010 at 4:06 am
Thanks Craig and Wayne. Once I am on that network I will forward the details of the deadlock graph 🙂
October 9, 2010 at 3:33 am
Sorry for the delay in sending the information friends. I have to be on the network on this client which I am on weekends only.
Here is the deadlock graph information.
1st Graph:
20/9/2010 3:21:14spid4sDeadlock encountered .... Printing deadlock information
20/9/2010 3:21:14spid4sWait-for graph
20/9/2010 3:21:14spid4sNULL
20/9/2010 3:21:14spid4sNode:1
20/9/2010 3:21:14spid4sPAGE: 14:1:195362 CleanCnt:3 Mode:SIU Flags: 0x2
20/9/2010 3:21:14spid4s Grant List 0:
20/9/2010 3:21:14spid4s Grant List 1:
20/9/2010 3:21:14spid4s Owner:0x00000000A6981980 Mode: S Flg:0x0 Ref:2 Life:00000000 SPID:64 ECID:0 XactLockInfo: 0x00000000802E2590
20/9/2010 3:21:14spid4s SPID: 64 ECID: 0 Statement Type: SELECT Line #: 9
20/9/2010 3:21:14spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 1369771937]
20/9/2010 3:21:14spid4s Requested By:
20/9/2010 3:21:14spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000012D038370 Mode: IX SPID:51 BatchID:0 ECID:0 TaskProxy:(0x0000000091DA0598) Value:0xf4534900 Cost:(0/7208)
20/9/2010 3:21:14spid4sNULL
20/9/2010 3:21:14spid4sNode:2
20/9/2010 3:21:14spid4sPAGE: 14:1:195748 CleanCnt:2 Mode:IX Flags: 0x2
20/9/2010 3:21:14spid4s Grant List 0:
20/9/2010 3:21:14spid4s Owner:0x00000000D43DF540 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0 XactLockInfo: 0x000000012D0383A8
20/9/2010 3:21:14spid4s SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 28
20/9/2010 3:21:14spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]
20/9/2010 3:21:14spid4s Requested By:
20/9/2010 3:21:14spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000802E2558 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000AB486598) Value:0xd8ae7c40 Cost:(0/0)
20/9/2010 3:21:14spid4sNULL
20/9/2010 3:21:14spid4sVictim Resource Owner:
20/9/2010 3:21:14spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000802E2558 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000AB486598) Value:0xd8ae7c40 Cost:(0/0)
20/9/2010 3:21:14spid18sdeadlock-list
20/9/2010 3:21:14spid18s deadlock victim=processc25c18
20/9/2010 3:21:14spid18s process-list
20/9/2010 3:21:14spid18s process id=processbbfc18 taskpriority=0 logused=7208 waitresource=PAGE: 14:1:195362 waittime=1765 ownerId=449682462 transactionname=ius lasttranstarted=2010-09-20T03:21:12.557 XDES=0x12d038370 lockMode=IX schedulerid=1 kpid=39992 status=suspended spid=51 sbid=0 ecid=0 priority=0 transcount=4 lastbatchstarted=2010-09-20T03:21:12.557 lastbatchcompleted=2010-09-20T03:21:12.557 clientapp=CBS hostname=DAWEBP100 hostpid=13508 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=449682462 currentdb=14 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
20/9/2010 3:21:14spid18s executionStack
20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000
20/9/2010 3:21:14spid18sUPDATE tsi_t_objects
20/9/2010 3:21:14spid18s SET NRight = NRight + 2
20/9/2010 3:21:14spid18s WHERE NRight >= @parentRight
20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000
20/9/2010 3:21:14spid18sEXEC tsi_sp_object_insert @id
20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000
20/9/2010 3:21:14spid18sINSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,
20/9/2010 3:21:14spid18s SortOrder, [Guid], ExternalId)
20/9/2010 3:21:14spid18s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000
20/9/2010 3:21:14spid18sEXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,
20/9/2010 3:21:14spid18s @modifiedById, @sortOrder, @externalId
20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000
20/9/2010 3:21:14spid18sEXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,
20/9/2010 3:21:14spid18s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,
20/9/2010 3:21:14spid18s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,
20/9/2010 3:21:14spid18s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,
20/9/2010 3:21:14spid18s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId
20/9/2010 3:21:14spid18s inputbuf
20/9/2010 3:21:14spid18sProc [Database Id = 14 Object Id = 482100758]
20/9/2010 3:21:14spid18s process id=processc25c18 taskpriority=0 logused=0 waitresource=PAGE: 14:1:195748 waittime=1812 ownerId=449682251 transactionname=SELECT lasttranstarted=2010-09-20T03:21:12.193 XDES=0x802e2558 lockMode=S schedulerid=2 kpid=41360 status=suspended spid=64 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-09-20T03:21:12.193 lastbatchcompleted=2010-09-20T03:21:12.193 clientapp=CBS hostname=DAWEBP100 hostpid=13508 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=449682251 currentdb=14 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
20/9/2010 3:21:14spid18s executionStack
20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.bkd_sp_get_unit_by_id line=9 stmtstart=222 stmtend=1438 sqlhandle=0x03000e00a10fa5511c7e1f01679d00000100000000000000
20/9/2010 3:21:14spid18sSELECT
20/9/2010 3:21:14spid18s u.Id,
20/9/2010 3:21:14spid18s u.[Name],
20/9/2010 3:21:14spid18s u.Code,
20/9/2010 3:21:14spid18s u.Active,
20/9/2010 3:21:14spid18s u.Notes,
20/9/2010 3:21:14spid18s u.PodId,
20/9/2010 3:21:14spid18s u.Template_Fk,
20/9/2010 3:21:14spid18s u.Model_Fk,
20/9/2010 3:21:14spid18s u.LastTran,
20/9/2010 3:21:14spid18s u.LastH,
20/9/2010 3:21:14spid18s u.LastT,
20/9/2010 3:21:14spid18s u.LastT,
20/9/2010 3:21:14spid18s u.Street,
20/9/2010 3:21:14spid18s u.City,
20/9/2010 3:21:14spid18s u.StateOrProvince,
20/9/2010 3:21:14spid18s u.ZipOrPostalCode,
20/9/2010 3:21:14spid18s u.Country,
20/9/2010 3:21:14spid18s sc.Id AS SiteCodeId,
20/9/2010 3:21:14spid18s sc.[Name] AS SiteCodeName,
20/9/2010 3:21:14spid18s sc.[Description] AS SiteCodeDescription,
20/9/2010 3:21:14spid18s sc.Entity_Fk
20/9/2010 3:21:14spid18s FROM
20/9/2010 3:21:14spid18s bkd_t_unit u INNER JOIN
20/9/2010 3:21:14spid18s bkd_t_sitecodes sc ON sc.Id = u.SiteCode_Fk INNER JOIN
20/9/2010 3:21:14spid18s tsi_f_get_object_permissions(@userId) op ON op.OwnedId = sc.Entity_Fk
20/9/2010 3:21:14spid18s WHERE
20/9/2010 3:21:14spid18s u.Id =@id AND
20/9/2010 3:21:14spid18s op.PermissionId = 7;
20/9/2010 3:21:14spid18s inputbuf
20/9/2010 3:21:14spid18sProc [Database Id = 14 Object Id = 1369771937]
20/9/2010 3:21:14spid18s resource-list
20/9/2010 3:21:14spid18s pagelock fileid=1 pageid=195362 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lock99c65d00 mode=SIU associatedObjectId=72057594523287552
20/9/2010 3:21:14spid18s owner-list
20/9/2010 3:21:14spid18s owner id=processc25c18 mode=S
20/9/2010 3:21:14spid18s waiter-list
20/9/2010 3:21:14spid18s waiter id=processbbfc18 mode=IX requestType=convert
20/9/2010 3:21:14spid18s pagelock fileid=1 pageid=195748 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=locka6b64880 mode=IX associatedObjectId=72057594523287552
20/9/2010 3:21:14spid18s owner-list
20/9/2010 3:21:14spid18s owner id=processbbfc18 mode=IX
20/9/2010 3:21:14spid18s waiter-list
20/9/2010 3:21:14spid18s waiter id=processc25c18 mode=S requestType=wait
2nd One
15/9/2010 11:16:53spid4sDeadlock encountered .... Printing deadlock information
15/9/2010 11:16:53spid4sWait-for graph
15/9/2010 11:16:53spid4sNULL
15/9/2010 11:16:53spid4sNode:1
15/9/2010 11:16:53spid4sKEY: 14:72057594517192704 (f500d97e6a8d) CleanCnt:3 Mode:U Flags: 0x0
15/9/2010 11:16:53spid4s Grant List 0:
15/9/2010 11:16:53spid4s Owner:0x000000011A163700 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:67 ECID:0 XactLockInfo: 0x00000000D08EE200
15/9/2010 11:16:53spid4s SPID: 67 ECID: 0 Statement Type: SELECT Line #: 9
15/9/2010 11:16:53spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 578101100]
15/9/2010 11:16:53spid4s Requested By:
15/9/2010 11:16:53spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000011B721850 Mode: X SPID:60 BatchID:0 ECID:0 TaskProxy:(0x000000013256C598) Value:0x91f43380 Cost:(0/266404)
15/9/2010 11:16:53spid4sNULL
15/9/2010 11:16:53spid4sNode:2
15/9/2010 11:16:53spid4sKEY: 14:72057594517192704 (aa00a25ad511) CleanCnt:2 Mode:X Flags: 0x0
15/9/2010 11:16:53spid4s Grant List 0:
15/9/2010 11:16:53spid4s Owner:0x00000000EF27A5C0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x000000011B721888
15/9/2010 11:16:53spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 28
15/9/2010 11:16:53spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]
15/9/2010 11:16:53spid4s Requested By:
15/9/2010 11:16:53spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000D08EE1C8 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy:(0x00000000FF174598) Value:0x96e27680 Cost:(0/0)
15/9/2010 11:16:53spid4sNULL
15/9/2010 11:16:53spid4sVictim Resource Owner:
15/9/2010 11:16:53spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000D08EE1C8 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy:(0x00000000FF174598) Value:0x96e27680 Cost:(0/0)
15/9/2010 11:16:53spid19sdeadlock-list
15/9/2010 11:16:53spid19s deadlock victim=processc25ac8
15/9/2010 11:16:53spid19s process-list
15/9/2010 11:16:53spid19s process id=processbbf438 taskpriority=0 logused=266404 waitresource=KEY: 14:72057594517192704 (f500d97e6a8d) waittime=1562 ownerId=434962837 transactionname=ius lasttranstarted=2010-09-15T11:16:51.390 XDES=0x11b721850 lockMode=X schedulerid=1 kpid=40560 status=suspended spid=60 sbid=0 ecid=0 priority=0 transcount=4 lastbatchstarted=2010-09-15T11:16:51.390 lastbatchcompleted=2010-09-15T11:16:51.390 clientapp=CBS hostname=DAWEBP100 hostpid=228 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=434962837 currentdb=14 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
15/9/2010 11:16:53spid19s executionStack
15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000
15/9/2010 11:16:53spid19sUPDATE tsi_t_objects
15/9/2010 11:16:53spid19s SET NRight = NRight + 2
15/9/2010 11:16:53spid19s WHERE NRight >= @parentRight
15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000
15/9/2010 11:16:53spid19sEXEC tsi_sp_object_insert @id
15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000
15/9/2010 11:16:53spid19sINSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,
15/9/2010 11:16:53spid19s SortOrder, [Guid], ExternalId)
15/9/2010 11:16:53spid19s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000
15/9/2010 11:16:53spid19sEXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,
15/9/2010 11:16:53spid19s @modifiedById, @sortOrder, @externalId
15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000
15/9/2010 11:16:53spid19sEXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,
15/9/2010 11:16:53spid19s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,
15/9/2010 11:16:53spid19s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,
15/9/2010 11:16:53spid19s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,
15/9/2010 11:16:53spid19s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId
15/9/2010 11:16:53spid19s inputbuf
15/9/2010 11:16:53spid19sProc [Database Id = 14 Object Id = 482100758]
15/9/2010 11:16:53spid19s process id=processc25ac8 taskpriority=0 logused=0 waitresource=KEY: 14:72057594517192704 (aa00a25ad511) waittime=1625 ownerId=434962892 transactionname=SELECT lasttranstarted=2010-09-15T11:16:51.723 XDES=0xd08ee1c8 lockMode=S schedulerid=2 kpid=40368 status=suspended spid=67 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-09-15T11:16:51.723 lastbatchcompleted=2010-09-15T11:16:51.723 clientapp=Routing hostname=DAWEBP100 hostpid=7292 loginname=DMZ\xProdWebServices isolationlevel=read committed (2) xactid=434962892 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
15/9/2010 11:16:53spid19s executionStack
15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.x_sp_get_user_by_id line=9 stmtstart=316 stmtend=602 sqlhandle=0x03000e006c1f75228597f300fc9c00000100000000000000
15/9/2010 11:16:53spid19sSELECT u.* FROM x_v_users u
15/9/2010 11:16:53spid19s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = u.Id
15/9/2010 11:16:53spid19s WHERE Id = @id AND PermissionId = 7
15/9/2010 11:16:53spid19s inputbuf
15/9/2010 11:16:53spid19sProc [Database Id = 14 Object Id = 578101100]
15/9/2010 11:16:53spid19s resource-list
15/9/2010 11:16:53spid19s keylock hobtid=72057594517192704 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects indexname=PK_tsi_tLoginHierarchy id=lockd7dc9780 mode=X associatedObjectId=72057594517192704
15/9/2010 11:16:53spid19s owner-list
15/9/2010 11:16:53spid19s owner id=processbbf438 mode=X
15/9/2010 11:16:53spid19s waiter-list
15/9/2010 11:16:53spid19s waiter id=processc25ac8 mode=S requestType=wait
15/9/2010 11:16:53spid19s keylock hobtid=72057594517192704 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects indexname=PK_tsi_tLoginHierarchy id=lock1297d3080 mode=U associatedObjectId=72057594517192704
15/9/2010 11:16:53spid19s owner-list
15/9/2010 11:16:53spid19s owner id=processc25ac8 mode=S
15/9/2010 11:16:53spid19s waiter-list
15/9/2010 11:16:53spid19s waiter id=processbbf438 mode=X requestType=convert
3rd one
22/9/2010 3:34:27spid4sDeadlock encountered .... Printing deadlock information
22/9/2010 3:34:27spid4sWait-for graph
22/9/2010 3:34:27spid4sNULL
22/9/2010 3:34:27spid4sNode:1
22/9/2010 3:34:27spid4sPAGE: 14:1:195360 CleanCnt:3 Mode:SIU Flags: 0x2
22/9/2010 3:34:27spid4s Grant List 1:
22/9/2010 3:34:27spid4s Owner:0x00000000D1F12800 Mode: S Flg:0x0 Ref:2 Life:00000000 SPID:66 ECID:0 XactLockInfo: 0x00000000A67437B0
22/9/2010 3:34:27spid4s SPID: 66 ECID: 0 Statement Type: SELECT Line #: 8
22/9/2010 3:34:27spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 802101898]
22/9/2010 3:34:27spid4s Requested By:
22/9/2010 3:34:27spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A88BB890 Mode: IX SPID:69 BatchID:0 ECID:0 TaskProxy:(0x00000000B16D2598) Value:0xa38d4340 Cost:(0/520)
22/9/2010 3:34:27spid4sNULL
22/9/2010 3:34:27spid4sNode:2
22/9/2010 3:34:27spid4sPAGE: 14:1:195748 CleanCnt:2 Mode:IX Flags: 0x2
22/9/2010 3:34:27spid4s Grant List 1:
22/9/2010 3:34:27spid4s Owner:0x0000000099644940 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:69 ECID:0 XactLockInfo: 0x00000000A88BB8C8
22/9/2010 3:34:27spid4s SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 28
22/9/2010 3:34:27spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]
22/9/2010 3:34:27spid4s Requested By:
22/9/2010 3:34:27spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A6743778 Mode: S SPID:66 BatchID:0 ECID:0 TaskProxy:(0x000000009C788598) Value:0x98f30800 Cost:(0/0)
22/9/2010 3:34:27spid4sNULL
22/9/2010 3:34:27spid4sVictim Resource Owner:
22/9/2010 3:34:27spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A6743778 Mode: S SPID:66 BatchID:0 ECID:0 TaskProxy:(0x000000009C788598) Value:0x98f30800 Cost:(0/0)
22/9/2010 3:34:27spid13sdeadlock-list
22/9/2010 3:34:27spid13s deadlock victim=processc256d8
22/9/2010 3:34:27spid13s process-list
22/9/2010 3:34:27spid13s process id=processc256d8 taskpriority=0 logused=0 waitresource=PAGE: 14:1:195748 waittime=2015 ownerId=456939113 transactionname=SELECT lasttranstarted=2010-09-22T03:34:24.867 XDES=0xa6743778 lockMode=S schedulerid=2 kpid=41552 status=suspended spid=66 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-09-22T03:34:24.867 lastbatchcompleted=2010-09-22T03:34:24.867 clientapp=CBS hostname=DAWEBP100 hostpid=6700 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=456939113 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
22/9/2010 3:34:27spid13s executionStack
22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_sp_list_securing_groups line=8 stmtstart=366 stmtend=706 sqlhandle=0x03000e008a1acf2fafada500169c00000100000000000000
22/9/2010 3:34:27spid13sSELECT * FROM tsi_v_secured s
22/9/2010 3:34:27spid13s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = s.SecurityGroupId
22/9/2010 3:34:27spid13s WHERE SecuredId = @securedId AND PermissionId =
22/9/2010 3:34:27spid13s inputbuf
22/9/2010 3:34:27spid13sProc [Database Id = 14 Object Id = 802101898]
22/9/2010 3:34:27spid13s process id=processc25978 taskpriority=0 logused=520 waitresource=PAGE: 14:1:195360 waittime=2015 ownerId=456939087 transactionname=ius lasttranstarted=2010-09-22T03:34:24.707 XDES=0xa88bb890 lockMode=IX schedulerid=2 kpid=41020 status=suspended spid=69 sbid=0 ecid=0 priority=0 transcount=4 lastbatchstarted=2010-09-22T03:34:24.690 lastbatchcompleted=2010-09-22T03:34:24.690 clientapp=CBS hostname=DAWEBP100 hostpid=6700 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=456939087 currentdb=14 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
22/9/2010 3:34:27spid13s executionStack
22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000
22/9/2010 3:34:27spid13sUPDATE tsi_t_objects
22/9/2010 3:34:27spid13s SET NRight = NRight + 2
22/9/2010 3:34:27spid13s WHERE NRight >= @parentRight
22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000
22/9/2010 3:34:27spid13sEXEC tsi_sp_object_insert @id
22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000
22/9/2010 3:34:27spid13sINSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,
22/9/2010 3:34:27spid13s SortOrder, [Guid], ExternalId)
22/9/2010 3:34:27spid13s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000
22/9/2010 3:34:27spid13sEXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,
22/9/2010 3:34:27spid13s @modifiedById, @sortOrder, @externalId
22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000
22/9/2010 3:34:27spid13sEXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,
22/9/2010 3:34:27spid13s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,
22/9/2010 3:34:27spid13s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,
22/9/2010 3:34:27spid13s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,
22/9/2010 3:34:27spid13s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId
22/9/2010 3:34:27spid13s inputbuf
22/9/2010 3:34:27spid13sProc [Database Id = 14 Object Id = 482100758]
22/9/2010 3:34:27spid13s resource-list
22/9/2010 3:34:27spid13s pagelock fileid=1 pageid=195748 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lock9b48fa00 mode=IX associatedObjectId=72057594523287552
22/9/2010 3:34:27spid13s owner-list
22/9/2010 3:34:27spid13s owner id=processc25978 mode=IX
22/9/2010 3:34:27spid13s waiter-list
22/9/2010 3:34:27spid13s waiter id=processc256d8 mode=S requestType=wait
22/9/2010 3:34:27spid13s pagelock fileid=1 pageid=195360 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lockf453bb00 mode=SIU associatedObjectId=72057594523287552
22/9/2010 3:34:27spid13s owner-list
22/9/2010 3:34:27spid13s owner id=processc256d8 mode=S
22/9/2010 3:34:27spid13s waiter-list
22/9/2010 3:34:27spid13s waiter id=processc25978 mode=IX requestType=convert
October 9, 2010 at 7:29 am
The deadlock graph will be in an XML format... do you have that available?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply