|Caching Data with GemFire XD / Using DBSynchronizer to Apply DML to an RDBMS|
This release of GemFire XD has several restrictions and limitations on the use of DBSynchronizer.
Redundancy for DBSynchronizer is controlled solely through the target server groups that you specify in the CREATE ASYNCEVENTLISTENER command. If you wish to deploy only a single DBSynchronizer instance (no redundancy), then you must ensure that the target server group contains only one GemFire XD data store. Otherwise, the DBSynchronizer implementation is deployed to all data stores in the server group.
When multiple data stores host DBSynchronizer, there is no direct way to ensure that a specific GemFire XD data store hosts the active DBSynchronizer instance. If you require a specific member to host the active instance, ensure that only that member is running in the target server groups when you deploy DBSynchronizer and attach the table. You can then start the remaining data stores in the server group, and they will host redundant, standby DBSynchronizer instances.
If a table has an automatically-generated identity column, by default DBSynchronizer does not apply the generated identity column value to the synchronized database. DBSynchronizer expects that the backend database table also has an identity column, and that the database generates its own identity value. If you would prefer that DBSynchronizer apply the same GemFire XD-generated identity column value to the underlying database, then set skipIdentityColumns=false in the DBSynchronizer parameter file. See Configuring and Using DBSynchronizer.
If a synchronized table is updated in the scope of a transaction, then DBSynchronizer delivers the full DML statement to the underlying database instead of synchronizing table values.
DBSynchronizer does not synchronize the results of bulk DML statements to the underlying database, but instead delivers the bulk DML statement to the synchronized database.
DBSynchronizer, AsyncEventHelper Example Code
GemFire XD installs the source code for the built-in DBSynchronizer and AsyncEventHelper classes into the /examples/com/pivotal/gemfirexd/callbacks directory. This source code is identical to the class implementations that are built into GemFire XD. Use the examples to create new classes either by copying and modifying the code for public APIs, or by extending the classes. Keep in mind:
Using triggers with DBSynchronizer is not supported. Triggers that are defined on both the external database and in the GemFire XD system can cause multiple executions of trigger-generated DML.
Handling database connectivity problems
If DBSynchronizer encounters an exception while updating or committing to the database, the batch is retained and the DBSynchronizer thread will continue trying to apply the batch until it is successful.
Concurrent DML operations
If DML operations are executed concurrently on a GemFire XD System that has a foreign key relationship based on parent and child table, a foreign key violation may occur while applying DML operations to the external database. This can occur even if the GemFire XD system successfully executed the DML. Although inserts into the parent and child table occur in order in the GemFire XD system, the inserts may reach DBSynchronizer in reverse order. To avoid this behavior, perform updates to parent and child tables in a single application thread. See How an AsyncEventListener Works.
Queuing DML operations
There is a window in which a DML operation executed on the GemFire XD system has not yet been placed into the internal queue of DBSynchronizer. The record is guaranteed to be placed into the internal DBSynchronizer queue only when the DML operation is completed.
This restriction also applies to DML operations placed on a gateway sender queue for WAN replication.
Case-insensitive identifiers required
If your third-party database provides an option for using case-sensitive SQL identifiers, you must configure the database to use case-insensitive identifiers. For example, if you use MySQL as a backend database you must set the lower_case_table_name server system variable to 1. See lower_case_table_names in the MySQL documentation for more information.
Per the SQL-99 standard, DBSynchronizer treats identifiers as insensitive unless they are delimited in quotes. Specifically, DBSynchronizer converts identifiers to uppercase characters for INSERT operations (but not DELETE operations), so the backend database must be compliant with case-insensitive identifiers.