InnoDB lock mechanism — checking a slow insert query

Time:2021-4-10

In the slow query log, an insert statement is issued, and the probability of slow query is relatively high. A simple insert takes 4-10s, which is very unusual. Insert statement is attached and some fields are omitted

INSERT INTO `fc_pay_out_trade_log` (`out_trade_no`,`dateline`) VALUES ('191120093724940457',1574185044) [ RunTime:4.176669s ]
fc_ pay_ out_ trade_ Log table except primary key, out_ trade_ The no field is a normal index and has no other indexes.
Mysql5.7, InnoDB engine, default RR level.

Business process:

1. Generate order flow ID (out)_ trade_ No), write to the order flow log table FC_ pay_ out_ trade_ Log is to send the blocking statement
2. Combine parameters, call wechat unified order interface, wechat payment operation, and then call back the interface
3. The callback interface is all put into one transaction. One operation is to update the order flow log table FC_ pay_ out_ trade_ Payment status of order flow ID corresponding to log

UPDATE `fc_pay_out_trade_log` SET `pay_status`=1,`pay_time`=1574221674 WHERE `out_trade_no` = '191120194747307594' [ RunTime:0.000716s ]

Order flow out_ trade_ No generation rule

$out_trade_no = date(‘YmdHis’).rand(1000,9999);

Those who know more about InnoDB locking mechanism should find the problem, because the update operation locks InnoDB’s gap to FC_ pay_ out_ trade_ Some regions of the log table are locked, causing the insertion operation to block. The gap lock will not be released until the transaction of the update operation is committed.

Analysis process

1. The previous payment operation occurs. After the payment is successful, call back the interface and execute the above UPDATE statement. The update condition is whereout_trade_no=”191120194747307594″, InnoDB uses the next key lock to lock the line and gap in order to avoid the phenomenon of unreal reading.
2. When the latter payment occurs, create an order flow and insert it into FC_ pay_ out_ trade_ Log table. It is found that the inserted area has been locked, blocking and waiting
3. The previous payment processes other things, submits the transaction, and releases FC_ pay_ out_ trade_ Next key lock of log table
4. The latter payment flow is inserted successfully

Which areas will the following statement lock? How long will the lock be released?

UPDATE `fc_pay_out_trade_log` SET `pay_status`=1,`pay_time`=1574221674 WHERE `out_trade_no` = '191120194747307594' [ RunTime:0.000716s ]

InnoDB lock type

S-shared lock: also called read lock, other transactions can continue to add shared lock, but cannot continue to add exclusive lock.
X-exclusive lock: also called write lock. Once the write lock is added, other transactions cannot be locked
Is intent shared lock: it indicates that a transaction wants to obtain the shared lock of some rows in a table.
IX intent exclusive lock: indicates that a transaction wants to obtain the shared lock of some rows in a table.

InnoDB lock algorithm

Record lock

The record lock locks the records. What we want to explain here is that it locks the index records, not our real data records.

If you lock a non – primary key index, you will lock it on your index and then lock it on the primary key
If there is no index (including no primary key) on the table, the hidden primary key index will be used for locking.
If the column to be locked has no index, the full table record will be locked.
For example: select * from user where id = 1 for update; add IX to the user table and X lock to the primary key index 1.

Clearance lock

Lock gap means to lock a certain range. Gap lock is also called gap lock. It will not block other gap locks, but it will block inserting gap lock. This is also the key to prevent unreal reading.

InnoDB lock mechanism -- checking a slow insert query

Let’s analyze FC_ pay_ out_ trade_ log,out_ trade_ The no field is a common index, and the new data is in order, so whereout_trade_no=The gap locked by “191120194747307594” will include [191120194747307594, ~), and the newly inserted value will be in this gap area, so blocking will occur.

Next key lock

This lock is essentially a record lock plus a gap lock. At the RR isolation level (InnoDB is the default), InnoDB uses this algorithm for row scan locking, but if there is a unique index in the query scan, it will degenerate to only using record locking.

Solution: modify FC_ pay_ out_ trade_ Out of log table_ trade_ The no field can be set as a unique index

This work adoptsCC agreementReprint must indicate the author and the link of this article

What tools have you used? Why use this tool (fast, support high concurrency…)? How is the underlying implementation?

Recommended Today

A detailed explanation of the differences between Perl and strawberry Perl and ActivePerl

Perl is the abbreviation of practical extraction and report language “practical report extraction language”. Application of activestateperl and strawberry PERL on Windows platformcompiler。 Perl   The relationship between the latter two is that C language and Linux system have their own GCC. The biggest difference between activestate Perl and strawberry Perl is that strawberry Perl […]