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

This article mainly explains "how to understand the transaction isolation level of the database". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand the transaction isolation level of the database.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read (current read). Snapshot reading, consistent reading at a certain time, without locking. The current read is the latest version of the record, and the record returned by the current read will be locked to ensure that other transactions will not modify the record concurrently.

MySQL under the RR isolation level, if snapshot reads and current reads occur successively in the same session, phantom readings may occur. Because snapshot reads are unlocked and new inserts are allowed, the current read needs to read the latest version of the block, so phantom reading may occur between the snapshot read and the current read.

MySQL brings a great price to realize the isolation level of RR. Next-Key Locking is introduced to solve the problem of phantom reading in the current reading mode. Next-Key Locking can cause a large number of DML failures. Oracle has no RR isolation level. At read only level, Oracle has no phantom reading problem (snapshot read mode). At read committed level, there are phantom reading problems under snapshot read, current read and mixed [snapshot read and current read].

RR isolation level, MySQL relies on MVCC to achieve repeatable reading (read view), while relies on MVCC to realize the phantom reading problem under snapshot reading, and relies on Next-Key Locking to realize the phantom reading problem under current reading. therefore, MySQL InnoDB repeatable reading does not guarantee to avoid phantom reading, it needs to be guaranteed by explicit use of locked reading, and the mechanism used in this locked reading is next-key locks.

Dirty reading, unrepeatable reading and phantom reading are a kind of defect, and the later, the higher the cost of solving the defect.

The higher the isolation level, the more "defects" can be solved, why not directly use the highest transaction isolation level, then there will be no defects? Because the higher the isolation level, the lower the concurrency.

Dirty reading, solving the problem that writing does not block reading, improving concurrency and sacrificing reading consistency. At present, the vast majority of mainstream databases solve the problem of write-unblocking reading through MVCC, not through classical locks.

Why is there an unrepeatable read?

In the classical read commited mode, there is no lock on the read data (the shared lock is added at the moment of the read, and the shared lock is released after the read is completed), then the data may have been modified by other transactions when it is accessed again.

How can I be readable again?

The way used by the older generation of database artists is to lock the read data, which ensures that the data read every time is the same, because after locking the read data, the data can not be modified. This is also a problem to be solved at the isolation level of repeatable read, but the classic implementation of repeatable readability can lead to phantom readings. Today, most mainstream databases are readable through MVCC, not by locking.

Classical repeatable reading provides a consistent reading mode (statement level and transaction level). Although there is illusion, it is not a big defect from the point of view of consistency. If you use the classical lock to achieve repeatable reading, the probability of deadlock is great, but it brings a (good) side effect and solves the problem of losing updates.

According to the classical definition of isolation level, read uncommited,read commited does not provide consistent reading. Because the current mainstream databases are implemented based on MVCC, not based on the classical locking method, so they all achieve statement-level consistency at the read commited level. Under the classic isolation level, repeatable read achieves transaction-level consistency on the basis of statement-level consistency.

For the isolation level of oracle, the read commited level can provide statement-level consistency, this isolation level can not avoid the problem of transaction-level phantom reading, and requires read only or the highest SERIALIZABLE level.

In a database that uses shared read locks (rather than multiple versions), you can avoid the problem of losing updates if REPEATABLE READ is enabled. The reason is that data that has been read will be added to it with a lock (shared read lock, non-exclusive lock), which ensures that the data cannot be modified by any other transaction.

Under the REPEATABLE READ (RR) isolation level, read view is created when the first read request is initiated. Under the READ COMMITTED (RC) isolation level, a read view is recreated each time a read request is made. According to the above mentioned, under the RC isolation level, every time a SELECT is initiated, the read view is created, that is, each time the SELECT can read the data already commit at the beginning of this query, so the phenomenon of non-repeatable reading and phantom reading occurs.

Read view determines whether the current version data item is visible or not

In innodb, when you create a new transaction, innodb creates a read view of the list of active transactions in the current system (trx_sys- > trx_list), which holds an id list of other transactions that the system should not currently see by this transaction. When the user wants to read the record of the row in this transaction, innodb compares the current version number of the row with the read view.

The specific algorithm is as follows:

Let the current transaction id of the row be trx_id_0,read view. The earliest transaction id is trx_id_1, and the latest transaction id is trx_id_2.

If trx_id_0

Trx_id_2 indicates that the transaction in which the row record is located does not open until after the new transaction is created, so the current value of the row record is not visible. Skip to step 5.

If trx_id_1

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.