Shulou(Shulou.com)06/01 Report--

Editor, I'd like to share mysql insert into with you. What is the deadlock problem of select? I hope you will gain a lot after reading this article. Let's discuss it together.

Problems arise:

There are several event in the mysql in the system, which are executed every few minutes for statistics and other functions, and then the event will write data to a table. General content: the field from b required by replace into a from select; the general structure is that the field from b required by select is abbreviated here, but it is actually very complex, with the operation of join with many tables. Then the event is executed every minute, and it may not be finished in a minute when there is a large amount of data. Then we will have various other insert and update operations to operate on the b table. At this point, you will find that there are often errors reported by deadlock and wait lock timeout in the backend log. Finally, the test found that there was no such problem when event was turned off. It was basically confirmed that it was this event problem.

Problem analysis:

In fact, the most time-consuming thing is to find that it is the problem of event, and it does not take much time to query the data to solve the problem. 1. First of all, according to the error information in the back-end log, locate which table caused the deadlock and which table timed out waiting for the lock.

two。 Then according to these table names and the printed sql, we found out what the problem might be, and roughly confirmed that it was caused by the sql in event.

3. Then verify our idea, turn off event and find that there is no problem with lock in the log.

4. Check the statement in event and find that it is probably the field from b that replace into a from select needs.

Here, it is not clear which cases of mysql will be locked. In theory, the operation of select will only be a shared lock, and the operations such as inserting and updating b tables are upper exclusive locks. These two are compatible, one read and one write, and there is no conflict. But according to the phenomenon of waiting until the timeout, it is as if the field from b required by select locks the b table, so inserts and updates are waiting for the lock.

Finally, I found a little bit of information in Stack Overflow, the link address. It says here that it should be set to the read-committed level. Then it also leads to a mysql configuration parameter: innodb_locks_unsafe_for_binlog.

So we looked at this information from the official website and found a passage like this:

INSERT INTO T SELECT... FROM S WHERE... Sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally. Copy the code

It means that for INSERT INTO T SELECT. FROM S WHERE... In this case, first of all, which record lock is the guy on the T-table (row-level lock), and there is no gap lock. For Table S, there are two cases in which locks are not added:

1. If the transaction isolation level is READ COMMITTED

two。 Or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE

Otherwise, InnoDB sets the shared next-key on the line of S. If you are not clear about next-key, you can take a look at the introduction and link address on the official website.

Therefore, it is clear that we have to solve the problem of waiting for timeout, that is, to keep the S table unlocked, but not to be locked, we can use the two ways mentioned on the official website. Both are fine, but according to the innodb_locks_unsafe_for_binlog parameter, it is best to use mode 1, setting the isolation level of the transaction to read-committed.

The reasons are as follows:

1. Is innodb_locks_unsafe_for_binlog this parameter is static, you must add a line innodb_locks_unsafe_for_binlog = 1 in my.cnf, and then restart the database to take effect. Enter the command in mysql:

"show variables like" innodb_locks_unsafe_for_binlog% "copy code"

If it is found that it is ON, it will be enabled successfully.

two。 The isolation level of a transaction is fine-grained and can be set for a certain session. Different session can use different isolation levels, and this parameter can be dynamically modified directly on the mysql command line.

The parameter introduction to 3.mysql5.7 says that the innodb_locks_unsafe_for_binlog parameter will be deprecated in a later version of mysql. This is telling the truth. I went to check the detailed explanation of the parameters of mysql8.0 and found that this parameter is no longer available.

Therefore, it is recommended to use the transaction isolation level to control.

After reading this article, I believe you are interested in mysql insert into. Select deadlock problem is what has a certain understanding, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

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.