14 июля 2009 г. 16:21
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?
14 июля 2009 г. 19:04Модератор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.
15 июля 2009 г. 2:05Модератор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
15 июля 2009 г. 15:23
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?
- Изменено $Nagi$ 15 июля 2009 г. 15:37 inserting code block
15 июля 2009 г. 23:37Модератор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.
17 июля 2009 г. 3:13We 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.
3 августа 2009 г. 18:53
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.
10 августа 2009 г. 19:00Fred,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.
8 сентября 2009 г. 20:53Greg, do you know if this was ever fixed and provided in a cumulative update? I didn't see it listed with CU4.
8 сентября 2009 г. 20:58I 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.
15 октября 2009 г. 13:29Did 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.
30 апреля 2010 г. 20:07
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?
28 октября 2010 г. 12:29
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...
7 июля 2011 г. 15:11
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:
- Do the SP2 really corrects this issue ?
- Do I have to drop and recreate the replication before seeing an amelioration?
- Do I have to install SP2 on peer 1 too ?
A desperate techguy,
8 сентября 2011 г. 14:51
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 the DBA
15 января 2012 г. 7:41
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?
18 января 2012 г. 21:05SA12 we are still having this issue on sql 2008 R2 SP1...
19 января 2012 г. 1:13Модератор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
20 августа 2012 г. 21:56
Are you able resolve this issue?
I think I am also noticing the same after SQL 2008 R2 +SP1+CU6.
Do we need to remove all the replication and redo it after applying the Service Pack to resolve this issue.