2.4. Isolation Levels

Isolation guarantees that transactions are processed in a reliable manner. To be more specific, it ensures that concurrently running transactions do not interfere with each other. In this way, isolation ensures data consistency. If the transactions were not isolated, one transaction could modify data that other transactions are reading. This would lead to data inconsistency.

The four isolation levels are, from lowest to highest:

  • READ-UNCOMMITTED—On this isolation level, transactions can see changes to data made by other transactions that are not committed yet. In other words, transactions can read data that may not eventually exist, as the other transactions can always roll-back the changes without commit. This is called a dirty read. READ-UNCOMMITTED has actually no real isolation at all.
  • READ-COMMITTED—On this isolation level, dirty reads are impossible, as uncommitted changes are invisible to other transactions until the transaction is committed. However, at this isolation level, SELECT clauses use their own snapshots of committed data, committed before the SELECT clause was executed. As a result, the same SELECT clause, when run multiple times within the same transaction, can return different result sets. This is called a non-repeatable read.
  • REPEATABLE-READ—On this isolation level, non-repeatable reads are impossible, as the snapshot for the SELECT clause is taken the first time the SELECT clause is executed during the transaction. This snapshot is used throughout the entire transaction for this SELECT clause and it always returns the same result set. This level does not take into account changes to data made by other transactions, regardless of whether they have been committed or not. In this way, reads are always repeatable.
  • SERIALIZABLE—This isolation level place locks on all records that are accessed within a transaction. SERIALIZABLE also locks the resource in a way that records cannot be appended to the table being operated on by the transaction. This level prevents a phenomenon known as a phantom read. A phantom read occurs when, within a transaction, two identical queries are executed, and the rows returned by the second query are different from the first.

Galera Cluster uses transaction isolation on two levels:

  • Locally, that is, on each node, transaction isolation works as with native InnoDB. You can use all levels. The default isolation level for InnoDB is REPEATABLE-READ.

  • At the cluster level, between transactions processing at separate nodes, Galera Cluster implements a transaction level called SNAPSHOT ISOLATION. The SNAPSHOT ISOLATION level is between the REPEATABLE READ and SERIALIZABLE levels.

    The SERIALIZABLE transaction isolation level is not supported in a multi-master use case, , not in the STATEMENT nor in the ROW format. This is due to the fact that Galera replication does not carry a transaction read set. Also, the SERIALIZABLE transaction isolation level is vulnerable for multi-master conflicts. It holds read locks and any replicated write to a read locked row will cause the transaction to abort. Hence, it is recommended not to use it in Galera Cluster.