Google
 

Thursday, February 03, 2005

Transaction Isolation Levels in Firebird

Source: http://www.dotnetfirebird.org/blog/2005/02/transaction-isolation-levels-in.html

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.


1 comment:

hou said...

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.
meizu m2 note
xiaomi mi5
meizu pro 5