FirebirdSQL logo
 Context VariablesSecurity 
Options for RESERVING Clause

If one of the keywords SHARED or PROTECTED is omitted, SHARED is assumed.If the whole FOR clause is omitted, FOR SHARED READ is assumed.The names and compatibility of the four access options for reserving tables are not obvious.

Table 1. Compatibility of Access Options for RESERVING

 

SHARED READ

SHARED WRITE

PROTECTED READ

PROTECTED WRITE

SHARED READ

Yes

Yes

Yes

Yes

SHARED WRITE

Yes

Yes

No

No

PROTECTED READ

Yes

No

Yes

No

PROTECTED WRITE

Yes

No

No

No

The combinations of these RESERVING clause flags for concurrent access depend on the isolation levels of the concurrent transactions:

  • SNAPSHOT isolation

    • Concurrent SNAPSHOT transactions with SHARED READ do not affect one other’s access

    • A concurrent mix of SNAPSHOT and READ COMMITTED transactions with SHARED WRITE do not affect one another’s access, but they block transactions with SNAPSHOT TABLE STABILITY isolation from either reading from or writing to the specified table(s)

    • Concurrent transactions with any isolation level and PROTECTED READ can only read data from the reserved tables.Any attempt to write to them will cause an exception

    • With PROTECTED WRITE, concurrent transactions with SNAPSHOT and READ COMMITTED isolation cannot write to the specified tables.Transactions with SNAPSHOT TABLE STABILITY isolation cannot read from or write to the reserved tables at all.

  • SNAPSHOT TABLE STABILITY isolation

    • All concurrent transactions with SHARED READ, regardless of their isolation levels, can read from or write (if in READ WRITE mode) to the reserved tables

    • Concurrent transactions with SNAPSHOT and READ COMMITTED isolation levels and SHARED WRITE can read data from and write (if in READ WRITE mode) to the specified tables but concurrent access to those tables from transactions with SNAPSHOT TABLE STABILITY is blocked whilst these transactions are active

    • Concurrent transactions with any isolation level and PROTECTED READ can only read from the reserved tables

    • With PROTECTED WRITE, concurrent SNAPSHOT and READ COMMITTED transactions can read from but not write to the reserved tables.Access by transactions with the SNAPSHOT TABLE STABILITY isolation level is blocked.

  • READ COMMITTED isolation

    • With SHARED READ, all concurrent transactions with any isolation level can both read from and write (if in READ WRITE mode) to the reserved tables

    • SHARED WRITE allows all transactions in SNAPSHOT and READ COMMITTED isolation to read from and write (if in READ WRITE mode) to the specified tables and blocks access from transactions with SNAPSHOT TABLE STABILITY isolation

    • With PROTECTED READ, concurrent transactions with any isolation level can only read from the reserved tables

    • With PROTECTED WRITE, concurrent transactions in SNAPSHOT and READ COMMITTED isolation can read from but not write to the specified tables.Access from transactions in SNAPSHOT TABLE STABILITY isolation is blocked.

Note

In Embedded SQL, the USING clause can be used to conserve system resources bylimiting the number of databases a transaction can access.USING is mutually exclusive with RESERVING.A USING clause in SET TRANSACTION syntax is not supported in DSQL.
See also

[fblangref50-transacs-commit], [fblangref50-transacs-rollback]

COMMIT

Commits a transaction

Available in

DSQL, ESQL

Syntax
COMMIT [TRANSACTION tr_name] [WORK]
  [RETAIN [SNAPSHOT] | RELEASE];
Table 1. COMMIT Statement Parameter
Parameter Description

tr_name

Transaction name.Available only in ESQL

The COMMIT statement commits all work carried out in the context of this transaction (inserts, updates, deletes, selects, execution of procedures).New record versions become available to other transactions and, unless the RETAIN clause is employed, all server resources allocated to its work are released.

If any conflicts or other errors occur in the database during the process of committing the transaction, the transaction is not committed, and the reasons are passed back to the user application for handling, and the opportunity to attempt another commit or to roll the transaction back.

The TRANSACTION and RELEASE clauses are only valid in ESQL.