Starting a Transaction - Firebird

SQL

The SQL statement for starting a transaction has the following syntax:

The final clause RESERVING implements the optional table reservation, discussed in the previous chapter. Its syntax further breaks down to

You can try out this statement in isql. Open the employee.fdb database and start a new transaction as follows:

Next, open another isql shell, or another utility tool that lets you configure transactions, and start another read-write transaction on the same database:

Now, from this interface, try to update any row in employee.fdb:

As expected, this transaction was blocked from updating any row in EMPLOYEE because, in SELECTing from the same table, the first transaction had acquired a table level lock. The second transaction had NO WAIT as its lock resolution setting and immediately excepted because it could not perform the update.

The Default Transaction

The following statement is also valid:

It starts a transaction, just as the more configured statements did, with a default configuration equivalent to

The API

The API function that does the equivalent job is called isc_start_transaction().

Starting each transaction has three parts:

  1. Creating (if necessary) and initializing the transaction handle.
  2. Creating (if necessary) and populating a transaction parameter buffer (TPB) to carry the configuration data. This is optional.
  3. Calling isc_start_transaction().

Without the optional TPB, the client starts a transaction exactly like the default transaction that a bare SET TRANSACTION statement starts.

In fact, there are two different functions for starting transactions:

The Transaction Handle

Each time you want to call this function, you must have a long pointer variable —called A transaction handle—already declared in your application and initialized to zero. An application needs to provide one transaction handle for each concurrent transaction, and you can “recycle” used handles by reinitializing them.

A transaction handle must be set to zero in order to initialize it before starting a transaction. A transaction will fail to start up if it is passed a non-zero handle.

The Transaction Parameter Buffer

A TPB is a byte array (or vector) of constants, each representing a transaction parameter and starting with the prefix isc_tpb_. The first parameter is always the constant isc_tpb_version3, which defines the version of TPB structure. The subsequent array members are all constants that map to an equivalent SQL transaction attribute.

Table shows each SQL transaction parameter and its equivalent TPB constant.

A typical TPB declaration in C looks like this:

This TPB is identical in its effect to

SET TRANSACTION READ WRITE WAIT READ COMMITTED NO RECORD_VERSION;

You can use the same TPB for all transactions that need the same characteristics. It is also fine to define a distinct TPB for each transaction. Transaction classes typically create a TPB instance whenever they need one and surface the settings to the design or runtime interface as read-write properties (aka data members).

In many cases, you can recognize the TPB constant in the property name. For standard DBC driver layers, the names of classes and members are more likely to be dictated by the standard and mapped to the attributes most nearly matching the prescribed functionality.

SQL Transaction Attributes and Equivalent TPB Constants

SQL Transaction Attributes and Equivalent TPB Constants

The Auto-Undo Log

By default, the server maintains an in-memory internal savepoint log of inserted and changed rows. In the normal course of events, log entries are erased as each transaction commits or rolls back. However, under certain conditions, the system will abandon the log and refer directly to the global transaction state bitmap (TSB) instead. The transition is likely to happen when a huge insert is rolled back or a transaction using many repeated user savepoints (see the section “Nested Transactions”) has exhausted the capacity of the log.

An application can disable the use of this auto-undo log in a transaction by passing the isc _ no _ auto _undo constant in the TPB.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics