Replication and concurrency check failed.

Category: sql server replication

Question

Barryb123 on Tue, 19 Mar 2013 22:26:14


I have several sql 2000 servers at outside location and sql 2008 at the corp office.

I have a few tables that I set up with transactional replication with updateable subscriber. Pull.

This was set up at 3 locations.  Things were looking good for a few days it appeared.  I tested changes at both ends good,  I check data integrity good.  I was to the point of linking them into the corp FE.

Then.......all three location came up with an error while preforming the same procedure. updating one of these tables from another of these tables.   (seem like at the same time?)(could be just timing)

  [Microsoft][odbc sql server driver][sql server]Optimistic concurrency check failed. the row was modified outside of this cursor.

I tried tested: shutting down the services "queue, log, snapshot" 

       stopping all agent jobs  (replication)

       disabling all agent jobs (replication)

      ensuring none were running at publisher or subscriber

this worked for a short time but the error returned and eventually had to delete the publications.

that worked.   

the database at the outside locations was written by a 3rd party that is not accessable.

Any help that I can get would be so much appreciated.

Replies

Allen Li - MSFT on Thu, 21 Mar 2013 06:26:11


Hi Barryb123,

First, please whether you are trying to update the rows that exist at the Subscriber but not at the Publisher, the Subscriber cannot update the pre-existing rows. Attempting to update these rows returns an error. The rows should be deleted from the table and inserted again.

Second, you can switch the update mode to “Immediate updating” and check the result again.

Additionally, you can refer to the following document to troubleshoot the conflict.

Updatable Subscriptions for Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151718.aspx

Queued Updating Conflict Detection and Resolution
http://msdn.microsoft.com/en-us/library/ms151177(v=sql.90).aspx

Shehap on Thu, 21 Mar 2013 08:12:23


Adding a little bit more for what Allen said, such replication cases are often related to other DML transactions are running on the same table concurrently with replication that can ends up with an orphaned data incident at subscriber for some data records

exists at subscriber  but not exists at publisher  so you can check that to avoid further such cases

If not able to figure out easily these orphaned  records, it is much to re-initialize the replication