Concurrency - Firebird

The term concurrency broadly refers to the state in which two or more tasks are running inside the same database at the same time. In these conditions, the database is sometimes said to be supporting parallel tasks.Inside a transaction’s own “bubble,” the owning process will be allowed to perform any operation that

• Is consistent with its own current view of the database
• Would not, if committed, interfere with the consistency of any other active transaction’s current view of the database

Each transaction is configured by means of a constellation of parameters that enable the client process to predict, with absolute assurance, how the database engine will respond if it detects a potential inconsistency. The engine’s interpretation of “consistency” is governed by the transaction’s configuration. That, in turn, is governed by the client application.

Factors Affecting Concurrency

The four configurable parameters that affect concurrency are

• Isolation level
• Lock resolution mode (aka blocking mode)
• Access mode
• Table reservation

For one level of isolation (READ COMMITTED), the current states of the record versions are also considered.

Isolation Level

Firebird provides three outright levels of transaction isolation to define the “depth” of consistency the transaction requires. At one extreme, a transaction can get exclusive write access to an entire table, while at the other extreme, the uncommitted transaction becomes current with every external change of database state. No Firebird transaction will ever see any uncommitted changes pending for other transactions.

In Firebird, isolation level can be READ COMMITTED, SNAPSHOT, or SNAPSHOT TABLE STABILITY. Within READ COMMITTED, two sub-levels are available:

Standard Levels of Isolation

The SQL standard for transaction isolation is “sympathetic” to the two-phase locking mechanism that most RDBMSs use to implement isolation. It is quite idiosyncratic in comparison with many of the other standards. It defines isolation not so much in terms of ideals as in terms of the phenomena each level allows (or denies). The phenomena with which the standard is concerned are

• Dirty read: Occurs if the transaction is able to read the uncommitted (pending) changes of others.
• Non-repeatable read: Occurs if subsequent reads of the same set of rows during the course of the transaction could be different from what was read in those rows when the transaction began.
• Phantom rows: Occur if a subsequent set of rows read during the transaction differs from the set that was read when the transaction began. The phantom phenomenon happens if the subsequent read includes new rows inserted and/or excludes rows deleted that were committed since the first read.

Table shows the four standard isolation levels recognized by the standard, with the phenomena that govern their definitions.

SQL Standard Isolation Levels and Governing Phenomena

READ UNCOMMITTED is not supported in Firebird at all. READ COMMITTED conforms to the standard. At the two deeper levels, the nature of MGA prevails over the two-phase locking limitations implied by the standard. Mapping to the standard governance of REPEATABLE READ and SERIALIZABLE is not possible.

READ COMMITTED

The shallowest level of isolation is READ COMMITTED. It is the only level whose view
of database state changes during the course of the transaction since, every time the latest committed version of a record it is accessing changes, the newly committed record version replaces the version the transaction began with. Inserts committed since this transaction began are made visible to it.

By design, READ COMMITTED isolation allows non-repeatable reads and does not prevent the phenomenon of phantom rows. It is the most useful level for high-volume, real-time data entry operations because it reduces data contention, but it is unsuitable for tasks that need a reproducible view.

Because of the transient nature of READ COMMITTED isolation, the transaction (ThisTransaction) can be configured to respond more conservatively to external commits and other pending transactions:

• With RECORD_VERSION (the default flag), the engine lets ThisTransaction read the latest committed version. If ThisTransaction is in READ WRITE mode, it will be allowed to overwrite the latest committed version if its own TID is newer than the TID on the latest committed version.
• With NO_RECORD_VERSION, the engine effectively mimics the behavior of systems that use two-phase locking to control concurrency. It blocks ThisTransaction from reading the row if there is a pending update on it. Resolution depends on the lock resolution setting:
• With WAIT, ThisTransaction waits until the other transaction either commits or rolls back its change. Its change will then be allowed if the other transaction is rolled back or if its own TID is newer than the other transaction’s TID. It will fail with a lock conflict if the other transaction’s TID is newer.
• With NOWAIT, ThisTransaction immediately receives a lock conflict notification.

