Phantom reading of InnoDB (II.)

The MySQL InnoDB transaction has level four isolation, and the default is "repeatable read" (Repeatable Read). Uncommitted read (read UNCOMMITTED). Another transaction modified the data, but it has not yet been submitted, and the select in this transaction reads the uncommitted data (dirty reads). Submit read (Read committed). This transaction reads the most current data (after other transactions have been committed). The problem is that in the same transaction, two times the same select will read different results (no repetition). REPEATABLE READ (Repeatable read). In the same transaction, the result of the select is the state of the point-in-time at the start of the transaction, so the same select operation reads the same result. However, there is a phantom reading phenomenon (explained later). Serialization (SERIALIZABLE). The read operation implicitly acquires shared locks, guaranteeing mutual exclusion between different transactions.

Four levels are gradually enhanced, and each level solves a problem. Dirty reading, the easiest to understand. Another transaction modified the data, but it has not yet been submitted, and the select in this transaction reads the uncommitted data. Do not repeat the repetition. When dirty reads are resolved, it is encountered that another transaction commits the new data during the execution of the same transaction, so the results of the data read two times in this transaction will be inconsistent. Phantom Reading. Resolved not to repeat, to ensure that in the same transaction, the result of the query is the state (consistency) at the start of the transaction. However, if another transaction submits new data at the same time, this transaction is then updated to "surprise" the discovery of the new data, which appears to be the "phantom" illusion.

Draw on and transform a funny metaphor: dirty reading. If, noon go to the canteen to eat, see a seat by classmate Little Q take up, think this seat was taken up, turned to find other seats. Unexpectedly, this classmate little Q got up and walked. Fact: The classmate little Q is only temporarily sitting a small, not "submitted." Do not repeat the repetition. If, noon go to the canteen to eat, see a seat is empty, then fart up to dozen rice, back to find this seat but was classmate little Q take up. Phantom Reading. If, noon go to the canteen to eat, see a seat is empty, then fart to dozen rice, back, found these seats are still empty (repeat read), secretly happy. When I was ready to sit down, I discovered a dinosaur sister who had a serious impact on her appetite. As if the empty seat was seen as "phantom".

------

It is not accurate to say that some articles are written to InnoDB to avoid "phantom reading" (Phantom Read).

Do a trial: (all of the following trials should pay attention to the storage engine and isolation level)

Mysql> Show CREATE TABLE t_bitfly\g;
CREATE TABLE ' T_bitfly ' (
' ID ' bigint 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 the table has no data, in fact, the data already exist, silly submission, only to find data conflicts.

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 in one line)
|
| SELECT * from T_bitfly;
| +------+-------+
| | ID | Value |
| +------+-------+
| | 1 | Z |
| | 2 | Z |
| +------+-------+
|
V

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

------

So, what InnoDB points out is to avoid the illusion of reading.

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

By default, InnoDB operates into repeatable READ transaction isolation level and with the Innodb_locks_unsafe_for_binlog sys TEM variable disabled. In the, InnoDB uses next-key locks for searches and index scans, which prevents, phantom rows (the "Avoiding the Phantom Problem Using next-key locking").

The understanding of preparation is that when the isolation level is repeatable and disabling Innodb_locks_unsafe_for_binlog, the Next-key locks used to search and scan index can avoid phantom reading.

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

MySQL Manual also has a paragraph:

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 the locking that combines index-row with gap locking.

You can use Next-key locking to implement a uniqueness check in your application:if your read your data in share mode and Do not-a duplicate for a row your are going to insert, then can safely insert your row and know this 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 you to "lock" the nonexistence of something in your, table.

My understanding is that InnoDB provides next-key locks, but requires the application to lock itself. Manual provides an example:

SELECT * from child WHERE ID > for UPDATE;

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

You can use Show InnoDB status to see if you've added a lock to the table.

To look at an experiment, note that the ID in the 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):
|