Exclusive Isolation Level

Introduction

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 SELECT, INSERT, UPDATE, and 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.

Usage

Use the 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 READ and 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_shared and 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.

Migration issues

Prior to InterBase 2017, isc_tpb_exclusive could be used, but it allowed select access by concurrent transactions. Starting with 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 COLUMN for encryption and TRUNCATE TABLE acquire exclusive table access to perform their function.
InterBase 2017 introduced the InterBase-specific SQL reserved keywords EXCLUSIVITY and EXCLUSIVE.

Advance To:

Wait time