How DBSynchronizer Works

You install DBSynchronizer as an AsyncEventListener on multiple data stores, preferably two. The DML statements executed on SQLFire 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").

Each instance of DBSynchronizer maintains an internal queue to batch up the DML statements and is serviced by its own dedicated thread. This thread picks up DML statements from the queue, and applies them to the external database using prepared statements.

Ensuring High Availability and Reliable Delivery

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.

How Failover Affects Synchronization

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.

If the table has any constraint (primary key, unique) defined, the following types of DML operations do not cause out-of-synch problems when they are reapplied during failover:
  • 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 SQLFire 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.

However, the following types of DML operations do cause out of synch issues when they are reapplied during failover:
  • 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 SQLFire data.