Visibility

In ACID transactions, the I stands for Isolation. Changes to data in one transaction is hidden from other transactions until the transaction making the change commits (or rolls back). Isolation, in turn, enables consistency (the C in ACID).

NuoDB supports two isolation Levels: Read Committed and Consistent Read (also known as Multi-Version Concurrency Control or MVCC). Unlike most RDBMS, Consistent Read is the default isolation level for transactions.

Read Committed

A given transaction can only see committed data (such as rows in a table). Any changes to data during the transaction by another client is not visible until that client commits. Data may therefore change during a Read Committed transaction.

As NuoDB is distributed, changes to data are not only sent to SMs for persistence (or Durability, the D in ACID), they are also sent to any TE that has the same data cached in memory. Once the transaction commits, all these TEs modify their cached copy, bringing it up to date. Read Committed transactions on those TEs will now see the changed value.

If a client wishes to change a data item, it must get a lock in the usual way. Once locked, other clients may not modify the data, no matter which TE they are using. Every atom acts as its own lock-manager, so once it registers a lock, all the copies in other TEs register the same lock. Only one copy of the atom handles locking logic and is termed the Chairman. Once changes are committed (or rolled back), the Chairman releases its lock and all copies release the same lock.

As with any RDBMS, deadlock is possible. NuoDB detects deadlock and forces all but one deadlocked transaction to abort and rollback, causing a deadlock exception in the client and releasing locks in the process. The remaining transaction can get the locks it needs and carry on. The other transactions must decide to give up or retry in the usual way.

Consistent Read

During a Consistent Read transaction, all changes to data are hidden until the transaction commits and a new transaction is started. The client effectively gets a version of the database frozen in time at the start of the transaction. Multiple concurrent Consistent Read transactions each get their own frozen version to use (hence the term MVCC). Data can always be read, the client is never blocked, but the data value may be out of date.

Data may be changed by the client, but if it has already been changed by another client during the Consistent Read transaction, the transaction will be rolled back and an exception raised. The client must decide whether to give up or try again, in a similar manner to Deadlock handling. Consistent Read is similar in behavior to Eventual Consistency

SQL Isolation Levels

SQL defines 4 isolation levels:

Dirty Read

Any data can be read, no locks are taken but there is no actual isolation. All data is visible, even if it is currently being modified (but uncommitted) in another transaction. Useful for intrusive transactions that need to read a lot of data and would otherwise block other clients. NuoDB does not need to support Dirty Read since Consistent Read provides the same advantages but without violating consistency - a Clean Read perhaps.

Read Committed

As described above.

Repeatable Read

Data once read will not change during the transaction but there is no guarantee that other data will not change leading to Phantom Reads.

  • For example, a query fetches all Accounts with a balance over a million dollars. An account, not retrieved in that search, receives a large credit, putting its balance over the million dollars. Rerunning the original query in the same transaction will now find the additional phantom Account.

  • NuoDB implements Repeatable Read using Consistent Read so, in fact, phantom reads cannot occur.

Serializable

Serializable guarantees no data will change during a transaction. Effectively acting like a lock on the entire database which is impractical. NuoDB implements Serializable using Consistent Read.

If your transaction needs the absolute up-to-date value, for example the number of available product items remaining to be sold, you must use Consistent Read. However in most other cases Consistent Read is acceptable.