Heikki, Thanks for the input. Right now we can't move to 4.1, we're stuck with 4.0.20 for sometime. So, it there someway that through improving the schema these issues could be addressed? I added an index on these tables reflecting the way that data is usually looked up (always by session_guid and label). Every user has a unique session_guid that looks something like: 7E6E4C38-4819-11D9-9802-84B0A828F8C4
What I don't understand is what the gap is. I can understand in the example on the mysql site (select * from table where id > 100 for update) how that would lock all records with id > 100. How does it work in the case of a string based index? Suppose there were two different users, one with guid 7E6E4C38-4819-11D9-9802-84B0A828F8C4 and the other with guid 5G4F4H10-4819-11D9-9802-84B0A828F8C4 (note, these two guids are fairly representative in form). The first user was performing a delete and the second was performing an insert what gap would be created? What kind of schema change might reduce/eliminate the contention? Thanks, Tripp --- Heikki Tuuri <[EMAIL PROTECTED]> wrote: > 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 > === message truncated === __________________________________ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
