SQL 2008:Peer 2 Peer Replication: Update-Update Conflict on the same node

Question

My environment is comprising of 2 SQL servers setup with P2P replication in SQL 2008, 2 Web servers connect to only one SQL server at a time and make changes to the published tables and 2 Application servers which connects to both the SQL servers all the time but they only read data.

We chose P2P since my app servers are in different locations which read data from both the SQL servers and we use it as HA solution.

Since we are changing data at only one SQL server, thought we should never get update-update conflicts but we are now on the same node.

A conflict of type 'Update-Update' was detected at peer 3 between peer 1 (incoming), transaction id 0x0000000000725ca2 and peer 1 (on disk), transaction id 0x00000000007259e9 (Source: MSSQLServer, Error number: 22815)

We are making changes only at Server A and Server B is my backup server.

I made a varchar field updated in 1 row on a table in server A then it gets replicated to Server B
then I made the same field updated for 160 rows on the same table in Server A then it gets replicated to Server B
then I reverted back the change made to the field on all 160 rows on the same table in Server A then my replication failing with Update-Update conflict.

I'm not sure whats causing this because I'm not making any changes on Server B. Even though its P2P, my current replication is from Server A to Server B.

Could someone please help me where I'm doing wrong?

Thanks
Nagi

Answer 1

I doubt you're doing anything wrong :)  Can you tell me how you "reverted the change back"?  I assume you just did an update of some sort.  Regardless I would try to figure out what the problematic transaction is.

Now that you know the txn id, look for the related commands in MSrepl_commands, which can be done via sp_browsereplcmds. Since this error is raised in the distribution agent, you may want to look at the proc to see if you can see where the problematic statements are.

But yes, it's weird that the conflict is between peer 1 and peer 1, which almost doesnt make sense.

Answer 2

Do you have any triggers on the table you are updating?  While I haven't seen this with peer-to-peer, I have seen this in merge.  While changes move via a different mechanism, conflict detection is still conflict detection.  I've seen merge throw conflicts when you make a change that fires a trigger that updates the table that was just changed.

---

Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals

Answer 3

Thanks Greg & Mike for the response.

Actually I'm doing the changes using the application where I can set a skill value to Close or Open. This udpates the Status field value to Close or Open and another filed Concatfiled to (E+Concatfiled ) or (O+Concatfiled).
When the above table gets updated, the application also updating 2 more published tables with date&time in one table and insert the user details to a log table.

I can either update a single Skill value or All skills at time. The change is actually being applied to Peer1 all the time and the conflict is when this is replicated to Peer2 but the error message at Peer2 shows as
A conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming), transaction id 0x0000000000725ca2 and peer 1 (on disk), transaction id 0x00000000007259e9 (Source: MSSQLServer, Error number: 22815)
 
Here are the commands its running on Peer2, seems like they are rolling back.

 

begin tran
  
exec sys.sp_MSgetpeerlsns N'Test_P2P',1

  
exec [dbo].[sp_MSupd_dboTest_SkillOpenClose] default,default,default,default,'OPEN','O99000000N00000000',445,0x30,0x010000009263720000000000,0x01000000B163720000000000
exec sys.xp_replposteor 0x1A004E005800500052004400440053005700490054003000310000000E005700490054005F0064006200000001000000DDB7228E00001199000020680004
exec [dbo].[sp_MSupd_dboTest_DateTime] default,'07142009182036',default,20,default,'AG11',0x0A,0x010000009363720000000000,0x01000000B263720000000000 
exec sys.xp_replposteor 0x1A004E005800500052004400440053005700490054003000310000000E005700490054005F0064006200000001000000DDB7228E000011990000206A0004exec [dbo].[sp_MSins_dboWIT_Log] 'nmerla','2009-07-14 18:20:36.2030000','SKILL UPDATE: EMERG. OPENED FINANCIAL CARE SKILL: CC1_PG11.033_Hotline',0,'open one',0x01000000B363720000000000
exec [dbo].[sp_MSins_dboTest_Log] 'username','2009-07-14 18:20:36.2030000','SKILL UPDATE: Emg. OPEN ',0,'open one',0x01000000B363720000000000 
exec sys.xp_replposteor 0x1A004E005800500052004400440053005700490054003000310000000E005700490054005F0064006200000001000000DDB7228E000011990000206C0003
if @@trancount > 0 rollback tran 
dbo.sp_MSupdatelastsyncinfo N'NODE1',N'Test_db', N'Test_P2P', 0, 6, N'A conflict of type ''Update-Update'' was detected at peer 2 between peer 1 (incoming), transaction id 0x00000000007263b1 and pee' 
 

