Joe, are you also using MySQL table level locks? I mean
LOCK TABLES ... READ, ... WRITE The deadlock detection algorithm does not know of them and we must resort to a timeout. Below we see that the second trx has shared row level locks though it is executing an UPDATE. An UPDATE sets exclusive row level locks. Maybe these row level locks are from an earlier SQL statement withing the same transaction? You can avoid deadlocks by locking the tables with table level locks at the start of a transaction. But then you have to use the table level locks also in other transactions which touch the same tables, otherwise deadlocks cycling through row level locks are possible. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB ----- Original Message ----- From: "Joe Shear" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, July 23, 2002 3:29 AM Subject: Innodb deadlock problems > 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 > --------------------------------------------------------------------- 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
