preface

In the project supported by the author recently, Mysql deadlock frequently occurred in the live network, which brought great risks to the stable operation of the system. The leader attached great importance to it and required to analyze and solve the problem from the root cause.

This paper will sort out the process of positioning and solving, on the one hand for summary and self-improvement, and on the other hand to provide some experience reference for colleagues dealing with similar problems in the future.

Problem Handling Procedure

Phenomenon of the problem

If the monitoring system detects a mysql deadlock in the live network system, it sends an alarm message to the responsible person through the alarm platform. The alarm message is sometimes more than a dozen times a day. The alarm message is as follows:

Analysis: This alarm contains some basic system information, including the system name, responsible person, mysql version, and server IP address. In addition, o&M needs to provide further information, including deadlock detection information.

Deadlock detection

Communicate with operation and maintenance colleagues to provide deadlock check results:

The complete DEADLOCK detection information is as follows:

------------------------LATEST DETECTED DEADLOCK------------------------190501 11:42:25*** (1) TRANSACTION:TRANSACTION E162CD59, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1MySQL thread id 8449185, OS thread handle 0x7f33af071700, query id 10168457281 10.95.59.103 nsfsmmusr updateINSERT INTO PIS_RANGE( SHOP_CODE,CMMDTY_CODE,MODE,SUPPLIER_CODE,DELETE_FLAG, PHYSICAL_SHOP_CODE, PRICE_FILE_NO,ONLINE_SALE_STATUS, OFFLINE_SALE_STATUS,UUID ) VALUES ('971K','11072232442','01','','', '', '20190501114223082','', '', '6de03e30cd9949bb9773ad8217c549ab')*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 465 page no 103854 n bits 320 index `idx_pis_shopcode_cmmdtycode` of table `nsfsmmprd02`.`pis_range` trx id E162CD59 lock_mode X locks gap before rec insert intention waiting*** (2) TRANSACTION:TRANSACTION E162CD5B, ACTIVE 0 sec inserting, thread declared inside InnoDB 500mysql tables in use 1, locked 13 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1MySQL thread id 8449234, OS thread handle 0x7f33ae526700, query id 10168457283 10.95.59.100 nsfsmmusr updateINSERT INTO PIS_RANGE( SHOP_CODE,CMMDTY_CODE,MODE,SUPPLIER_CODE,DELETE_FLAG, PHYSICAL_SHOP_CODE, PRICE_FILE_NO,ONLINE_SALE_STATUS, OFFLINE_SALE_STATUS,UUID ) VALUES ('971K','11072232449','01','','', '', '20190501114223087','', '', '3ff912d6e83c424285bfd3312a295f58')*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 465 page no 103854 n bits 312 index `idx_pis_shopcode_cmmdtycode` of table `nsfsmmprd02`.`pis_range` trx id E162CD5B lock mode S*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 465 page no 103854 n bits 320 index `idx_pis_shopcode_cmmdtycode` of table `nsfsmmprd02`.`pis_range` trx id E162CD5B lock_mode X locks gap before rec insert intention waiting*** WE ROLL BACK TRANSACTION (2)------------TRANSACTIONS------------Copy the code

According to the log, the deadlock is generated by the PIS_RANGE table. The deadlock occurs when the insert operation is performed. The next step in analyzing why this happens is to look at the conditions necessary for deadlock to occur.

Conditions under which a deadlock occurs

  • Multiple concurrent transactions (2 or more);

  • Each transaction holds a lock (or is already waiting for a lock);

  • Each transaction needs to hold the lock again;

  • A locked loop waits between transactions, resulting in a deadlock.

Now that you understand the conditions under which the lock occurs, you need to look at the relevant business scenarios to see why this problem occurs.

Business Scenario Analysis

Through communication with the r&d staff, we learned that there are two scenarios in the project that insert data into the PIS_RANGE table:

  1. MQ message queue listening

    Stripe processing. When certain conditions are met, the message will be directly entered into the database; otherwise, the message will be temporarily stored; when the task is to be determined, the task will be periodically processed. Related code business processing is as follows (some branch processes have been omitted and the trunk is retained) :

    @Overridepublic NullResponseDto handleBizz(PisCmmdtyRangeRequestDto bizzRequestObject) {List<PisRangeInfo> pisRangeInfos  = bizzRequestObject.getPisRangeInfos(); for (PisRangeInfo rangeInfo : pisRangeInfos) { PisRangeInfoVo pisRangeInfoVo = convertParams(rangeInfo); Try {... if (! StringUtils.equals("D", PisRangeInfoVo. GetDeleteFlag ())) {/ / save data (operating pis_range table) pisRangeService. AddPisRagngeByVersion (pisCmRangeEty); continue; } / / insert table to be processed, waiting for job execution pisRangeService. InsertPisRange (pisRangeInfoVo); } catch (Exception e1) {...... } } return null; }Copy the code

  2. Job Scheduled task

