A read is called non-repeatable if repeating the same query twice within the same transaction without modifying the data within the transaction returns different results. This happens because the current transaction is not fully isolated from changes requested by other connections.

Of course, this improves the overall performance in an environment where concurrency exists. But the application developers should be aware that this can happen, and if this represents a problem, it must be avoided.

The mechanisms that make a read repeatable are consistent reads and locking reads. Next-key locks also guarantee protection from the insertions of new values in a given range after a query. These mechanisms will be discussed in the following sections. If none of this is used, the read is non-repeatable.

The next-key locks avoid a problem called phantom rows. What is it? Suppose that a transaction performs a query involving a range of values from a non-indexed column, for example, WHERE column BETWEEN 10 AND 20. The query returns three rows with the values 10, 15, and 20. But then, another connection adds a row with the value 13. If the first connection repeats the same query, it will see that a new row has appeared. This is called a phantom row.

If the column is indexed, InnoDB uses a next-key lock. The second connection will still be able to insert the new row immediately. It will not have to wait until the first transaction ends. But the new row will not be visible for the first transaction. This guarantees a good level of isolation between different transactions.

Let's see an example.

First, let's open a mysql client instance. We will create a table with an indexed column that contains the values 1, 3, and 5. Then, let's start a transaction (we will use the REPEATABLE READ isolation level) and retrieve all values >= 3:

MariaDB [test]> CREATE TABLE t (a INT PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.40 sec) 
MariaDB [test]> INSERT INTO t VALUES (1), (3), (5);
Query OK, 3 rows affected (0.08 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t WHERE a >= 3; 
+---+ 
| a | 
+---+ 
| 3 | 
| 5 | 
+---+ 
2 rows in set (0.00 sec)

InnoDB uses a next-key lock in this case.

Now, let's open another mysql instance, in the autocommit mode. We will insert a new row with the value 4. This row is in the interval that was requested by the former query:

MariaDB [test]> INSERT INTO t VALUES (4); 
Query OK, 1 row affected (0.06 sec) 

Now, let's repeat the query in the first mysql instance, commit the transaction, and repeat the same query for the last time to see whether there is a difference:

MariaDB [test]> SELECT * FROM t WHERE a >= 3; 
+---+ 
| a | 
+---+ 
| 3 | 
| 5 | 
+---+ 
2 rows in set (0.00 sec) 
MariaDB [test]> COMMIT; 
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t WHERE a >= 3; 
+---+ 
| a | 
+---+ 
| 3 | 
| 4 | 
| 5 | 
+---+ 
3 rows in set (0.00 sec) 

As expected, the new row is not visible before a commit, so the data remains consistent within the transaction. But after a commit, the row becomes visible.

A consistent read is a read from a table that is consistent within the current transaction. It uses no locks. When a table is accessed for the first time by the current transaction, a snapshot is created. The snapshot represents the table data in an exact point in time. Changes requested by other connections do not affect the snapshot, even after a COMMIT. If the current transaction performs DML statements on the table, like INSERT, only its own snapshot is modified. So other transactions will not be aware of the changes. When a COMMIT statement is made, the snapshot changes are copied into the real table and become visible for all the connections. Changes made by other connections also become visible for the current connection.

Consistent reads can be obtained by using the REPEATABLE-READ isolation level and START TRANSACTION WITH CONSISTENT SNAPSHOT. They are also used for the SELECT statements when the isolation level is READ COMMITTED, but remember that in this case, each statement will use a separate snapshot even within the same transaction.

The following example illustrates how consistent reads work in practice.

First, let's open a mysql client instance. We'll create the table we are going to use. Then, we will start a transaction with a consistent read and insert the first record:

MariaDB [test]> CREATE TABLE t (a INT UNIQUE) ENGINE = InnoDB;
Query OK, 0 rows affected (0.47 sec) 
MariaDB [test]> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.00 sec) 