SNAPSHOT (Concurrency)

The “middle” level of isolation is SNAPSHOT, alternatively termed Repeatable Read or Concurrency. However, Firebird’s SNAPSHOT isolation does not accord exactly with Repeatable Read as defined by the standard. It isolates the transaction’s view from row-level changes to existing rows. However, because the MGA architecture, by nature, completely isolates SNAPSHOT transactions from new rows committed by other transactions, by denying SNAPSHOT transactions access to the global transaction state bitmap, there is no possibility of SNAPSHOT transactions seeing phantom rows. Hence, a Firebird SNAPSHOT transaction provides a deeper level of isolation than the SQL REPEATABLE READ.

Yet SNAPSHOT is not identical to SERIALIZABLE, because other transactions can update and delete rows that are in the purview of the SNAPSHOT transaction, provided they post first.

The transaction is guaranteed a non-volatile view of the database that will be unaffected by any changes committed by other transactions before it completes. It is a useful level for “historical” tasks like reporting and data export, which would be inaccurate if not performed over a reproducible view of the data.

SNAPSHOT is the default isolation level for the isql query tool and for many component and driver interfaces.

SNAPSHOT TABLE STABILITY (Consistency)

The “deepest” level of isolation is SNAPSHOT TABLE STABILITY, alternatively termed Consistency because it is guaranteed to fetch data in a non-volatile state that will remain externally consistent throughout the database as long as the transaction lasts. Read-write transactions cannot even read tables that are locked by a transaction with this isolation level.

The table-level locking imposed by this isolation comprises all tables accessed by the transaction, including those with referential constraint dependencies.

This level constitutes an aggressive extension that guarantees serialization in the strict sense that no other transaction can insert or delete—or indeed, change—rows in the tables involved if any transaction succeeds in acquiring a handle with this isolation. Conversely, the TABLE STABILITY transaction will not be able to acquire a handle if any read-write transaction is currently reading any table that is in its purview. In terms of the standard, it is unnecessary, since SNAPSHOT isolation already protects transactions from all three of the phenomena governed by the SQL standard SERIALIZABLE level.

A consistency transaction is also referred to as a blocking transaction because it blocks access by any other read-write transaction to any of the records that it accesses and to any records that depend on those records.

Access Mode

Access mode can be READ WRITE or READ ONLY. A READ WRITE transaction can select, insert, update, and delete data. A READ ONLY transaction can only select data. The default access mode is READ WRITE.

Lock Resolution Mode (“Blocking Mode”)

Lock resolution mode determines behavior in the event the transaction (ThisTransaction) tries to post a change that conflicts with a change already posted by another transaction. The options are WAIT and NOWAIT.

WAIT

WAIT (the default) causes the transaction to wait until rows locked by a pending transaction are released, before determining whether it can update them. At that point, if the other transaction has posted a higher record version, the waiting transaction will notify that a lock conflict has occurred.

WAIT is often not the preferred blocking mode in high-volume, interactive environments because of its potential to slow down the busiest users and, in some conditions, to cause “livelocks” (see the section “What Is a Deadlock?”).

WAIT is virtually pointless in SNAPSHOT isolation. Unless the blocking transaction eventually rolls back—the least likely scenario—the outcome of waiting is certain to be a lock conflict, anyway. In a READ COMMITTED transaction, the likelihood that the outcome of waiting would be a lock conflict is much reduced.

That is not to deny the usefulness of WAIT for some conditions. If the client application’s exception handler handles conflicts by continually retrying without pausing, the bottlenecking caused by repeated retries and failures is likely to be worse than if WAIT is specified, especially if the blocking transaction takes a long time to complete. By contrast, WAIT is potentially going to cause one exception, eventually handled by one rollback.

