List:General Discussion« Previous MessageNext Message »
From:Gu Lei Date:November 28 2006 6:08am
Subject:deadlock with autocommit=1
View as plain text  
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

[gulei@ARCHITECT ~]$ cat /proc/version
Linux version 2.6.9-5.ELsmp (bhcompile@stripped) (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

Thread
deadlock with autocommit=1Gu Lei28 Nov