Then, let's open another myql instance. This will use the autocommit mode, for brevity. We will insert another record and then check what records are visible for this connection:

MariaDB [test]> INSERT INTO t VALUES (2);
Query OK, 1 row affected (0.05 sec) 
MariaDB [test]> SELECT * FROM t;
+------+ 
| a    | 
+------+ 
|    2 | 
+------+ 
1 row in set (0.00 sec) 

The SELECT statement shows that only the record inserted by this connection is visible. The record inserted by the first connection cannot be seen at this point.

Now, let's return to the first mysql instance. Let's commit the transaction and check which rows are visible:

MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.08 sec) 
MariaDB [test]> SELECT * FROM t;
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
+------+ 
2 rows in set (0.00 sec) 

As expected, after the commit, the record inserted by the first connection becomes visible.

Now let's repeat the SELECT command on the second connection:

MariaDB [test]> SELECT * FROM t;
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
+------+ 
2 rows in set (0.00 sec) 

Since the first connection was committed, the record it inserted became visible to everyone.

We can also open another mysql instance and check for InnoDB locks at any point during the former example. Since consistent reads do not imply any locks, we will always get an empty result set:

MariaDB [(none)]> SELECT * FROM information_schema.INNODB_LOCKS;
Empty set (0.00 sec)

A more complex example could show that the effects of DELETE and UPDATE statements are also not visible for other connections with consistent reads.

If we execute the previous statements without the WITH CONSISTENT SNAPSHOT clause, each row inserted by the second connection will be immediately visible to the first one.

Locking reads are another way to guarantee the consistency of data within a transaction. It is stronger than consistent reads because it locks data so that other connections will not be able to access them at all, or will only be able to read them, until the current transaction ends.

Locking reads can be obtained with two clauses of the SELECT statement: LOCK IN SHARE MODE and FOR UPDATE. The type of locks that are acquired depends on the used clause.

The LOCK IN SHARE MODE clause prevents other connections from modifying the rows that are returned by SELECT. But the other connections will still be able to read them.

With FOR UPDATE, the SELECT statement acts like an UPDATE; returned rows are locked so that they cannot be modified by other connections. Other connections will not be able to read those rows, unless they use the READ UNCOMMITTED isolation level. Even in that case, those connections will not be able to lock the rows in the shared mode.

If the current transaction's isolation level is SERIALIZABLE, and the autocommit mode is disabled, the LOCK IN SHARE MODE clause is always added to SELECT statements, unless they use LOCK IN SHARE MODE.

The following example shows how LOCK IN SHARE MODE works.

First, open a mysql client instance. Let's create a table with an index; this is very important because InnoDB locks are based on index records. Then, we will start a transaction and select one record in the share mode:

MariaDB [test]> CREATE TABLE t (a INT UNIQUE) ENGINE=InnoDB;
Query OK, 0 rows affected (0.54 sec) 
MariaDB [test]> INSERT INTO t VALUES (1), (2), (3);
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t WHERE a = 1 LOCK IN SHARE MODE;
+------+ 
| a    | 
+------+ 
|    1 | 
+------+ 
1 row in set (0.00 sec) 

Then, let's open another mysql instance. We will try to select all the records and modify two records, of which only one has been returned by the SELECT command executed by the first connection:

MariaDB [test]> SELECT * FROM t;
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
|    3 | 
+------+ 
3 rows in set (0.00 sec) 
MariaDB [test]> UPDATE t SET a = 300 WHERE a = 3;
Query OK, 1 row affected (0.13 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
MariaDB [test]> UPDATE t SET a = 100 WHERE a = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

As shown in the preceding code, the SELECT command works; the UPDATE command on record 3 worked, but the UPDATE command on record 1, which was locked by the first connection, had to wait because of the lock. In this example, we never committed the first transaction, so after a period of time MariaDB returned an error to the second connection. Usually, connections are supposed to be committed or rolled back, which unlocks all the records.