The four transaction isolation levels of Mysql InnoDB and (the issues solved step by step) dirty reads, non-repeated reads, virtual reads, and mysqlinnodb

The four transaction isolation levels of Mysql InnoDB and (the issues solved step by step) dirty reads, non-repeated reads, virtual reads, and mysqlinnodb

There are four transaction isolation levels for MySqlInnoDB :(DefaultIs repeatable read)

Uncommitted read uncommit: The data has been modified in another transaction, but has not yet been committed. In this case, the SELECT statement may query the uncommitted data, and dirty reads occur.

Submit read commit:Two SELECT queries occur in a transaction. When some data is queried after the first SELECT statement is executed, the other transaction modifies the data and submits the data, when the second SELECT statement is executed, the queried data is different from that of the first SELECT statement, but non-repeated read and phantom read occur, however, dirty reading is solved (locking the current row read ).

Repeatable read:In the same transaction, the SELECT result is the result of the time point when the transaction is started. Therefore, the results of the same SELECT query are always consistent. Fixed the problem of non-repeated reads, but virtual reads may occur (locking all rows read ).

Serializable:

(Reprinted)

1). Dirty read

First, distinguish between dirty pages and dirty data

Dirty pages are modified pages in the memory buffer pool. They are not flushed to the hard disk in time, but are already written to the redo log. It is normal to read and modify the page of the buffer pool, which can improve efficiency and synchronize with flush. Dirty data indicates that the transaction has modified the row record in the buffer pool, but it has not been committed !!!, If uncommitted row data is read from the buffer pool, it is called dirty read, which violates the isolation of transactions. Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used.

2). Repeated read is not allowed.

A transaction reads the same data multiple times. When the transaction is not completed, another transaction also accesses the same data. Therefore, the second transaction has been committed due to the modification of the second transaction between the two reads in the first transaction. The data read by the first transaction may be different. In this way, the data read twice in a transaction is different, so it is called non-repeated read. For example, an editor reads the same document twice, but the author overwrites the document between the two reads. The document has been changed when the editor reads the document for the second time. The original reads cannot be repeated. This problem can be avoided if the editor can read the document only after the author has completed the compilation.

3). Virtual read:

It refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion. For example, an editor changes the document submitted by the author, but when the production department merges the changes into the primary copy of the document, the author has added unedited new materials to this document. This issue can be avoided if no one can add new materials to the document before the editors and production departments process the original document.

Transaction	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 '; Found 1000		Select money from account where name = 'a '; 1100 found		Select money from account where name = 'a '; 1100 found		Select * from account (the query result contains fff Information)	Commit ();
Description					Transaction B reads uncommitted data from transaction A, and dirty reads occur.		After transaction A is committed, the values of the SELECT query money before and after transaction B are inconsistent. That is, transaction B reads the data of transaction A and update, and the data cannot be read repeatedly.		Virtual read (that is, one transaction reads the insert data of another transaction)
Configurable transaction level					Read commit		Repeatable read		Serializable