0Euf31zz

1MySql Deadlock on already acquired locks
2------------------------
3LATEST DETECTED DEADLOCK
4------------------------
5110918 14:56:36
6*** (1) TRANSACTION:
7TRANSACTION 0 40261686, ACTIVE 0 sec, process no 1686, OS thread id 1358170432 updating or deleting
8mysql tables in use 1, locked 1
9LOCK WAIT 9 lock struct(s), heap size 3024, undo log entries 1
10MySQL thread id 101203, query id 77147262 localhost 127.0.0.1 operator Updating
11update 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
12*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
13RECORD 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
14*** (2) TRANSACTION:
15TRANSACTION 0 40261595, ACTIVE 0 sec, process no 1686, OS thread id 1360034112 updating or deleting, thread declared inside InnoDB 499
16mysql tables in use 1, locked 1
179 lock struct(s), heap size 3024, undo log entries 2
18MySQL thread id 101209, query id 77147276 localhost 127.0.0.1 operator Updating
19update 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
20*** (2) HOLDS THE LOCK(S):
21RECORD LOCKS space id 0 page no 7148 n bits 952 index `status` of table `test_table` trx id 0 40261595 lock_mode X
22*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
23RECORD 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
24*** WE ROLL BACK TRANSACTION (1)
25	
26DROP TABLE IF EXISTS `knowledge`.`aaa`;
27CREATE TABLE  `knowledge`.`aaa` (
28  `id` int(11) NOT NULL AUTO_INCREMENT,
29  `message` longtext,
30  `status` varchar(16) DEFAULT NULL,
31  `priority` int(11) NOT NULL DEFAULT '0',
32  `retries` int(8) NOT NULL DEFAULT '0',
33  `modified` datetime DEFAULT NULL,
34  `customer_id` int(11) unsigned NOT NULL DEFAULT '1',
35  PRIMARY KEY (`id`),
36  KEY `status` (`status`),
37  KEY `customer_id` (`customer_id`) -- ,
38  -- CONSTRAINT `aaa_customer_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
39) ENGINE=InnoDB AUTO_INCREMENT=515 DEFAULT CHARSET=utf8;
40
41
42insert into aaa
43values (1, '', 'AAA', 0, 0, null, 1);
44insert into aaa
45values (2, '', 'AAA', 0, 0, null, 1);
46insert into aaa
47values (3, '', 'AAA', 0, 0, null, 1);
48	
49select * from aaa where status = 'AAA' for update;
50	
51insert into aaa
52values (10, '', 'BBB', 0, 0, null, 1);
53	
54update aaa set status = 'BBB' where id = 1;
55update aaa set status = 'BBB' where id = 2;