Hi everyone, I got deadlock information below by SHOW INNODB STATUS\G when I tested DBMAIL.
------------------------ LATEST DETECTED DEADLOCK ------------------------ 061128 9:50:11 *** (1) TRANSACTION: TRANSACTION 0 4067778, ACTIVE 0 sec, process no 17263, OS thread id 2968161200 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 31, query id 90778 172.20.23.28 dbmail Searching rows for update UPDATE dbmail_messages SET status=2 WHERE mailbox_idnr = 3585 AND deleted_flag=1 AND status < 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 38662 n bits 744 index `mailbox_status` of table `dbmail/dbmail_messages` trx id 0 4067778 lock_mode X waiting Record lock, heap no 124 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000e01; asc ;; 1: len 1; hex 00; asc ;; 2: len 8; hex 8000000000001c64; asc d;; *** (2) TRANSACTION: TRANSACTION 0 4067776, ACTIVE 0 sec, process no 17263, OS thread id 2943343536 updating or deleting, thread declared inside InnoDB 496 mysql tables in use 1, locked 1 5 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 95, query id 90781 172.20.23.28 dbmail Updating UPDATE dbmail_messages SET status=2 WHERE mailbox_idnr = 3584 AND deleted_flag=1 AND status < 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 38662 n bits 744 index `mailbox_status` of table `dbmail/dbmail_messages` trx id 0 4067776 lock_mode X Record lock, heap no 122 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 8; hex 8000000000000e00; asc ;; 1: len 1; hex 00; asc ;; 2: len 8; hex 8000000000001c62; asc b;; Record lock, heap no 123 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000e00; asc ;; 1: len 1; hex 00; asc ;; 2: len 8; hex 8000000000004eee; asc N ;; Record lock, heap no 124 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000e01; asc ;; 1: len 1; hex 00; asc ;; 2: len 8; hex 8000000000001c64; asc d;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 38662 n bits 744 index `mailbox_status` of table `dbmail/dbmail_messages` trx id 0 4067776 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 124 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000e01; asc ;; 1: len 1; hex 00; asc ;; 2: len 8; hex 8000000000001c64; asc d;; *** WE ROLL BACK TRANSACTION (1) MySQL Version : 5.0.27-standard-log [EMAIL PROTECTED] ~]$ cat /proc/version Linux version 2.6.9-5.ELsmp ([EMAIL PROTECTED]) (gcc version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)) #1 SMP Wed Jan 5 19:30:39 EST 2005 mysql> show create table dbmail_messages\G *************************** 1. row *************************** Table: dbmail_messages Create Table: CREATE TABLE `dbmail_messages` ( `message_idnr` bigint(21) NOT NULL auto_increment, `mailbox_idnr` bigint(21) NOT NULL default '0', `physmessage_id` bigint(21) NOT NULL default '0', `seen_flag` tinyint(1) NOT NULL default '0', `answered_flag` tinyint(1) NOT NULL default '0', `deleted_flag` tinyint(1) NOT NULL default '0', `flagged_flag` tinyint(1) NOT NULL default '0', `recent_flag` tinyint(1) NOT NULL default '0', `draft_flag` tinyint(1) NOT NULL default '0', `unique_id` varchar(70) NOT NULL default '', `status` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`message_idnr`), KEY `physmessage_id_index` (`physmessage_id`), KEY `mailbox_idnr_index` (`mailbox_idnr`), KEY `seen_flag_index` (`seen_flag`), KEY `unique_id_index` (`unique_id`), KEY `status_index` (`status`), KEY `mailbox_status` (`mailbox_idnr`,`status`), CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> show indexes from dbmail_messages; +-----------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ | dbmail_messages | 0 | PRIMARY | 1 | message_idnr | A | 22850 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | physmessage_id_index | 1 | physmessage_id | A | 22850 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | mailbox_idnr_index | 1 | mailbox_idnr | A | 22850 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | seen_flag_index | 1 | seen_flag | A | 1 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | unique_id_index | 1 | unique_id | A | 22850 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | status_index | 1 | status | A | 1 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | mailbox_status | 1 | mailbox_idnr | A | 22850 | NULL | NULL | | BTREE | | | dbmail_messages | 1 | mailbox_status | 2 | status | A | 22850 | NULL | NULL | | BTREE | | +-----------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ 8 rows in set (0.01 sec) DBMAIL uses INNODB tables with autocommit=1. How could deadlocks happen? Thanks Gu Lei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
