Microsoft KB Archive/295445

= BUG: Deadlock Between Merge Replication and Article Updates =

Article ID: 295445

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Service Pack 2
 * Microsoft SQL Server 7.0 Service Pack 3

-



This article was previously published under Q295445



BUG #: 101508 (SQLBUG_70)



SYMPTOMS
When a merge replication with row filters is enumerating changes, and data is entered or modified at the same time, you may experience a deadlock between the merge process and the customer's process that is updating data. The lock occurs between the article that is being updated and the MSmerge_contents table.



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) </li></ol>

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.

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 Service Pack 2 and later.

Keywords: kbbug kbpending KB295445

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.