Two Phase Commit Transactions

Connecting to Firebird from ApplicationsFirebird Events

Two Phase Commit Transactions

The ODBC/JDBC driver supports two-phase commit transactions, that is, a single transaction across different Firebird databases.Up to 16 databases can be accessed simultaneously in one such transaction — that is an absolute limit.

The call to start a two-phase commit transaction is:

SQLSetConnectAttr (connection, 4000, (void*) TRUE, 0);

To cancel the common connection:

SQLSetConnectAttr (connection, 4000, (void*) FALSE, 0);

More Transactions

Firebird ODBC by default uses one transaction per connection.Programmatically you can use a more flexible transaction structure.For example, you can use multiple transactions within one connection, whereby a single connection can be using a number of read/write transactions simultaneously.

An Example

HSTMT stmtRd;
HSTMT stmtWr;
SQLAllocHandle( SQL_HANDLE_STMT, connection, &stmtRd );
SQLAllocHandle( SQL_HANDLE_STMT, connection, &stmtWr );
SQLExecDirect( stmtRd, (UCHAR*)
  "SET TRANSACTION LOCAL\n"
  "READ ONLY\n"
  "ISOLATION LEVEL\n"
  "READ COMMITTED NO RECORD_VERSION WAIT\n",
  SQL_NTS );
SQLExecDirect( stmtWr, (UCHAR*)
  "SET TRANSACTION LOCAL\n"
  "READ WRITE\n"
  "ISOLATION LEVEL\n"
  "READ COMMITTED NO RECORD_VERSION WAIT\n",
  SQL_NTS );
SQLExecDirect( stmtRd,(UCHAR*)
  "SELECT CURRENCY FROM COUNTRY"
  "   WHERE country = 'Canada'"
  "   FOR UPDATE OF CURRENCY",
  SQL_NTS );
SQLFetch( stmtRd );
SQLPrepare( stmtWr, (UCHAR*)
  "update COUNTRY\n"
  "set    CURRENCY = 'CndDlr'\n"
  "where  COUNTRY = 'Canada'\n",
  SQL_NTS );
SQLExecute( stmtWr );
SQLExecDirect( stmtWr, (UCHAR*)"COMMIT", SQL_NTS );