MySQLInnoDB has four transaction levels: Dirty read, no repeated read, and phantom read.

MySQL InnoDB Transaction isolation level dirty read, Repeatable read, phantom read

MySQL InnoDB transactions are isolated at four levels. The default value is repeatable read ).

· READUNCOMMITTED ). Another transaction modifies the data but has not yet committed it, And the SELECT statement in this transaction reads the uncommitted data (dirty read ).

· READCOMMITTED ). This transaction reads the latest data (after other transactions are committed ). The problem is that in the same transaction, two identical SELECT statements will read different results (no repeated reads ).

· REPEATABLEREAD ). In the same transaction, the SELECT result is the state at the time point when the transaction starts. Therefore, the results read by the same SELECT operation will be consistent. However, there will be Phantom reads (explained later ).

· SERIALIZABLE ). Read operations implicitly obtain shared locks to ensure mutual exclusion between different transactions.

The four levels are gradually enhanced, and each level solves one problem.

· Dirty reading is the easiest to understand. Another transaction modifies the data but has not yet committed it, And the SELECT statement in this transaction reads the uncommitted data.

· No repeated read. After the dirty read is solved, new data is committed in another transaction during the execution of the same transaction. Therefore, the data results read twice in this transaction are inconsistent.

· Phantom read. It solves the problem of non-repeated reads and ensures that the query results are consistent at the beginning of the transaction ). However, if another transaction commits new data at the same time, the new data will be "surprised" when the transaction is updated, it seems that the data we read is just like a ghost movie.

CREATETABLE 'T '(

'A' int (11) NOT NULL PRIMARY KEY

) ENGINE = InnoDBDEFAULT CHARSET = utf8;

InsertINTO t (a) values (1), (2), (3 );

The above text is not so easy to understand. The following describes in detail the four transaction isolation levels of InnoDB using several experiments, we hope to deepen our understanding of the transaction isolation level of InnoDB through experiments.

Tutorial 1: Explanation of dirty read and Repeatable read Problems

Update transactions	Transaction a read-UNCOMMITTED	Transaction B READ-COMMITTED,	Transaction C-1 REPEATABLE-READ	Transaction C-2 REPEATABLE-READ	Transaction D SERIALIZABLE
Set autocommit = 0;
Start transaction;				Start transaction;
Insert into t (a) values (4 );
	Select * from t; 1, 2, 3, 4 (dirty read: reads data from uncommitted transactions)	Select * from t; 1, 2, 3 (solve dirty read)	Select * from t; 1, 2, 3	Select * from t; 1, 2, 3	Select * from t; 1, 2, 3
Commit;
	Select * from t: 1, 2, 3, 4	Select * from t: 1, 2, 3, 4	Select * from t: 1, 2, 3, 4 (not in the same transaction as above, so read the latest one after transaction commit, so you can read 4)	Select * from t: 1, 2, 3 (repeated read: because it is in the same transaction as above, it is read-only to the data starting the transaction, that is, repeated read)	Select * from t: 1, 2, 3, 4
				Commit (commit a transaction, the following is a new transaction, so you can read the latest data after the transaction is committed)
				Select * from t: 1, 2, 3, 4
READ-UNCOMMITTED generates dirty reads, which are rarely used in actual scenarios.

Experiment 2: Test the READ-COMMITTED and REPEATABLE-READ

Transaction	Transaction B READ-COMMITTED	Transaction C REPEATABLE-READ
Set autocommit = 0;
Start transaction;	Start transaction;	Start transaction;
Insert into t (a) values (4 );
	Select * from t; 1, 2, 3	Select * from t; 1, 2, 3
Commit;
	Select * from t: 1, 2, 3, 4	Select * from t: 1, 2, 3 (repeated read: because it is in the same transaction as above, it is read-only to the data starting the transaction, that is, repeated read)
		Commit (commit a transaction, the following is a new transaction, so you can read the latest data after the transaction is committed)
		Select * from t: 1, 2, 3, 4
The REPEATABLE-READ ensures that the data read in a transaction is repeatable, that is, the same read (after the first read, even if other transactions have committed new data, in the same transaction, the re-select statement will not be read ). READ-COMMITTED only ensures that the data submitted by the latest transaction is READ.

Of course, the data visibility is for different transactions, and the same transaction can read the latest data in this transaction.

Starttransaction;

Insertinto t (a) values (4 );

Select * from t;

1, 2, 3, 4;

Insertinto t (a) values (5 );

Select * from t;

1, 2, 3, 4, 5;

Experiment 3: test the impact of SERIALIZABLE transactions on others

