MySql Deadlock on already acquired locks

I encountered a deadlock which puzzles me. I read in a different post Mysql deadlock explanation needed an answer that didn't solve my confusion. The situation is also caught in another referenced explanation page about SHOW INNODB STATUS.

I'm attaching my instance as well - which is a bit different, as it is locking an index, once with gaps and once without.

But I think the main question is still the same:

If Transaction (2) has lock A , then Transaction (1) is waiting on Lock A, can it be that Transaction (2) will be deadlocked if it asks for lock A again?

This doesn't sound right, although that is what we see in the InnoDb Status. When I try to recreate directly in MySql, using different tabs for the different transactions - everything works fine, and there is no such deadlock. I'm also attaching my futile attempt to recreate.

I'm probably missing something in my interpretation of the situation and I would greatly appreciate a good explanation.

I am using MySql 5.1, with Hibernate 3.

------------------------
LATEST DETECTED DEADLOCK
------------------------
110918 14:56:36
*** (1) TRANSACTION:
TRANSACTION 0 40261686, ACTIVE 0 sec, process no 1686, OS thread id 1358170432 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 3024, undo log entries 1
MySQL thread id 101203, query id 77147262 localhost 127.0.0.1 operator Updating
update test_table set created='2011-09-18 14:56:28', customer_id=3, ended=null, lead_id=423, message=null, modified='2011-09-18 14:56:36', priority=0, project_id=74, retries=0, started='2011-09-18 14:56:36', status='PROCESS', user_id=2, inquiry_id=1542 where id=1541
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 7148 n bits 952 index `status` of table `test_table` trx id 0 40261686 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 0 40261595, ACTIVE 0 sec, process no 1686, OS thread id 1360034112 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
9 lock struct(s), heap size 3024, undo log entries 2
MySQL thread id 101209, query id 77147276 localhost 127.0.0.1 operator Updating
update test_table set created='2011-09-18 14:53:22', customer_id=3, ended=null, lead_id=401, message='', modified='2011-09-18 14:56:36', priority=0, project_id=74, retries=3, started='2011-09-18 14:55:46', status='PENDING', user_id=2, inquiry_id=1474 where id=1473
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 7148 n bits 952 index `status` of table `test_table` trx id 0 40开发者_如何学Python261595 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 7148 n bits 952 index `status` of table `test_table` trx id 0 40261595 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

attempt in recreating (I dropped a few columns that had foreign keys - as they where not mentioned in the InnoDb status) :

1) initial setup:

DROP TABLE IF EXISTS `knowledge`.`aaa`;
CREATE TABLE  `knowledge`.`aaa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` longtext,
  `status` varchar(16) DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT '0',
  `retries` int(8) NOT NULL DEFAULT '0',
  `modified` datetime DEFAULT NULL,
  `customer_id` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `customer_id` (`customer_id`) -- ,
  -- CONSTRAINT `aaa_customer_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=515 DEFAULT CHARSET=utf8;


insert into aaa
values (1, '', 'AAA', 0, 0, null, 1);
insert into aaa
values (2, '', 'AAA', 0, 0, null, 1);
insert into aaa
values (3, '', 'AAA', 0, 0, null, 1);

2) Transaction 2: Begin transaction (I use the gui) presumably acquiering the first lock of transaction 2

select * from aaa where status = 'AAA' for update;

3) Transaction 1: Begin transaction Presumably waiting for the lock - btw, the transaction does wait.

insert into aaa
values (10, '', 'BBB', 0, 0, null, 1);

4) Transaction 2: presumably trying to wait on the same lock again - btw, this doesn't wait at all.

update aaa set status = 'BBB' where id = 1;
update aaa set status = 'BBB' where id = 2;

now I can commit Transaction 2, and then Transaction 1 and everything is ok... no recreation of the deadlock situation..