public void process(String dataSource,String shopCode, int count) { List<PisCmRangeEty> pisCmRangeEtyList = pisRangeService.queryInitData(dataSource,shopCode, count); For (PisCmRangeEty: PisCmRangeEty) {try {... If (...) {/ / save data (operating pis_range table) pisRangeService. AddPisRagngeByVersion (pisCmRangeEty); ... continue; }} Catch (Exception e){...... }}}Copy the code

Source through the above analysis, can be found that MQ and job pisRangeService final call is the same way. The addPisRagngeByVersion (pisCmRangeEty); Meet one of the necessary conditions for deadlock generation — there are multiple concurrent transactions, but do the concurrent transactions wait for each other to lock, and what is the lock? To answer this question, you need to understand the various locks in mysql.

MySql in the lock

The basic lock

InnoDB implements standard row-level locks, including shared locks (S) and exclusive locks (X).

  • Shared Locks (Sharaed Locks: S Locks)

    • Mysql allows transactions with the S lock to read a row

    • Add S lock record, allow other transactions to add S lock, not allow other transactions to add X lock

    • Syntax: select… lock in share mode;

  • Exclusive Locks (X)

    • Mysql allows transactions with an X lock to update or delete a row

    • X – locked records do not allow other transactions to have X – or S-locked records

    • Syntax: select… for update;

Intent locks

InnoDB introduces intentional locks to support multi-granularity (table and row locks). Intent locks are table locks, which are divided into IS locks and IX locks.

  • Intended Shared Lock (IS)

    Before a transaction can request an S lock, it needs to obtain the corresponding IS lock

  • Intentional Exclusive Lock (IX)

    Before a transaction can request an X lock, it needs to obtain the IX lock

Compatibility matrix for table locks

X IX S IS
X conflict conflict conflict conflict
IX conflict Compatible with conflict Compatible with
S conflict conflict Compatible with Compatible with
IS conflict Compatible with Compatible with Compatible with

Note: Horizontal is the lock that has been held, vertical is the lock that is being requested.

The following conclusions can be drawn from the analysis of compatibility matrix:

  • X IS incompatible with no one (X-x X-S X-IX X-IS)

  • S IS compatible with S and IS; S conflicts with X,IX

  • IX IS compatible with IX,IS (intent locks are compatible with each other), IX conflicts with X,S

  • IS IS compatible with IS,IX, and S, but conflicts with X

Auto-inc Locks

Auto-inc lock is a special table-level lock in a transaction, implemented by AUTO_INCREMENT columns.

Record Lock (RK)

A record lock, a row lock, locks only one row of the index record.

Innodb creates a hidden aggregate primary key index in the background, even if there is no index on the table. So when an SQL query does not run any indexes, an X lock will be placed after each clustered index. This is similar to a table lock, but the principle should be completely different from a table lock.

Gap Lock (GK)

Interval locking, which locks only one index interval (open interval). Locking in gaps between index records, or before or after an index record, does not include the index record itself. The purpose of the GAP lock is to prevent the illusion of two current reads of the same transaction.

Next Key Lock: NK

Next-Key-Lock, Next-Key-Lock, Next-Key-Lock, Next-Key-Lock By default, InnoDB uses next-key locks to lock records. However, when the index of the query has a unique attribute, next-key Lock optimizes and demotes it to a Record Lock, which locks only the index itself, not the range.

Insert intention lock (IK)

Gap Locks have an Insert Intention Lock that occurs during an Insert operation. When multiple transactions simultaneously write different data to the same index gap, there is no need to wait for other transactions to complete and no lock wait occurs. Suppose there is a record index with key values 4 and 7, and different transactions insert 5 and 6, respectively. Each transaction generates an insert intent lock between 4 and 7, acquiring an exclusive lock on the insert row, but not locking each other, because rows do not conflict.

Note: Insert intention lock is not intention lock, but a special gap lock.

If the gap already has a gap lock before the insert, then the insert will request the intent lock. Because of phantom avoidance, inserting an intent lock will be blocked when another transaction holds an interval lock for that gap (not directly used because gap locks are not mutually exclusive).

Compatibility matrix for row locks

Record Gap Next-key Insert Intention
Record conflict Compatible with conflict Compatible with
Gap Compatible with Compatible with Compatible with Compatible with
Next-Key conflict Compatible with conflict Compatible with
Insert Intention Compatible with conflict conflict Compatible with

Note: Horizontal is the lock that has been held, vertical is the lock that is being requested.

The following conclusions can be drawn from the analysis of compatibility matrix:

  • Record conflicts with Record and next-key.

  • GAP compatible with all (Record, GAP, next-key,Insert Intention)

  • Next-key conflicts with Record and next-key

  • There will be no conflicts between INSERT operations. The existing Insert lock does not prevent any locks from being added.

MySql > insert deadlock; mySql > insert deadlock; mySql > insert deadlock;

Mysql INSERT lock operations

The mysql database does the following when inserting:

Insert places an exclusive lock on the successfully inserted row. This lock is an index record lock, not a next-key lock (and certainly not a gap lock), and does not prevent other concurrent transactions from inserting records in front of this record.

