Summary

A deadlock problem was sorted out a few days ago. At first, it was puzzled because the two transactions where the deadlock occurred were single-statement transactions with the same type of statement (where attribute columns are the same, but the values are different), and the statements were indexed by the same index, but ultimately a deadlock did occur. Through location checking, we find that the source of the problem is index_merge, and the cause of deadlock is very common. The two transactions lock in different order and wait for each other. Because this case is quite special, so I share it with you here.

Deadlock message

To get the deadlock problem, we first need to look at several basic information, including deadlock waiting relationship, table structure definition and so on.

1. Definition of table structure

Create Table: CREATE TABLE `t_xxx_customer` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `partner_id` bigint(20) unsigned DEFAULT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `deleted` tinyint(4) DEFAULT NULL,
  `partner_user_id` bigint(20) unsigned DEFAULT NULL,
  `xxx_id` varchar(128) DEFAULT NULL,
  `xxx_name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `partner_id` (`partner_id`),
  KEY `customer_id` (`customer_id`),
  KEY `partner_user_id` (`partner_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=140249 DEFAULT CHARSET=utf8;　　

2. Deadlock Information Extraction and Analysis

The last deadlock in the innodb engine can be retrieved by show engine innodb status; the command has the following information

*** (1) TRANSACTION: UPDATE t_xxx_customer SET xxx_id='101', xxx_name='bbb' where customer_id=235646 and partner_id=1688 and deleted=0;

*** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 1640 page no 3947 n bits 432 index partner_id of table xxx.t_xxx_customer trx id 2625291980 lock_mode X locks rec but not gap Record lock, heap no 334 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 0000000000000698; asc ;; 1: len 8; hex 0000000000021747; asc G;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1640 page no 3395 n bits 160 index  PRIMARY of table t_xxx_customer trx id 2625291980 lock_mode X locks rec but not gap waiting Record lock, heap no 89 PHYSICAL RECORD: n_fields 25; compact format; info bits 0

*** (2) TRANSACTION: UPDATE t_xxx_customer SET xxx_id='102', xxx_name='aaa' where customer_id=151069 and partner_id=1688 and deleted=0;

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1640 page no 3395 n bits 160 index PRIMARY of table xxx.t_xxx_customer trx id 2625291981 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1640 page no 3947 n bits 432 index partner_id of table xxx.t_xxx_customer trx id 2625291981 lock_mode X locks rec but not gap waiting Record lock, heap no 334 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 0000000000000698; asc ;; 1: len 8; hex 0000000000021747; asc G;;

*** WE ROLL BACK TRANSACTION (2)

From the result of deadlock, it is easy to see that transaction 1 holds the lock on partner_id secondary index and waits for the lock on PK index, while transaction 2 holds the lock on partner_id secondary index, and the two transactions hold the lock resources needed by each other, so they can not move forward, resulting in deadlock. Looking at the deadlock information alone, we may be confused that there is only one statement per transaction. Why do the same statements have different locking order for secondary index and primary key?

Reasons for deadlock

First, let's look at the execution plan of the statement.

The type of the statement is index_merge, and the information of Extra is Using intersect(customerid,partnerid). Thus we know that the execution plan of the statement has gone through index_merge optimization. A single statement extracts the record set through two indexes (customerid,partnerid) and takes the intersection to obtain the final result set. The index_merge algorithm does not expand here. The basic usage scenario is that the statement contains multiple query conditions, each condition has an index separately, and the index filtering degree of a single condition is not high. When combined, the index_merge optimization may be adopted, so that a single SQL statement can use two indexes to filter at the same time. Is it related to index_merge?

In the case of index_merge, will the order of secondary index and primary key index be inconsistent? Combined with the above deadlock information, we know that the two secondary index keys of deadlock are 0x698, and the primary key index keys are 0x21747. Let's see which record's primary key and secondary index are deadlocked.

You can see that 0x21747 corresponds to customer_id of 15169 and partner_id of 1688. Does it feel familiar? Yes, the statement query condition of the second transaction is the combination of these two conditions? This shows that for this record, only the partner ID index (1688) satisfies the first transaction statement; for the second transaction, both the customer_id and partner_id indexes satisfy the condition. Because each statement needs two secondary indexes to execute, assuming that the customer_id index is used to scan first, and then the partner_id index is used to scan, then the partner_id=1688 of transaction 1 satisfies the condition for the record with ID 0x21747, and then the partner_id lock is added to the corresponding PK index; for transaction 2, the customer_id= 151069 is locked, and the corresponding PK index is locked. The partner_id = 1688 index is then locked. Then the order of locking the partner_id secondary index and the PK primary key index in the two transactions is opposite, which leads to deadlock. For records with ID 0x21747:

In step 2 and 3 of the table, the lock order of the two transactions is opposite, resulting in deadlocks.

How to avoid deadlock

I've talked a lot about the history of a deadlock case, but it's just a case of deadlock. In the production environment, we certainly do not need deadlocks to occur frequently, after all, some transactions need to be rolled back to unlock. Here are some simple principles to help reduce the occurrence of deadlocks.

1) Lock in sequence as far as possible to avoid deadlocks from the source
2) Choose the appropriate isolation level. The higher the isolation level, the more intense the concurrent conflict. Read-Committed is enough for the actual scenario.
3) Avoid using big transactions. According to the two-stage lock principle, only when the transaction ends (commit or rollback) will the lock be released. The more locks held, the greater the conflict that may result.
4) Adding appropriate indexes to tables to avoid losing the index and locking each record