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.