10.1.1. SET TRANSACTION

Used for

Configuring and starting a transaction

Available

DSQL, ESQL

Syntax

SET TRANSACTION
   [NAME tr_name]
   [READ WRITE | READ ONLY]
   [[ISOLATION LEVEL]
     { SNAPSHOT [TABLE STABILITY]
     | READ COMMITTED [[NO] RECORD_VERSION] }]
   [WAIT | NO WAIT]
   [LOCK TIMEOUT seconds]
   [NO AUTO UNDO]
   [IGNORE LIMBO]
   [RESERVING <tables> | USING <dbhandles>]
<tables> ::= <table_spec> [, <table_spec> ...]
<table_spec> ::= tablename [, tablename ...]
      [FOR [SHARED | PROTECTED] {READ | WRITE}]
<dbhandles> ::= dbhandle [, dbhandle ...]

The SET TRANSACTION statement configures the transaction and starts it. As a rule, only client applications start transactions. The exceptions are the occasions when the server starts an autonomous transaction or transactions for certain background system threads/processes, such as sweeping.

A client application can start any number of concurrently running transactions. A limit does exist, for the total number of running transactions in all client applications working with one particular database from the moment the database was restored from its backup copy or from the moment the database was created originally. The limit is 231-1, or 2,147,483,647.

All clauses in the SET TRANSACTION statement are optional. If the statement starting a transaction has no clauses specified in it, it the transaction will be started with default values for access mode, lock resolution mode and isolation level, which are:

SET TRANSACTION
  READ WRITE
  WAIT
  ISOLATION LEVEL SNAPSHOT;

The server assigns integer numbers to transactions sequentially. Whenever a client starts any transaction, either explicitly defined or by default, the server sends the transaction ID to the client. This number can be retrieved in SQL using the context variable CURRENT_TRANSACTION.

Transaction Parameters

The main parameters of a transaction are:

• data access mode (READ WRITE, READ ONLY)

• lock resolution mode (WAIT, NO WAIT) with an optional LOCK TIMEOUT specification

• isolation level (READ COMMITTED, SNAPSHOT, TABLE STABILITY)

• a mechanism for reserving or releasing tables (the RESERVING clause)

Transaction Name

The optional NAME attribute defines the name of a transaction. Use of this attribute is available only in Embedded SQL. In ESQL applications, named transactions make it possible to have several transactions active simultaneously in one application. If named transactions are used, a host-language variable with the same name must be declared and initialized for each named transaction. This is a limitation that prevents dynamic specification of transaction names and thus, rules out transaction naming in DSQL.

Access Mode

The two database access modes for transactions are READ WRITE and READ ONLY.

• If the access mode is READ WRITE, operations in the context of this transaction can be both read operations and data update operations. This is the default mode.

• If the access mode is READ ONLY, only SELECT operations can be executed in the context of this transaction. Any attempt to change data in the context of such a transaction will result in database exceptions. However, it does not apply to global temporary tables (GTT) that are allowed to be changed in READ ONLY transactions.

Lock Resolution Mode

When several client processes work with the same database, locks may occur when one process makes uncommitted changes in a table row, or deletes a row, and another process tries to update or delete the same row. Such locks are called update conflicts.

Locks may occur in other situations when multiple transaction isolation levels are used.

The two lock resolution modes are WAIT and NO WAIT.

WAIT Mode

In the WAIT mode (the default mode), if a conflict occurs between two parallel processes executing concurrent data updates in the same database, a WAIT transaction will wait till the other transaction has finished — by committing (COMMIT) or rolling back (ROLLBACK). The client application with the WAIT transaction will be put on hold until the conflict is resolved.

If a LOCK TIMEOUT is specified for the WAIT transaction, waiting will continue only for the number of seconds specified in this clause. If the lock is unresolved at the end of the specified interval, the error message “Lock time-out on wait transaction” is returned to the client.

Lock resolution behaviour can vary a little, depending on the transaction isolation level.

NO WAIT Mode

In the NO WAIT mode, a transaction will immediately throw a database exception if a conflict occurs.

Isolation Level

Keeping the work of one database task separated from others is what isolation is about. Changes made by one statement become visible to all remaining statements executing within the same transaction, regardless of its isolation level. Changes that are in process within other transactions remain invisible to the current transaction as long as they remain uncommitted. The isolation level and, sometimes, other attributes, determine how transactions will interact when another transaction wants to commit work.

The ISOLATION LEVEL attribute defines the isolation level for the transaction being started. It is the most significant transaction parameter for determining its behavior towards other concurrently running transactions.

The three isolation levels supported in Firebird are:

• SNAPSHOT

• SNAPSHOT TABLE STABILITY

• READ COMMITTED with two specifications (NO RECORD_VERSION and RECORD_VERSION)

SNAPSHOT Isolation Level

SNAPSHOT isolation level — the default level — allows the transaction to see only those changes that were committed before this one was started. Any committed changes made by concurrent transactions will not be seen in a SNAPSHOT transaction while it is active. The changes will become visible to a new transaction once the current transaction is either committed or rolled back completely, but not if it is just rolled back to a savepoint.

SNAPSHOT TABLE STABILITY Isolation Level

The SNAPSHOT TABLE STABILITY isolation level is the most restrictive. As in SNAPSHOT, a transaction in SNAPSHOT TABLE STABILITY isolation sees only those changes that were committed before the current transaction was started. After a SNAPSHOT TABLE STABILITY is started, no other transactions can make any changes to any table in the database that has changes pending. Other transactions are able to read other data, but any attempt at inserting, updating or deleting by a parallel process will cause conflict exceptions.

The RESERVING clause can be used to allow other transactions to change data in some tables.

If any other transaction has an uncommitted change of data pending in any database table before a transaction with the SNAPSHOT TABLE STABILITY isolation level is started, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an exception.

READ COMMITTED Isolation Level

The READ COMMITTED isolation level allows all data changes that other transactions have committed since it started to be seen immediately by the uncommitted current transaction. Uncommitted changes are not visible to a READ COMMITTED transaction.

To retrieve the updated list of rows in the table you are interested in — “refresh” — the SELECT statement just needs to be requested again, whilst still in the uncommitted READ COMMITTED transaction.

RECORD_VERSION

One of two modifying parameters can be specified for READ COMMITTED transactions, depending on the kind of conflict resolution desired: RECORD_VERSION and NO RECORD_VERSION. As the names suggest, they are mutually exclusive.

• NO RECORD_VERSION (the default value) is a kind of two-phase locking mechanism: it will make the transaction unable to write to any row that has an update pending from another transaction.

  • if NO WAIT is the lock resolution strategy specified, it will throw a lock conflict error immediately

  • with WAIT specified, it will wait until the other transaction either commits or is rolled back. If the other transaction is rolled back, or if it is committed and its transaction ID is older than the current transaction’s ID, then the current transaction’s change is allowed. A lock conflict error is returned if the other transaction was committed and its ID was newer than that of the current transaction.

• With RECORD_VERSION specified, the transaction reads the latest committed version of the row, regardless of other pending versions of the row. The lock resolution strategy (WAIT or NO WAIT) does not affect the behavior of the transaction at its start in any way.

NO AUTO UNDO

The NO AUTO UNDO option affects the handling of unused record versions (garbage) in the event of rollback. With NO AUTO UNDO flagged, the ROLLBACK statement just marks the transaction as rolled back without deleting the unused record versions created in the transaction. They are left to be mopped up later by garbage collection.

NO AUTO UNDO might be useful when a lot of separate statements are executed that change data in conditions where the transaction is likely to be committed successfully most of the time.

The NO AUTO UNDO option is ignored for transactions where no changes are made.

IGNORE LIMBO

This flag is used to signal that records created by limbo transactions are to be ignored. Transactions are left “in limbo” if the second stage of a two-phase commit fails.

RESERVING

The RESERVING clause in the SET TRANSACTION statement reserves tables specified in the table list. Reserving a table prevents other transactions from making changes in them or even, with the inclusion of certain parameters, from reading data from them while this transaction is running.

A RESERVING clause can also be used to specify a list of tables that can be changed by other transactions, even if the transaction is started with the SNAPSHOT TABLE STABILITY isolation level.

One RESERVING clause is used to specify as many reserved tables as required.

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.

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 completely 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 completely.

• 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 completely 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 completely.

See also

COMMIT, ROLLBACK