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:
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
Post a Comment