locked
Will there be a PK conflict if there are multiple cleints creating new records with autogenerated PK's RRS feed

  • Question

  • I understand that with sync services the server does not distinguish one client from another.

     

    Having multiple clients and given that the server does not distinguish each cleint, what is to keep there from being repeated Primary Key constaint violations when the clients create new records in a table that has an auto generated Identity field for a primary key?  Just to clearify this question let us say we are setting up an app for salesmen to take orders. the orders table has an integer Primary key which is an auto generated Identity field. Let us say that salesman A and Salesman B both go out on the first day and take an order. Their individual local client DBs will both create an new record in the orders table with a primary key of 1. When they are both synced ("Upload Only" for orders) what is to keep sync services from producing a primary key violation every time or overwriting the records that share a PK?

    • Moved by Hengzhe Li Friday, April 22, 2011 7:44 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, June 26, 2008 2:13 PM

Answers

  • there are two issues here in this post:

     

    1. identity range management: this is not currently supported in this release. however, this can be relatively easily managed by the application by setting the identity range at the client after the inital sync. the dll to alter the seed and incremental value of the ID column is supported at the client db.

     

    2. server to know the clients: this will need to have a table on the server db to store the client IDs, the ID is avaiable in the syncSession so you can put some logic to retrive it.

     

    but for the particular problem you described, #1 should be able to resolve it.

     

    thanks

    Yunwen

    Thursday, June 26, 2008 8:19 PM
    Moderator

All replies

  • there are two issues here in this post:

     

    1. identity range management: this is not currently supported in this release. however, this can be relatively easily managed by the application by setting the identity range at the client after the inital sync. the dll to alter the seed and incremental value of the ID column is supported at the client db.

     

    2. server to know the clients: this will need to have a table on the server db to store the client IDs, the ID is avaiable in the syncSession so you can put some logic to retrive it.

     

    but for the particular problem you described, #1 should be able to resolve it.

     

    thanks

    Yunwen

    Thursday, June 26, 2008 8:19 PM
    Moderator
  • There is a good section in our documentation that discusses choosing a primary key in distributed environments at the following location:

     

    http://msdn.microsoft.com/en-us/library/bb726011(SQL.100).aspx

     

    Let us know if this helps.

     

    Sean Kelley

     

     

    Thursday, June 26, 2008 10:27 PM
    Moderator
  • What about using one of these SP's to reset the clients Identity range? Or am I missunderstanding the application of these procedures?

    • Sp_adjustpublisheridentityrange
    • Sp_addmergearticle


     

    Friday, June 27, 2008 2:14 PM
  • those two stored procedures provide the essential ideas how the ID range is managed. but they are designed for merge replication so I doubt you can directly re-use them for your sync service applications.

     

    thanks

    Yunwen

     

    Friday, June 27, 2008 4:27 PM
    Moderator
  • ok, well I may create the client key values programmatically specific to the client machine instead of having SQL auto-generate it, or us the following T-SQL command to reset the Identity seed to a certain machine specific start point if the current start seed is 1.

    ALTER TABLE test ALTER COLUMN TestID IDENTITY (<ClientID + 1>, 1)

     

    I'm just having trouble extracting the ClientID and the current Identity seed. Can you point me in the right direction to get the current Identity seed from the cleint?

    Friday, June 27, 2008 8:08 PM
  • I would recommend you have a table on the server that holds the current ID range, and everytime when a new client sync to it ( or a client with a full range already on it ), your app will invoke your own logic to distribute the ID range to the client and client will reset the range in its database.

     

    this approach, will not only take care the inital sync for new clients, but also take care the existing client with full range --- this is actually roughly what the merge replication uses to handle the ID range.

     

    thanks

    Yunwen

    Friday, June 27, 2008 8:45 PM
    Moderator
  • Sounds like a winner, Thanx!

    Monday, June 30, 2008 4:11 PM
  • There’s strange behavior of SqlCeClientSyncProvider in Sync Services for ADO.NET that I’d like to be clarified.

     

    We have an auto-increment PK and use manual identity range management (MIRM) solution similar to described above and in more detail at http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication.

     

    The problem is that when SqlCeClientSyncProvider applies inserts from the server to the client DB the auto-increment counter (AUTOINC_NEXT) gets incremented even though server IDs are inserted. Now this behavior seems strange since:

    • It does not solve the initial PK violation problem which MIRM is aimed to solve (two clients might still produce identical IDs).
    • It interferes with the MIRM by making holes in ID sequences and thus reducing the number of inserts that a client would be able to do.

    Unfortunately, this behavior does not seem to be configurable; it seems to be controlled by a SeTransactionFlags.GENERATEIDENTITY flag which is set in SqlCeClientSyncProvider.EnterSystemAPI() private method.

     

    A workaround that I know of is to cache the AUTOINC_NEXT value before synchronization and reseed with the cached value after synchronization.

     

    Questions:

    1. Are there any architectural reasons for incrementing the auto-increment counter during synchronization?
    2. Is there any way to override that behavior?
    3. Is there any news on the “new techniques” mentioned at http://forums.microsoft.com/sync/ShowPost.aspx?PostID=1335337&SiteID=75 ?

    Versions:

    Tested with SQL CE 3.5; Sync Services for ADO.NET 2.0 (as a part of Sync Framework v1.0).

    The Microsoft.Synchronization.Data.SqlServerCe, Version=3.0.0.0 seem to implement the same behavior, didn't test it though.

    Monday, December 8, 2008 2:57 PM