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 theRESERVING
clause to guarantee itself sole write access to the table.
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:
Reservation option | Purpose |
---|---|
|
Prevents other transactions from updating rows. All transactions can select from the table. |
|
Prevents other transactions from updating rows. |
|
Any transaction can select from this table. Any |
|
Any |
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.