Firebird Documentation IndexFirebird ODBC/JDBC Driver ManualDeveloping with the Firebird ODBC/JDBC Driver → Transactions
Firebird Home Firebird Home Prev: MultithreadingFirebird Documentation IndexUp: Developing with the Firebird ODBC/JDBC DriverNext: Password Security

Transactions

Table of Contents

Locking
Transaction Request Syntax
Two Phase Commit Transactions
More Transactions
MS DTC Transactions

Firebird supports three transaction isolation levels:

The default isolation level of the ODBC/JDBC driver is READ COMMITTED, which maps with read committed in other database systems. Firebird's other isolation levels do not map so easily. In the ODBC/JDBC driver, SNAPSHOT maps to REPEATABLE READ and SNAPSHOT TABLE STABILITY maps to SERIALIZABLE, with some tweaks.

Since v.2.0, the driver has been able to support every transaction configuration that Firebird can support, including table reservation (“table blocking”). That was achieved by incorporating the so-called “EmbeddedSQL” syntax that is native to the old pre-compiler, gpre, to prepare calls to the ODBC API by the function SQLExecDirect.

Locking

Firebird implements optimistic row-level locking under all conditions. A transaction does not attempt to lock a record until it is ready to post an update operation affecting that record. It can happen, though rarely, for an update to fail because another client has a lock on the record, even if the transaction that fails started before the one that secured the lock.

Firebird's record versioning engine is able to achieve a granularity finer than that provided by traditional row-level locking. Versioning allows any number of transactions to read a consistent copy of any given record, even if other transactions are updating the same row simultaneously. Readers and writers never block one another and Firebird's maintenance of record versions is totally transparent to the user.

Transaction Request Syntax

Table of Contents

What the Options Mean

The syntax for an ODBC-friendly transaction request follows.

SET | DECLARE TRANSACTION [LOCAL] [NAME <transaction-name> [USING <namedUniqueWorkspace>]]
[READ WRITE | READ ONLY]
[WAIT | NO WAIT]
[AUTOCOMMIT]
[NO_AUTO_UNDO]
[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY] or REPEATABLE READ
| SERIALIZABLE
| READ COMMITTED [[NO] RECORD_VERSION]}]
[RESERVING <table-name-1> [, <table-name-2>[, ...<table-name-n>] ]
[FOR [SHARED | PROTECTED] {READ | WRITE}] [, ]
        

What the Options Mean

DECLARE TRANSACTION... declares the described transaction, without activating it. SET TRANSACTION..., on the other hand, activates the transaction, temporarily switching the SQL_ATTR_AUTOCOMMIT global attribute of the ODBC API to SQL_AUTOCOMMIT_OFF. The transaction will have to be finished explicitly; when it ends, the abiding rule of the API resumes.

LOCAL limits a transaction to acting only within the context of the current connection.

NAME <transaction-name> is a uniquely-named transaction, prepared for use by any connections in the global environment.

USING <namedUniqueWorkspace> is a uniquely-named transaction workspace in which NAME <transaction-name> can be set to run by any connections in the global environment. Identically named transactions with differing parameters can run in the same named workspace.

Named Transactions and Transaction Workspaces

The construct DECLARE TRANSACTION ... NAME <transaction-name> [USING <namedUniqueWorkspace>] allows explicit transactions to be configured and saved into the global environment in preparation for repeated use for any connection request or by any active connection. An instance of the saved transaction can be called into action by a specific form of the SET TRANSACTION command:

For a connection request:

SET TRANSACTION NAME MyReadTransaction
            

or

SET TRANSACTION NAME MyReadTransaction USING MyDsnDb1
            

for separate requests within a single active connection:

SET TRANSACTION LOCAL NAME MyReadTransaction
            

or

SET TRANSACTION LOCAL NAME MyReadTransaction USING MyDsnDb1
            

and, in this connection, for another request:

SET TRANSACTION LOCAL NAME MyWriteTransaction
            

or

SET TRANSACTION LOCAL NAME MyWriteTransaction USING MyDsnDb1
            

The form SET TRANSACTION ... NAME <transaction-name> [USING <namedUniqueWorkspace>] differs from earlier implementations whereby the configuration set by the SET command would be repeated for the next transaction. The inclusion of the NAME and/or USING clauses makes the configuration repeatable on demand by use of the name.

Important

A return to the usual mode of operation requires a detach/connect cycle.

Ending Explicit Transactions

In SQL, a transaction is completed by a COMMIT or ROLLBACK request. ODBC has methods that do one or the other, such as SQLEndTran. Some programs are able to invoke SQLExecDirect but cannot call SQLEndTran. For those programs it is necessary to call an explicit

SQLExecDirect( hStmt, "COMMIT" )
            

to ensure that the interface will call

   
SQLEndTran( SQL_HANDLE_DBC, hConnection, SQL_COMMIT );
            

Note

If a transaction is initiated locally, the driver will execute SQLEndTran for the local hStmt.

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. Programatically you can use a more flexible transaction stucture. 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 );
      

