Creating a Transaction Parameter Buffer
The transaction parameter buffer (TPB) is an optional, application-defined byte vector that is passed as an argument to isc_start_transaction(). It sets up a transaction’s attributes, its operating characteristics, such as whether the transaction has read and write access to tables, or read-only access, and whether or not other simultaneously active transactions can share table access with the transaction. Each transaction may have its own TPB, or transactions that share operating characteristics can use the same TPB.
Note: If a TPB is not created for a transaction, a NULL pointer must be passed to isc_start_transaction() in its place. A default set of attributes is automatically assigned to such transactions. For more information about the default TPB, see Using the Default TPB.
A TPB is declared in a C program as a char array of one-byte elements. Each element is a parameter that describes a single transaction attribute. A typical declaration is as follows:
- static char isc_tpb[] = {isc_tpb_version3,
- isc_tpb_write,
- isc_tpb_read_committed,
- isc_tpb_no_rec_version,
- isc_tpb_wait};
This example makes use of parameter constants defined in the InterBase header file, f. The first element in every TPB must be the isc_tpb_version3 constant. The following table lists available TPB constants, describes their purposes, and indicates which constants are assigned as a default set of attributes when a NULL TPB pointer is passed to isc_start_transaction():
Table 5.3 TPB Constants
| Parameter | Description | |
|---|---|---|
| isc_tpb_version3 | InterBase version 3 transaction | |
| isc_tpb_consistency | Table-locking transaction model. This mode is serializable. | |
| isc_tpb_concurrency | High throughput, high concurrency transaction with repeatable read consistency. This mode takes full advantage
of the InterBase multi-generational transaction model [Default]. | |
| isc_tpb_shared | Concurrent, shared access of a specified table among all transactions; use in conjunction with isc_tpb_lock_read and
isc_tpb_lock_write to establish the lock option [Default]. | |
| isc_tpb_protected | Concurrent, restricted access of a specified table; use in conjunction with isc_tpb_lock_read and isc_tpb_lock_write to establish the lock option. | |
| isc_tpb_wait | Specifies that the transaction is to wait until the conflicting resource is released before retrying an operation [Default]. | |
| isc_tpb_nowait | Specifies that the transaction is not to wait for the resource to be released, but instead, should return an update conflict error immediately. | |
| isc_tpb_read | Read-only access mode that allows a transaction only to select data from tables. | |
| isc_tpb_write | Read-write access mode of that allows a transaction to select, insert, update, and delete table data [Default]. | |
| isc_tpb_lock_read | Read-write access mode of that allows a transaction to select, insert, update, and delete table data [Default]. | |
| isc_tpb_lock_write | Read-write access of a specified table. Use in conjunction with isc_tpb_shared, isc_tpb_protected, and isc_tpb_exclusive to establish the lock option [Default]. | |
| isc_tpb_read_committed | High throughput, high concurrency transaction that can read changes committed by other concurrent transactions. Transactions in this mode do not provide repeatable read. | |
| isc_tpb_rec_version | Enables an isc_tpb_read_committed transaction to read the most recently committed version of a record even if other,
uncommitted versions are pending. | |
| isc_tpb_no_rec_version | Enables an isc_tpb_read_committed transaction to read only the latest committed version of a record. If an uncommitted
version of a record is pending and isc_tpb_wait is also specified, then the transaction waits for the pending record to be committed or rolled back before proceeding. Otherwise, a lock conflict error is reported at once. |
Important: The combination of the options isc_tpb_read_commited, isc_tpb_no_rec_version, and isc_tpb_nowait will cause frequent deadlocks. This combination is not recommended.
TPB parameters specify the following classes of information:
- Transaction version number is used internally by the InterBase engine. It is always be the first attribute specified in the TPB, and must always be set to isc_tpb_version3.
- Access mode describes the actions that can be performed by the functions associated with the transaction. Valid access modes are:
- isc_tpb_read
- isc_tpb_write
- Isolation level describes the view of the database given a transaction as it relates to actions performed by other simultaneously occurring transactions.
- Valid isolation levels are:
- isc_tpb_concurrency
- isc_tpb_consistency
- isc_tpb_read_committed, isc_tpb_rec_version
- isc_tpb_read_committed, isc_tpb_no_rec_version
- Lock resolution describes how a transaction should react if a lock conflict occurs. Valid lock resolutions are:
- isc_tpb_wait
- isc_tpb_nowait
- Table reservation optionally describes an access method and conflict resolution for a specified table that the transaction accesses. When table reservation is used, tables are reserved for the specified access when the transaction is started, rather than when the transaction actually accesses the table. Valid reservations are:
- isc_tpb_shared, isc_tpb_lock_write
- isc_tpb_shared, isc_tpb_lock_read
- isc_tpb_protected, isc_tpb_lock_write
- isc_tpb_protected, isc_tpb_lock_read
TPB parameters are described in detail in the following sections.
Note: Reserving tables is a way to produce programs that are guaranteed to be deadlockfree. If your application uses short TP-style transactions, reserving all tables required for protected read or write, as necessary, can improve performance. This method should not be used for interactive applications. A transaction that reserves tables will receive an error if it attempts to access any tables that are not reserved.
Specifying the Transaction Version Number
The first parameter in a TPB must always specify the version number for transaction processing. It must always be set to isc_tpb_version3. The following TPB declaration illustrates the correct use and position of this parameter:
- static char isc_tpb[] = {isc_tpb_version3, ...};
Specifying Access Mode
The access mode parameter describes the actions a transaction can perform against a table. The default access mode, isc_tpb_write, enables a transaction to read data from a table and write data to it. A second access mode, isc_tpb_read,
restricts table access to read only. For example, the following TPB declaration specifies a read-only transaction:
- static char isc_tpb[] = {isc_tpb_version3, isc_tpb_read};
A TPB should specify only one access mode parameter. If more than one is specified, later declarations override earlier ones.
If a TPB is declared that omits the access mode parameter, InterBase interprets transaction access as read and write.
Specifying Isolation Level
The isolation level parameter specifies the view of the database permitted a transaction as it relates to actions performed by other simultaneously occurring transactions.
isc_tpb_concurrency
By default, a transaction sees a stable view of the database as of the instant it starts and can share read/write access to tables with simultaneous transactions. This mode is known as “concurrency” because it allows concurrent transactions to
share data. The following declaration creates a TPB specifying an isolation level of isc_tpb_concurrency:
static char isc_tpb[] = {isc_tpb_version3, isc_tpb_write, isc_tpb_concurrency};
isc_tpb_read_committed
A second isolation level, isc_tpb_read_committed, does not provide a consistent view of the database. Unlike a concurrency transaction, a read committed transaction sees changes made and committed by transactions that were active after this transaction started. Two other parameters, isc_tpb_rec_version, and isc_tpb_no_rec_version, should be used with the isc_tpb_read_committed parameter. They offer refined control over the committed changes a transaction is permitted to access:
- isc_tpb_no_rec_version, the default refinement, specifies that a transaction can only read the latest version of a row. If a change to a row is pending, but not yet committed, the row cannot be read. Although default, this refinement may impose unnecessary wait conditions for your transaction. This option should be combined with isc_tpb_nowait to avoid deadlock messages.
- isc_tpb_rec_version specifies that a transaction can read the latest committed version of a row, even if a more recent uncommitted version is pending.
The following declaration creates a TPB with a read committed isolation level, and specifies that the transaction can read the latest committed version of a row:
- static char isc_tpb[] = {isc_tpb_version3, isc_tpb_write,
- isc_tpb_read_committed, isc_tpb_rec_version};
isc_tpb_consistency
InterBase also supports a restrictive isolation level. isc_tpb_consistency prevents a transaction from accessing tables if they are written to by other transactions; it also prevents other transactions from writing to a table once this transaction reads from or writes to it. This isolation level is designed to guarantee that if a transaction writes to a table before other simultaneous read and write transactions, then only it can change a table’s data. Because it restricts shared access to tables, isc_tpb_consistency should be used with care. However, this mode is serializable, so it meets the highest requirements for transaction consistency. Combined with table reservations, it is also deadlock-free.
A TPB should only specify one isolation mode parameter (and one refinement parameter, if isolation mode is isc_tpb_read_committed). If more than one is specified, later declarations override earlier ones.
If a TPB is declared that omits the isolation mode parameter, InterBase interprets it as isc_tpb_concurrency.
Isolation level interactions
To determine the possibility for lock conflicts between two transactions accessing the same database, each transaction’s isolation level and access mode must be considered. The following table summarizes possible combinations:
Table 5.4 Isolation level interaction with read and write operations
As this table illustrates, isc_tpb_concurrency and isc_tpb_read_committed transactions offer the least chance for conflicts. For example, if t1 is an isc_tpb_concurrency transaction with isc_tpb_write access, and t2 is an isc_tpb_read_committed transaction with isc_tpb_write access, t1 and t2 only conflict when they attempt to update the same rows. If t1 and t2 have isc_tpb_read access, they never conflict with other transactions.
An isc_tpb_consistency transaction with isc_tpb_write access is guaranteed that if it gains access to a table that it alone can update a table, but it conflicts with all other simultaneous transactions except for isc_tpb_concurrency and isc_tpb_read_committed transactions running in isc_tpb_read mode. An isc_tpb_consistency transaction with isc_tpb_read access is compatible with any other read-only transaction, but conflicts with any transaction that attempts to insert, update, or delete data.
Specifying Lock Resolution
The lock resolution parameter describes what happens if a transaction encounters an access conflict during a write operation (update and delete operations on existing rows). There are two possible choices for this parameter:
- isc_tpb_wait, the default, specifies that the transaction should wait until locked resources are released. Once the resources are released, the transaction retries its operation.
- isc_tpb_nowait specifies that the transaction should return a lock conflict error without waiting for locks to be released.
For example, the following declaration creates a TPB with write access, a concurrency isolation mode, and a lock resolution of isc_tpb_nowait:
- static char isc_tpb[] = {isc_tpb_version3,
- isc_tpb_write,
- isc_tpb_concurrency,
- isc_tpb_nowait};
A TPB should specify only one lock resolution parameter. If more than one is specified, later declarations override earlier ones.
If a declared TPB omits the lock resolution parameter, InterBase interprets it as isc_tpb_concurrency.
Specifying Conflict Resolution
The conflict resolution parameter describes what happens if a transaction encounters an access conflict during an update or delete operation. There are two possible choices for this parameter:
- isc_tpb_wait, the default, specifies that the transaction should wait until resources are released. Once the resources are released, the transaction retries its operation. If the conflicting transaction is committed, the waiting transaction receives an update conflict error. If the conflicting transaction is rolled back, the waiting transaction proceeds without an error.
- isc_tpb_nowait specifies that the transaction should return an update conflict error without waiting for resources to be released. This mode can lead to “live locks” if the transaction that received the error retries its operation again, only to fail again because the resources are still locked.
The following declaration creates a TPB with write access, a concurrency isolation mode, and a conflict resolution of isc_tpb_nowait:
- static char isc_tpb[] = {isc_tpb_version3,
- isc_tpb_write,
- isc_tpb_concurrency,
- isc_tpb_nowait};
A TPB should only specify one conflict resolution parameter. If more than one is specified, later declarations override earlier ones. If a TPB is declared that omits the conflict resolution parameter, InterBase interprets it as isc_tpb_concurrency.
Specifying Table Reservation
Ordinarily, transactions gain specific access to tables only when they actually read from or write to them. Table reservation parameters can be passed in the TPB to acquire access to tables when the transaction starts. Table reservation describes an access method and conflict resolution for a specified table that the transaction accesses. Table reservation has three main purposes:
- Prevent deadlocks and update conflicts that can occur if locks are taken only when actually needed (the default behavior).
- Provide for dependency locking, the locking of tables that may be affected by triggers and integrity constraints. While explicit dependency locking is not required, it can assure that update conflicts do not occur because of indirect table conflicts.
- Change the level of shared access for one or more individual tables in a transaction. For example, an isc_tpb_write transaction with an isolation level of isc_tpb_concurrency may need exclusive update rights for a single table, and could use a reservation parameter to guarantee itself sole write access to the table.
Valid reservations are:
- isc_tpb_shared, isc_tpb_lock_write, which permits any transaction with an access mode of isc_tpb_write and isolation levels of isc_tpb_concurrency or isc_tpb_read_committed, to update, while other transactions with these isolation levels and an access mode of isc_tpb_read can read data.
- isc_tpb_shared, isc_tpb_lock_read, which permits any transaction to read data, and any transaction with an access mode of isc_tpb_write to update. This is the most liberal reservation mode.
- isc_tpb_protected, isc_tpb_lock_write, which prevents other transactions from updating. Other transactions with isolation levels of isc_tpb_concurrency or isc_tpb_read_committed can read data, but only this transaction can update.
- isc_tpb_protected, isc_tpb_lock_read, which prevents all transactions from updating, but permits all transactions to read data.
The name of the table to reserve must immediately follow the reservation parameters. You must reserve all tables referenced in the transaction, including those referenced through triggers or stored procedures. For example, the following TPB declaration reserves a table, EMPLOYEE, for protected read access:
- static char isc_tpb[] = {isc_tpb_version3,
- isc_tpb_write,
- isc_tpb_concurrency,
- isc_tpb_nowait,
- isc_tpb_protected,
- isc_tpb_lock_read,
- 8,
- 'E','M','P','L','O','Y','E','E');
Several tables can be reserved at the same time. The following declaration illustrates how two tables are reserved, one for protected read, the other for protected write:
- static char isc_tpb[] = {isc_tpb_version3,
- isc_tpb_write,
- isc_tpb_concurrency,
- isc_tpb_nowait,
- isc_tpb_protected,
- isc_tpb_lock_read,
- 7,
- 'C','O','U','N','T','R','Y',
- isc_tpb_protected,
- isc_tpb_lock_write,
- 8,
- 'E','M','P','L','O','Y','E','E');
Using the Default TPB
Providing a TPB for a transaction is optional. If one is not provided, then a NULL pointer must be passed to isc_start_transaction() in place of a pointer to the TPB. In this case, InterBase treats a transaction as if the following TPB had been declared for it:
- static char isc_tpb[] = {isc_tpb_version3,
- isc_tpb_write,
- isc_tpb_concurrency,
- isc_tpb_wait};