UPDATE Statements May Fail To Replicate If Not Executed As Singleton Updates

  • Article

What is Deferred Update?

If any column that is part of a unique constraint is updated, then SQL Server implements the update as a deferred update, which means as a pair of DELETE/INSERT operations. This deferred update causes replication to send a pair of DELETE/INSERT statements to the subscribers.

SCENARIO

During updating data in a replicated table, you may receive a foreign key error for a delete in a Transactional replication topology with immediate updating subscribers (2-way). The concern here is why the error is generating for a delete when we are doing an update? Ideally, the update should be processed as singleton update.

ERROR MESSAGE

Command attempted:
{CALL sp_MSdel_Users ({4DD9AE58-4C79-43B2-936F-2C8AD63E325A}, 'XXXXX/xas9999', 'XXXXX/S-1-5-21-730164109-1811643884-1700950580-120399', {2A584EAB-8298-455E-AD61-657A51DD4D07})}
(Transaction sequence number: 0x000006A50000034E000100000000, Command ID: 1)
Error messages:
• DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserParmeterValues_Tablename1'. The conflict occurred in database 'databasename', table 'TableName2', column 'UserGUID'. (Source: MSSQLServer, Error number: 547)
Get help: https://help/547

TOPOLOGY
1 Publisher Server and 2 Subscriber
PULL Subscription
Immediate updating subscribers (2-way).

EXPLANATION

First of all, since this is a replication setup we have to find what exactly is going on in background. Is the Update really going as a single Update or as a pair of Delete and Insert? To find this, you can do a simple test:

TEST 1

1. Before the test count the number of rows of MSrepl_transactions and MSrepl_commands table in distribution database by filtering the results based on article_id or publisher_database_id columns.
2. Reproduce the issue and wait until the process is failed and you get error message.
3. Again check the count of MSrepl_transactions and MSrepl_commands table by filtering the results based on article_id or publisher_database_id columns.

If the count of MSrepl_transactions and MSrepl_commands tables increases by only 1, this means only a single Update is executed, otherwise Update is executed as a pair of Delete and Insert.

TEST 2

            You could also use sp_browsereplcmds on the distribution database to confirm if the updates are deferred. Just pass the same transaction sequence number (from the error message, in this case it is 0x000006A50000034E000100000000) in xact_seqno_start & xact_seqno_end parameter of the stored procedure.

If you determine that the Update is executed as a pair of Delete and Insert, please verify if any column that is part of a unique constraint is updated. If this is true, this means SQL Server is implementing the Update as a Deferred Update, which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers.

Microsoft has published a KB article to validate the same: https://support.microsoft.com/kb/238254/EN-US

Now, to resolve this issue and to make sure that the Updates are NOT executed as a pair of Delete and Insert statements you can enable trace flag 8207 on Publisher Server. This trace flag force the updates to be singleton updates. You can enable trace flag -T8207 on the publisher server by running following command:
DBCC TRACEON (8207,-1)
GO

Please note that once you restart SQL Server you have to again enable this trace flag using DBCC command mentioned above. If you want to enable this trace flag permanently please use this trace flag as a startup parameter on SQL Server. For more information on trace flag 8207 please visit https://support.microsoft.com/kb/302341

NOTE:

1. The above scenario\explanation is also applicable to SQL Server 2005 as well.

2. This is not an issue with SQL Server but behaviour by design due to the RDBMS logic for Unique Key updates. To address these concerns, you must change either the business logic in update triggers or enable the trace flags.

Sumit Sarabhai
SE, Microsoft Sql Server

Reviewed by

Anurag Sharma
Technical Lead, Microsoft Sql Server.