A magical MySQL deadlock troubleshooting record

background

Speaking of MySQL deadlock, I have written a basic introduction to MySQL locking before. For some basic MySQL locks or deadlocks, you can read this article on why developers need to understand database locks. With the above experience, I thought I could easily deal with deadlocks. Unexpectedly, another deadlock was reported on a sunny afternoon, but this time it was not as simple as I imagined.

Problems first appeared

One afternoon, the system suddenly alarmed and threw an exception:

A closer look seemed to be an abnormal transaction rollback. It said that it was rolled back due to a deadlock. It turned out to be a deadlock problem. Since I have some understanding of MySQL locks, I began to actively investigate this problem.

First, search for Innodb Status in the database. Innodb Status will record the information of the last deadlock. 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 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) 
------------

Let me briefly analyze and explain this deadlock log. When transaction 1 executes the Update statement, it needs to obtain the X lock (row lock) on the uidx_tenant index in the where condition. Transaction 2 executes the same Update statement and also wants to obtain the X lock (row lock) on uidx_tenant. Then a deadlock occurs and transaction 1 is rolled back. I was confused at the time and recalled the necessary conditions for deadlock:

1. Mutually exclusive.

2. Request and maintain conditions.

3. No deprivation of conditions.

4. Wait in a loop.

From the log, we can see that both transaction 1 and transaction 2 are competing for the row lock of the same row, which is a little different from the previous circular lock competition. No matter how you look at it, the circular wait condition cannot be met. After being reminded by a colleague, since the deadlock log cannot be used for troubleshooting, the only option is to use the business code and business log for troubleshooting. 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 saving a configuration file. If a unique index conflict occurs, it will be updated. Of course, it may not be written in a standard way. In fact, it can be used

insert into ... 
on duplicate key update

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

You can see that there are three logs that occurred simultaneously, indicating that a unique index conflict occurred and the update statement entered, and then a deadlock occurred. At this point the answer finally seems to be starting to become clear.

At this time, let's 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 are all based on the unique index.

UPDATE tenant_config SET 
 open_card_point = 0 
 where tenant_id = 123

At this point, it seems that locking the unique index during insertion is related. Next, we will proceed to the next step of in-depth analysis.

In-depth analysis

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

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

We can see from the above process that the key to this deadlock is to obtain the S lock. Why do we need to obtain the S lock when we insert it again? Because we need to detect unique index? If you want to read at the RR isolation level, then it is a current read, so you actually need to add an S lock. Here it is found that the unique key already exists. At this time, the update execution will be blocked by the S locks of the two transactions, thus forming the above circular waiting condition.

Tip: In MVCC, the difference between current read and snapshot read is: current read needs to lock each time (shared lock or mutex lock can be used) to obtain the latest data, while snapshot read reads the snapshot at the beginning of the transaction, which is achieved through undo log.

This is the cause of the entire deadlock. Another situation in which this deadlock may occur is when three insert operations are performed at the same time. If the transaction that is inserted first is finally rolled back, the other two transactions will also experience this deadlock.

Solution

The core problem here is that the S lock needs to be removed. Here are three solutions for reference:

• Reduce the RR isolation level to the RC isolation level. Here, the RC isolation level will use snapshot read, so no S lock will be added.
• When inserting again, use select * for update and add an X lock, so that no S lock is added.
• You can add distributed locks in advance, using Redis, ZK, etc. For distributed locks, please refer to this article of mine. Let's talk about distributed locks

The first method is not very realistic, after all, the isolation level cannot be easily modified. The third method is more troublesome. So the second method is the one we finally settled on.

Summarize

Having said so much, let me make a brief summary. When troubleshooting deadlock problems, sometimes just looking at the deadlock log may not solve the problem. It is necessary to analyze the entire business log, code, and table structure to get the correct result. Of course, there is some basic knowledge about database locks above. If you don’t understand it, you can check out my other article Why Developers Need to Know About Database Locks.

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

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.