lock table for insert/update/delete actions-sync framework 2.1-sqlsyncprovider RRS feed

  • Question

  • I am working on synchronizing multiple instance of client database(sql server 2005) to a database hosted on cloud.

    with multiple instance participating in synchronization with central database on cloud they are more chances of conflicts :


    conflict arise when two clients trying to insert same row on to cloud database.

    To handle this should I implement table lock or row lock in storedprocedures (<tablename>_insert, <tablename>_update, <tablename>_bulkinsert)
    before doing any DML activity or sync framework internally implements any locking mechanisam to handle such cases?

    please advice


    • Edited by saitrips Tuesday, January 29, 2013 9:14 PM
    Tuesday, January 29, 2013 8:52 PM

All replies

  • a conflict in sync framework is when a  copy is updated on the source and the corresponding row in the server is updated as well.

    if your conflicts are coming from inserts, then that's a PK violation actually. are you using identity column for your PK? if you are, locks wont solve that as you really have duplicate PKs.

    Wednesday, January 30, 2013 1:37 AM
  • ya sorry i was referring to conflicts on updates.

    what is the best way to handle. should I use row lock ? 


    Wednesday, January 30, 2013 2:24 AM
  • if one client syncs first, then its update on the server row would lock the row already and the lock will be in effect until the transaction commits. it's locking/concurrency issue and not a conflict from a sync perspective.

    Wednesday, January 30, 2013 2:52 AM