Shulou(Shulou.com)05/31 Report--

How to solve the problem of Mysql deadlock, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Show engine innodb status\ G Click (here) to collapse or open

* * (1) TRANSACTION:

TRANSACTION 9CC3, ACTIVE 0 sec, OS thread id 2384 fetching rows

Mysql tables in use 2, locked 2

LOCK WAIT 6 lock struct (s), heap size 1024, 12 row lock (s), undo log entries 2

MySQL thread id 20, query id 138891 localhost 127.0.0.1 root Sending data

DELETE FROM User WHERE ID IN (SELECT UserID FROM BusinessUser WHERE BusinessID=124001692)

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 92015 n bits 88 index `PRIMARY` of table `df_ demo`.`user`trx id 9CC3 lock_mode X waiting

Record lock, heap no 14 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

0: len 4; hex 80000788; asc

....

* * (2) TRANSACTION:

TRANSACTION 9CC0, ACTIVE 0 sec, OS thread id 1696 starting index read, thread declared inside InnoDB 370

Mysql tables in use 3, locked 3

9 lock struct (s), heap size 1024, 59 row lock (s), undo log entries 10

MySQL thread id 21, query id 138887 localhost 127.0.0.1 root Sending data

DELETE FROM UserOptions WHERE UserID IN (SELECT u.ID FROM User u JOIN BusinessUser bu ON u.ID=bu.UserID WHERE bu.BusinessID=124001691)

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 0 page no 92015 n bits 88 index `PRIMARY` of table `df_ demo`.`user`trx id 9CC0 lock mode S locks rec but not gap

Record lock, heap no 14 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

0: len 4; hex 80000788; asc

....

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 92015 n bits 88 index `PRIMARY` of table `df_ demo`.`user`trx id 9CC0 lock mode S locks rec but not gap waiting

Record lock, heap no 11 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

0: len 4; hex 80000786; asc

....

* WE ROLL BACK TRANSACTION (1)

1: analysis: look at the SQL execution plan that caused the deadlock: click (here) to collapse or open

Mysql > explain select * FROM User WHERE ID IN (SELECT UserID FROM BusinessUser WHERE BusinessID=124001692)

+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-

| | 1 | PRIMARY | User | ALL | NULL | NULL | NULL | NULL | 6095 | Using where |

| | 2 | DEPENDENT SUBQUERY | BusinessUser | index_subquery | idx_UserID_Type,idx_BusinessID | idx_UserID_Type | 5 | func | 1 | Using where |

+-

2 rows in set (0.00 sec)

User table full table scan, will cause deadlock. 2: solve the problem of rewriting SQL and change IN to JOIN. The execution plan is as follows: click (here) to collapse or open

Mysql > explain select a.* FROM User an inner join BusinessUser b on a.id=b.userid WHERE b.BusinessID=124001692

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | b | ref | idx_UserID_Type,idx_BusinessID | idx_BusinessID | 5 | const | 1 | Using where |

| | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | ng1.b.UserID | 1 |

+-+-

2 rows in set (0.00 sec)

Indexes are used for both tables.

The problem has been solved.

The answer to the question on how to solve the Mysql deadlock problem is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.