T1:
insert into stock_21 (cid,cmid,pid,num) values (194621,240,1055,4) ON DUPLICATE KEY UPDATE num=num+4
and get message:
`hr_stock/stock_21` trx id 0 206366540 lock_mode X locks rec but not gap waiting

T2.
delete from stock_00 where cid=172900 and cmid in (select cmid from commodity)
`hr_stock/stock_21` trx id 0 206459364 lock_mode X locks rec but not gap
WE ROLL BACK TRANSACTION (2)
stock_21 and Stock_00 structure is :
stock_21 CREATE TABLE `stock_21` (
`cid` int(11) NOT NULL,
`pid` int(11) NOT NULL,
`cmid` int(11) default NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`pid`,`cid`),
UNIQUE KEY `pid` (`pid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 65536 kB'

Actually,
all transaction is a xa,T1 is :

connect1:xa start 'stockin';
connect1:insert into stock_21 (cid,cmid,pid,num) values (194621,240,1055,4) ON DUPLICATE KEY UPDATE num=num+4;
connect1:xa end 'stockin'
connect2:xa start 'del';
connect2: delete from world_animal_...
connect2:xa end 'del';
... and then prepare and commit the transaction,

T2 is:

connect1:xa start 'stockout';
connect1:delete from stock_00 where cid=172900 and cmid in (select cmid from commodity);
connect1:xa end 'stockout'
connect3:xa start 'addmoney';
connect3:update customer set money...
connect3:xa end 'addmoney';
... and then prepare and commit the transaction,

i don't know why got deadlock like this,no recycled tables reference!
but there is three app to execute T1 or T2,
am I clear?
the detail message is here


LATEST DETECTED DEADLOCK
------------------------
091002 11:06:48
*** (1) TRANSACTION:
TRANSACTION 0 206366540, ACTIVE 4352 sec, OS thread id 1632646912 inserting
mysql tables in use 1, locked 1
LOCK WAIT 391 lock struct(s), heap size 44352, undo log entries 431
MySQL thread id 10155, query id 15077970 wenewlaptop.flashpk.com.cn 202.173.255.4 cfgbeam update
insert into stock_21 (cid,cmid,pid,num) values (194621,240,1055,4) ON DUPLICATE KEY UPDATE num=num+4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 11036 n bits 400 index `PRIMARY` of table `hr_stock/stock_21` trx id 0 206366540 lock_mode X locks rec but not gap waiting
Record lock, heap no 333 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000041f; asc ;; 1: len 4; hex 8002f83d; asc =;; 2: len 6; hex 00000c4e51e4; asc NQ ;; 3: len 7; hex 80000052a9043a; asc R :;; 4: len 4; hex 800000f0; asc ;; 5: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 0 206459364, ACTIVE 315 sec, OS thread id 1689901056 fetching rows, thread declared inside InnoDB 238
mysql tables in use 2, locked 2
54 lock struct(s), heap size 5504, undo log entries 77
MySQL thread id 10128, query id 15079163 wenewlaptop.flashpk.com.cn 202.173.255.4 cfgbeam updating
delete from stock_00 where cid=172900 and cmid in (select cmid from commodity)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 11036 n bits 400 index `PRIMARY` of table `hr_stock/stock_21` trx id 0 206459364 lock_mode X locks rec but not gap
Record lock, heap no 333 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000041f; asc ;; 1: len 4; hex 8002f83d; asc =;; 2: len 6; hex 00000c4e51e4; asc NQ ;; 3: len 7; hex 80000052a9043a; asc R :;; 4: len 4; hex 800000f0; asc ;; 5: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 14452 n bits 328 index `PRIMARY` of table `hr_stock/stock_00` trx id 0 206459364 lock_mode X locks rec but not gap waiting
Record lock, heap no 254 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000042e; asc .;; 1: len 4; hex 8002bdf4; asc ;; 2: len 6; hex 00000c4ce74c; asc L L;; 3: len 7; hex 8000003a3701a0; asc :7 ;; 4: len 4; hex 80000047; asc G;; 5: len 4; hex 80000005; asc ;;

*** WE ROLL BACK TRANSACTION (2)