In an earlier article we have gone through the process of Certification Based Replication in Galera Cluster for MySQL and MariaDB. To recall the overview, Galera Cluster is a multi-master cluster setup that uses the Galera Replication Plugin for synchronous and certification based database replication. This article examines the concept of Isolation Levels in Galera Cluster, that is an inherent feature of the MySQL InnoDB database engine.

A transactional DBMS uses the concept of isolation in which concurrent transactions are isolated from each other for performance, reliability and consistency. The setting “isolation level” determines the degree of isolation needed when multiple transactions try to make changes and issue queries to the database. Transaction Isolation represents the “I” in the ACID model; a set of database design principles that ensure reliability, consistency and durability needed for any transactional DBMS.

The ACID Model of Database Design
ACID stands for atomicity, consistency, isolation and durability. Database transactions should be atomic units of changes that can be committed upon successful completion or can be rolled back upon abortion of transaction. The database state should be consistent at all times, during the progression of a transaction and after commits and rollbacks. Queries should be able to see either the updated values or old values, but not a mix of two, when related data is being updated by a transaction across multiple tables. Transactions should be isolated from one another, so that one transaction could not interfere with the work set of another transaction and also to avoid conflicts. This is achieved through table and row locking. Isolation is controlled by the isolation level parameter. The more intense and prolonged is the isolation level, so us the locking, thus causing delays and performance issues for the sake of transaction integrity. So a balance between performance and isolation is what needed to be set as the isolation level. The new state of a database resulted after a committed transaction should be safe from possible database vulnerabilities like data loss, crashes, hardware/software failure, power failure etc. This quality represents durability of a transaction and is typically achieved through writing data to disk storage or like the doublewrite buffer mechanism in InnoDB.

Transaction Isolation Levels
The MySQL InnoDB engine and hence Galera Cluster supports 4 different isolation levels:

READ-UNCOMMITED: In this level, transactions can see data changes made by other transactions even before they are committed. Also known as dirty read, this level does not provide real isolation at all.
READ-COMMITED: Here transactions can see only committed changes made by other transactions. Here non-repeatable reads take place, means the SELECT queries read the committed data prior to the query. So when a single transaction runs multiple SELECT queries, each one sees their own snapshot of committed data that are different due to the changes in data caused by other transactions.
REPEATABLE–READ: This is the default isolation level for MySQL InnoDB. Here snapshots of data are taken before the first SELECT query and all subsequent queries see the same snapshot causing repeated read of same data. So queries will not see changes committed by other transactions making reads repeatable.
SERIALIZABLE: In this level, all rows accessed by the transaction are locked and appends are blocked. Since the data snapshot available to SELECT queries are the same ones, this is similar to REPEATABLE-READ but read-only.

Isolation Levels in Galera Cluster
Isolation levels are set when a client request causes a transaction. In the cluster these can be applied on a single node level (Intra node level) and on global cluster level (Inter node level) for all nodes. Single node level isolation can extend to the maximum level allowed by InnoDB engine, while for global cluster level isolation depends on the replication protocol.

In the master-slave mode of Galera Cluster, all four levels of isolation can be used, but multi-master mode supports only the REPEATABLE-READ level.

Working of Transaction Isolation Levels in Galera Cluster
Galera Cluster is utilizing the transaction isolation level feature that is inherent to the MySQL InnoDB engine. So transactions running on the same node in the cluster have isolation levels set for its MySQL configuration. That means if we have configured MySQL with the default REPEATABLE-READ level, transactions at same nodes will be isolated at that level. For transactions issued on separate cluster nodes, Galera Cluster uses SNAPSHOT ISOLATION, also called “first committer wins” isolation. This is used to solve the “lost update” problem that generally affects the plain REPEATABLE-READ isolation.
The lost update problem with REPEATABLE-READ on transaction with separate nodes typically occurs as follows when concurrent transactions operate:

1. Transaction 1 and Transaction 2 operating on node 1 is getting the same data snapshot before executing the operation.
2. Transaction 1 uses SELECT and get the snapshot from node 1.
3. Transaction 2 uses SELECT and get the same snapshot from node 1.
4. Transaction 1 updates the data on node 1, locking node 1 table.
5. Before Transaction 1 committing the above transaction, Transaction 2 trying to update data on node 1 (locked for Transaction 1’s commit), based on the previous snapshot that is no longer relevant now, because in step 4 above, Transaction 1 has already changed the data, but pending commit. Since this is plain REPEATABLE-READ and Transaction 1’s commit is pending, this change is not visible to Transaction 2
6. Transaction 1 commits data update made in step 4 and lock is released.
7. Transaction 2 commits its data update since lock is released. But note that, in step 5, Transaction 2 totally missed the update made by Transaction 1.

With Galera Cluster’s SNAPSHOT ISOLATION, the first committer wins logic is used for ensuring data integrity when concurrent transactions operate on separate nodes in the cluster (same database). It is implemented as follows:

Transaction 1 operates on node 1 and Transaction 2 on node 2, same database.
1. Transaction 1 begins transaction on node 1.
2. Transaction 2 begins transaction on node 2.
3. Transaction 1 reads (SELECT) from node 1 and gets the initial snapshot.
4. Transaction 1 reads (SELECT) from node 2 and gets the same initial snapshot as Transaction 1.
5. Transaction 1 updates data on node 1.
6. Transaction 2 updates same data on node 2. Here node 2 does not have the lock since Transaction 1 is operating on node 1. However Transaction 2 did not see the updated data (step 5).
7. Transaction 1 commits the update.
8. Transaction 2 starts commit, but failed due to the deadlock caused due to conflicting WRITE because Transaction 1 is working on the database. So transaction is aborted and restarted.
9. Transaction 1 sees (SELECT) the updated snapshot on node 1.
10. Transaction 2 reads (SELECT) and sees the updated snapshot as seen by Transaction 1 on step 9. So the first committer has won.

If the above scenario changes to concurrent transactions working on same node with REPEATABLE_READ isolation level, “lost update” issue arises. To overcome this issue, Galera Cluster utilizes MySQL/InnoDB’s SELECT FOR UPDATE statement for reading for updates. This statement locks the table from the READ operation prior to UPDATE operation itself making the table read only so that conflicting WRITEs are prevented. This is depicted below:

1. Transaction 1 and begins operating same data on node 1.
2. Transaction 1 issues SELECT FOR UPDATE statement. Lock is applied to table.
3. Transaction 2 issues SELECT FOR UPDATE statement, but is blocked by the InnoDB lock on node 2 data.
4. Transaction 1 updates and commits transaction on node 1.
5. The waiting Transaction 2 sees the updated snapshot on node 1 once the lock is removed. It issues update and commit the transaction without missing the update from Transaction 1.
In this way Galera Cluster can be configured to use REPEATABLE-READ isolation to overcome lost update problem using proper read locks. As of now, Galera does not support SERIALIZABLE isolation level in multi-master setup. This is because, currently the replication protocol does not have mechanism to prevent the read locks from being overwritten by replication. SERIALIZABLE isolation works in controlled master-slave setup.

Conclusion
Thus Galera Cluster effectively utilizes MySQL/InnoDB capability of isolation levels, especially REPEATABLE-READ isolation level coupled with proper read lock configurations to achieve data integrity and consistency. So Galera replication maintains and upheld the ACID compliance that is an essential requirement for any modern DBMS.