Isolation level of MySQL InnoDB transactions There are four levels, and the default is REPEATABLE READ.

• Uncommitted Read (READ) UNCOMMITTED).Another transaction modifies the data but has not committed it yet, and SELECT in this transaction will read the uncommitted data (dirty read).
• Committed Read (READ) COMMITTED).This transaction reads the latest data (after other transactions have been committed).The problem is that in the same transaction, two identical SELECT reads different results (no repetition).
• Repeatable Read (REPEATABLE) READ).In the same transaction, the result of SELECT is the state of the point at which the transaction started, so the same SELECT operation will read the same result.However, there will be hallucinations (explained later).
• Serialization (SERIALIZABLE).Read operations implicitly acquire shared locks, which guarantee mutual exclusion between different transactions.

Each of the four levels solves a problem.

• Dirty reading, the easiest to understand.Another transaction modifies the data but has not committed it yet, and the SELECT in this transaction will read the uncommitted data.
• Do not repeat reading.After resolving dirty reads, you will encounter that during the same transaction execution, another transaction submitted new data, so the data results read twice by this transaction will be inconsistent.
• Fantasy reading.Resolves non-repetitive reading, ensuring that in the same transaction, the results of the query are in the state at the beginning of the transaction (consistency).However, if another transaction submits new data at the same time, although this transaction finds the same result set again under the same conditions, when this transaction specifies an update (you will know from the later demonstration), it will "be amazed" to discover the new data, which appears to have been read before is "ghost" oneHallucinations like this.

Some articles write that InnoDB's repeatable reading avoids phantom read ing, which is not accurate.

Make a test: (Note the storage engine and isolation level for all tests below)

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 |
+---—–+--—–+

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 I don't have this record)

This led to the illusion that there was no data in the table. In fact, the data already exists. After silly submission, data conflicts were found.

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 to get one more line)
|
| SELECT * FROM t_bitfly;
| +-+--+
| | id   | value |
| +-+--+
| |    1 | z     |
| |    2 | z     |
| +-+--+
|
v

The first time a row is read out in this transaction, after an update, data submitted in another transaction appears.It can also be thought of as an illusion.

-

So what does InnoDB point out about avoiding fantasy 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").

The prepared understanding is that next-key is used when searching and scanning index when isolation level is repeatable and innodb_locks_unsafe_for_binlog is disabled locks can avoid magic reading.

The key point is that InnoDB adds next-key to a common query by default locks, or do you need to apply yourself to lock?If you look at this sentence alone, you might think InnoDB has also locked a common query. If so, what is the difference from serialization (SERIALIZABLE)?

There is still a 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 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 itself is required to lock them.An example is provided in manual:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

In this way, InnoDB locks rows with IDS greater than 100 (if one row in the child table has IDS 102) and gap s with IDS 100-102, 102+.

You can use show innodb status to see if a table is locked.

Looking at another experiment, note that the id in the table t_bitfly is the primary key field.Experiment 3:

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)
|