The InnoDB of MySQL's magic reading problem

The level of isolation for MySQL InnoDB transactions is level four, which is "repeatable read" by default (Repeatable Read).

• Unread (Read UNCOMMITTED). Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data (dirty read).
• Submit Read (COMMITTED). This transaction reads the most up-to-date data (after other transactions have been committed). The problem is that in the same transaction, two times the same select will read different results (do not repeat).
• REPEATABLE READ (Repeatable read). In the same transaction, the result of select is the state of the point in time at which the transaction started, so the same select operation will read the same result. However, there is a phantom reading phenomenon (explained later).
• Serialization (SERIALIZABLE). A read operation implicitly acquires a shared lock, which guarantees mutual exclusion between different transactions.

Four levels are gradually enhanced, and each level solves a problem.

• Dirty read, the most easy to understand. Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data.
• Do not repeat the repetition. After the dirty read is resolved, the same transaction is executed, another transaction commits the new data, so the result of the data read two times in this transaction will be inconsistent.
• Phantom read. solves the non-repetition, guarantees the same transaction, the result of the query is the state (consistency) at the beginning of the transaction. However, if another transaction commits new data at the same time, when the transaction is updated, the new data is "surprisingly" discovered, as if the previously read data were "ghost" hallucinations.

Draw on and transform a funny metaphor:

• Dirty read. If, at noon to eat food, see a seat was classmate small Q accounted for, think this seat was taken up, turned to find other seats. Unexpectedly, this classmate little Q got up and walked. Fact: The classmate small Q just sat down for a little while, did not "submit".
• Do not repeat the repetition. If, at noon to eat food in the cafeteria, see a seat is empty, then fart on the top of the fart to play rice, came back to find this seat was classmate small Q took up.
• Phantom read. If, at noon to eat food in the cafeteria, see a seat is empty, then fart on the fart to play rice, come back, found these seats are still empty (repeated reading), stealing hi. Walk to the front just ready to sit down, but a dinosaur sister, seriously affect appetite. As if the empty seat I had seen before was "phantom".

------

Some articles write that InnoDB's repeatable reading avoids "phantom reading" (Phantom Read), which is inaccurate.

Do an experiment: (all of the following trials should be aware of the storage engine and isolation level)

