Understand the lock problem of mysql insert into...select

mysql tutorialThe column introduces the lock problem of mysql insert into...select.

Quote:

I recently encountered a database deadlock problem. Here is a record of the solution process.

The problem arises:

There are several events in mysql in the system, which are executed every few minutes for functions such as statistical data, and then this event will be added to a table Write data inside. General content: replace into a from select required fields from b; The general structure is as follows. The field required by select from b is the abbreviation here. It is actually very complicated and involves many table join operations. Then this event is executed every minute, when the amount of data is large It may not be completed in one minute. Then we will have various other insert and update operations to operate on table b. At this time, you will find that there are often deadlock and wait lock timeout errors in the backend logs. The final test found that turning off the event eliminated the problem, and it was basically confirmed that the problem was with this event.

Problem Analysis:

In fact, the most time-consuming thing is to find out that it is an event problem. It does not take too much time to query the data and solve the problem. 1. First, based on the error information in the back-end log, locate which table caused the deadlock and which table waited for the lock timeout
2. Then based on these table names and the printed SQL, find out where it may be. The problem was roughly confirmed to be caused by the sql in the event
3. Verify our idea again. After turning off the event, we found that there is no lock problem in the log
4. Check the statements in the event and find that it is probably replace into a from select required fields from b;

The main reason here is that it is not clear in what situations mysql will be locked. In theory, the select operation will only take a shared lock, for the insertion and update of table b, etc. The operation is to lock the exclusive lock. These two are compatible, one reads and one writes, and there is no conflict. But judging from the timeout phenomenon, it seems that the fields required by select from b also lock the b table. So both inserts and updates are waiting for the lock.

Finally I found some interesting information and link address in Stack Overflow. It is said here that it needs to be set to the read-committed level. Then a mysql configuration parameter is also introduced: innodb_locks_unsafe_for_binlog.

So we followed this information to check it on the official website and found this paragraph:

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.复制代码

It means that for INSERT INTO T SELECT ... FROM S WHERE ... this situation First of all, it will be known which record lock (row-level lock) is on the T table, and it does not have gap lock. For table S, there are two situations where no locking will occur:
1. If the transaction isolation level is READ COMMITTED
2. 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 row of S. If you are not sure about next-key, you can read this introduction and link address on the official website.

Therefore, the way we want to solve the waiting timeout is relatively clear, which is to prevent the S table from being locked. However, to avoid being locked, we can use the two methods mentioned on the official website. Both of these are possible, but according to the introduction of the innodb_locks_unsafe_for_binlog parameter, it is best to use method 1 and set the transaction isolation level to read-committed.

The reasons are as follows:
1. The parameter innodb_locks_unsafe_for_binlog is static. You must add a line innodb_locks_unsafe_for_binlog = 1 to my.cnf and then restart the database to take effect. Enter the command in mysql:

show variables like "%innodb_locks_unsafe_for_binlog%"复制代码

If it is found to be ON, it is successfully turned on.
2. The isolation level of a transaction is relatively fine-grained and can be set for a certain session. Different sessions can use different isolation levels, and this parameter is dynamic and can be modified directly on the mysql command line.
3. The parameter introduction of mysql5.7 says that the parameter innodb_locks_unsafe_for_binlog will be abandoned in subsequent mysql versions. This is true. I checked the detailed parameter explanation of mysql8.0 and found that this parameter no longer exists.

So it is recommended to use transaction isolation level for control.

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of Understand the lock problem of mysql insert into...select. For more information, please follow other related articles on the PHP Chinese website!