Specifying Table Reservation

Go Up to Creating a Transaction Parameter Buffer

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');

Advance To:

Using the Default TPB