Re: Deadlock between Distribution Agent and Distribution Agent Cleanup

Here it goes:

Deadlock encountered .... Printing deadlock information
2006-12-21 13:29:58.05 spid4
2006-12-21 13:29:58.05 spid4 Wait-for graph
2006-12-21 13:29:58.05 spid4
2006-12-21 13:29:58.05 spid4 Node:1
2006-12-21 13:29:58.05 spid4 PAG: 6:1:304949
CleanCnt:2 Mode: X Flags: 0x2
2006-12-21 13:29:58.05 spid4 Grant List 0::
2006-12-21 13:29:58.05 spid4 Owner:0x9d1a3b40 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:201 ECID:0
2006-12-21 13:29:58.05 spid4 SPID: 201 ECID: 0 Statement Type:
DELETE Line #: 162
2006-12-21 13:29:58.05 spid4 Input Buf: Language Event: EXEC
dbo.sp_MSdistribution_cleanup @min_distretention = 0,
@max_distretention = 72
2006-12-21 13:29:58.05 spid4 Requested By:
2006-12-21 13:29:58.05 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:76 ECID:0 Ec:(0x24E45A50) Value:0x3955af40 Cost:(0/0)
2006-12-21 13:29:58.05 spid4
2006-12-21 13:29:58.05 spid4 Node:2
2006-12-21 13:29:58.05 spid4 PAG: 6:1:304955
CleanCnt:2 Mode: IS Flags: 0x0
2006-12-21 13:29:58.05 spid4 Grant List 1::
2006-12-21 13:29:58.05 spid4 Owner:0x3955af80 Mode: IS
Flg:0x0 Ref:1 Life:00000000 SPID:76 ECID:0
2006-12-21 13:29:58.05 spid4 SPID: 76 ECID: 0 Statement Type:
SELECT Line #: 72
2006-12-21 13:29:58.05 spid4 Input Buf: RPC Event:
sp_MSget_repl_commands;1
2006-12-21 13:29:58.05 spid4 Requested By:
2006-12-21 13:29:58.05 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:201 ECID:0 Ec:(0x366CD5F8) Value:0x9d1a3cc0 Cost:(1/0)
2006-12-21 13:29:58.05 spid4 Victim Resource Owner:
2006-12-21 13:29:58.05 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:201 ECID:0 Ec:(0x366CD5F8) Value:0x9d1a3cc0 Cost:(1/0)
2006-12-21 13:38:01.66 spid4
Deadlock encountered .... Printing deadlock information
2006-12-21 13:38:01.66 spid4
2006-12-21 13:38:01.66 spid4 Wait-for graph
2006-12-21 13:38:01.66 spid4
2006-12-21 13:38:01.66 spid4 Node:1
2006-12-21 13:38:01.66 spid4 PAG: 6:1:606735
CleanCnt:2 Mode: X Flags: 0x2
2006-12-21 13:38:01.66 spid4 Grant List 3::
2006-12-21 13:38:01.66 spid4 Owner:0x6ed511a0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:217 ECID:0
2006-12-21 13:38:01.66 spid4 SPID: 217 ECID: 0 Statement Type:
DELETE Line #: 162
2006-12-21 13:38:01.66 spid4 Input Buf: Language Event: EXEC
dbo.sp_MSdistribution_cleanup @min_distretention = 0,
@max_distretention = 72
2006-12-21 13:38:01.66 spid4 Requested By:
2006-12-21 13:38:01.66 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:76 ECID:0 Ec:(0x24E45A50) Value:0x89d873c0 Cost:(0/0)
2006-12-21 13:38:01.66 spid4
2006-12-21 13:38:01.66 spid4 Node:2
2006-12-21 13:38:01.66 spid4 PAG: 6:1:494567
CleanCnt:2 Mode: IS Flags: 0x0
2006-12-21 13:38:01.66 spid4 Grant List 1::
2006-12-21 13:38:01.66 spid4 Owner:0x89d6c8a0 Mode: IS
Flg:0x0 Ref:1 Life:00000000 SPID:76 ECID:0
2006-12-21 13:38:01.66 spid4 SPID: 76 ECID: 0 Statement Type:
SELECT Line #: 72
2006-12-21 13:38:01.66 spid4 Input Buf: RPC Event:
sp_MSget_repl_commands;1
2006-12-21 13:38:01.66 spid4 Requested By:
2006-12-21 13:38:01.66 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:217 ECID:0 Ec:(0x9AC675F8) Value:0x33685820 Cost:(1/0)
2006-12-21 13:38:01.66 spid4 Victim Resource Owner:
2006-12-21 13:38:01.66 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:217 ECID:0 Ec:(0x9AC675F8) Value:0x33685820 Cost:(1/0)
2006-12-21 13:46:39.30 spid4
Deadlock encountered .... Printing deadlock information
2006-12-21 13:46:39.30 spid4
2006-12-21 13:46:39.30 spid4 Wait-for graph
2006-12-21 13:46:39.30 spid4
2006-12-21 13:46:39.30 spid4 Node:1
2006-12-21 13:46:39.30 spid4 PAG: 6:1:168158
CleanCnt:2 Mode: IS Flags: 0x0
2006-12-21 13:46:39.30 spid4 Grant List 1::
2006-12-21 13:46:39.30 spid4 Owner:0x394e9300 Mode: IS
Flg:0x0 Ref:1 Life:00000000 SPID:76 ECID:0
2006-12-21 13:46:39.30 spid4 SPID: 76 ECID: 0 Statement Type:
SELECT Line #: 72
2006-12-21 13:46:39.30 spid4 Input Buf: RPC Event:
sp_MSget_repl_commands;1
2006-12-21 13:46:39.30 spid4 Requested By:
2006-12-21 13:46:39.30 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:62 ECID:0 Ec:(0xB7C355F8) Value:0x29bb1840 Cost:(1/0)
2006-12-21 13:46:39.30 spid4
2006-12-21 13:46:39.30 spid4 Node:2
2006-12-21 13:46:39.30 spid4 PAG: 6:1:168154
CleanCnt:2 Mode: X Flags: 0x2
2006-12-21 13:46:39.30 spid4 Grant List 0::
2006-12-21 13:46:39.30 spid4 Owner:0x29a1cac0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2006-12-21 13:46:39.30 spid4 SPID: 62 ECID: 0 Statement Type:
DELETE Line #: 162
2006-12-21 13:46:39.30 spid4 Input Buf: Language Event: EXEC
dbo.sp_MSdistribution_cleanup @min_distretention = 0,
@max_distretention = 72
2006-12-21 13:46:39.30 spid4 Requested By:
2006-12-21 13:46:39.30 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:76 ECID:0 Ec:(0x24E45A50) Value:0x394e91a0 Cost:(0/0)
2006-12-21 13:46:39.30 spid4 Victim Resource Owner:
2006-12-21 13:46:39.30 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:62 ECID:0 Ec:(0xB7C355F8) Value:0x29bb1840 Cost:(1/0)
2006-12-21 13:56:09.30 spid4
Deadlock encountered .... Printing deadlock information
2006-12-21 13:56:09.30 spid4
2006-12-21 13:56:09.30 spid4 Wait-for graph
2006-12-21 13:56:09.30 spid4
2006-12-21 13:56:09.30 spid4 Node:1
2006-12-21 13:56:09.30 spid4 PAG: 6:1:71310
CleanCnt:2 Mode: X Flags: 0x2
2006-12-21 13:56:09.30 spid4 Grant List 3::
2006-12-21 13:56:09.30 spid4 Owner:0x827e4ae0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:244 ECID:0
2006-12-21 13:56:09.30 spid4 SPID: 244 ECID: 0 Statement Type:
DELETE Line #: 162
2006-12-21 13:56:09.30 spid4 Input Buf: Language Event: EXEC
dbo.sp_MSdistribution_cleanup @min_distretention = 0,
@max_distretention = 72
2006-12-21 13:56:09.30 spid4 Requested By:
2006-12-21 13:56:09.30 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:76 ECID:0 Ec:(0x24E45A50) Value:0x4d372680 Cost:(0/0)
2006-12-21 13:56:09.30 spid4
2006-12-21 13:56:09.30 spid4 Node:2
2006-12-21 13:56:09.30 spid4 PAG: 6:1:93326
CleanCnt:2 Mode: IS Flags: 0x0
2006-12-21 13:56:09.30 spid4 Grant List 1::
2006-12-21 13:56:09.30 spid4 Owner:0x4d10ef00 Mode: IS
Flg:0x0 Ref:1 Life:00000000 SPID:76 ECID:0
2006-12-21 13:56:09.30 spid4 SPID: 76 ECID: 0 Statement Type:
SELECT Line #: 72
2006-12-21 13:56:09.30 spid4 Input Buf: RPC Event:
sp_MSget_repl_commands;1
2006-12-21 13:56:09.30 spid4 Requested By:
2006-12-21 13:56:09.30 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:244 ECID:0 Ec:(0x532C35F8) Value:0x8d172f80 Cost:(1/0)
2006-12-21 13:56:09.30 spid4 Victim Resource Owner:
2006-12-21 13:56:09.30 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:244 ECID:0 Ec:(0x532C35F8) Value:0x8d172f80 Cost:(1/0)

