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

The small series in this issue will bring you an introduction to the isolation level of mysql, analyze and describe it from a professional perspective. After reading this article, I hope you can gain something.

Mysql's four isolation levels

Transactions specify an isolation level that defines the degree to which a transaction must be isolated from resource or data changes made by other transactions. Isolation levels are described in terms of allowable concurrent side effects (e.g., dirty or phantom reads).

The SQL standard defines four levels of isolation, including specific rules that define which changes inside and outside a transaction are visible and which are invisible. Lower isolation levels generally support higher concurrency and have lower overhead.

1. 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 a dirty read.

2. Read Submitted

This is the default isolation level for most database systems (but not MySQL).

It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable reads, because other instances of the same transaction may have new commits in between, so the same select may return different results.

Repeatable Read (Repeatable)

This is MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently.

In theory, though, this leads to another tricky problem: Phantom Read. In simple terms, phantom reading means that when a user reads a range of data rows, another transaction inserts new rows into the range, and when the user reads the range of data rows again, he will find new "phantom" rows.

InnoDB and Falcon storage engines solve this problem through the Multiversion Concurrency Control (MVCC) mechanism.

Serializable (serializable)

This is the highest level of isolation and solves the phantom read problem by forcing transactions to be ordered so that they are unlikely to conflict with each other. In short, it places a shared lock on each row of data read. At this level, a lot of timeouts and lock contention can result.

problem

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

1. Drity Read:

One transaction has updated a piece of data, another transaction reads the same piece of data at the same time, and for some reason, the previous RollBack operation fails, so the data read by the latter transaction will be incorrect.

Non-repeatable read:

Data inconsistency between two queries of a transaction may be due to the insertion of original data updated by a transaction between the two queries.

3. Phantom Read:

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

Concurrent side effects allowed by different isolation levels

isolation level

dirty reads

unrepeatable reading

dummy read

read uncommitted

is

is

is

Submitted for reading

no

is

is

repeatable read

no

no

is

snapshot

no

no

no

a serializable

no

no

no

The above is the introduction of the isolation level of mysql shared by Xiaobian. If you have similar doubts, it does not prevent you from understanding with reference to the above analysis. If you want to know more about it, please pay attention to industry information.

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.