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

This article mainly introduces the relevant knowledge of "Mysql Deadlock Troubleshooting Example Analysis". Xiaobian shows you the operation process through actual cases. The operation method is simple, fast and practical. I hope this article "Mysql Deadlock Troubleshooting Example Analysis" can help you solve the problem.

The problem begins.

One afternoon, the system suddenly gave an alarm and threw an exception:

A closer look seems to be a transaction rollback exception, written because of deadlock rollback, it turned out to be a deadlock problem, because I still have a certain understanding of Mysql lock, so I began to take the initiative to investigate this problem.

First, look up Innodb Status in the database. The information of the last deadlock will be recorded in Innodb Status. Enter the following command:

SHOW ENGINE INNODB STATUS

Deadlock information is as follows, sql information is simply processed:

------------------------

LATEST DETECTED DEADLOCK

------------------------

2019-02-22 15:10:56 0x7eec2f468700

*** （1） TRANSACTION:

TRANSACTION 2660206487, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct（s）， heap size 1136, 1 row lock（s）

MySQL thread id 31261312, OS thread handle 139554322093824, query id 11624975750 10.23.134.92 erp_crm__6f73 updating

/*id:3637ba36*/UPDATE tenant_config SET

open_card_point = 0

where tenant_id = 123

*** （1） WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table --erp_crm_member_plan--。-- tenant_config-- trx id 2660206487 lock_mode X locks rec but not gap waiting

*** （2） TRANSACTION:

TRANSACTION 2660206486, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

3 lock struct（s）， heap size 1136, 2 row lock（s）

MySQL thread id 31261311, OS thread handle 139552870532864, query id 11624975758 10.23.134.92 erp_crm__6f73 updating

/*id:3637ba36*/UPDATE tenant_config SET

open_card_point = 0

where tenant_id = 123

*** （2） HOLDS THE LOCK（S）：

RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table --erp_crm_member_plan--。-- tenant_config-- trx id 2660206486 lock mode S

*** （2） WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table --erp_crm_member_plan--。-- tenant_config-- trx id 2660206486 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION （1）

------------

To give you a simple analysis and explanation of this deadlock log, transaction 1 needs to obtain the uidx_tenant index and then the X lock (row lock) on the where condition when executing the Update statement, transaction 2 executes the same Update statement, also wants to obtain the X lock (row lock) on the uidx_tenant, and then a deadlock occurs, rolling back transaction 1. At that time, I was very confused and recalled the necessary conditions for deadlock:

mutual exclusion.

Request and hold conditions.

No deprivation conditions.

Cycle waiting. From the log, transaction 1 and transaction 2 both take row locks that compete for the same row, which is a bit different from the previous round robin lock competition. No matter how you look at it, you can't meet the round robin waiting condition. After colleagues remind, since the deadlock log can not be investigated, then only from the business code and business log from the investigation. The logic of this code is as follows:

public int saveTenantConfig（PoiContext poiContext, TenantConfigDO tenantConfig） {

try {

return tenantConfigMapper.saveTenantConfig（poiContext.getTenantId（）， poiContext.getPoiId（）， tenantConfig）;

} catch （DuplicateKeyException e） {

LOGGER.warn ("[saveTenantConfig] primary key conflict, update the record. context:{}, config:{}", poiContext, tenantConfig）;

return tenantConfigMapper.updateTenantConfig（poiContext.getTenantId（）， tenantConfig）;

}

}

The meaning of this code is to save a configuration file, if there is a unique index conflict then it will be updated, of course, here may not be written very standard, in fact, you can use

insert into …

on duplicate key update

The same effect can be achieved, but even with this, deadlocks can occur. After reading the code, my colleague sent me the business log at that time.

You can see that there are three simultaneous logs, indicating that a unique index conflict occurred into the updated statement, and then a deadlock occurred. At this point, the answer finally began to look a little bit clearer.

At this point, look at our table structure as follows (simplified):

CREATE TABLE --tenant_config-- （

--id-- bigint（21） NOT NULL AUTO_INCREMENT,

--tenant_id-- int（11） NOT NULL,

--open_card_point-- int（11） DEFAULT NULL,

PRIMARY KEY （--id--），

UNIQUE KEY --uidx_tenant-- （--tenant_id--）

） ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

Our tenant_id is used as a unique index, and our insert and update where conditions operate on unique indexes.

UPDATE tenant_config SET

open_card_point = 0

where tenant_id = 123

At this point, I feel that the unique index lock has something to do with the insertion, so let's go to the next step.

in-depth analysis

Above we said that there are three transactions into the update statement, to simplify the explanation here we only need two transactions to enter the update statement at the same time, the following table shows our entire occurrence process:

Tip: S locks are shared locks, X locks are mutex locks. X lock and S lock are mutually exclusive, S lock and S lock are not mutually exclusive.

We see from the above flow that the key to this deadlock is to acquire the S lock. Why do we need to acquire the S lock when we insert it again? Because we need to detect unique indexes? In RR isolation level, if you want to read, then it is the current read, then in fact, you need to add S lock. The unique key is found here, and at this time the update will be blocked by the S-locks of the two transactions, thus forming the loop waiting condition above.

Note: In MVCC, the difference between current read and snapshot read is that current read needs to be locked each time (can make shared lock or mutex lock) to get the latest data, while snapshot read is the snapshot read at the beginning of this transaction, which is implemented through undo log.

This is the reason for the whole deadlock. There is another case where this deadlock can occur, that is, three insert operations at the same time. If the transaction inserted first is rolled back at last, the other two transactions will also have this deadlock.

solutions

The core problem here is that you need to kill the S lock. Here are three solutions for reference:

Reduce RR isolation level to RC isolation level. Here the RC isolation level is read using snapshots, so no S-locks are added.

When inserting again, use select * for update, add X lock, so that S lock will not be added.

You can add distributed locks in advance, you can use Redis, or ZK, etc., distributed locks can refer to my article. Talk about distributed locks.

The first approach is unrealistic, since isolation levels cannot be easily modified. The third method is more troublesome. So the second option is what we finally decided on.

About "Mysql deadlock troubleshooting instance analysis" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the industry information channel. Xiaobian will update different knowledge points for you every day.

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.