MS DTC Transactions

The Microsoft Distributed Transaction Coordinator (MS DTC) service is a Windows component that is responsible for coordinating transactions that span multiple resource managers, such as database systems, message queues, and file systems. It can perform global, single-phase or two-phase commit transactions involving MSSQL Server, Sybase and other servers that are able to to work with it. Our ODBC/JDBC driver provides that capability for Firebird servers.

An Example Using MS DTC

// Include MS DTC specific header files.
//------------------------------------------------------------------------------
#define INITGUID
#include "txdtc.h"
#include "xolehlp.h"
    ITransactionDispenser *pTransactionDispenser;
    ITransaction *pTransaction;
    // Obtain the ITransactionDispenser Interface pointer
    // by calling DtcGetTransactionManager()
    DtcGetTransactionManager( NULL,// [in] LPTSTR pszHost,
         NULL,// [in] LPTSTR pszTmName,
         IID_ITransactionDispenser,// [in] REFIID rid,
         0,// [in] DWORDdwReserved1,
         0, // [in] WORDwcbReserved2,
         NULL,// [in] void FAR * pvReserved2,
         (void **)&pTransactionDispenser // [out] void** ppvObject
         );
    // Establish connection to database on server#1
    LogonToDB( &gSrv1 );
    // Establish connection to database on server#2
    LogonToDB( &gSrv2 );
    // Initiate an MS DTC transaction
    pTransactionDispenser->BeginTransaction(
         NULL,// [in] IUnknown __RPC_FAR *punkOuter,
         ISOLATIONLEVEL_ISOLATED,// [in] ISOLEVEL isoLevel,
         ISOFLAG_RETAIN_DONTCARE,// [in] ULONG isoFlags,
         NULL,// [in] ITransactionOptions *pOptions
         &pTransaction// [out] ITransaction **ppTransaction
         );
    // Enlist each of the data sources in the transaction
    SQLSetConnectOption( gSrv1->hdbc, SQL_COPT_SS_ENLIST_IN_DTC, (UDWORD)pTransaction );
    SQLSetConnectOption( gSrv2->hdbc, SQL_COPT_SS_ENLIST_IN_DTC, (UDWORD)pTransaction );
    // Generate the SQL statement to execute on each of the databases
    sprintf( SqlStatement,
      "update authors set address = '%s_%d' where au_id = '%s'",
       gNewAddress, i, gAuthorID );
    // Perform updates on both of the DBs participating in the transaction
    ExecuteStatement( &gSrv1, SqlStatement );
    ExecuteStatement( &gSrv2, SqlStatement );
    // Commit the transaction
    hr = pTransaction->Commit( 0, 0, 0 );
    // or roll back the transaction
    //hr = pTransaction->Abort( 0, 0, 0 );
      
Prev: MultithreadingFirebird Documentation IndexUp: Developing with the Firebird ODBC/JDBC DriverNext: Password Security
Firebird Documentation IndexFirebird ODBC/JDBC Driver ManualDeveloping with the Firebird ODBC/JDBC Driver → Transactions