Mysql InnoDB Four transaction isolation levels and (problem solved separately) dirty read, non-repeatable read, virtual read

There are four transaction isolation levels for Mysqlinnodb: ( default is repeatable read REPEATABLE read)

UNCOMMITTED Read Uncommit : The data was modified in another transaction but not yet committed, and in this transaction the SELECT statement may query for these uncommitted data and a dirty read occurs.

commit reads Read commit: Two select queries occur in a transaction, and when the first select executes the query to some data, another transaction modifies the data and commits it. When the second select executes, the data queried differs from the first select, and a non-repeatable read, Phantom read problem occurs, but dirty reads are resolved (lock the current row being read).

REPEATABLE Read REPEATABLE READ: in the same transaction, the result of select is the result of a point in time when the transaction is opened, so the results of the same select query are always consistent. A non-repeatable read is resolved, but a virtual read is possible (all rows that are read are locked).

Serialization serializable:

(reproduced)

1). Dirty Read

First distinguish between dirty pages and dirty data

The Dirty page is a modified page in the buffer pool of memory and is not flush to the hard disk in time, but is already written to the redo log. Read and modify the buffer pool page is normal, can improve efficiency, flush can be synchronized. Dirty data refers to a transaction that modifies the record of a row in the buffer pool, but has not yet submitted!!! , if reading uncommitted row data in the buffer pool at this time is called dirty read, it violates the isolation of the transaction. Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data.

2). Non-repeatable reading

is to read the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the second transaction has been committed due to the modification of the second transaction. Then the data that the first transaction reads two times may be different. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read. For example, an editor reads the same document two times, but between two reads, the author rewrites the document. When the editor reads the document for the second time, the document has changed. The original read is not repeatable. You can avoid this problem if the editor can read the document only after the author has finished writing it all

3). Virtual read:

A phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred. For example, an editor changes the document submitted by the author, but when the production department merges its changes into the primary copy of the document, it finds that the author has added the unedited new material to the document. This problem can be avoided if no one is able to add new material to the document until the editor and production department have finished processing the original document.

Transaction A	Connection.setautocommit (False)	Start Transaction		Update student Set money=money+100 From account Where Name= ' a '		Commit ();		Insert into account (Name,money) VALUES (' FFF ', 10000);
(Transaction B)	Connection.setautocommit (False)	Start Transaction	Select money from account where name= ' a '; We found 1000 bucks.		Select money from account where name= ' a '; found that there were 1100		Select money from account where name= ' a '; found that there were 1100		SELECT * From account (query result contains FFF information)	Commit ();
Description					Transaction B reads the uncommitted data of transaction A, and a dirty read occurs		After transaction a commits, the value of select query Money before and after transaction B is inconsistent, that is, transaction B reads the data after the transaction a,update, and a non-repeatable read occurs		Virtual read (that is, one transaction reads the data from another transaction insert)
The level of transaction that can be set					Read Commit		REPEATABLE READ		Serialization serializable

Mysql InnoDB Four transaction isolation levels and (problem solved separately) dirty read, non-repeatable read, virtual read