Howdy all, We're having concurrency problems with a table in our database and I'm not sure if I'm interpreting the following chunk of output from SHOW INNODB STATUS correctly.
>From what I gather, the row could not be inserted because the table was locked. I think that this insert was a victim of the next key locking stategy used by INNODB based on statement "lock_mode X locks gap before rec". We don't use SELECT FOR UPDATE statements so I'm wondering how there could be a lock. If someone was performing a regular SELECT statement (they would be using the index session_guid, label to retrieve the records) would that cause the insert statement to get locked out? What confuses me is how there could be two or more different guids involved in any one query (the session guid is always in the where clause of any select statement against this table, as is the label, though the labels are almost all the same, a value of "SelectedCustomer"). Any ideas as to how I can clean this up so that the contention is reduced? BTW, we're using a transaction isolation level of REPEATABLE READ. Thanks in advance, Tripp LATEST DETECTED DEADLOCK ------------------------ 041206 17:13:50 *** (1) TRANSACTION: TRANSACTION 0 10790587, ACTIVE 151 sec, process no 31424, OS thread id 2949241776 inserting mysql tables in use 1, locked 1 LOCK WAIT 46 lock struct(s), heap size 5504, undo log entries 158 MySQL thread id 85684, query id 14714501 host ip user update INSERT INTO s_contact_log (log_id, customer_id, entry, author, log_dt, log_type, office_id, session_guid, label, sort_id) VALUES (228072,38755, 'test entry' - *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2263 n bits 264 index `idx_session_label` of table `ahf_test/s_contact_log` trx id 0 10790587 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 130 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 35384143373041392d343745312d313144392d393830322d383442304138; asc 58AC70A9-47E1-11D9-9802-84B0A8;...(truncated); 1: len 16; hex 53656c6563746564437573746f6d6572; asc SelectedCustomer;; 2: len 6; hex 0000004880b1; asc H ;; Here's the structure of the table: CREATE TABLE `s_contact_log` ( `session_guid` varchar(36) NOT NULL default '', `label` varchar(50) NOT NULL default '', `log_id` int(11) NOT NULL default '0', `office_id` int(11) NOT NULL default '0', `customer_id` int(11) NOT NULL default '0', `entry` text NOT NULL, `author` varchar(60) NOT NULL default '', `log_dt` date NOT NULL default '0000-00-00', `sort_id` int(11) NOT NULL default '0', `log_type` int(11) NOT NULL default '0', KEY `idx_session_guid` (`session_guid`), KEY `idx_session_label` (`session_guid`,`label`), CONSTRAINT `s_contact_log_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) ) TYPE=InnoDB __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
