Exclusive Isolation Level
Go Up to SET TRANSACTION
A Tool performing online reorganization of tables may need temporary exclusive table access to perform its functions. Transactions use exclusive table access to acquire an exclusive lock on a target table, and they are the only ones able to execute
DELETE on a table. When a transaction acquires an exclusive lock, other transactions with lock requests must wait until the lock is released or downgraded to a compatible level. Transactions that maintain exclusive table access can modify data on a table without interference from other transactions. This isolation level is different from
TABLE STABILITY and
PROTECTED access because it does not allow other transactions to select from the table.
SET TRANSACTION statement to specify the
TABLE EXCLUSIVITY clause, or use the existing
RESERVING clause to request exclusive access to one or more tables.
TABLE EXCLUSIVITY acquires exclusive access to every table that a transaction accesses during statement execution. The
RESERVING clause acquires exclusive access to a list of tables at transaction startup. To use the
RESERVING clause, specify
FOR <table_list> EXCLUSIVE [READ | WRITE]. Note that there is no difference between
WRITE because both modes do not allow other transactions to access the table. As with
TABLE STABILITY, there is an increased likelihood of lock conflicts and waits when this isolation level is used.
In addition to
isc_tpb_protected, you can use
isc_tpb_exclusive in a transaction parameter block (TPB) to specify exclusive table access when calling
isc_start_transaction() at the API level.
Requirements and Constraints
- It is possible to acquire exclusive table access even if one or more statements or requests that access the table have been prepared.
- It is possible to acquire exclusive table access even if one or more statements or requests that access the table have been executed as long as they have not yet accessed the table.
- Prior to InterBase 2017,
isc_tpb_exclusivecould be used, but it allowed select access by concurrent transactions. Under InterBase 2017, a transaction has to wait until those readers terminate and subsequent readers block until the transaction with exclusive access terminates or downgrades the exclusive lock.
ALTER TABLE ... ALTER COLUMNfor encryption and
TRUNCATE TABLEacquire exclusive table access to perform their function.
- InterBase 2017 introduces the InterBase-specific SQL reserved keywords EXCLUSIVITY and EXCLUSIVE.