Shulou(Shulou.com)05/31 Report--

This article will explain in detail the sample analysis of MySQL transaction isolation level for everyone. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

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.

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.

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

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.

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

Dirty Read: A 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, the data read by the latter transaction will be incorrect.

Non-repeatable read: The data is inconsistent between two queries of a transaction, which may be the original data updated by a transaction inserted between the two queries.

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 the other transaction inserts new columns of data at this time. The previous transaction will find several columns of data that it did not have before in the next query.

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

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.