Merge Replication Deadlocks

[Hugo]

On our servers that participate in the same merge replication various
deadlocks happen. Below some examples. I’ve done some research but have not
come up with a solution so far. What is the best way to prevent these
deadlocks from happening?

1) sp_MSupdategenhistory conflicts with sp_MSmakegeneration
2) sp_MSmakegeneration conflicts with sp_MSenumgenerations90
3) sp_MSupdategenhistory conflicts with sp_mergemetadataretentioncleanup
4) sp_MSenumchangesdirect conflicts with user transactions
5) MSmerge_ins_sp_ conflicts with MSmerge_upd_sp
6) MSmerge_upd_sp conflicts with user transactions

I am guessing I can lower the amount of deadlocks of example (2) by changing
the Merge Agent Profile –PollingInterval parameter value from 32 (current) to
60 (default) or even higher, but I am also guessing deadlocks won’t disappear
this way.

Also I read here
(http://www.eggheadcafe.com/forumarchives/SQLServerreplication/Mar2006/post26059633.asp)
to change the DownloadReadChangesPerBatch parameter (lower it from 100 to
25). Do I need to change the DownloadWriteChangesPerBatch also to get better
results? What are the downsides changing those parameters?

Are there other things I can tune or change?

Hugo

[Hilary Cotter]

PollingInterval will change how long the merge agent waits after processing
all changes and applying them on the subscriber and publisher. If it
processes all changes in 1 second it will be waiting for 59 seconds if you
use the default, if you change it to 32 it will only be waiting 32 seconds.
You basically have to time your synchronizations to see if this will be a
factor or not.

While changing the DownloadRead and Write ChangesPerBatch will affect the
lenght of time locks are held, it will also increase your sync time.

I would your user transactions to verify they are JIT and not holding locks
too long, also check your indexing to make sure everything is optimal.
Update statistics and rebuild your indexes if they are fragmented.

A quick check to see where the problem lies is to see if the merge agents
lock at night when no-one is on the system. For example stop the merge
agents from running during the day or for some hours so unsync'd changes
build up, then at night when no-one is on the system, start the agents up
and see if you still see the locking.

Note that sp_MSMakeGeneration holds application locks which are pretty
severe, so you might want to evaluate how many concurrent agents you have
running.
"Hugo" <Hu...@discussions.microsoft.com> wrote in message
news:8787A855-16A0-400E...@microsoft.com...

[Hugo]

Thanks for your input so far.

Our data lines are pretty crappy, if we were to pause the merge agents we
might not catchup until the weekend so that's business wise not possible.

We now have paralel upload/download enabled (necesary because of the bad
data lines), with two concurrent agents each. Our idea is to lower the
downloadread values, and also lowering the polling interval (because
otherwise the sync time would increase). Before doing so, I'll make a
meassure system to check the impact of the changes. Any other input?

[Hilary Cotter]

How long do your sync's take. Polling interval is really only relevant for
the contiuous option, it appears that you agents run continuously.

"Hugo" <Hu...@discussions.microsoft.com> wrote in message

news:ACF441D9-99C0-41B2...@microsoft.com...

[Hugo]

Yesterday one server which was still in the merge replication did not have
any users working on it, and only 2 deadlocks did happen, both
sp_MSupdategenhistory conflicts with sp_MSmakegeneration.

Does this 'proof' the user transactions are causing the deadlocks? The
programmers might as easily argue that the replication is buggy? Any advice
on how to handle this further?

[Amy2006]

Hugo,
Did you get a solution to this deadlocking issue?
We are getting the same type of deadlocking in the MSmerge_genhistory table.
There are 100 - 200 Subscribers trying to sync every min. and this is
causing much deadlocking.
I am looking at increasing the sync interval but this is not desirable.

Any help would be great.
Thanks.

2008-09-25 10:09:31.44 spid4s Deadlock encountered .... Printing
deadlock information
2008-09-25 10:09:31.44 spid4s Wait-for graph
2008-09-25 10:09:31.44 spid4s
2008-09-25 10:09:31.44 spid4s Node:1

2008-09-25 10:09:31.44 spid4s KEY: 9:72057597064445952 (0800fb47ae3f)
CleanCnt:3 Mode:X Flags: 0x0
2008-09-25 10:09:31.44 spid4s Grant List 1:
2008-09-25 10:09:31.44 spid4s Owner:0x368EBE40 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:135 ECID:0 XactLockInfo: 0x3F67A97C
2008-09-25 10:09:31.44 spid4s SPID: 135 ECID: 0 Statement Type:
UPDATE Line #: 62
2008-09-25 10:09:31.44 spid4s Input Buf: RPC Event: Proc [Database
Id = 32767 Object Id = 793474348]
2008-09-25 10:09:31.44 spid4s Requested By:
2008-09-25 10:09:31.44 spid4s ResType:LockOwner
Stype:'OR'Xdes:0x06635608 Mode: U SPID:119 BatchID:0 ECID:0
TaskProxy:(0x17166378) Value:0x369f3500 Cost:(5/196)
2008-09-25 10:09:31.44 spid4s
2008-09-25 10:09:31.44 spid4s Node:2

2008-09-25 10:09:31.44 spid4s KEY: 9:72057597064577024 (1000078f79b2)
CleanCnt:2 Mode:U Flags: 0x0
2008-09-25 10:09:31.44 spid4s Grant List 1:
2008-09-25 10:09:31.44 spid4s Owner:0x3E8EF760 Mode: U
Flg:0x0 Ref:1 Life:00000001 SPID:119 ECID:0 XactLockInfo: 0x0663562C
2008-09-25 10:09:31.44 spid4s SPID: 119 ECID: 0 Statement Type:
UPDATE Line #: 163
2008-09-25 10:09:31.44 spid4s Input Buf: RPC Event: Proc [Database
Id = 32767 Object Id = 103774808]
2008-09-25 10:09:31.44 spid4s Requested By:
2008-09-25 10:09:31.44 spid4s ResType:LockOwner
Stype:'OR'Xdes:0x3F67A958 Mode: X SPID:135 BatchID:0 ECID:0
TaskProxy:(0x5CF4A378) Value:0x41b57ae0 Cost:(0/356)
2008-09-25 10:09:31.44 spid4s
2008-09-25 10:09:31.44 spid4s Victim Resource Owner:
2008-09-25 10:09:31.44 spid4s ResType:LockOwner
Stype:'OR'Xdes:0x06635608 Mode: U SPID:119 BatchID:0 ECID:0
TaskProxy:(0x17166378) Value:0x369f3500 Cost:(5/196)
2008-09-25 10:09:31.44 spid18s deadlock-list
2008-09-25 10:09:31.44 spid18s deadlock victim=process92e4d8
2008-09-25 10:09:31.44 spid18s process-list
2008-09-25 10:09:31.44 spid18s process id=process92e4d8
taskpriority=5 logused=196 waitresource=KEY: 9:72057597064445952
(0800fb47ae3f) waittime=4328 ownerId=194624139
transactionname=user_transaction lasttranstarted=2008-09-25T10:09:27.110
XDES=0x6635608 lockMode=U schedulerid=3 kpid=228 status=suspended spid=119
sbid=0 ecid=0 priority=-5 transcount=2
lastbatchstarted=2008-09-25T10:09:27.080
lastbatchcompleted=2008-09-25T10:09:27.080
clientapp={CD292107-6272-4D88-A809-D44434303F0A} hostname=975 hostpid=7040
loginname=APINT\apiRDM isolationlevel=read committed (2) xactid=194624139
currentdb=9 lockTimeout=4294967295 clientoption1=673384544
clientoption2=128024
2008-09-25 10:09:31.44 spid18s executionStack
2008-09-25 10:09:31.44 spid18s frame
procname=mssqlsystemresource.sys.sp_MSmakegeneration line=163 stmtstart=10828
stmtend=11958 sqlhandle=0x0300ff7f587a2f0639854d014e9a00000100000000000000
2008-09-25 10:09:31.44 spid18s update dbo.MSmerge_genhistory with
(rowlock)
2008-09-25 10:09:31.44 spid18s set genstatus = 3,
2008-09-25 10:09:31.44 spid18s coldate = getdate(),
2008-09-25 10:09:31.44 spid18s nicknames = @nickbin
2008-09-25 10:09:31.44 spid18s where generation <=
@current_max_gen and genstatus = 4 and
2008-09-25 10:09:31.44 spid18s coldate not in (select
login_time from sys.dm_exec_sessions) and
2008-09-25 10:09:31.44 spid18s subscriber_number not in
(select s.subscriber_number from dbo.sysmergesubscriptions s
2008-09-25 10:09:31.44 spid18s
inner join sys.dm_exec_sessions p on p.program_name = s.application_name
collate database_default)
2008-09-25 10:09:31.44 spid18s inputbuf
2008-09-25 10:09:31.44 spid18s Proc [Database Id = 32767 Object Id =
103774808]
2008-09-25 10:09:31.44 spid18s process id=processbd46b8
taskpriority=0 logused=356 waitresource=KEY: 9:72057597064577024
(1000078f79b2) waittime=4328 ownerId=194624135
transactionname=user_transaction lasttranstarted=2008-09-25T10:09:27.110
XDES=0x3f67a958 lockMode=X schedulerid=4 kpid=2676 status=suspended spid=135
sbid=0 ecid=0 priority=0 transcount=2
lastbatchstarted=2008-09-25T10:09:27.110
lastbatchcompleted=2008-09-25T10:09:27.017
clientapp={032973B3-0D8D-4CD1-801B-6DCD76765F04} hostname=1004 hostpid=7040
loginname=APINT\apiRDM isolationlevel=read committed (2) xactid=194624135
currentdb=9 lockTimeout=4294967295 clientoption1=536938592
clientoption2=128024
2008-09-25 10:09:31.44 spid18s executionStack
2008-09-25 10:09:31.44 spid18s frame
procname=mssqlsystemresource.sys.sp_MSupdategenhistory line=62 stmtstart=3390
stmtend=4004 sqlhandle=0x0300ff7f2c754b2fda1c4d014e9a00000100000000000000
2008-09-25 10:09:31.44 spid18s update dbo.MSmerge_genhistory with
(rowlock)
2008-09-25 10:09:31.44 spid18s set genstatus = 2,
2008-09-25 10:09:31.44 spid18s art_nick = case when
isnull(@art_nick,0) <> 0 then @art_nick else art_nick end,
2008-09-25 10:09:31.44 spid18s coldate= getdate(),
2008-09-25 10:09:31.44 spid18s changecount = @changecount
2008-09-25 10:09:31.44 spid18s where generation = @gen -- and
guidsrc = @guidsrc
2008-09-25 10:09:31.44 spid18s inputbuf
2008-09-25 10:09:31.44 spid18s Proc [Database Id = 32767 Object Id =
793474348]
2008-09-25 10:09:31.44 spid18s resource-list
2008-09-25 10:09:31.44 spid18s keylock hobtid=72057597064445952
dbid=9 objectname=Prod_database.dbo.MSmerge_genhistory
indexname=c1MSmerge_genhistory id=lock1f46e0c0 mode=X
associatedObjectId=72057597064445952
2008-09-25 10:09:31.44 spid18s owner-list
2008-09-25 10:09:31.44 spid18s owner id=processbd46b8 mode=X
2008-09-25 10:09:31.44 spid18s waiter-list
2008-09-25 10:09:31.44 spid18s waiter id=process92e4d8 mode=U
requestType=wait
2008-09-25 10:09:31.44 spid18s keylock hobtid=72057597064577024
dbid=9 objectname=Prod_database.dbo.MSmerge_genhistory
indexname=nc2MSmerge_genhistory id=lock3d3c3400 mode=U
associatedObjectId=72057597064577024
2008-09-25 10:09:31.44 spid18s owner-list
2008-09-25 10:09:31.44 spid18s owner id=process92e4d8 mode=U
2008-09-25 10:09:31.44 spid18s waiter-list
2008-09-25 10:09:31.44 spid18s waiter id=processbd46b8 mode=X
requestType=wait

[Hugo]

Hi,

No, I do not have a solution for this yet. If you do, please share it. I'll
do the same.

Best regards,