This is happening in periods of 'high' transaction activity. (once a
day at peak hours)

Is there any recomendation from you guys ? How do you solve this
problem?

I don't have a profiler trace for this (running profiler at those times
could be dangerous ...)

Hopefully you can offer some workaround.

Many Thanks!

-Noel
Sr. DBA







Raymond Mak [MSFT] wrote:
I can't promise there is anything M$ can do about it (how often does this
occur?) but it would be great if you can post the deadlock trace here. (Or,
you can log a feedback item @ http://www.microsoft.com/connect)

-Raymond

<zerg2k@xxxxxxxxx> wrote in message
news:1166732530.463614.305580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am experiencing this problem. Deadlock of these two M$ stored
procedures :

sp_MSget_repl_commands (Executed by the Distribution Agent --pull
subscriber ) and
sp_MSdistribution_cleanup (Executed by the Distribution Agent Cleanup
job)

the offending queries are :

From sp_MSdistribution_cleanup:

DELETE MSrepl_commands WITH (PAGLOCK) where
publisher_database_id = @publisher_database_id and
xact_seqno <= @max_xact_seqno

From sp_MSget_repl_commands:
select @max_xact_seqno = max(xact_seqno) from MSrepl_commands
(READPAST)
where
publisher_database_id = @publisher_database_id and
command_id = 1 and
type <> -2147483611

I searched this and other groups and no convincing answer was posted.
Is there anyone experiencing this problem ? if so what did you do to
"resolve" it (not to decrease its frequency)

Thanks in Advance.

-Noel
Sr. DBA


.