FirebirdSQL logo
 Context VariablesSecurity 
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 needs to be executed again, whilst still in the uncommitted READ COMMITTED transaction.

Variants of READ COMMITTED

One of three modifying parameters can be specified for READ COMMITTED transactions, depending on the kind of conflict resolution desired: READ CONSISTENCY, RECORD_VERSION or NO RECORD_VERSION.When the ReadConsistency setting is set to 1 in firebird.conf (the default) or in databases.conf, these variants are effectively ignored and behave as READ CONSISTENCY.Otherwise, these variants are mutually exclusive.

  • NO RECORD_VERSION (the default if ReadConsistency = 0) 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.

    • with NO WAIT specified, it will throw a lock conflict error immediately

    • with WAIT specified, it will wait until the other transaction is either committed or 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.

  • With READ CONSISTENCY specified (or ReadConsistency = 1), the execution of a statement obtains a snapshot of the database to ensure a consistent read at the statement-level of the transactions committed when execution started.

    The other two variants can result in statement-level inconsistent reads as they may read some but not all changes of a concurrent transaction if that transaction commits during statement execution.For example, a SELECT COUNT(*) could read some, but not all inserted records of another transaction if the commit of that transaction occurs while the statement is reading records.

    This statement-level snapshot is obtained for the execution of a top-level statement, nested statements (triggers, stored procedures and functions, dynamics statements, etc.) use the statement-level snapshot created for the top-level statement.

Note

Obtaining a snapshot for READ CONSISTENCY is a very cheap action.
Caution

Setting ReadConsistency is set to 1 by default in firebird.conf.
Handling of Update Conflicts with READ CONSISTENCY

When a statement executes in a READ COMMITTED READ CONSISTENCY transaction, its database view is retained in a fashion similar to a SNAPSHOT transaction.This makes it pointless to wait for the concurrent transaction to commit, in the hope of being able to read the newly-committed record version.So, when a READ COMMITTED READ CONSISTENCY transaction reads data, it behaves similarly to a READ COMMITTED RECORD VERSION transaction: it walks the back versions chain looking for a record version visible to the current snapshot.

When an update conflict occurs, the behaviour of a READ COMMITTED READ CONSISTENCY transaction is different from READ COMMITTED RECORD VERSION.The following actions are performed:

  1. Transaction isolation mode is temporarily switched to READ COMMITTED NO RECORD VERSION.

  2. A write-lock is taken for the conflicting record.

  3. Remaining records of the current UPDATE/DELETE cursor are processed, and they are write-locked too.

  4. Once the cursor is fetched, all modifications performed since the top-level statement was started are undone, already taken write-locks for every updated/deleted/locked record are preserved, all inserted records are removed.

  5. Transaction isolation mode is restored to READ COMMITTED READ CONSISTENCY, a new statement-level snapshot is created, and the top-level statement is restarted.

This algorithm ensures that already updated records remain locked after restart, they are visible to the new snapshot, and could be updated again with no further conflicts.Also, due to READ CONSISTENCY nature, the modified record set remains consistent.

Note

  • This restart algorithm is applied to UPDATE, DELETE, SELECT WITH LOCK and MERGE statements, with or without the RETURNING clause, executed directly by a client application or inside a PSQL object (stored procedure/function, trigger, EXECUTE BLOCK, etc).

  • If an UPDATE/DELETE statement is positioned on an explicit cursor (using the WHERE CURRENT OF clause), then the step (3) above is skipped, i.e. remaining cursor records are not fetched and write-locked.

  • If the top-level statement is selectable and update conflict happens after one or more records were returned to the client side, then an update conflict error is reported as usual and restart is not initiated.

  • Restart does not happen for statements executed inside autonomous blocks (IN AUTONOMOUS TRANSACTION DO …​).

  • After 10 unsuccessful attempts the restart algorithm is aborted, all write locks are released, transaction isolation mode is restored to READ COMMITTED READ CONSISTENCY, and an update conflict error is raised.

  • Any error not handled at step (3) above aborts the restart algorithm and statement execution continues normally.

  • UPDATE/DELETE triggers fire multiple times for the same record if the statement execution was restarted and the record is updated/deleted again.

  • Statement restart is usually fully transparent to client applications and no special actions should be taken by developers to handle it in any way.The only exception is the code with side effects that are outside the transactional control, for example:

    • usage of external tables, sequences or context variables

    • sending e-mails using UDF or UDR

    • usage of autonomous transactions or external queries

    and so on.Beware that such code could be executed more than once if update conflicts happen.

  • There is no way to detect whether a restart happened, but it could be done manually using code with side effects as described above, for example using a context variable.

  • Due to historical reasons, error isc_update_conflict is reported as the secondary error code, with the primary error code being isc_deadlock.