Database Transaction ISOLATION LEVEL

Database Transaction ISOLATION LEVEL

There are 4 isolation levels for database transactions, from low to high, READ UNCOMMITTED,Read Committed,Repeatable read, andSerializable, which can be resolved individually by each of the four levels Problems such as dirty reading, non-repetition reading, and Phantom reading.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

about READ UNCOMMITTEDAt the beginning of this afternoon, do not appear dirty data, back again, the success, and then spent half an hour to try to not show dirty data before the cause, and has been dirty data, so I do not know where I was before the problem, the following post successful operation diagram; Fig. 1-1 Fig. 1-2 Figure 1-3 Figure 1-1 is the command I operated on the first DOS system, figure 1-2 is the command that operates under the second DOS system, and then the first DOS line The command for operation Figure 1-3 found that the uncommitted transaction was read.about Read CommittedCan avoid dirty reading, but can not avoid the situation of non-repeatable reading and phantom reading Figure 2-1 Figure 2-2 Figure 2-3 Figure 2-1 Open a transaction under DOS1, and query the current RE Ader table data, and then figure 2-2dos2 Modify the data, and finally in DOS1 transaction re-query the reader table data, found that the id=6 data has changed, this is the same transaction, two reads the data in a different situation, that is, non-repeatable read.about REPEATABLE READ (repeatable Read)MySQL default transaction isolation level, you can avoid dirty read, non-repeatable read problems, because the MySQL storage engine through the multi-version concurrency control mechanism to solve the phantom read problem, so this level can avoid phantom read, the following is today's test: Figure 3-1 Fig. 3-2 Fig. 3-3 Figure 3-1 Open a transaction under DOS1, then query the reader table data, then insert a piece of data under DOS2, then query the reader table data in DOS1, find that the data is not Change, stating that there is no phantom reading.Dirty Read, non-repeatable read, Phantom Read concept:

1. Dirty read: Dirty read refers to when a transaction is accessing the data, and the data is modified, and this modification has not been committed to the database, at this time, another

The transaction also accesses this data and then uses that data.

2. Non-repeatable reads: Refers to the same data that is read multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. So

Between two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This will send

The data that was read two times within a transaction is not the same, so it is called non-repeatable read. For example, an editor reads the same document two times, but at two times

Between 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. If only the author has completed all

The issue can be avoided if the editor can read the document after it has been written.

3. Phantom reads: A phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table, which involves a change in the table

All data rows. 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 operation will occur in the first

The user of the transaction 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

When its changes are merged into the primary copy of the document, it is discovered that the author has added unedited new material to the document. If the editor and the production department complete the original

No one can add new material to a document until the document is processed, you may avoid the problem.

Excerpt from: https://www.cnblogs.com/wuxiang/p/5477807.htmlabout non-repeatable reading, magic reading, understanding from the bar:The key to non-repeatable reading is to modify:
The same condition that you read the data, read it again and find that the value is different
The focus of Phantom reading is to add or delete
The same conditions, the 1th and 2nd readings are different. Of course, judging from the overall result, it seems that the results of the two reads are inconsistent.
But if you look at it from the point of view of control, the difference between the two is larger
For the former, only the record that satisfies the condition needs to be locked
For the latter, it is necessary to lock the line to avoid the non-repeatable reading of the record that satisfies the condition and its close records.
Avoid phantom reading need to lock table stick link Address: https://bbs.csdn.net/topics/120024254 reference link for this article: http://blog.csdn.net/jiesa/article/details/51317164

Database Transaction ISOLATION LEVEL