Advanced Replication Guide : Planning and designing for advanced replication applications : Designing and preparing databases for synchronization : Handling concurrency conflict in synchronized tables in replica
  
Handling concurrency conflict in synchronized tables in replica
solidDB® uses the same concurrency control mechanism to handle such data management functions as online queries and write operations. As a default method of concurrency control, optimistic concurrency control is automatically set for all tables. This means that if two users concurrently attempt to modify the same data, the later attempt fails and an error is returned to the user.
During synchronization, concurrency conflicts can occur through a sequence of events as shown in this example:
1 A replica creates and executes a synchronization script that makes a refresh from a publication.
2 Simultaneously, another user in the replica updates a row that will be refreshed.
3 Before the user commits the transaction, the reply message of the synchronization message arrives at the replica and the engine starts applying the refresh data to the database.
4 The user commits the online transaction.
5 The refresh attempts to modify the same row that the online user already modified.
6 The execution of the synchronization reply message fails because of a concurrency conflict.
The following table shows you the various ways you can handle a concurrency conflict reflected in this example or a similar situation.
Criteria and/or method of recovery
Use this command for recovery
If you do not anticipate concurrency conflicts to happen often, then you can recover from this incident by re-executing the failed reply message in a replica.
The command for re-executing the failed reply is:
MESSAGE msgname EXECUTE
If you anticipate concurrency conflicts to happen often and the re-execution of the message fails because of a concurrency conflict, you can execute the message using pessimistic table-level locking; this ensures the message execution is successful.
In this mode, all other concurrent access to the table affected is blocked until the synchronization message has completed.
The command for executing the message in pessimistic mode is:
MESSAGE msgname EXECUTE PESSIMISTIC
You can define the reply message to use table-level pessimistic locking when it is initially executed.
The command for requesting the reply message in pessimistic mode from the master is:
MESSAGE msgname GET REPLY PESSIMISTIC
As part of the MESSAGE FORWARD operation, the reply message can use table-level pessimistic locking when it is initially executed.
The command for requesting the reply message in pessimistic mode is:
MESSAGE msgname FORWARD TIMEOUT seconds PESSIMISTIC
The synopsis has been placed on two lines for layout purposes. The command is entered as one line.
solidDB® also allows you to define a table to be pessimistically locked using row-level locking. This approach is useful if lots of conflicting updates are expected on the table.
The command for setting a table to use pessimistic locking is:
ALTER TABLE tablename SET PESSIMISTIC
See also
Designing and preparing databases for synchronization