<< Click to Display Table of Contents >> Navigation:  Application development > Database driver characteristics > SQL drivers > SQL transaction control

To control explicit transactions, use DbiBeginTran and DbiEndTran. Except for explicit transactions, the BDE isolation level is Read Committed, with auto-committed modifications. Some SQL drivers support only the server default isolation level inside of an explicit transaction. To verify the actual isolation level used, call DbiGetTranInfo after a successful call to DbiBeginTran.

Example 1: No explicit transaction

The SQL driver automatically starts a server transaction if necessary:

DbiGetNextRecord (hCursor, dbiWRITELOCK, &myRecBuff, NULL);

The application changes the record buffer data:

DbiModifyRecord (hCursor, &myRecBuff, TRUE);

If the record modification succeeds, it is automatically committed to the database.

Example 2: Explicit transaction used

The application uses a transaction:

DbiBeginTran (hDb, xilREADCOMMITTED, NULL);

The SQL driver starts a server transaction:

DbiGetNextRecord (hCursor, dbiWRITELOCK, &myRecBuff, NULL);

The application changes the record buffer data:

DbiModifyRecord (hCursor, &myRecBuff, TRUE);

The application can make more changes in the transaction:

DbiEndTran (hDb, NULL, xendCOMMIT);

The SQL driver commits the server transaction.

InterBase: By default, when the InterBase SQL Link driver's SQLPASSTHRUMODE is set to SHAREDAUTOCOMMIT, the BDE uses the InterBase API call isc_commit_transaction to commit transactions, which close all cursors. When using implicit transactions, running a new query makes the BDE pre-fetch all records of the previous query, if it is still open. When using an explicit transaction, a pre-fetch of all open queries will occur when the transaction is committed. If the result sets of the opened queries are large, then slower performance can occur.

When using Interbase 4 with the InterBase SQL Links driver, you can improve performance for implicit transactions by setting DRIVER FLAGS to 4096. This makes the BDE use isc_commit_retaining to keep cursors open; this avoids having to pre-fetch records when a commit occurs. Setting DRIVER FLAGS to 4096 does not effect explicit transaction behavior; when an explicit commit occurs, any open queries on that connection will be pre-fetched.

Transaction isolation levels

Extended transaction isolation levels are supported. If an unsupported isolation level is specified in DbiBeginTran, the next-highest supported isolation level is used. If the requested isolation level is higher than any supported isolation level, then an error is returned (DBIERR_NOTSUPPORTED). The highest level (most isolated) level is Repeatable Read, then Read Committed, and finally Dirty Read. As always, you can verify the actual isolation level that was used by calling DbiGetTranInfo.

This database property is used with DbiGetProp to retrieve the server's default transaction isolation level:

dbDEFAULTTXNISO                , ro eXILType                Server's default transaction isolation level

Compatibility

InterBase
Supports Repeatable Read and Read Committed. The wait mode is set to NO WAIT.

Sybase
Supports only the server default, Read Committed.

Oracle
Supports Read Committed and Repeatable Read. However, a Repeatable Read transaction is always READ ONLY.

Informix
In some cases, when connecting with your Informix database, your BDE application overrides the current Informix transaction isolation settings. The following table shows under which circumstances these overrides occur.

DB2
Supports all BDE transaction isolation levels. Any DB2 isolation levels not supported by the BDE are converted to Read Committed.

The following table shows the changes from previous versions of BDE.

You can maintain compatibility with pre-BDE 2.5 behavior by setting the DRIVER FLAGS parameter in the BDE configuration file. All SQL drivers have a field called DRIVER FLAGS in the DRIVER INIT section. To obtain pre-BDE 2.5 transaction behavior, set the bit corresponding to 0x0200 (512 decimal).