Before inserting, mysql sets a gap lock type called Insert Intention gap Lock. This lock represents the intention of insertion that multiple transactions inserted into the same index gap do not need to wait for each other if they are not inserted at the same place within the gap. Suppose there is a record index with key values 4 and 7, and different transactions insert 5 and 6, respectively. Each transaction generates an insert intent lock between 4 and 7, acquiring an exclusive lock on the insert row, but not locking each other, because rows do not conflict.

If a unique key conflict error occurs, a read lock will be placed on duplicate index records (if multiple transactional INSERT inserts result in a duplicate-key, the index records will be set to a shared lock). In the multi-concurrency case, if a session already has an X-lock and other concurrent transactions insert the same row of records, an S-lock on that record will cause a deadlock.

Note: Official documentation

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Now that we understand the various locks in the Mysql InnoDB engine and understand the lock handling process involved in insert operations, we can go back to the problem itself and analyze the cause of the problem

Root cause analysis

First look at the pis_range table information

Table information

The deadlock log shows that the deadlock occurs because the idx_PIS_shopCODE_CMMDtycode index is hit.

Transaction action diagram

T1 T2
begin begin
INSERT INTO PIS_RANGE (SHOP_CODE, CMMDTY_CODE,…) VALUES (‘ 971 (k) ‘, ‘11072232440’…
INSERT INTO PIS_RANGE(SHOP_CODE,CMMDTY_CODE…) VALUES (‘ 971 (k) ‘, ‘11072232442’…
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Query OK, 1 row affected (5.45 sec)

In high concurrency scenarios, T1 and T2 are inserted simultaneously

  • Transaction T1 on index shop_code = ‘971 k with exclusive Next – key lock (LOCK_X | LOCK_ORDINARY), will lock the idx_pis_shopcode_cmmdtycode index range of A

  • Transaction T2 on index shop_code = ‘971 k with exclusive Next – key lock (LOCK_X | LOCK_ORDINARY), will lock the idx_pis_shopcode_cmmdtycode index range A, Since the requested GAP lock is compatible with the GAP lock already held, transaction T2 can also be successfully locked

  • Transaction T1 executes the Insert statement and puts an exclusive Insert Intention lock first. Because the requested Insert Intention lock is incompatible with the existing GAP lock, transaction T1 waits for T2 to release the GAP lock.

  • Transaction T2 executes the insert statement and waits for T1 to release the GAP lock. Thus, deadlocks occur.

Once you’ve identified the root cause of the problem, the next thing you need to do is look at how can you solve the problem? The best way to deal with deadlocks is to avoid them, so let’s look at some common ways to avoid deadlocks.

Deadlock avoidance

  • Similar business logic accesses tables and rows in a fixed order.

  • Great things are divided into small things. Large transactions tend to be deadlocked, and if business permits, large transactions are broken down.

  • In the same transaction, try to lock all the resources needed at one time to reduce the deadlock probability.

  • Lower the isolation level, or if the business allows, lower the isolation level

  • Add a reasonable index to the table. As you can see, a lock (or table lock) will be added to each row of the table if the index is not moved.

The solution

Based on the above analysis, combined with the business scenario, the final solution is to adopt the first way to avoid deadlock: “reduce concurrency, try to access tables and rows with fixed data” :

  • Delete the operation of directly inserting data into the PIS_RANGE table in the MQ Listener and insert a preprocessing table to perform scheduled task processing

  • Scheduled tasks are executed in a single thread without concurrency, reducing the probability of such problems to more than 90% of the current frequency

  • To prevent the job did not finish within the time specified, the new task, again lead to cluster at the same time there are multiple tasks at the same time, the way the model is in UTS unified dispatching system (task) configured in the job does not support concurrent processing, at the same time, adopted the depots table for database processing, in accordance with the code to make the depots, avoids the multi-task concurrent execution scene.

  • Through the above treatment, the problem of frequent deadlocks can be solved effectively

  • The code has been modified and is awaiting release for production validation

conclusion

Mysql insert cause deadlock problem, there are a lot of materials on the Internet, the author in the analysis of this problem, refer to a lot of related materials, also refer to the mysql official website of the relevant chapters, to solve the deadlock problem, mainly need to prevent, behind the content of mysql is very extensive, such as: Various locks, indexes, InnoDB storage engine, transaction isolation levels, dirty reads, unrepeatable reads, unreal reads, current and snapshot reads, MVCC, etc., all need to be studied. Fixing code and solving problems can be quick, but it takes a lot of work to really understand the principles behind it.

Database issues are not just for dbAs, we need to learn more, come on.

Reference documentation

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htmlhttps://blog.csdn.net/qq_16681169/article/details/73359670

https://blog.csdn.net/varyall/article/details/80219459

http://hedengcheng.com/?p=844

http://hedengcheng.com/?p=771

http://www.cnblogs.com/jiangxu67/p/4242346.html

https://zhuanlan.zhihu.com/p/64435240