In this section we will see which reads are consistent within a transaction, and how InnoDB guarantees this consistency. The consistency of queries is determined by the transaction level, by using the WITH CONSISTENT SNAPSHOT
option for START TRANSACTION
, and the LOCK IN SHARE MODE
or FOR UPDATE
options for SELECT
. Augmenting the consistency of reads can be important to be sure that applications work properly, while relaxing it improves the concurrency.
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.