Remember an online mysql deadlock

problem : Yesterday, an error occurred online and a function could not be executed. Searching the log found that it was a deadlock problem of mysql.

Analysis of the problem : In fact, the biggest difficulty in solving the problem is to analyze the problem and find where the problem occurs. This process takes the most time and thinking, and the use of code to solve the problem is very fast.

(1) The error log is as follows:

2021-10-09 18:59:04] local.INFO: RobotAuction-SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: select id, cash, freeze, area_id from users where users.id in (2634, 2662, 2672, 2673, 2675, 2685, 2808, 2811, 2818, 2834, 2869, 2886, 2926, 2961, 2962, 2981, 3066, 3080, 3124, 3131, 3135) and users.deleted_at is null for update)-669

View the deadlock log of msyql from the error log, and analyze it in combination with possible operations on the business

(2) Deadlock log:

2021-10-09 18:59:04 0x150d81d79700
* (1) TRANSACTION:
TRANSACTION 385220, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1136, 19 row lock(s)
MySQL thread id 1376113, OS thread handle 23147762767616, query id 23440050 localhost 127.0.0.1 lpt Sending data

select `id`, `cash`, `freeze`, `area_id` from `users` where `users`.`id` in (2634, 2662, 2672, 2673, 2675, 2685, 2808, 2811, 2818, 2834, 2869, 2886, 2926, 2961, 2962, 2981, 3066, 3080, 3124, 3131, 3135) and `users`.`deleted_at` is null
for update

(1) WAITING FOR THIS LOCK TO BE GRANTED: [1] Hold the lock with id = 2675 and wait for the transaction lock with id = 3124
RECORD LOCKS space id 191 page no 14 n bits 120 index PRIMARY of table lpt.users trx id 385220 lock_mode X locks rec but not gap waiting
Record lock, heap no 49 PHYSICAL RECORD: n_fields 52; compact format; info bits 0
** omitted part

* (2) TRANSACTION:
TRANSACTION 385219, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 6
MySQL thread id 1376112, OS thread handle 23147757147904, query id 23440106 localhost 127.0.0.1 lpt statistics

select `id`, `cash` from `users` where `users`.`id` = 2675 and `users`.`deleted_at` is null limit 1 for update

(2) HOLDS THE LOCK(S): [2] Holds the lock with id=3124
RECORD LOCKS space id 191 page no 14 n bits 120 index PRIMARY of table lpt.users trx id 385219 lock_mode X locks rec but not gap
Record lock, heap no 49 PHYSICAL RECORD: n_fields 52; compact format; info bits 0
** omitted part

(2) WAITING FOR THIS LOCK TO BE GRANTED: [3] Waiting for the data with id = 2675 to release the lock
RECORD LOCKS space id 191 page no 6 n bits 120 index PRIMARY of table lpt.users trx id 385219 lock_mode X locks rec but not gap waiting
Record lock, heap no 32 PHYSICAL RECORD: n_fields 52; compact format; info bits 0
** omitted part

* WE ROLL BACK TRANSACTION (1) [4] Select a small overhead for rollback, select transaction (1), and compare the previous tasks that were not executed and rolled back

problem is :
1. The locks used are all exclusive locks
2. This typical deadlock caused by the different locking sequence
3. Analysis process [1] [2] [3] [4]

analysis method
1. Solve by analyzing the business and adjusting the order of the business ✔
2. It is solved by table lock, but performance will be sacrificed and index cannot be used ×