Sybase DC38133-01-0902-01 manual Managing conflicts

Chapter 8. SQL Remote Design for Adaptive Server Enterprise

Managing conflicts

An UPDATE conflict occurs when the following sequence of events takes place:

1.User 1 updates a row at remote site 1.

2.User 2 updates the same row at remote site 2.

3.The update from User 1 is replicated to the consolidated database.

4.The update from User 2 is replicated to the consolidated database.

When the SQL Remote Message Agent replicates UPDATE statements, it does so as a separate UPDATE for each row. Also, the message contains the old row values for comparison. When the update from user 2 arrives at the consolidated database, the values in the row are not those recorded in the message.

First UPDATE succeeds

ID Rep Dept

1 Ann 101 Second UPDATE

2 Marc 101 overwrites the first

3 Shih 104

UPDATE SalesRep

SET Dept=103

WHERE ID = 3

ID	Rep	Dept
1	Ann	101
2	Marc	101
3	Shih	102>103

UPDATE SalesRep

SET Dept=104

WHERE ID = 3

ID Rep Dept

1 Ann 101

2 Marc 101

3 Shih 102>104

Default conflict resolution By default, the UPDATE still proceeds, so that the User 2 update (the last to reach the consolidated database) becomes the value in the consolidated database, and is replicated to all other databases subscribed to that row. In general, the default method of conflict resolution is that the most recent operation (in this case that from User 2) succeeds, and no report is made of the conflict. The update from User 1 is lost.

SQL Remote also allows custom conflict resolution, using a stored procedure to resolve conflicts in a way that makes sense for the data being changed.

165