Mysql 死锁问题（二）

show engine innodb status \G

*** (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：分析：

看看引起死锁的SQL执行计划：

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表全表扫描，会引起死锁。

 

2：解决

重写SQL，把IN改成JOIN，执行计划如下：

mysql> explain select a.* FROM User a 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)

两表都用到索引了。

 

问题得到解决。