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

Background

Speaking of Mysql deadlocks, I wrote a basic introduction to Mysql locking before, and have a simple understanding of some basic Mysql locks or deadlocks. You can take a look at this article on why developers need to understand distributed locks. With the above experience, I thought I was good at deadlocks, but I didn't expect to report a deadlock on another sunny afternoon, but this time it was not as simple as I thought.

The problem is emerging.

One afternoon, the system suddenly called the police and threw an exception:

If you look closely, it looks like a transaction rollback exception, which is written because of deadlock rollback. It turns out that it is a deadlock problem, because I still have a certain degree of Mysql lock.

Understand, so began to take the initiative to troubleshoot this problem.

First look for Innodb Status in the database, and the last deadlock information will be recorded in Innodb Status. Enter the following command:

SHOW ENGINE INNODB STATUS

The deadlock information is as follows, and the 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 534n bits 960 index uidx_tenant of table `erp_crm_member_ plan`.`examples _ 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 534n bits 960 index uidx_tenant of table `erp_crm_member_ plan`.`examples _ config` trx id 2660206486 lock mode S

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1322 page no 534n bits 960 index uidx_tenant of table `erp_crm_member_ plan`.`examples _ 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 acquire the uidx_tenant index and then the X lock (row lock) on the where condition when executing the Update statement, and transaction 2 executes the same Update statement and also wants to acquire the X lock (row lock) on uidx_tenant, and then there is a deadlock and transaction 1 is rolled back. At that time, I was confused and recalled the necessary conditions for a deadlock:

Mutually exclusive.

Request and hold conditions.

Do not deprive the conditions.

Wait in a loop.

From the point of view of the log, transaction 1 and transaction 2 both take the row locks that compete for the same line, which is a little different from the previous cyclic lock competition, which can not meet the loop waiting condition. After being reminded by colleagues, since it is impossible to troubleshoot from the deadlock log, it can only be checked from the business code and the business log. 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)

}

}

This code means to save a configuration file, which will be updated if there is a unique index conflict. Of course, it may not be very standard here, but you can actually use the

Insert into...

On duplicate key update

The same effect can be achieved, but even with this, deadlocks can actually 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 there is a unique index conflict into the updated statement, and then the deadlock occurs. At this point, the answer is finally getting a little better.

At this time, the structure of our table is 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 for unique indexes, and our where conditions for inserts and updates are based on unique indexes.

UPDATE tenant_config SET

Open_card_point = 0

Where tenant_id = 123

At this point, it feels like it has something to do with locking the unique index, so let's move on to the next step.

Deep analysis

We said above that there are three transactions entering the update statement. To simplify this, we only need two transactions to enter the update statement at the same time. The following table shows our entire occurrence process:

Tip: s lock is a shared lock and X lock is a mutex. Generally speaking, X lock and Smaine X lock are mutually exclusive, S lock and S lock are not mutually exclusive.

We can see from the above process 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 a unique index? Under the RR isolation level, if you want to read it, it is the current read, so you actually need to add an S lock. It is found that the unique key already exists, and the execution of the update will be blocked by the S locks of the two transactions, thus forming the above loop wait condition.

Tip: in MVCC, the difference between the current read and the snapshot read: each read needs to be locked (can make a shared lock or mutex) to get the latest data, while the snapshot read reads the snapshot at the beginning of the transaction, which is realized through undo log.

This is the cause of the whole deadlock. Another situation in which this deadlock can occur is that there are three insert operations at the same time. If the transaction inserted first is finally rolled back, the other two transactions will also have this deadlock.

Solution

The core problem here is to kill the S lock, and here are three solutions for reference:

Lower the RR isolation level to the RC isolation level. Here the RC isolation level is read by snapshot, so that the S lock is not added.

When inserting again, use select * for update and add an X lock, so that the 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 method is not very realistic, after all, the isolation level cannot be easily changed. The third method is more troublesome. So the second method is what we finally decided on.

Summary

Having said so much, let's make a small summary at last. When troubleshooting deadlock problems, sometimes just looking at the deadlock log sometimes can not solve the problem, you need to combine the entire business log, code and table structure for analysis in order to get the correct results. Of course, there are some basics of database locking if you don't understand it, you can check out my other article on why developers need to know about distributed locks.

The last article was included in JGrowing-CaseStudy, a comprehensive, excellent Java learning route co-built by the community. If you want to participate in the maintenance of open source projects, you can build it together at the github address: https://github.com/javagrowing/JGrowing

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.