Post reply

Updating the same record at the same time using merge replication

  • January 24, 2007 at 4:19 am

    #92955

    I'm currently setting up merge replication (SQL 2000). There is a "slim" chance that different subscribers will attempt to update the same column of the same row at the same time (using something like "update stock set qty=qty-1 where id=1000 and branch=48"). Only one of these updates will be committed, thereby leading to inaccurate stock information.

    I think I've read somewhere that transactional replication updates subscribers by using the actual SQL statements. Is there a way you can make merge replication do this? If not, is there a recommended way of dealing with such conflicts? Or am I going to have to do a whole heap of work to make sure this doesn't occur ?

    Any suggestions would be very much appreciated

    Thank you

  • January 24, 2007 at 7:28 am

    #685200

    Replication is one of those things I've read a LOT about but haven't had to put in to practice (yet).

    However, I do know that you can specify what "conflict resolution method" the merge agent will use when a conflict occurs. It detects the conflict by comparing what the subscriber had prior to a subscriber data change with what the publisher has (I think).

    Anyhow, you can choose resolution methods such as latest change wins (specifying a "last updated" column), highest/lowest value wins or a custom conflict resolver that you can write yourself.

    I don't think that you can have the update statement merge in the way you've specified. I've seen other stock systems keep a table akin to "stock on hand modications" so that once a row is written it isn't changed and the data from all rows is used to calculate quantities. This way you'll never have conflicts at the data row level, although you may still sell stock that you don't actually have if two sites sell the same stock before they exchange data

  • January 25, 2007 at 4:38 am

    #685472

    Thanks very much for you input, Ian. It is very much appreciated. Your reply confirmed what I had suspected (ie, I need to get some work done !)

  • January 25, 2007 at 7:25 am

    #685524

    Merge definitely has some latency issues - even when the agents are running continuously. Where the same row is updated the conflict resolver is very useful - you can monitor the conflict tables for new rows, then send alerts to your administrator to resolve immediately, however for your issue the latency will still be a problem.

    You should consider Transactional Replication with Immediate Updateable Subscribers. A lock is acquired at the publisher (through MSDTC) when the subscriber is updated, which in-turn causes the pub to be updated "immediately".  Trans repl in my enirvironment offers 1 to 3 seconds latency - much better than merge.

    Regards,

    Chris B. MCDBA MCSE OCP

    http://www.MSSQLConsulting.com

     

    Chris Becker bcsdata.net

Viewing 4 posts - 1 through 3 (of 3 total)

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