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 database executes a synchronization script to receive a refresh from a publication.
2 Simultaneously, another user of the replica database 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 database 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 describes the various ways that you can handle a concurrency conflict that occurs as a result of this example or a similar situation.
Criteria and method of recovery
Statement to use
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.
If you anticipate concurrency conflicts to happen often and for the re-execution of the message to fail because of a concurrency conflict, you can execute the message by using pessimistic table-level locking; this ensures the message execution is successful.
In this mode, all other concurrent access to the affected table is blocked until the synchronization message has completed.
MESSAGE EXECUTE with PESSIMISTIC option, see MESSAGE EXECUTE.
You can configure the reply message to use table-level pessimistic locking when it is initially executed.
As part of the MESSAGE FORWARD operation, the reply message can use table-level pessimistic locking when it is initially executed.
MESSAGE FORWARD with PESSIMISTIC option, see MESSAGE FORWARD.
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.
ALTER TABLE with PESSIMISTIC option, see ALTER TABLE.