Transaction A SERIALIZABLE	Transaction B READ-UNCOMMITTED	Transaction c read-COMMITTED,	Transaction D REPEATABLE-READ	Transaction E SERIALIZABLE
Set autocommit = 0;
Start transaction;			Start transaction;
Select a from t union all select sleep (1000) from dual;
	Insert into t (a) values (5 );	Insert into t (a) values (5 );	Insert into t (a) values (5 );	Insert into t (a) values (5 );
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	SERIALIZABLE execution, so that all other transactions have to wait for transaction A to end before they can be executed. Here we use the sleep function, which directly leads to transactions B, C, D, e. Wait for transaction A to hold the released lock. Since I sleep for 1000 seconds, innodb_lock_wait_timeout is 120 s. Therefore, HY000 error is reported when S is reached.
SERIALIZABLE is a strictly SERIALIZABLE execution mode. Whether it is read or write, it will affect other transactions that read the same table. Is a strict exclusive table-level read/write lock. It also loses the advantages of the innodb engine. There are few practical applications.

Experiment 4: phantom reading

Some articles write the Repeatable read Statement to InnoDB to avoid "phantom read". This statement is not accurate.

Make a test: (pay attention to the 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 |
+ ----------------------- + ----------------- +

Test 4-1:

TSessionA Session B
|
| 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 there is no such record)

In this case, Phantom reads appear, thinking that there is no data in the table. In fact, the data already exists. After a silly commit, the data conflict is discovered.

Test 4-2:

TSessionA Session B
|
| 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 add one more row)
|
| SELECT * FROM t_bitfly;
| + ------ + ------- +
| Id | value |
| + ------ + ------- +
| 1 | z |
| 2 | z |
| + ------ + ------- +
|
V

This is the first time that a row is read and retrieved. After an update is performed, the data committed in another transaction appears. It can also be seen as a phantom read.

------

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 operatesin repeatable read transaction isolation level and with the innodb_locks_unsafe_for_binlogsystem variable disabled. in this case, InnoDB uses next-key locks for searchesand index scans, which prevents phantom rows (see Section 13.6.8.5, "Avoidingthe Phantom Problem Using Next-Key Locking ").

It is understood that when the isolation level is repeatable and innodb_locks_unsafe_for_binlog is disabled, the next-keylocks used for searching and scanning indexes can avoid phantom read.

The key point is that InnoDB adds next-key locks to a common query by default, or does it need to apply the lock itself? If I look at this sentence, I may think InnoDB also locks the normal query. If so, what is the difference between it and SERIALIZABLE?

There is another section in MySQL manual:

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

Toprevent phantoms, InnoDB usesan algorithm calledNext-key lockingThat combinesindex-row locking with gap locking.

Youcan 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 youare going to insert, then you can safely insert your row and know that thenext-key lock set on the successor of your row during the read prevents anyonemeanwhile inserting a duplicate for your. thus, the next-key lockingenables you to "lock" the nonexistence of something in your table.

In my understanding, InnoDB provides the next-key locks, but the application needs to lock itself. Manual provides an example:

SELECT * FROM child WHERE id> 100 for update;

In this way, InnoDB adds a lock to rows with IDs greater than 100 (if the id of a row in the child table is 102) and 100-102,102 + gap.

You can use showinnodb status to check whether the table is locked.

In another experiment, note that the id in the t_bitfly table is the primary key field.

Lab 4-3:

T SessionA Session B
|
| Start transaction;
|
| SELECT * FROM t_bitfly
| WHERE id & lt; = 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

We can see that the lock with id <= 1 only locks the range of id <= 1 and records with id 2 can be successfully added, when a record whose id is 0 is added, it will wait for the lock to be released.

MySQL manual:

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

Forlocking reads (SELECT with FORUPDATE or lock in share mode), UPDATE, and DELETE statements, lockingdepends on whether the statement uses a unique index with a unique searchcondition, or a range-type search condition. for a unique index with a uniquesearch condition, InnoDB locksonly the index record found, not the gap before it. for other searchconditions, InnoDB locksthe 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.

------

Consistent read and commit read. Read the experiment first,

Lab 4-4:

TSessionA Session B
|
| STARTTRANSACTION; 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 normal reads are used, consistent results will be obtained. If locked reads are used, the read results of the "latest" and "committed" will be read.

In itself, repeatable reads and committed reads are in conflict. In the same transaction, if repeatable reads are ensured, the commit of other transactions will not be visible, violating the committed read; if committed read is ensured, this will cause inconsistency between the two read results, which violates the Repeatable read.

In this case, InnoDB provides such a mechanism to query the latest data at the default isolation level of Repeatable read.

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

Ifyou want to see the "freshest" state of the database, you shoshould use either theREAD COMMITTED isolation level or a locking read:
SELECT * FROM t_bitfly lock in share mode;

------

Conclusion: The Repeatable read of MySQLInnoDB does not guarantee the avoidance of phantom read, which must be ensured by applying lock reading. The locking mechanism is next-keylocks.