大家帮忙分析mysql的死锁问题［根据下面的innodb status信息］

Question

mysql> show innodb status;

---

LATEST DETECTED DEADLOCK

---

090220 10:22:45

*** (1) TRANSACTION:

TRANSACTION 0 16339728, ACTIVE 1 sec, process no 9502, OS thread id 1081506112 starting index read

mysql tables in use 1, locked 1

LOCK WAIT 24 lock struct(s), heap size 3024, 12 row lock(s), undo log entries 14

MySQL thread id 12261700, query id 169722445 192.168.10.11 nowbesixi_writer Updating

UPDATE cns_mail_log_2 SET cns_mail_log_2.mail_from_user_type=3 WHERE cns_mail_log_2.mail_id IN (32224) AND cns_mail_log_2.type = 1

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

RECORD LOCKS space id 1525 page no 4 n bits 448 index PRIMARY of table nowbesixi.cns_mail_log_2 trx id 0 16339728 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0

0: len 3; hex 000001; asc ;; 1: len 6; hex 000000608eb7; asc ` ;; 2: len 7; hex 80000000320137; asc 2 7;; 3: len 1; hex 00; asc ;; 4: len 4; hex 8000262b; asc &+;; 5: len 4; hex 8000f957; asc W;; 6: len 4; hex 80000002; asc ;; 7: len 1; hex 80; asc ;; 8: len 4; hex cb0b4d2b; asc M+;; 9: len 1; hex 80; asc ;;

*** (2) TRANSACTION:

TRANSACTION 0 16339725, ACTIVE 1 sec, process no 9502, OS thread id 1160436032 fetching rows, thread declared inside InnoDB 320

mysql tables in use 1, locked 1

1040 lock struct(s), heap size 145392, 384472 row lock(s), undo log entries 7

MySQL thread id 12262568, query id 169722418 192.168.10.12 nowbesixi_writer Updating

UPDATE cns_mail_log_2 SET cns_mail_log_2.mail_from_user_type=2 WHERE cns_mail_log_2.mail_id IN (32223) AND cns_mail_log_2.type = 1 AND cns_mail_log_2.mail_from_user_type = 1

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1525 page no 4 n bits 448 index PRIMARY of table nowbesixi.cns_mail_log_2 trx id 0 16339725 lock_mode X

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

0: len 8; hex 73757072656d756d; asc supremum;;

Answer 1

你看看你的事务里面
UPDATE cns_mail_log_2 SET cns_mail_log_2.mail_from_user_type=3 WHERE cns_mail_log_2.mail_id IN (32224) AND cns_mail_log_2.type = 1
这句话到事务提交这中间，是不是有比较大的查询，长时间无法运行完毕。
只要不提交就会暂时锁住部分记录，这时有并发出现的话，就会出现等待。