RESERVING Clause

Go Up to Specifying SET TRANSACTION Behavior

The optional RESERVING clause enables transactions to guarantee themselves specific levels of access to a subset of available tables at the expense of other simultaneous transactions. Reservation takes place at the start of the transaction instead of only when data manipulation statements require a particular level of access. RESERVING is only useful in an environment where simultaneous transactions share database access. It has three main purposes:

To prevent possible deadlocks and update conflicts that can occur if locks are taken only when actually needed (the default behavior).
To 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.
To change the level of shared access for one or more individual tables in a transaction. For example, a READ WRITE SNAPSHOT transaction may need exclusive update rights for a single table, and could use the RESERVING clause to guarantee itself sole write access to the table.

Important:
A single SET TRANSACTION statement can contain either a RESERVING or a USING clause, but not both. Use the SET TRANSACTION syntax to reserve tables for a transaction:

EXEC SQL
SET TRANSACTION [NAME name]
[READ WRITE| READ ONLY]
[WAIT | NO WAIT]
[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION]}]
RESERVING <reserving_clause>;
<reserving_clause> = table [, table ...]
[FOR [SHARED | PROTECTED] {READ | WRITE}] [, <reserving_clause>]

Each table should only appear once in the RESERVING clause. Each table, or a list of tables separated by commas, must be followed by a clause describing the type of reservation requested. The following table lists these reservation options:

Table reservation options for the RESERVING clause
Reservation option	Purpose
`PROTECTED READ`	Prevents other transactions from updating rows. All transactions can select from the table.
`PROTECTED WRITE`	Prevents other transactions from updating rows. `SNAPSHOT` and `READ COMMITTED` transactions can select from the table, but only this transaction can update rows.
`SHARED READ`	Any transaction can select from this table. Any `READ WRITE` transaction can update this table. This is the most liberal reservation mode.
`SHARED WRITE`	Any `SNAPSHOT` or `READ COMMITTED READ WRITE` transaction can update this table. Other `SNAPSHOT` and `READ COMMITTED` transactions can also select from this table.

The following statement starts a SNAPSHOT transaction, t1, for READ WRITE access, and reserves a single table for PROTECTED WRITE access:

EXEC SQL
SET TRANSACTION NAME t1 READ WRITE WAIT SNAPSHOT
RESERVING EMPLOYEE FOR PROTECTED WRITE;

The next statement starts a READ COMMITTED transaction, t1, for READ WRITE access, and reserves two tables, one for SHARED WRITE, and another for PROTECTED READ:

EXEC SQL
SET TRANSACTION NAME t1 READ WRITE WAIT READ COMMITTED
RESERVING EMPLOYEES FOR SHARED WRITE, EMP_PROJ
FOR PROTECTED READ;

SNAPSHOT and READ COMMITTED transactions use RESERVING to implement more restrictive access to tables for other simultaneous transactions. SNAPSHOT TABLE STABILITY transactions use RESERVING to reduce the likelihood of deadlock in critical situations.

Advance To:

USING Clause