Multi Version Concurrency Control
Overview​
MDB implements isolation levels using an entirely lockless version of MVCC adapted to an append-only system. MVCC works in conjunction with historical record implementation and enforcement, multi-version data access, and the blockchain cryptography protocols.
bQL Isolation Levels
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Not Possible | Possible | Possible |
| Serializable | Not Possible | Not Possible | Not Possible |
[!NOTE] Currently the only supported isolation level are READ UNCOMMITTED, READ COMMITTED, and SERIALIZABLE.
Shadowed Columns
To implement isolation levels, bSQL uses two system columns on each blockchain. The sys_timestamp and sys_xact track the mutation information. They can be queried from if specified in the SELECT clause and used like any other column.
Isolation Levels
Read Uncommitted Isolation Level​
READ UNCOMMITTED does not prevent other transactions from modifying data read by the current transaction or prevent the
current transaction from reading rows that have been modified but not committed by other transactions.
When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.
Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.
This is the least restrictive of the isolation levels.
Read Committed Isolation Level​
When a transaction uses READ COMMITTED, a SELECT query sees only data committed before the query began; it never sees either uncommitted data
or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database
as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction,
even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction,
if other transactions commit changes after the first SELECT starts and before the second SELECT starts.
Serializable Isolation Level​
SERIALIZABLE provides the highest transaction isolation. This level emulates serial transaction execution,
as if transactions had been executed one after another, serially, rather than concurrently. However, applications using
this level must be prepared to retry transactions due to serialization failures.
When a transaction is on the serializable level, a SELECT query sees only data committed before the transaction began and never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within this same transaction, even though they are not yet committed.)
If a target row found by a query while executing an AMEND or DISCONTINUE statement has already been updated by a concurrent uncommitted transaction then the second transaction that tries to update this row will not wait, but fail.
When the application receives an error message, it should abort the current transaction and then retry the whole transaction from the beginning. The second time through, the transaction sees the previously-committed change as part of its initial view of the database, so there is no logical conflict in using the new version of the row as the starting point for the new transaction's update. Note that only updating transactions may need to be retried --- transactions that don't update never have serialization conflicts.
Serializable transaction level provides a rigorous guarantee that each transaction sees a wholly consistent view of the database. However, the application has to be prepared to retry transactions when concurrent updates make it impossible to sustain the illusion of serial execution, and the cost of redoing complex transactions may be significant.