|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.
If a table has an automatically-generated identity column, DBSynchronizer does not apply the generated identity column value to the synchronized database. Instead, the full DML statement is delivered to the underlying database. See Considerations for Auto-Generated Keys for alternate ways of managing auto-generated keys using custom listener implementations or procedures.
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 the 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.