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

This article will explain in detail how to understand the deadlock after MySQL upgrading WRITE_SET. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Background

MySQL used WRITE_SET when it launched MGR. To borrow this idea, MySQL introduced a parallel replication scheme based on WRITE_SET [1] in version 5.7.22. In the original master-slave replication technology, the same batch of things can enter the prepare phase of things, indicating that those things do not conflict, so they can be executed concurrently. We all know that innodb is a row lock-based database, so being able to play back data concurrently at the row-level granularity will greatly improve performance. There are many performance advantages of this scheme, one of which can be easily seen is that we do not have to rely on the submission of the main thing when playing back, which is called less is more. With less dependency, parallelism can also be played back and forth according to logical lines macroscopically, so the performance must be greatly improved [2]. Therefore, this parallel playback feature is enabled on some instances on our database side.

The phenomenon that leads to our deadlock is that we find that instances with write_set parallel playback are much more likely to deadlock from the library than before, and the instances that have deadlocks are all performing xtrabackup backups.

Scene

We know that MySQL things will design a lot of locks, such as MDL locks, innodb row locks, intent locks, latch locks, and so on. There are also many differences in the behavior of different isolation level locks. From the point of view of deadlock theory: deadlock is the existence of rings in the digraph, which causes each other to wait. To solve the deadlock, simply break any side to break the loop waiting. Of course, the actual implementation may be optimized due to the different weights of each ring node, and choose the one with the lowest cost. But the focus before must have been to find this "ring". And some of these locks can be seen during operation and maintenance, and some can not be seen. For example, latch locks are generally invisible to users. For performance reasons, the details of our MDL and INNODB locks are not collected. If it is turned on, you can query the information about MDL locks through the table performance_schema.metadata_lock, and view the lock information of innodb in detail through show engine innodb status.

Through a simple analysis, we lock a MDL deadlock. Therefore, in such a scenario, we can only view the current status through show full processlist, as shown below:

Case1: figure 1

Case2: figure 2-1

Figure 2-2

=

To make it easier for you to understand, I drew a diagram [figure 3] to explain the deadlock of the two case: figure 3

Case1 deadlock analysis:

You can see that in the work thread group, there is a transaction handled by work that first reaches the commit state of the transaction, but the transaction needs to be judged by order_commit before it is submitted, because we have set up slave_preserve_commit_order to ensure that the transaction is committed in the order in which it is submitted on the main library. So at this time, we have to wait for the previous things to be submitted before we can proceed. So you can see that the status of this thread is: "Waiting for preceding transaction to commit". When the "front" thing is ready to be submitted, you have to get the mdl::commit_lock lock and find that you can't get it. Form the "ring waiting" as above.

From the analysis, we can see that FTWRL (flush table with read lock) is executed at the same time, and this operation acquires a shared lock of MDL. But there is also no version to get mdl::commit_lock and wait. This wait causes new update requests to be blocked because the updated statement is an exclusive type of lock. Due to the space, we will not elaborate on the details of MDL lock compatibility. Only the conclusion is given here, which will block some updated statements, which in turn will affect the business.

= = figure 4

Case2 deadlock analysis:

By the way: it can also be seen that new requests are blocked in this case. Note that this is the core idea of backup. Block new requests and block the submission of the same batch. Ensure that there is no new data insertion during backup

At the beginning, a more "backward" thing got the mdl::commit_lock, and when it was ready to submit, it was found that the system was configured with slave_preserve_commit_order, and there were things in front of the thing that had not been submitted, so it was necessary to wait for the previous thing to be executed before it could continue. Then FTWRL first acquires the mdl::global_read_lock lock, but there is no way to acquire the mdl::commit_lock lock.

At this time, if the "front thing" is an update operation, then it is mutually exclusive with the mdl::global_read_lock lock, thus forming the deadlock above.

Verification

Because of such a deadlock, it is probabilistic. In order to reproduce the problem efficiently, we intend to use mysql's testing framework to verify it. The first step is: through the above analysis, modify the kernel source code to increase the probability of deadlock. It is proved that our conjecture can indeed produce deadlocks. But the deadlock that appears is not necessarily the deadlock of the environment online. Therefore, we need to verify the modified source code under the actual scenario. Of course, we can't verify it in the production environment. We can modify the source code in the first step, and then use the backed-up data to simulate. If we use the backup data + our modified source database example is reproduced, we can objectively judge our deadlock research. Of course, readers may say that we modify the source code to destroy the previous environment, of course, there is a premise here. This premise is: modify only one thread in the parallel playback thread group, do not change the original logic, just let it support slower to increase the probability of deadlock, testify to our deadlock research.

First of all, our first step is to generate two things on the main library (of course, we can also use brute force and loops, but it may not work well or may not even be able to reproduce). Using MySQL's test framework, you can see the following code:

