Hi,
I have a mysql database with innodb tables, and I keep running into a
deadlock that is not detected (causes a timeout) and that I don't think
should be happening.  Can anyone explain why this is causing a deadlock,
and secondly, how can I fix it or how can I change this so that the
deadlock will be detected? 

This is the innodb lock monitor data for the transactions that seem to
be locking.  I'm most concerned about the second transaction.  It
doesn't  claim to be waiting for a lock, but I believe it only occurs
when these transactions occur simultaneously.  The first transaction is
inserting into a temporary table with data from the same table that the
second transaction is writing to.  We are running in the serializable
mode.

---TRANSACTION 0 38838991, OS thread id 2355683345 fetching rows,
active, lock wait, has 31 lock struct(s), undo log entries 2
MySQL thread id 575109, query id 9375633 192.168.1.102 pas Copying to
tmp table
insert into temp_plx_user_id select distinct user_id  from plx_entry
where send_state = 2   and send
------------------TRX IS WAITING FOR THE LOCK:
RECORD LOCKS space id 0 page no 4105 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S waiting
Record lock, heap no 15 RECORD: info bits 0 0: len 4; hex 80004b56; asc
..KV;; 1: len 6; hex 00000250a27d; asc ...P.};; 2: len 7; hex
0000001ea22204; asc .....".;; 3:
------------------
TABLE LOCK table user/plx_lock trx id 0 38838991 lock_mode IX
RECORD LOCKS space id 0 page no 50 n bits 80 table user/plx_lock index
PRIMARY trx id 0 38838991 lock_mode X
Record lock, heap no 5 RECORD: info bits 0 0: len 8; hex
706c785f7065726d; asc plx_perm;; 1: len 6; hex 00000250a2cf; asc
...P..;; 2: len 7; hex 0000001ead03a1; asc .......;; 3:
Record lock, heap no 6 RECORD: info bits 0 0: len 9; hex
706c785f656e747279; asc plx_entry;; 1: len 6; hex 00000250a2cf; asc
...P..;; 2: len 7; hex 0000001ead037c; asc ......|;; 3:
TABLE LOCK table user/plx_entry trx id 0 38838991 lock_mode IS
RECORD LOCKS space id 0 page no 3852 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S
Record lock, heap no 32 RECORD: info bits 0 0: len 4; hex 80000b2a; asc
...*;; 1: len 6; hex 0000024c3e2e; asc ...L>.;; 2: len 7; hex
0000003698302a; asc ...6.0*;; 3:
RECORD LOCKS space id 0 page no 3891 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S
Record lock, heap no 54 RECORD: info bits 0 0: len 4; hex 80001527; asc
...';; 1: len 6; hex 000002500600; asc ...P..;; 2: len 7; hex
0000001e8b2769; asc .....'i;; 3:
RECORD LOCKS space id 0 page no 3954 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S
Record lock, heap no 31 RECORD: info bits 0 0: len 4; hex 8000250f; asc
..%.;; 1: len 6; hex 0000024d117f; asc ...M..;; 2: len 7; hex
00000040172c23; asc ...@.,#;; 3:
RECORD LOCKS space id 0 page no 4032 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S
Record lock, heap no 42 RECORD: info bits 0 0: len 4; hex 800038e8; asc
..8.;; 1: len 6; hex 0000024daca1; asc ...M..;; 2: len 7; hex
000000044624aa; asc ....F$.;; 3:
RECORD LOCKS space id 0 page no 1166 n bits 704 table user/plx_entry
index send_state trx id 0 38838991 lock mode S
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex
73757072656d756d00; asc supremum.;;
Record lock, heap no 44 RECORD: info bits 0 0: len 4; hex 80000002; asc
....;; 1: len 8; hex 800012357007e0c0; asc ...5p...;; 2: len 4; hex
80004700; asc ..G.;;
Record lock, heap no 45 RECORD: info bits 0 0: len 4; hex 80000002; asc
....;; 1: len 8; hex 800012357007e0c0; asc ...5p...;; 2: len 4; hex
80004701; asc ..G.;;
3 LOCKS PRINTED FOR THIS TRX AND PAGE: SUPPRESSING FURTHER PRINTS
RECORD LOCKS space id 0 page no 4088 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S
Record lock, heap no 10 RECORD: info bits 0 0: len 4; hex 80004700; asc
..G.;; 1: len 6; hex 0000024e1d8d; asc ...N..;; 2: len 7; hex
80000040150084; asc ...@...;; 3:
Record lock, heap no 11 RECORD: info bits 0 0: len 4; hex 80004701; asc
..G.;; 1: len 6; hex 0000024e1d8d; asc ...N..;; 2: len 7; hex
800000401500aa; asc ...@...;; 3:
Record lock, heap no 12 RECORD: info bits 0 0: len 4; hex 80004702; asc
..G.;; 1: len 6; hex 0000024e1d8d; asc ...N..;; 2: len 7; hex
800000401500d0; asc ...@...;; 3:
3 LOCKS PRINTED FOR THIS TRX AND PAGE: SUPPRESSING FURTHER PRINTS
RECORD LOCKS space id 0 page no 4089 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838991 lock mode S
Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 80004739; asc
..G9;; 1: len 6; hex 0000024e1eae; asc ...N..;; 2: len 7; hex
800000002d069a; asc ....-..;; 3:
Record lock, heap no 3 RECORD: info bits 0 0: len 4; hex 8000473a; asc
..G:;; 1: len 6; hex 0000024e1eae; asc ...N..;; 2: len 7; hex
800000002d06c0; asc ....-..;; 3:
Record lock, heap no 4 RECORD: info bits 0 0: len 4; hex 8000473b; asc
..G;;; 1: len 6; hex 0000024e1eae; asc ...N..;; 2: len 7; hex
800000002d06e6; asc ....-..;; 3:
3 LOCKS PRINTED FOR THIS TRX AND PAGE: SUPPRESSING FURTHER PRINTS
10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS


