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

This article focuses on "MySQL multi-version concurrency control mechanism source code analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MySQL multi-version concurrency control mechanism source code analysis" it!

MVCC (multi-version concurrency control mechanism)

Isolation can also be called concurrency control, serializability, and so on. Lock is the first thing that comes to mind when talking about concurrency control. MySQL realizes the serializability of updates by using two-phase locks. At the same time, in order to speed up the query performance, it uses the mechanism of MVCC (Multi Version Concurrency Control), so that consistent versions can be obtained without locks.

Repeatable Read

MySQL realizes Repeatable Read through MVCC and (Next-Key Lock). Its idea (MVCC) is to record the version changes of data, so that it can present consistent results to users through ingenious selection of different versions of data. As shown in the following figure:

In the figure above, the initial version of (Apati50 | Back50) is 1.

1. Transaction T1 saw version 1 in select A, that is, Aban 50.

two。 The modification of transaction T2 to An and B upgrades the version to 2, that is, Acuro and Bever100.

3. When transaction T1 is again in this select B, the version that you see is still 1, that is, transaction 50.

This isolates the impact of the version, and AbeliB is always 100.

Read Commit

If you read the result of the most recent submission without going through the version control mechanism, the isolation level is read commit, as shown in the following figure:

In this case, you need to use a locking mechanism (such as select for update) to lock this Amaine B record to get the correct and consistent results, as shown in the following figure:

Advantages of MVCC

When we want to do some read-only operations on some data to check consistency, such as checking whether the accounts are aligned, we do not want to add locks that are costly to performance. At this point, the consistent version of MVCC has a great advantage.

MVCC (implementation mechanism)

This section starts with the implementation mechanism of MVCC, noting that MVCC is only valid for pure select (excluding locking operations such as select for update,lock in share mode, updateinsert, etc.).

Select run stack

First of all, let's track the running process of a common query sql in the mysql source code. The sql is (select * from test).

Its running stack is:

Because the default isolation level of mysql is repeatable_read (RR), read_record is overloaded as rr_sequential (currently we don't care about the process that select scans out row through index and then filters through condition). Continue to track:

Let's take a look inside the function:

Bool lock_clust_rec_cons_read_sees (const rec_t* rec / * a line scanned by innodb * /,) {. / / get its modified version of trx_id (transaction id) trx_id = row_get_rec_trx_id (rec, index, offsets) from the currently scanned row. / / determine the row snapshot return (read_view_sees_trx_id (view, trx_id)) seen by parameters (consistent snapshot view and transaction id);}

The process of creating read_view

Let's first look at the process of creating a consistent view, and let's take a look at the read_view structure:

Then through debug, it is found that the creation of the read_view structure is also operated in the above rr_sequential, and continue to trace the call stack:

Let's take a look at a branch of row_search_for_mysql:

Row_search_for_mysql: / / Conformance views else if (prebuilt- > select_lock_type = = LOCK_NONE) {/ / create Conformance views trx_assign_read_view (trx); prebuilt- > sql_stat_start = FALSE;} are created only when select unlocked mode

The above comment is the reason why select for update (in share model) will not go to MVCC. Let's further analyze the trx_assign_read_view function:

Well, we have finally reached the main stage of creating a read_view, which is shown in the following figure:

The code process is:

Row version visibility:

As you can see from the lock_clust_rec_cons_read_sees above, the row version visibility is judged by the read_view_sees_trx_id function:

In fact, the above function is a dichotomy. Read_view actually stores all the transaction id of the current active transaction. If the transaction id modified by the current line version is not in the current active transaction, it returns true, indicating that the current version is visible, otherwise it is invisible, as shown in the following figure.

Return from the above lock_clust_rec_cons_read_sees:

The process of undolog searching for visible versions

Let's now take a look at the row_sel_build_prev_vers_for_mysql function:

Row_sel_build_prev_vers_for_mysql |-row_vers_build_for_consistent_read

The row_ver_build_for_consistent_read method is called to return the visible version:

The whole process is shown in the following figure:

As for undolog how to restore the corresponding version of the row record is a complex process, due to space reasons, skip here.

Discussion on the timing of the creation of read_view

In the code that creates the consistent view in the row_search_for_mysql

Trx_assign_read_view consists of such a piece of code

Therefore, combining these two pieces of code, that is, in a transaction, a consistency view is created only when select is run * times (without locking), as shown in the following figure:

The author has constructed this kind of scene and simulated it, and it is true.

Some phenomena caused by the simultaneous action of MVCC and lock

MySQL uses MVCC and two-phase locking (2PL) to balance performance and consistency, but because MySQL creates a consistent view only when select, and does not do so during locking operations such as update, something weird happens. As shown in the following figure:

If you understand that update does not take the consistent view (read_view), while select takes the consistent view (read_view), you can well explain this phenomenon. As shown in the following figure:

At this point, I believe that everyone on the "MySQL multi-version concurrency control mechanism source code analysis" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.