Merge replication - conflicts with primary keys

  • 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

  • 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[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply