Transaction handling

The Firebird architecture allows high transaction concurrency. Transaction save points (nested transactions) are also supported. All Firebird transactions are ACID compliant. ACID is explained below:

• Atomicity ensures that transactions either complete in their entirety or not at all, even if the system fails halfway through the process.
• Consistency ensures that only valid data will be written to the database. If a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. If a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules, without necessarily preserving consistency at all intermediate levels.
• Isolation ensures that transactions are isolated from one another, even if several transactions are running concurrently. Concurrency refers to a state within the database where two or more tasks are running simultaneously. This way, a transaction's updates are concealed from the rest until that transaction commits. Transactions in Firebird are isolated within separate contexts defined by client applications passing transaction parameters.
• Durability ensures that once a transaction commits, its updates survive within the database, even if there is a subsequent system crash.

There are several parameters available to configure transactions in order to ensure consistency within the database. These parameters invoke the concept of concurrency. To ensure data integrity, there are four configurable parameters affecting concurrency: isolation level; lock resolution mode; access mode; and table reservation.

• Isolation level: A transaction isolation level defines the interaction and visibility of work performed by simultaneously running transactions. There are four transaction isolation levels according to the SQL standard:
  • READ COMMITTED: A transaction sees only data committed before the statement has been executed.
  • READ UNCOMMITTED: A transaction sees changes done by uncommitted transactions.
  • REPEATABLE READ: A transaction sees during its lifetime only data committed before the transaction has been started.
  • SERIALIZABLE: This is the strictest isolation level, which enforces transaction serialization. Data accessed in the context of a serializable transaction cannot be accessed by any other transaction.

In isql, a transaction is begun as soon as the utility is started. The transaction is begun in SNAPSHOT isolation, with a lock resolution set to WAIT. Since the Firebird isql utility accepts DDL, DML and other commands, transactions are handled accordingly, in the following ways:

• DDL statements are committed automatically when issued at the SQL prompt in two ways:
  • When COMMIT statements are included in the script.
  • By ensuring the automatic commit of DDL in an isql script, by issuing a SET AUTODDL ON statement. To turn it off, issue a SET AUTODDL OFF statement at the isql prompt.
• DML statements are not committed automatically. You must issue a COMMIT statement to commit any DML changes to the database.
• You can use various SHOW commands in isql to query database meta data. Meta data is stored in system tables. When a SHOW command is issued it operates in a separate transaction from user statements. They run as READ COMMITTED background statements and acknowledge all meta data changes immediately.

Users can specify the access mode and level of isolation for the next transaction, and explicitly commit the current transaction by using the SET TRANSACTION statement. SET TRANSACTION can be executed only when there is no other transaction being processed. It does not by itself initiate a transaction. Here is the syntax:

SQL> SET TRANSACTION;

In Firebird 2.0 the SET TRANSACTION statement has been enhanced to support all Transaction Parameter Buffer (TPB) options. These include:

• NO AUTO UNDONE
• IGNORE LIMBO
• LOCK TIMEOUT <number>

Example

SET TRANSACTION WAIT SNAPSHOT NO AUTO UNDONE LOCK TIMEOUT 10;

Note: If you request help on the SET in isql then the SET TRANSACTION command is not shown.