My company is using merge replication in combination with Mobile devices.
During merge replication there are situations that a record is updated several times.
While investigating msmerge_update_sp_xxxx stored procedures in combination with SQL Profiler and triggers, I think I discovered a strange behaviour in the way that records are updated during a merge synchronization process. Correct me if I am wrong.
When a synchronised record is marked for update:
Step I -> For every Filter column that is updated an update statement is executed.
Step II -> When an indexing column is updated, a separate update statement is executed.
Step III -> For non indexing and non filter columns finally an update statement is executed.
The multiple update executions can cause troubles. Only after all updates have been transmitted, the server holds a valid record that ever existed at the subscriber.
But during a transaction... triggers are able to react on changes made to records and (wrong) decisions can be taken based on the incomplete updated data. I agree that triggers can be marked NOT FOR REPLICATION but then they aren't fired at all during the replication process and the action will not be performed at all!
A simple record structure (datatypes int) of table has columns Fx, Fy, Ix, Iy, Nx, Ny
- Fx and Fy are Filter Columns,
- Ix and Iy are Indexed Columns
- Nx and Ny are non indexed, non filter columns
Record R1(Fx=1, Fy=1, Ix=1, Iy=1, Nx=1, Ny=1)
Record R1 is updated at the subscriber by statement
set Fx=2, Fy=2, Ix=2, Iy=2, Nx=2, Ny=2
where Guid = @Guid;
The user synchronizes.
Step Ia-> Merge replication sends update statement to update Filter column Fx
-->R1(Fx=2, Fy=1, Ix=1, Iy=1, Nx=1, Ny=1)
Step Ib-> Merge replication sends update statement to update Filter column Fy
-->R1(Fx=2, Fy=2, Ix=1, Iy=1, Nx=1, Ny=1)
Step II-> Merge sends update statement for Indexes columns Ix, Iy
-->R1(Fx=2, Fy=2, Ix=2, Iy=2, Nx=1, Ny=1)
Step III-> Merge replication executes the update statement for the other columns
-->R1(Fx=2, Fy=2, Ix=2, Iy=2, Nx=2, Ny=2)
Only after the update of step III the record holds a consistent state with values that definitely existed.
Updating records in steps breaks the rule that an atomic transfer cannot be subdivided and must be processed in its entirety or not at all. Sure, the update is performed as a whole inside a transaction, but the record update is subdivided in several update statements and that is incorrect!
Vital information is lost this way. Fx, Fy, Ix, Iy, Nx and Ny were updated at the same time, so inside a trigger the:
- inserted table should have values (Fx=2, Fy=2, Ix=2, Iy=2, Nx= 2, Ny=2)
- deleted table should have values (Fx=1, Fy= 1, Ix=1, Iy=1, Nx= 1, Ny=1)
Unfortunately this is not the case. Merge replication splits the updates.
After Step Ia the:
- inserted table has values (Fx=2, Fy=1, Ix=1, Iy=1, Nx=1, Ny=1)
- deleted table has values (Fx=1, Fy=1, Ix=1, Iy=1, Nx=1, Ny=1)
After Step Ib the:
- inserted table has values (Fx=2, Fy=2, Ix=1, Iy=1, Nx=1, Ny=1)
- deleted table has values (Fx=2, Fy=1, Ix=1, Iy=1, Nx=1, Ny=1)
The information of the new values for Ix and Nx aren't available yet. Information that Fx is updated is not available!
After Step II the:
- inserted table has values (Fx=2, Fy=2, Ix=2, Iy=2, Nx=1, Ny=1)
- deleted table has values (Fx=2, Fy=2, Ix=1, Iy=1, Nx=1, Ny=1)
In step II the information that Fx and Fy have been changed is not available and the updated data of Nx and Ny isn't available yet.
After Step III the:
- inserted table has values (Fx=2, Fy=2, Ix=2, Iy=2, Nx=2, Ny=2)
- deleted table has values (Fx=2, Fy= 2, Ix=2, Iy=2, Nx=1, Ny=1)
Step III looses the information that Fx, Fy, Ix and Iy have been changed.
There are more side effects. A trigger could be checking a logical key. Step Ia, Ib and II for example simply could cause a violation of such a check, because Record R1 holds an inconsistent state! Combination Ix=2, Nx=1 doesn't have to exist in the original table!
What if data needs to be updated if Ix has been changed. After step II, Nx isn't updated yet, so that value can't be used. After Step III, the trigger doesn't know column Ix is updated.
I updated the row in one step, why is the update performed in multiple steps?
Marking a trigger not for replication is not an option in some situations, some actions (like updating redundant data columns) need to be done and send back immediately to the client. Running a SQL job simply isn't always an option.
In another closed topic, the response was that replication was acting like it should. I think this conclusion is incorrect.
I think merge update procedure really need a redesign, where an update is performed as exactly one statement.