Transaction Isolation Levels

Every database operation in Firebird runs inside a transaction. The transactions have these features: Atomicity, Consistency, Isolation, Durability (read more about ACID). Let's talk a bit about the isolation feature.

Isolation says that the transaction shouldn't interfere with other transactions. Achieving full isolation would mean serializing the transactions and that would slow down the database. Therefore there are four transaction isolation levels defined and every of them represents a different balance between isolation and performance:

• Read uncommitted
• Read committed
• Repeatable read
• Serializable

Firebird doesn't match the standard (SQL92) isolation levels exactly. The following table compares the isolation levels that are supported by Firebird ADO.NET with the SQL92 standard.

Firebird ADO.NET Provider IsolationLevel	Firebird Isolation Level	Corresponding SQL92 Isolation Level
IsolationLevel.ReadUncommitted	READ COMMITTED RECORD_VERSION	Read Committed
IsolationLevel.ReadCommitted (default)	READ COMMITTED NO RECORD_VERSION	Read Committed
IsolationLevel.RepeatableRead	SNAPSHOT/CONCURRENCY	Repeatable Read
IsolationLevel.Serializable	SNAPSHOT TABLE STABILITY/CONSISTENCY	Serializable

All transactions have these additional Firebird transaction options set:

• WAIT - when using READ COMMITTED isolation level it waits for uncommitted transactions to finish before reading a row
• READ WRITE - allows data modifications

You can use the predefined isolation levels by calling public FbTransaction BeginTransaction(IsolationLevel) or you can tune the transaction options more precisely by using this BeginTransaction overload: public FbTransaction BeginTransaction(FbTransactionOptions).

IsolationLevel.ReadUncommitted

ANSI/ISO Read Uncommitted isolation level is not supported by Firebird. IsolationLevel.ReadUncommitted behaves like ReadCommitted but it returns the latest committed version of a rowand ignores any other (uncommitted) versions.

Reading
What it reads	The latest committed version of a row and ignores any other (uncommitted) versions.
Dirty reads	No
Phantom data	Possible
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	Yes
Usage
Suitable for	Short transactions that modify data.

IsolationLevel.ReadCommitted

Reading
What it reads	Waits for uncommitted transactions modifyinga row (to be either committed or rolled back).
Dirty reads	No
Phantom data	Possible
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	Yes
Usage
Suitable for	Short transactions that modify data.

IsolationLevel.RepeatableRead

Reading
What it reads	When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.
Dirty reads	No
Phantom data	No
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	Yes
Usage
Suitable for	Long transactions that require lots of reading and stable data view (reports).

IsolationLevel.Serializable

Reading
What it reads	When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.
Dirty reads	No
Phantom data	No
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	No. All tables that the transaction has read from are locked. Other transactions are prevented from writing to that tables.
Usage
Suitable for	Long transactions that require exclusive access to a table.