The log table & DateTime on the peer2 node is getting updated but not the Test_SkillOpenClosetable. We don't have any triggers on the database.

sys.sp_MSgetpeerlsns N'Test_P2P',1Can you give me a query to get the  commands SQL running using the tran id?

Thanks
Nagi

Answer 4

This looks to be a known issue with P2P when you do a dummy update, i.e. set a column to itself.  It's slated to be fixed in the upcoming SQL 2008 CU4, but if this is blocking production I would open a support case with CSS to ensure it's the same issue.

Answer 5

We are not setting a column to itselft, we are doing a substring from the 2nd character and appending E or O to the substring before updation. Thanks for the support. May open a case with MS after some more research.

Answer 6

Hi,

We got a problem with a dummy update on a peer to peer replicated table with conflict detection.  When we update a column to itself, the hidden P2P id ($sys_p2p_cd_id) is changed but the transaction is put in transaction log then not grabbed by the log reader.  So we end with a de-synchronisation of $sys_p2p_cd_id column in both database and the next update is rejected by the conflict detection.

It took a long time to find the cause of this problem but it seems that "dummy update" are updating $sys_p2p_cd_id column but are not replicated.
Is it a known bug and will it be part of future fix?

I created a sample scenario to illustrate this bug (with two P2P database).  I can provide it to you if you need.

Best regards,

Fred

Answer 7

Fred,

We have also been studying this problem and have come across the same result -- that a dummy update causes the update following it to create a conflict.  Thanks for your insight on the log reader.  We did not observe this behavior in Sql Server 2005.

Answer 8

Greg, do you know if this was ever fixed and provided in a cumulative update?  I didn't see it listed with CU4.

Thanks.

Answer 9

I see now that you were probably talking about CU4 for SP1 (slightly confusing there) which isn't out yet, so I guess we'll just have to wait and see.

Answer 10

Did CU4 for SP1 fix your issue with $sys_p2p_cd_id ?

I have the same issue and it is still occurring even after applying CU4 for SP1.  This only happens on one of the peers though.  The other one is able to send successfully.

Answer 11

We seem to be experiencing a similar issue.  We are running CU7 for SP1 on an x64 platform.

Can someone confirm if the dummy update issue still exists post-CU7?

Thank you!

Answer 12

We are also experiencing this problem. We installed CU10 for SP1 and are still having the problem. Does anyone have any ideas for a workaround until this gets fixed?

Update: It appears that the CU10 (and very likely CU4) patch really did resolve the issue, however, any rows with differing values for $sys_p2p_cd_id between the 2 servers prior to the patch would not be corrected. My solution to the pre-existing desynchronized rows was to copy the data to an intermediate, non-replicated table on one of the servers, delete the rows from both servers simultaneously (accepting the resulting replication conflict log records), removing the conflict log rows using the conflict viewer from both servers, and finally reinserting the rows into the table on one of the servers from the intermediate table. The inserted rows were then replicated over to the other server and they are back in sync. I understand this may not be the best solution, but I have not used a snapshot to synchronize before and getting the data back available was critical, so I chose the more labor intensive over the unfamiliar path as a solution...

Answer 13

Hi Stephen (and others),

We are experiencing this problem too:

An update-update conflict between peer 1 (incoming) and peer 1 (on disk) was detected and resolved. The incoming update was skipped by peer 2.

But for us I'm quite sure we do not make any dummy update. Anyway, I updated peer 2 with the SP2 hoping it will solve the issue... unsuccessfully. The issue is still here even for the new replicated lines. So my questions are:

1. Do the SP2 really corrects this issue ?
2. Do I have to drop and recreate the replication before seeing an amelioration?
3. Do I have to install SP2 on peer 1 too ?

A desperate techguy,

Romain

---

Romain

Answer 14

I had faced the same issue before when I removed subscription and recreated it. Seems it is a bug on peer-to-peer replication.

A work-around is, remove all publication and distribution settings from all participant nodes and then recreate distribution db and peer-to-peer replication

George

---

George the DBA

Answer 15

Hey George, are u on SQL 2008 R2? is this problem still with R2?

what was ur scenarios, are u saying, u drop subscription/recreated and it started causing Update-Update conflicts? is tht right?

 

---

SA

Answer 16

SA12 we are still having this issue on sql 2008 R2 SP1...

Answer 17

There was this for SQL 2008. http://support.microsoft.com/kb/973223 

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941