Quote:

Recently encountered a database deadlock problem, here is a record of the resolution process.

Problems arise:

Mysql has several events that are executed every few minutes for statistics and other functions, and then this event will write data to a table. Replace into a from select select from b; The general structure is as follows: select requires a field from B, which is shorthand, and is actually very complicated. There are many join operations on the table. However, the event is executed every minute, which may be more than one minute in case of large data volume. And then we’re going to have all sorts of other insert and update operations to do with table B. In this case, the backend logs often contain deadlock and wait lock timeout errors. Finally, when the event is turned off, no such problem is found, and the event is basically the fault.

Problem analysis:

In fact, the most time-consuming thing is to find the problem is the event. It doesn’t take much time to search the data and solve the problem. 1. Locate the table that causes a deadlock and the table that waits for a lock timeout based on the error information in the back-end log. 2. Then, according to these table names and the printed SQL, we found out where the problem might be, and roughly confirmed that the SQL in the event caused 3. To verify our idea again, after turning off the event, we found that there was no lock problem in the log. 4. Replace into a from SELECT; replace into a from select

SQL > select * from mysql; SQL > select * from MYSQL; SQL > select * from MYSQL; SQL > select * from MYSQL; Select * from b; select * from B; select * from B; select * from B;

Finally, I found some meaningful information in Stack Overflow, the link address. I’m just going to set it to read-committed. Then a mysql configuration parameter is also introduced: innodb_locks_unsafe_for_binlog.

So we followed this information from the official website to check, 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.Copy the code

INSERT INTO T SELECT… FROM S WHERE … In this case, first of all, T table will be which record lock (row level lock), and there is no gap lock. For table S, locks are not locked in two cases: 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 rows of S. If you’re not sure about next-Key, you can check out this introduction on the official website.

So the way to solve the timeout is to make the table S not locked. However, there are two ways to avoid locking. Either option is acceptable, but innodb_locks_unsafe_for_binlog is recommended to use mode 1 and set the transaction isolation level to read-committed.

The reasons are as follows: 1. Yes Innodb_lockS_unSAFE_for_binlog this parameter is static, you must add a line innodb_lockS_unSAFE_for_binlog = 1 to my.cnf, and then restart the database to take effect. Select * from mysql;

show variables like "%innodb_locks_unsafe_for_binlog%"
Copy the code

If it’s ON, it’s ON. 2. The transaction isolation level is fine-grained and can be set for a particular session. Different sessions can use different isolation levels. Innodb_locks_unsafe_for_binlog will be deprecated in later mysql releases. This is true. I checked the mysql8.0 parameter description and found that this parameter is no longer available.

It is recommended to use transaction isolation levels for control.

References:

Innodb lock: innodb_locks_unsafe_for_binlog: innodb_locks_unsafe_for_binlog