|Caching Data with GemFire XD / Using DBSynchronizer to Apply DML to an RDBMS|
You install DBSynchronizer as an AsyncEventListener on multiple data stores, preferably two. The DML statements executed on GemFire XD are passed on to the DBSynchronizer and the configured JDBC RDBMS. DBSynchronizer can be installed only on data stores (configured with host-data property set to "true").
Configure the DBSynchronizer queue for both persistence and redundancy to ensure high availability and reliable delivery of events.
Installing DBSynchronizer on more than one data store provides high availability. At any given time only one member has a DBSynchronizer thread active for executing DML on the external database. The threads on other members are on standby (redundant) to guarantee execution of DML if the member with the active DBSynchronizer thread fails. Install no more than one standby DBSynchronizer (redundancy of at most one) for performance and memory reasons.
By default, any pending DML operations residing in the internal queue of the DBSynchronizer are lost if the active member shuts down. You can avoid losing operations by configuring the internal queue of DBSynchronizer to be persistent.
A DML operation may be reapplied to the RDBMS if the member with the active DBSynchronizer thread fails. If the member with the active DBSynchronizer fails while sending a batch of operations, some DML statements in the batch may already have been applied to the RDBMS. On failover, the new DBSynchronizer thread resends the failed batch and reapplies the initial DML operations. When this occurs, the RDBMS may get out-of-synch depending upon the nature of the DML operation, how it modifies table columns, and the presence or absence of column constraints.
A create operation that is reapplied to a table with a primary key. A primary key constraint violation occurs and a SQLException is thrown, but DBSynchronizer ignores the exception.
A create or update operation that causes a unique constraint violation. Reapplying a create or update operation causes a duplicate value, violating the unique constraint. DBSynchronizer ignores the SQLException that is thrown in this situation.
A create or update operation that causes a check constraint violation. Reapplying create or an update (for example, incrementing or decrementing a column value) may cause a check constraint to be violated. DBSynchronizer ignores the SQLException that is thrown in this situation.
In the preceding cases, the constraints prevent the tables from going out of synch with the GemFire XD data.
Reapplying certain update operations (such as update T1 set Col1 = 5 where col2 =7) and delete operations (such as delete from T1 where col1 = 5) do not cause out-of-synch problems.
Create operations on a table without a primary key constraint.
In this case, reapplying a create operation creates additional rows.
Update operations that modify the column value relative to its current value.
Reapplying update operations such as update T1 set col1 = col1 +? where col2 = ? cause the external database to go out-of-synch with the GemFire XD data.