Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge replication - conflicts with primary keys Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2008 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:04 AM
Points: 21, Visits: 272
I am running merge replication (pull) between few dozens leptops and once in a while when two people update information in close proximty in time, I will get a primary key violation. It happens because application that running in laptops does next steps :

BEGIN TRANSACTION
DELETE FROM TABLE1 WHERE Key_Field = @KEY
DELETE FROM TABLE2 WHERE Key_Field = @KEY
….
DELETE FROM TABLEn WHERE Key_Field = @KEY

INSERT INTO TABLE1… the same keys
INSERT INTO TABLE2… the same keys

INSERT INTO TABLEn… the same keys
END TRANSACTION

I can not change application in order to replace delete + insert with update, it is hardcoded and I don’t want to change table structure and define another primary
Key.

What is the best way to handle this error. Should I redesing the table and primary keys or there is another way just remove all rows from replicated tables where Key_Field = @KEY on Subscriber? My intent is resolve conflicts without using the interactive conflict resolution.
Any assistance in the matter would be greatly appreciated.

My System is:
Publisher: SQL Server 2005 std + SP2 + CU8
Subscribers: SQL Server 2005 Express + SP2 + CU8

--CREATE Publication
exec sp_addmergepublication
@publication = N'Pub_MyDB',
@description = N'Merge publication of database ''MyDB'' from Publisher ''MYSERVER''.',
@sync_mode = N'native',
@retention = 14,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'false',
@alt_snapshot_folder = '\\...\repldata',
@compress_snapshot = N'true',
@ftp_port = 21,
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@dynamic_filters = N'false',
@conflict_retention = 14,
@keep_partition_changes = N'false',
@allow_synctoalternate = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0,
@use_partition_groups = null,
@publication_compatibility_level = N'90RTM',
@replicate_ddl = 1,
@allow_subscriber_initiated_snapshot = N'false',
@allow_web_synchronization = N'false',
@allow_partition_realignment = N'true',
@retention_period_unit = N'days',
@conflict_logging = N'both',
@automatic_reinitialization_policy = 0

-----------------BEGIN: Script to be run at Publisher 'Publisher'-----------------
DECLARE @subscriber VARCHAR(120)
SET @subscriber = 'XP_MYSYBSCRIBER_O'
use [GilatConfig]

exec sp_addmergesubscription @publication = N'Pub_MyDB',
@subscriber = @subscriber,
@subscriber_db = N'MyDB',
@subscription_type = N'pull',
@subscriber_type = N'local',
@subscription_priority = 0,
@sync_type = N'Automatic'


GO
-----------------END: Script to be run at Publisher 'Publisher'-----------------
-----------------BEGIN: Script to be run at Subscriber 'Subscriber'-----------------
use [GilatConfig]
DECLARE @publisher VARCHAR(120)
DECLARE @distributor VARCHAR(120)
SET @publisher = 'MYSERVER'
SET @distributor= 'MYSERVER'
exec sp_addmergepullsubscription @publisher = @publisher,
@publication = N'Pub_MyDB',
@publisher_db = N'MyDB',
@subscriber_type = N'local',
@subscription_priority = 0,
@description = N'',
@sync_type = N'Automatic'

-----------------END: Script to be run at Subscriber 'Subscriber'-----------------


-- Merge Agent
@ECHO OFF
REM -- Declare the variables.
SET Publisher= MYSERVER
SET Subscriber=%COMPUTERNAME%
SET PublicationDB=MYDB
SET SubscriptionDB= MYDB
SET Publication=Pub_ MYDB
SET DistributorLogin=XXXXXXX
SET DistributorPassword=XXXXXXX
SET PublisherLogin=XXXXXXX
SET PublisherPassword=XXXXXXX
@ECHO ON
REM --Start the Merge Agent with concurrent upload and download processes.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publication %Publication% -Publisher %Publisher% -Subscriber %Subscriber% -Distributor %Publisher% -PublisherDB %PublicationDB% -SubscriberDB %SubscriptionDB% -PublisherSecurityMode 0 PublisherLogin %PublisherLogin% -PublisherPassword %PublisherPassword% -OutputVerboseLevel 2 -SubscriberSecurityMode 1 -SubscriptionType 1 -DistributorSecurityMode 0 -DistributorLogin %DistributorLogin% -DistributorPassword %DistributorPassword% -Validate 3 -ParallelUploadDownload 0 -StartQueueTimeout 60 -QueryTimeout 600




Post #538489
Posted Thursday, March 10, 2016 2:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:37 AM
Points: 468, Visits: 1,646
As long as your application allows users to specify the primary key you'll get these primary key violation conflicts. In a non-replicated environment the users would not be allowed to this same operation: the primary key would prevent them from creating the duplicate row.

I would suggest to adapt the application such that it allows the users to update the rows instead of deleting and then recreating them. Plus make the users aware of the problem they're creating when deleting and recreating the same values.

An alternative approach could be to have the software not physically delete the rows upon the ned user's request, but instead update a column indicating the row is not to be shown anymore, i.e. 'soft-deleted'. When recreating a previously 'soft-deleted' row, you do not insert a new row but update the soft-deleted row to remove the 'deleted' marking. This way the row's guid will remain the same. A conflict will still occur if the laptops haven't been synced in time, but this conflict will only be a type 2(Column update conflict) which is easy to resolve once you know which is the latest update. The application logic for creating a new row is going to be a little more complicated, as well as you now need to check for the "deleted" column in any and of the queries referencing this table. But it does solve your problem




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1768183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse