4 Sep 2024

Transaction Isolation Levels in Galera Cluster

By now you must have read our documentation on isolation levels and also our Support for Transaction Isolation Levels. It is worth noting that the default transaction isolation level in MySQL 8 is REPEATABLE READ.

Here is a simple example of this, in action (you can test this on two different nodes, even across a 9-node Galera Cluster!).

First we do some simple setup:

CREATE DATABASE isolate;

USE isolate;

CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10, 2)
);

Then we insert some initial data:

INSERT INTO products (id, name, price) VALUES (1, 'Widget', 10.00);

Then we ensure that we are using READ COMMITTED:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Check this via:

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

Then on node 1, we execute:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT price FROM products WHERE id = 1;
+-------+
| price |
+-------+
| 10.00 |
+-------+
1 row in set (0.00 sec)

mysql> do sleep(15);
Query OK, 0 rows affected (15.00 sec)

mysql> SELECT price FROM products WHERE id = 1;
+-------+
| price |
+-------+
| 15.00 |
+-------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

The 15 second sleep is to give us some time to go to node 2 to execute:

START TRANSACTION;
UPDATE products SET price = 15.00 WHERE id = 1;
COMMIT;

You will notice that even before the COMMIT; was executed on node 1, the select returned 15. So with READ COMMITTED, you will notice that the first SELECT returned 10.00, the second SELECT returned 15.00, showing that with READ COMMITTED as an isolation level, the transaction sees the committed changes from other transactions.

Now, with REPEATABLE READ, this does differ!

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

We then proceed to execute the same on node 1:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT price FROM products WHERE id = 1;
+-------+
| price |
+-------+
| 10.00 |
+-------+
1 row in set (0.00 sec)

mysql> do sleep(15);
Query OK, 0 rows affected (15.00 sec)

mysql> SELECT price FROM products WHERE id = 1;
+-------+
| price |
+-------+
| 10.00 |
+-------+
1 row in set (0.00 sec)

And we do similarly on node 2:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE products SET price = 15.00 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.20 sec)

You’ll notice that on node 1, the second select is still 10.0 – both statements return 10.00 even though T2 updated it to 15.00.

So on node 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT price FROM products WHERE id = 1;
+-------+
| price |
+-------+
| 15.00 |
+-------+
1 row in set (0.00 sec)

Only after executing COMMIT; will it display the new value. REPEATABLE READ thus provides a consistent snapshot of the data.