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

This article mainly explains "what is the concept of MVCC". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is the concept of MVCC"?

MVCC action

MVCC makes most of the transaction engines that support row locks no longer simply use row locks to control the concurrency of the database, but combine the row locks of the database with the row version number, which can be read without locking with little overhead. So as to improve the concurrency performance of the database.

MVCC solves the read-write conflict problem in the form of no lock. The reading here refers to snapshot reading. That is, the snapshot reading realized by MVCC!

What is MVCC?

Multi-version concurrency control (MVCC) is a lock-free concurrency control that solves read-write conflicts.

Each row of records has two hidden columns: create the version number and roll back the pointer. There is a transaction id after the transaction is opened. Multiple concurrent transactions operate on a row at the same time, and different transactions produce multiple versions of the update operation of the row, and then roll back the pointer to form a undo log chain. The snapshot read of MVCC is realized by transaction id and creating version number.

The relationship between MVCC and isolation level

MVCC is designed to solve the read-write problem. And through different configurations, we can also solve the problem that snapshot reading can not be repeated after the transaction is opened.

Unrepeatable: some data read in the same transaction has changed, or some records have been deleted.

Phantom reading: a transaction re-reads previously retrieved data according to the same query condition, but finds that other transactions insert new data that meets the query condition. This phenomenon is called phantom reading.

Both RC and RR implement MVCC, but why does RR solve the problem of unrepeatable reading of RC?

You can think of it this way that RC has an unrepeatable read problem only because the developer intentionally set it (set multiple isolation levels, which users can set according to the situation). Originally, the data was submitted to the database, but there is no problem for RC to read it out. And the isolation level of the Oracle database itself is RC.

READ-COMMITTED (read submitted)

Read committed RC, under this isolation level, consistent reads can be achieved at the SQL level, and each SQL statement produces a new ReadView. This means that there are other transactions committed between the two queries, and inconsistent data can be read.

REPEATABLE-READ (repeatable)

A repeatable RR, which is maintained until the end of the transaction after the ReadView is created for the first time, that is, the visibility does not change during transaction execution, thus achieving repeatable readability within the transaction.

MVCC and clearance Lock

MVCC unlocking solves the problem of read-write conflicts. And the problem of unrepeatable reading is solved. Thus, two isolation levels, RC and RR, are realized.

The gap lock is still essentially a lock, blocking the execution of two concurrent transactions.

So why did RR enter the gap lock, just to solve the problem of phantom reading?

Note: gap locks exist only at the RR isolation level.

Gap locks can solve the problem of phantom reading to some extent, but I think the introduction of gap locks is more to deal with the bug of binlog's statement mode.

Master-slave replication of mysql databases relies on binlog. Before mysql5.0, binlog schemas were only in statement format. The characteristic of this mode: the record order of binlog is in the order of database transaction commit.

When there is no gap lock, there are the following scenarios:

The master library has two transactions:

1. Transaction a comes first delete id

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.