Mysql> Show CREATE TABLE t_bitfly\g;
CREATE TABLE ' T_bitfly ' (
' ID ' bigint (a) Not NULL default ' 0 ',
' Value ' varchar (+) default NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT CHARSET=GBK

Mysql> SELECT @ @global. tx_isolation, @ @tx_isolation;
+-----------------------+-----------------+
| @ @global. tx_isolation | @ @tx_isolation |
+-----------------------+-----------------+
| Repeatable-read | Repeatable-read |
+-----------------------+-----------------+

Test One:

T session A Session B
|
|          START TRANSACTION; START TRANSACTION;
|
| SELECT * from T_bitfly;
| Empty set
| INSERT into T_bitfly
| VALUES (1, ' a ');
|
| SELECT * from T_bitfly;
| Empty set
| COMMIT;
|
| SELECT * from T_bitfly;
| Empty set
|
| INSERT into T_bitfly VALUES (1, ' a ');
| ERROR 1062 (23000):
| Duplicate entry ' 1 ' for key 1
V (shit, just tell me I don't have this record)

So there is a phantom reading, that there is no data in the table, in fact, the data already exist, after the silly submission, only to find the data conflict.

Test Two:

T session A Session B
|
|         START TRANSACTION; START TRANSACTION;
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
| INSERT into T_bitfly
| VALUES (2, ' B ');
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
| COMMIT;
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
|
| UPDATE t_bitfly SET value= ' z ';
| Rows matched:2 Changed:2 warnings:0
| (How to come out one more line)
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | Z |
| | 2 | Z |
| +------+-------+
|
V

The first time a row is read in this transaction, once an update is made, the data submitted in the other transaction appears. can also be seen as a kind of phantom reading.

------

So what does InnoDB point out to avoid phantom reading?

Http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

By default, InnoDB operates in repeatable READ transaction isolation level and with the Innodb_locks_unsafe_for_binlog sys TEM variable disabled. InnoDB uses Next-key locks for searches and index scans, which prevents phantom rows (see section 13.6.8.5, "Avoiding the Phantom problem Using next-key Locking").

The understanding is that when the isolation level is repeatable, and the Innodb_locks_unsafe_for_binlog is disabled, the Next-key locks used when searching for and scanning index can avoid phantom reads.

The key point is, is innodb default to a common query will also add Next-key locks, or the need to apply their own to lock it? If you look at this sentence, you may think that InnoDB to the ordinary query also added lock, if so, and serialization (SERIALIZABLE) The difference between it?

There is another section in MySQL Manual:

13.2.8.5. Avoiding the Phantom problem Using Next-key Locking (http://dev.mysql.com/doc/refman/5.0/en/ innodb-next-key-locking.html)

To prevent Phantoms, InnoDB uses a algorithm called Next-key locking that combines index-row locking with Gap Loc King.

You can use Next-key locking to implement a uniqueness check in your application:if you read your data in share mode and Do not see a duplicate for a row you is going to inserts, then can safely inserts your row and know that the Next-key l Ock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the Next-key locking enables the "lock" the nonexistence of something in your table.

My understanding is that InnoDB provides next-key locks, but requires the application to add locks on its own. An example is provided in manual:

SELECT * from child WHERE ID > + for UPDATE;

In this way, the InnoDB will give a row with an ID greater than 100 (if the child table has a row ID of 102), and the gap in 100-102,102+ is added to the lock.

You can use Show InnoDB status to see if a lock is added to the table.

To see an experiment, note that the ID in table t_bitfly is the primary key field. Experiment Three:

T session A Session B
|
|        START TRANSACTION; START TRANSACTION;
|
| SELECT * from T_bitfly
| WHERE id<=1
| For UPDATE;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
| INSERT into T_bitfly
| VALUES (2, ' B ');
| Query OK, 1 row affected
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
| INSERT into T_bitfly
| VALUES (0, ' 0 ');
| (Waiting for lock ...
| Then timeout)
| ERROR 1205 (HY000):
| Lock wait timeout exceeded;
| Try restarting transaction
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
| COMMIT;
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | A |
| +------+-------+
V

As you can see, with id<=1 Lock, only the scope of the id<=1 is locked, you can successfully add a record with ID 2 and add a record with id 0 to wait for the lock to be released.

A detailed explanation of the lock in a repeatable read in MySQL manual:

Http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

For locking reads ( SELECT with FOR UPDATE or LOCK IN SHARE MODE ), UPDATE , and DELETE statements, locking depends on whether the statement uses a Unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (Gap plus Index-record) lock s to block insertions by and sessions into the gaps covered by the range.

------

Consistent read and submit reading, first look at experiments, experiment four:

T session A Session B
|
|             START TRANSACTION; START TRANSACTION;
|
| SELECT * from T_bitfly;
| +----+-------+
| | ID | Value |
| +----+-------+
| | 1 | A |
| +----+-------+
| INSERT into T_bitfly
| VALUES (2, ' B ');
| COMMIT;
|
| SELECT * from T_bitfly;
| +----+-------+
| | ID | Value |
| +----+-------+
| | 1 | A |
| +----+-------+
|
| SELECT * from T_bitfly LOCK in SHARE MODE;
| +----+-------+
| | ID | Value |
| +----+-------+
| | 1 | A |
| | 2 | B |
| +----+-------+
|
| SELECT * from T_bitfly for UPDATE;
| +----+-------+
| | ID | Value |
| +----+-------+
| | 1 | A |
| | 2 | B |
| +----+-------+
|
| SELECT * from T_bitfly;
| +----+-------+
| | ID | Value |
| +----+-------+
| | 1 | A |
| +----+-------+
V

If you use normal reading, you will get consistent results, and if you use locking read, you will read the results of the "latest" "Commit" read.

itself, repeatable reads and submissions are contradictory. In the same transaction, if repeatable reading is guaranteed, the submission of other transactions is not seen, the submission is violated, and if the submission is guaranteed, the result is inconsistent with the results of the two reads, and the repeatable reading is violated.

As you can tell, InnoDB provides a mechanism to use lock-read to query the latest data in the default repeatable read isolation level.

Http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

If you want to see the ' freshest ' state of the database, you should use either the READ COMMITTED isolation level or a loc King read:
SELECT * from T_bitfly LOCK in SHARE MODE;

------

Conclusion: The repeatable reading of MySQL InnoDB does not guarantee the avoidance of phantom reading, and it needs to be guaranteed by the application using lock reading. The locking mechanism used is next-key locks.

The InnoDB of MySQL's magic reading problem