How to Run a Geode Cache Transaction that Coordinates with an External Database

Coordinate a Geode cache transaction with an external database by using CacheWriter/CacheListener and TransactionWriter/TransactionListener plug-ins, to provide an alternative to using JTA transactions.

There are a few things you should be careful about while working with Geode cache transactions and external databases:

• When you set up the JDBC connection, make sure that auto-commit is disabled. For example, in Java:

  Connection getConnection() throws SQLException {
      Connection con = ... // create the connection
      con.setAutoCommit(false);
      return con;
  }
  

• The BEGIN statement, database operations and the PREPARE statement must all happen in the same connection session. In order to accomplish this, you will need to obtain the same JDBC connection session across multiple CacheWriter and TransactionWriter/TransactionListener invocations. One way to do this would be to look up the connection (from a user managed Map) based on cacheTransactionManager.getTransactionId().

• Make sure that the prepare transaction feature is enabled in your external database. It is disabled in PostgreSQL by default. In PostgreSQL, the following property must be modified to enable it:

  max_prepared_transactions = 1 # 1 or more enables, zero (default) disables this feature.
  

Use the following procedure to write a Geode cache transaction that coordinates with an external database:

1. Configure Geode regions as necessary as described in How to Run a Geode Cache Transaction.
2. Begin the transaction.
3. If you have not previously committed a previous transaction in this connection, start a database transaction by issuing a BEGIN statement.
4. Perform Geode cache operations; each cache operation invokes the CacheWriter. Implement the CacheWriter to perform the corresponding external database operations.

5. Commit the transaction. At this point, the TransactionWriter is invoked. The TransactionWriter returns a TransactionEvent, which contains all the operations in the transaction. Call PREPARE TRANSACTION within your TransactionWriter code.

6. After a transaction is successfully committed in Geode, the TransactionListener is invoked. The TransactionListener calls COMMIT PREPARED to commit the database transaction.