Where the likelihood of transactions colliding is high but transactions are short, WAIT is to be preferred because it guarantees that waiting requests will proceed in a FIFO sequence, rather than be required to take their chances with each repeated request. However, in user environments where a quick turnover cannot be guaranteed, WAIT transactions are contraindicated because of their potential to hold back garbage collection.

NO WAIT

In a NO WAIT transaction, the server will notify the client immediately if it detects a new, uncommitted version of a row the transaction tries to change. In a reasonably busy multi-user environment, NO WAIT is sometimes preferable to the risk of creating bottlenecks of waiting transactions.

As a rule of thumb for SNAPSHOT transactions, throughput will be faster and interfaces more responsive if the client application chooses NO WAIT and handles lock conflicts through the use of rollback, timed retries, or other appropriate techniques.

Table Reservation

Firebird supports a table locking mode to force full locks on one or more tables for the duration of a transaction. The optional RESERVING <list of tables> clause requests immediate full locks on all committed rows in the listed tables, enabling a transaction to guarantee itself exclusive access at the expense of any transactions that become concurrent with it.

Unlike the normal locking tactic, reservation locks all rows pessimistically—it takes effect at the start of the transaction, instead of waiting until the point at which an individual row lock is required.

Table reservation has three main purposes:

• To ensure that the tables are locked when the transaction begins, rather than when they are first accessed by a statement, as is the case when TABLE STABILITY isolation is used for table-level locking. The lock resolution mode (WAIT/NOWAIT) is applied during the transaction request and any conflict with other transactions having pending updates will result in a WAIT for the handle or a denial of the handle in the NOWAIT case. This feature of table reservation is important because it greatly reduces the possibility of deadlocks.
• To provide dependency locking (i.e., the locking of tables that might be affected by triggers and integrity constraints). Dependency locking is not normal in Firebird. However, it will ensure that update conflicts arising from indirect dependency conflicts will be avoided.
• To strengthen the transaction’s precedence with regard to one or more specific tables with which it will be involved. For example, a SNAPSHOT transaction that needs sole write access to all rows in a particular table could reserve it, while assuming normal precedence with regard to rows in other tables. This is a less aggressive way to apply table-level locking than the alternative, to use TABLE STABILITY isolation.

You can reserve more than one table in a transaction.

Uses of Table Reservation

Using table reservation with SNAPSHOT or READ COMMITTED isolation is recommended in preference to using SNAPSHOT TABLE STABILITY when table-level locking is required. Table reservation is the less aggressive and more flexible way to lock tables pre-emptively. It is available for use with any isolation level. However, using it in combination with SNAPSHOT TABLE STABILITY is not recommended, because it has no effect in mitigating access restrictions on tables that the transaction might access that are outside the scope of the RESERVING clause.

Pre-emptive table locking is not for everyday use, but it can be usefully employed for a task such as a pre-audit valuation or a “stock freeze” report prior to a stocktake.

Parameters for Table Reservation

Each table reservation can be configured with distinct attributes to specify how multiple transactions should be treated when they request access to a reserved table.

The choices are

The PROTECTED attribute gives ThisTransaction an exclusive lock on the table it is reading and allows any other transaction that is in SNAPSHOT or READ COMMITTED isolation to read rows. Writes are restricted by one of the two modifiers:

• PROTECTED WRITE allows ThisTransaction to write to the table and blocks all other writes.
• PROTECTED READ disallows writing to the table by any transaction, including ThisTransaction itself.
  

  The SHARED attribute lets any SNAPSHOT or READ COMMITTED transaction read the table and provides two options for concurrent updating by other transactions:

• SHARED WRITE allows any SNAPSHOT read-write or READ COMMITTED read-write transaction to update rows in the set as long as no transaction has or requests exclusive write.
• SHARED READ is the most liberal reserving condition. It allows any other read-write transaction to update thetable.