57 # = = 58 # create two links on master: master and master159-- source include/rpl_connection_master.inc60 send SET DEBUG_SYNC='waiting_in_the_middle_of_flush_stage SIGNAL w WAIT_FOR bounty 6162-- source include/rpl_connection_master1.inc63 send SET DEBUG_SYNC=' now WAIT_FOR wayside 6465-- source include/rpl_connection_master.inc66-- reap67 show master status;68 send insert into test.t1 values (1) 6970-source include/rpl_connection_master1.inc71-reap72 SET DEBUG_SYNC= 'bgc_after_enrolling_for_flush_stage SIGNAL breadth 73 insert into test.t1 values (1000)

How to verify that these two things on our main library belong to the same batch? Binlog, of course. The results are as follows:

Show master status File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Setmaster-bin.000001 849#200107 9:26:14 server id 1 end_log_pos 219 CRC32 0x059fa77a Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no#200107 9:26:24 server id 1 end_log_pos 408 CRC32 0xa1a6ea99 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes#200107 9:26:24 server id 1 end_log_pos 661 CRC32 0x2b0fc8a5 Anonymous_GTID last_committed=1 sequence_number=3 rbr_only=yes

You can see that we have generated two sets of binlog in the last_commit field. One is 0. Here is the create table statement. The other is 1, which is our two insert statements above.

The next step is to modify the source code of MySQL, mainly considering the parallel replication logic of MTS. Because we let the big things execute first through DEBUG_SYNC on the main library, for example, the big things are assigned to the first one in the woker thread group. So we give the first worker sleep enough time to execute FTWRL in the critical path of binlog playback: Xid_apply_log_event::do_apply_event_worker.

Directly modify the source code compilation needs to compile back and forth, we use systemstap this tool, JIT at run time to inject a piece of code to change the behavior of some worker. Execute the script to verify whether the injection can be performed before performing the injection:

41-exec sudo stap-L'process ("$MYSQLD"). Function ("pop_jobs_item")'42-- exec sudo stap-L 'process ("$MYSQLD"). Function ("* Xid_apply_log_event::do_apply_event_worker")'

It should be noted that because of the architecture principle of stap, for more information, please refer to the link [3] below, which requires root permission. Here is the injected code:

Stap-v-g-d $MYSQLD-- ldd-e'probe process ($server_pid) .function ("Xid_apply_log_event:: Xid_apply_log_event") {printf ("hit in do_apply_log_event\ n") if ($w-> id = = 0) {mdelay (30000)} 'stap-v-g-d $MYSQLD-- ldd-e'probe process ($server_pid) .function ("pop_jobs_item") {printf ("hit inpop_") Jobs_item ") if ($worker- > id = = 0) {mdelay (3000)}}'

Let the first thread of the copy thread group sleep 3s. This gives you enough time to run FTWRL. Final implementation result:

Show full processlist Id User Host db Command Time State Info3 root localhost:10868 test Sleep 83 NULL4 root localhost:10870 test Sleep 84 NULL7 root localhost:10922 test Query 61 Waiting for commit lock flush table with read lock8 root localhost:10926 test Query 0 starting show full processlist9 system user NULL Connect 82 Waiting for master to send event NULL10 system user NULL Connect 61 Slave has read all relay log Waiting for more updates NULL11 system user NULL Connect 71 Waiting for global read lock NULL12 system user NULL Connect 71 Waiting for preceding transaction to commit NULL13 system user NULL Connect 82 Waiting for an event from Coordinator NULL14 system user NULL Connect 81 Waiting for an event from Coordinator NULL

As you can see, our conjecture completely recreates the deadlock. Give a general explanation:

When we construct this deadlock, because the worker we control will sleep 3s. So we can query the status of worker, and when worker is in the state of Waiting for preceding transaction to commit, FTWRL is executed immediately. Then you can see that FTWRL will block in commit_lock. Then another update, of course, is to wait: global read lock, which forms a deadlock.

First of all, for students who do not quite understand the principle of backup, you should be able to see the role of FTWRL clearly from these two deadlock waiting diagrams. It controls the consistency of backups through two GLOBAL READ LOCK and COMMIT_ lock locks. It will not be discussed in detail here. To solve the deadlock problem, through the deadlock theory, we must break the ring in the digraph.

Through the analysis of our deadlock case, we can know that there are only two sides that can be operated:

1. Slave_preserve_commit_order

2. FTWRL obviously: for those who can accept the submission of things on the slave library can be "out of order", we only need to turn off this configuration option to release the deadlock

If it is required to be ordered, then we can only turn off the backup thread (the nodes in the figure, and related edges) can also break the deadlock. When a deadlock occurs, I feel that shutting down the backup thread code is smaller. Copying from the library will cause an error if you turn off the worker thread.

On how to understand the generation of deadlocks after MySQL upgrade WRITE_SET is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.