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 ifReadConsistency = 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
orNO WAIT
) does not affect the behavior of the transaction at its start in any way. -
With
READ CONSISTENCY
specified (orReadConsistency = 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 |
Caution
|
Setting |