CAUSE
When updating the table [MyTable], the deadlock occurs on the pages
between
MSmerge_contents and the [MyTable] index page (the index on the rowguid column). The deadlock occurs when the
sp_MSsetupbelongs stored procedure calls the
view_sel_proc article and in the
view_sel_proc article, the deadlock occurs when data is inserted into the #belong function, as in the following code snippet:
insert into #belong (tablenick, rowguid, flag, partchangegen, joinchangegen)
select ct.tablenick, ct.rowguid, 0,
ct.partchangegen, ct.joinchangegen
from MSmerge_contents
ct with (nolock), #genlist g,
[dbo].[MyTable] v where
ct.tablenick = @tablenick
and ct.generation =
g.generation
and ct.rowguid = v.[rowguid]
UNION
select ct.tablenick, ct.rowguid, 0,
ct.partchangegen, ct.joinchangegen
from MSmerge_contents
ct with (nolock), #genlist g,
[dbo].[MyTable] v where
ct.tablenick = @tablenick
and ct.partchangegen =
g.generation
and ct.rowguid = v.[rowguid]
The SELECT statements generate X-locks on the
MSmerge_contents table
and the table [MyTable] (Index page).
The customer's application INSERT and UPDATE statements generate X-locks on the same tables through the trigger created for Merge Replication. So, when there is a high volume of data to merge for the [MyTable] table, the probability of getting a deadlock is fairly high.
WORKAROUND
The deadlock behavior change seems to have been introduced by index changes in Microsoft SQL Server 7.0 Service Pack 2 (SP2).
One possible workaround is to:
- Look at the indices in the MSmerge_contents table, and then drop the following:
- nc2MSmerge_contents on MSmerge_contents(tablenick, generation)
- nc3MSmerge_contents on MSmerge_contents(tablenick, partchangegen)
- nc4MSmerge_contents on MSmerge_contents(generation, partchangegen)
- Create the following two indexes:
create index nc2MSmerge_contents on MSmerge_contents(generation)
create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
Please remember that index changes in general are intrusive because they change the locking behavior significantly. So, you must make these changes in a test environment and run enough tests to make sure that performance has not degraded.