Shulou(Shulou.com)06/01 Report--

Editor to share with you what data isolation levels in MySQL data, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The four levels of the database are divided into: read uncommitted content, read committed content, reread and serializable. But the isolation level can also cause problems such as dirty reading, unrepeatable reading and phantom reading.

The four levels of database isolation are:

Read Uncommitted (read uncommitted)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

Problems caused by isolation levels

These four isolation levels are implemented with different lock types, which are prone to problems if the same data is read. For example:

Dirty reading (Drity Read):

One transaction has updated one piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

Non-repeatable (Non-repeatable read):

The data is inconsistent between the two queries of a transaction, which may be due to the insertion of the original data updated by a transaction between the two queries.

Phantom Read:

In the two queries of a transaction, the number of data pens is inconsistent, for example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time, the previous transaction will find that there are several columns of data that it did not have before in the next query.

In MySQL, these four isolation levels are implemented, each of which may cause problems as follows:

Isolation level dirty reading can not be repeated.

Fantasy read unsubmitted √√√ read submitted X √√ repeatable readable XX √ serializable XXX above is all the content of the article "what are the data isolation levels in MySQL data". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.