1, There are four levels of isolation for MySQL InnoDB transactions, and the default is REPEATABLE READ.
- READ UNCOMMITTED. Another transaction modifies the data but has not yet committed, and the SELECT in this transaction will read the uncommitted data (dirty read).
- READ COMMITTED. This transaction reads the latest data (after other transactions are committed). The problem is that in the same transaction, two previous and two identical selections will read different results (no repeat reading).
- REPEATABLE READ. In the same transaction, the result of SELECT is the state of the time point at the beginning of the transaction. Therefore, the results read by the same SELECT operation will be consistent. However, there will be unreal reading (to be explained later).
- SERIALIZABLE. The read operation will implicitly acquire the shared lock, which can ensure the mutual exclusion between different transactions.
2, Four levels are gradually enhanced, each solving a problem.
- Dirty reading is the easiest to understand. Another transaction modifies the data but has not yet committed, and the SELECT in this transaction will read the uncommitted data.
- Do not repeat. After the dirty reading is solved, we will encounter that during the execution of the same transaction, another transaction submits new data, so the data read by this transaction twice in succession will not be consistent.
- Fantasy reading. It solves the problem of no repeat reading, and ensures that in the same transaction, the query result is the state at the beginning of the transaction (consistency). However, if another transaction submits new data at the same time, when the transaction is updated again, it will be "surprised" to find the new data. It seems that the data read before is a ghost like illusion.
A funny metaphor is borrowed and transformed:
Dirty reading. If, go to dining hall to have lunch at noon, see a seat occupied by schoolmate small Q, think this seat was occupied, turn around to look for other seat. Unexpectedly, this schoolmate xiaoq got up and left. Fact: the classmate, Xiao Q, just sat down for a while and didn't "submit".
Do not repeat. If, go to dining hall at noon to have a meal, see a seat is empty, then go to have a meal, come back to find this seat is occupied by schoolmate xiaoq.
Fantasy reading. If you go to the canteen to have a meal at noon and see that a seat is empty, you will go to have a meal. When you come back, you will find that these seats are still empty (repeat reading), and you will be very happy. When I came to the front and just wanted to sit down, I saw a dinosaur girl, which seriously affected my appetite. As if the empty seat we saw before was a mirage.
Some articles write that InnoDB's repeatable reading avoids "phantom read", which is not accurate.
Do a test: (pay attention to storage engine and isolation level for all the following tests)
mysql> show create table t_bitfly\G; CREATE TABLE `t_bitfly` ( `id` bigint(20) NOT NULL default '0', `value` varchar(32) 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 | +-----------------------+-----------------+
3, Test 1:
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 told me that I didn't have this record)
In this way, there is unreal reading, thinking that there is no data in the table, in fact, the data already exists, and only after silly submission can we find the data conflict.
4, Test 2:
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 can I get one more line) | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | z | | | 2 | z | | +------+-------+ | v
A row is read for the first time in this transaction. After an update is made, the data submitted in another transaction appears. It can also be regarded as a kind of unreal reading.
So what does InnoDB point out about avoiding unreal 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 system variable disabled. In this case, 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").
It is prepared to understand that when the isolation level is repeatable and InnoDB ﹣ locks ﹣ unsafe ﹣ for ﹣ binlog is disabled, the next key locks used in index search and scanning can avoid unreal reading.
The key point is that InnoDB will add next key locks to a common query by default, or do you need to apply your own locks? If you look at this sentence, you may think that InnoDB also locks ordinary queries. If so, what's the difference between InnoDB and SERIALIZABLE?
There is another paragraph 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 an algorithm called next-key locking that combines index-row locking with gap locking. 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 are going to insert, then you can safely insert your row and know that the next-key lock 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 the application needs to lock itself. An example is provided in manual:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
In this way, InnoDB will lock the rows with id greater than 100 (if there is a row with id 102 in the child table), as well as the gap of 100-102102 +.
You can use show innodb status to see if the table is locked.
5, Experiment 3
In another 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
It can be seen that the lock added with id < = 1 only locks the range of id < = 1. The record with id 2 can be added successfully. When the record with id 0 is added, the lock will be released.
Detailed explanation of locks in repeatable reading 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) locks to block insertions by other sessions into the gaps covered by the range.
6, Experiment 4
For consistency reading and submission reading, first look at the experiment, Experiment 4:
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. If you use locked reading, you will read the "latest" and "submit" reading results.
In itself, there is a contradiction between repeatable reading and submitted reading. In the same transaction, if the repeatable read is guaranteed, the commit of other transactions will not be seen and the commit read will be violated; if the commit read is guaranteed, the results read before and after will be inconsistent and the repeatable read will be violated.
It can be said that InnoDB provides such a mechanism that in the default repeatable isolation level, lock reading can be used to query the latest data.
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 locking read: SELECT * FROM t_bitfly LOCK IN SHARE MODE;
7, Conclusion:
The repeatable reading of MySQL InnoDB does not guarantee the avoidance of unreal reading. It needs to be guaranteed by the application of lock reading. The mechanism used for this locking is next key locks.