Emmett,
ok, trx (1) is trying to lock gap G, but trx (2) already has a lock on it. And trx (2) is trying to insert a new record into that same gap G.
Even a waiting lock request on a gap prevents new inserts into it => a deadlock.
In 4.1.7, you might avoid this deadlock. In 4.1.7, a waiting gap lock does not need to wait just because someone else has a granted lock on the same gap. But, of course, your transactions might deadlock a bit later. DELETEs and INSERTs in the same gap very easily lead to a deadlock.
In 4.1, you can also set
innodb_locks_unsafe_for_binlog
in my.cnf. That removes almost all gap locking.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
----- Original Message ----- From: "Emmett Bishop" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, December 07, 2004 5:44 PM
Subject: Re: Help interpreting SHOW INNODB Status Message
Heikki,
We're using MySQL 4.0.20. Here's the full output:
===================================== 041207 8:33:26 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 49506785, signal count 48823098 Mutex spin waits 963371106, rounds 3338424450, OS waits 2853796 RW-shared spins 80886102, OS waits 38728129; RW-excl spins 2488320, OS waits 302033 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 041206 23:52:41 Transaction: TRANSACTION 0 10799828, ACTIVE 2 sec, process no 31424, OS thread id 2942950320 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 88051, query id 14858689 host ip user update INSERT INTO s_filter (fname, lname, email, eff_oldest_dt, eff_latest_dt, move_oldest_dt, move_latest_dt, agent_id, no_agent_sw, exclude_holds_sw, custom_message_sw, pics_only_sw, price, wiz_city_name, subdivision, tot_finished_sqft, bedrooms, garage_spaces, bathrooms, area, subarea, year, property_t Foreign key constraint fails for table `ahf_test/s_filter`: , CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) Trying to add in child table, in index `idx_session_guid` tuple: DATA TUPLE: 2 fields; 0: len 36; hex 37453645344333382d343831392d313144392d393830322d383442304138323846384334; asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len 6; hex 00000048a79b; asc H ;;
But in parent table `ahf_test/s_session`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32 0: len 30; hex 37454139364146452d343743442d313144392d393830322d383442304138; asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); 1: len 6; hex 000000a49aaf; asc ;; 2: len 7; hex 00000340082073; asc @ s;; 3: len 8; hex 8000123a34f916a6; asc :4 ;;
------------------------ LATEST DETECTED DEADLOCK ------------------------ 041207 7:32:14 *** (1) TRANSACTION: TRANSACTION 0 10812524, ACTIVE 0 sec, process no 31424, OS thread id 2916936624 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 89288, query id 15070554 host ip user updating DELETE FROM s_customer WHERE session_guid = '0B10634A-485A-11D9-9802-84B0A828F8C4' AND label = 'CustomerDTO' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812524 lock_mode X locks gap before rec waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 00000048b228; asc H (;;
*** (2) TRANSACTION: TRANSACTION 0 10812523, ACTIVE 0 sec, process no 31424, OS thread id 2926173104 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 7 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 89289, query id 15070556 host ip user update INSERT INTO s_customer (customer_id, address_id, agent_id, pref_id, lname, fname, email, home_phone, work_phone, comments, house_for_sale, address1, city, state, zip, subdivision_list, price_lower, price_upper, tot_finished_sqft, bedrooms, bathrooms, garage_spaces, style, eff_dt, move_dt, customer_i *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 00000048b228; asc H (;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 00000048b228; asc H (;;
*** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 0 10819864 Purge done for trx's n:o < 0 10819842 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2925570992 MySQL thread id 89423, query id 15136606 host ip user show innodb status ---TRANSACTION 0 10819861, not started, process no 31424, OS thread id 2919033776 MySQL thread id 89421, query id 15136544 host ip user ---TRANSACTION 0 10819843, not started, process no 31424, OS thread id 2912832432 MySQL thread id 89420, query id 15136392 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2950908848 MySQL thread id 89418, query id 15136078 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2951310256 MySQL thread id 89417, query id 15136084 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2922826672 MySQL thread id 89415, query id 15136081 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2956626864 MySQL thread id 89416, query id 15136080 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2951846832 MySQL thread id 89412, query id 15136058 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2955623344 MySQL thread id 89411, query id 15136060 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2922023856 MySQL thread id 89413, query id 15136059 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2957630384 MySQL thread id 89414, query id 15136057 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2918833072 MySQL thread id 89409, query id 15136041 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2913434544 MySQL thread id 89407, query id 15136065 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2925771696 MySQL thread id 89410, query id 15136044 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2926574512 MySQL thread id 89408, query id 15136085 host ip user ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2914638768 MySQL thread id 89406, query id 15136063 host ip user ---TRANSACTION 0 10819863, ACTIVE 1 sec, process no 31424, OS thread id 2909641648 fetching rows, thread declared inside InnoDB 293 mysql tables in use 3, locked 0 MySQL thread id 89334, query id 15136561 host ip user Copying to tmp table SELECT listing.*, mls.mls_name FROM listing INNER JOIN mls ON listing.mls_id = mls.mls_id AND mls.mls_id IN (2) LEFT JOIN sent ON sent.customer_id = 33839 AND sent.pref_id = 34981 AND sent.office_id = 10 AND listing.listing_id = sent.listing_id AND listing.price = sent.price WHERE sent.listing_id I Trx read view will not see trx with id >= 0 10819864, sees < 0 10819836 ---TRANSACTION 0 10819841, ACTIVE 19 sec, process no 31424, OS thread id 2915040176 MySQL thread id 89405, query id 15136380 host ip user Trx read view will not see trx with id >= 0 10819842, sees < 0 10819826 ---TRANSACTION 0 10819840, ACTIVE 19 sec, process no 31424, OS thread id 2920819632 MySQL thread id 89404, query id 15136371 host ip user Trx read view will not see trx with id >= 0 10819841, sees < 0 10819826 ---TRANSACTION 0 10819839, ACTIVE 20 sec, process no 31424, OS thread id 2949241776 MySQL thread id 89403, query id 15136364 host ip user Trx read view will not see trx with id >= 0 10819840, sees < 0 10819826 ---TRANSACTION 0 10819838, ACTIVE 20 sec, process no 31424, OS thread id 2934852528 MySQL thread id 89402, query id 15136352 host ip user Trx read view will not see trx with id >= 0 10819839, sees < 0 10819826 ---TRANSACTION 0 10819836, ACTIVE 20 sec, process no 31424, OS thread id 2959571888 MySQL thread id 89401, query id 15136342 host ip user Trx read view will not see trx with id >= 0 10819837, sees < 0 10819826 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 920779104 OS file reads, 6170043 OS file writes, 1441924 OS fsyncs 1671.82 reads/s, 16852 avg bytes/read, 14.40 writes/s, 2.47 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 63, free list len 343, seg size 407, 3091615 inserts, 3083366 merged recs, 890592 merges Hash table size 34679, used cells 0, node heap has 1 buffer(s) 8.47 hash searches/s, 217.72 non-hash searches/s --- LOG --- Log sequence number 2 2572123120 Log flushed up to 2 2572123120 Last checkpoint at 2 2572119623 0 pending log writes, 0 pending chkp writes 982669 log i/o's done, 1.40 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 19321504; in additional pool allocated 1047808 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 16 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 995213365, created 41511, written 5483611 1719.69 reads/s, 0.00 creates/s, 13.47 writes/s Buffer pool hit rate 871 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue Main thread process no. 31424, id 2979605424, state: sleeping Number of rows inserted 1641021, updated 284334, deleted 1286005, read 945797617 0.53 inserts/s, 0.33 updates/s, 0.00 deletes/s, 17451.24 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT
__________________________________ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
