Readers responded to an interesting deadlock case, and I analyzed and summarized an article.

Need some foundation, here are five other debug source code analysis lock articles I wrote in nuggets, you can take a look:

Juejin. Cn/post / 684490…

Juejin. Cn/post / 684490…

Juejin. Cn/post / 684490…

Juejin. Cn/post / 684490…

Juejin. Cn/post / 684490…

Let’s get down to the real stuff:

Construction sentences:

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 

Copy the code

There is an initialization statement in the table:

INSERT INTO 'tenant_config' (' tenant_id ', 'open_card_point') VALUES (123,0);Copy the code

Database isolation level: RC

First case: two inserts, two updates

Transaction 1 and transaction 2 statements are exactly the same as the following:

INSERT INTO 'tenant_config' (' tenant_id ', 'open_card_point') VALUES (123,111111); UPDATE tenant_config SET open_card_point = 0where tenant_id = 123;
Copy the code

The logic of the code is roughly as follows: insert first and update if there are conflicts

try {
    insert();
} catch (DuplicateKeyException e) {
    update()
}
Copy the code

The process of deadlock conditions is as follows

Transaction 1:

INSERT INTO 'tenant_config' (' tenant_id ', 'open_card_point') VALUES (123,111111); ERROR 1062 (23000): Duplicate entry'123' for key 'uidx_tenant'
Copy the code

Lock situation, add S lock for UK, as follows:

Transaction 2:

INSERT INTO 'tenant_config' (' tenant_id ', 'open_card_point') VALUES (123,111111); ERROR 1062 (23000): Duplicate entry'123' for key 'uidx_tenant'
Copy the code

Lock situation, add S lock for UK, as follows:

Transaction 1:

UPDATE tenant_config SET open_card_point =  0 where tenant_id = 123;
Copy the code

Lock X on UK because transaction 2 has acquired S lock and enters lock wait

Transaction 2:

UPDATE tenant_config SET open_card_point =  0 where tenant_id = 123;
Copy the code

Deadlock condition: transaction 2 has S lock, wants to lock X, transaction 1 has S lock, also wants to lock X, each is waiting for the other S lock.

This is the simplest case, if it were that simple, I wouldn’t have written it, ha ha, let’s do the second case.

Second case: one INSERT, two updates

Step 1: Transaction 1, insert unique key conflicts

begin; INSERT INTO 'tenant_config' (' tenant_id ', 'open_card_point') VALUES (123,111111); ERROR 1062 (23000): Duplicate entry'123' for key 'uidx_tenant'
Copy the code

Step 2: Transaction 2

begin;
UPDATE tenant_config SET open_card_point =  0 where tenant_id = 123 and 1 =1;
Copy the code

Step 3: Transaction 1

UPDATE tenant_config SET open_card_point =  0 where tenant_id = 123 and 1 =1;
Copy the code

Occurrence: transaction 2 deadlock

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Copy the code

The analysis process is as follows:

Transaction 1

INSERT INTO 'tenant_config' (' tenant_id ', 'open_card_point') VALUES (123,111111);Copy the code

There is no ambiguity about the UK + S lock.

Next transaction 2

UPDATE tenant_config SET open_card_point =  0 where tenant_id = 123 and 1 =1;
Copy the code

This one locks ux and goes into the lock wait state, which is fine as well.

Next, transaction 1 performs an UPDATE, which is much more complicated and also attempts to acquire the X lock, but not as smoothly.

Enter the deadlock detection process, the lock_deadlock_occurs() function is the focus of the code, and most recently the lock_deadlock_recursive() function is called recursively.

  • Start represents the pointer to the transaction at the top level that calls the function, for example, transaction 1 currently executing is start
  • Wait_lock represents the lock to be acquired, in this case transaction 1’s X lock on the UK.
  • TRX The transaction pointer waiting for the lock

The essence of deadlock is: in the recursive process, if the lock transaction ID of the conflict is equal to the top transaction ID (lock_trx == start), then there is a ring, deadlock occurs.

Let transaction 1 be t1 and transaction 2 be T2

The first recursion

Wait_lock belongs to T1’s lock_X, which is the X lock that T1 update wants to acquire

At this point, all locks on the record are checked. The first lock is the S lock for the T1 transaction, and the second lock is the X lock for the T2 transaction wait state

Check the first lock, the S lock of the T1 transaction, since it belongs to the same transaction as wait_lock and there is no conflict, and continue to check the second lock.

Check the second lock, which is the X lock of T2 transaction in wait state, is mutually exclusive, and the X lock of T2 is in wait state. Start the second recursive call, check the X lock of T2 to see what lock it is waiting for.

The second recursion

At this point, the start passed in is unchanged, and wait_lock is changed to t2’s X lock, which is checked to see if it has any dependencies on the existing lock.

The X lock of T2 is waiting for the S lock of T1, lock_trx equals start, and a loop deadlock occurs.

Insert into T1 (S lock); insert into T2 (X lock); T1 tried to obtain the X lock again, but found that it conflicted with the X lock in t2 waiting state. It is found that the X lock in t2 waiting state conflicts with the S lock in T1, resulting in deadlock.

I’ve drawn a picture for you to understand:

Afterword.

Deadlock analysis is more complex, debugging source can be more clear clear ideas, the above is my debugging source code some conclusions, if there is an understanding of the wrong place, remember to help me out in time.