---TRANSACTION 0 38838909, OS thread id 2348675090, active, runs or
sleeps, has 12 lock struct(s), undo log entries 2
MySQL thread id 573398, query id 9375686 192.168.1.102 pas
UPDATE plx_entry set modified = now(), edit_counter = 2, contact_db_id =
0, contact_user_id = 1257, 
TABLE LOCK table user/plx_user trx id 0 38838909 lock_mode IX
RECORD LOCKS space id 0 page no 11319 n bits 176 table user/plx_user
index PRIMARY trx id 0 38838909 lock_mode X
Record lock, heap no 61 RECORD: info bits 0 0: len 4; hex 8000028d; asc
....;; 1: len 6; hex 00000250a27d; asc ...P.};; 2: len 7; hex
0000001ea221e4; asc .....!.;; 3:
TABLE LOCK table user/plx_entry trx id 0 38838909 lock_mode IS
RECORD LOCKS space id 0 page no 3732 n bits 824 table user/plx_entry
index user_id trx id 0 38838909 lock mode S
RECORD LOCKS space id 0 page no 4104 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838909 lock mode S
Record lock, heap no 63 RECORD: info bits 0 0: len 4; hex 80004b45; asc
..KE;; 1: len 6; hex 0000024e457b; asc ...NE{;; 2: len 7; hex
800000002d0084; asc ....-..;; 3:
Record lock, heap no 64 RECORD: info bits 0 0: len 4; hex 80004b46; asc
..KF;; 1: len 6; hex 0000024e457b; asc ...NE{;; 2: len 7; hex
800000002d00aa; asc ....-..;; 3:
Record lock, heap no 65 RECORD: info bits 0 0: len 4; hex 80004b47; asc
..KG;; 1: len 6; hex 0000024e457b; asc ...NE{;; 2: len 7; hex
800000002d00d0; asc ....-..;; 3:
3 LOCKS PRINTED FOR THIS TRX AND PAGE: SUPPRESSING FURTHER PRINTS
RECORD LOCKS space id 0 page no 4105 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838909 lock mode S
Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 80004b49; asc
..KI;; 1: len 6; hex 0000024e457b; asc ...NE{;; 2: len 7; hex
800000002d011c; asc ....-..;; 3:
Record lock, heap no 3 RECORD: info bits 0 0: len 4; hex 80004b4a; asc
..KJ;; 1: len 6; hex 0000024e457b; asc ...NE{;; 2: len 7; hex
800000002d0142; asc ....-.B;; 3:
Record lock, heap no 4 RECORD: info bits 0 0: len 4; hex 80004b4b; asc
..KK;; 1: len 6; hex 0000024e457b; asc ...NE{;; 2: len 7; hex
800000002d0168; asc ....-.h;; 3:
3 LOCKS PRINTED FOR THIS TRX AND PAGE: SUPPRESSING FURTHER PRINTS
TABLE LOCK table user/plx_contact_field trx id 0 38838909 lock_mode IS
RECORD LOCKS space id 0 page no 3779 n bits 368 table
user/plx_contact_field index PRIMARY trx id 0 38838909 lock mode S
Record lock, heap no 8 RECORD: info bits 0 0: len 4; hex 8000028d; asc
....;; 1: len 4; hex 80004b45; asc ..KE;; 2: len 4; hex 80000002; asc
....;; 3: len 6; hex 0000024e457b; asc ...NE{;;
Record lock, heap no 9 RECORD: info bits 0 0: len 4; hex 8000028d; asc
....;; 1: len 4; hex 80004b46; asc ..KF;; 2: len 4; hex 80000002; asc
....;; 3: len 6; hex 0000024e457b; asc ...NE{;;
Record lock, heap no 10 RECORD: info bits 0 0: len 4; hex 8000028d; asc
....;; 1: len 4; hex 80004b47; asc ..KG;; 2: len 4; hex 80000002; asc
....;; 3: len 6; hex 0000024e457b; asc ...NE{;;
3 LOCKS PRINTED FOR THIS TRX AND PAGE: SUPPRESSING FURTHER PRINTS
TABLE LOCK table user/plx_entry trx id 0 38838909 lock_mode IX
RECORD LOCKS space id 0 page no 4105 n bits 136 table user/plx_entry
index PRIMARY trx id 0 38838909 lock_mode X
Record lock, heap no 15 RECORD: info bits 0 0: len 4; hex 80004b56; asc
..KV;; 1: len 6; hex 00000250a27d; asc ...P.};; 2: len 7; hex
0000001ea22204; asc .....".;; 3:
10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

thanks for you help,
Joe


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to