I am using mysql 5.0.92. Recently, we have many insert locks in one table, to which rows are inserted (and updated or deleted) relatively quickly. I studied the questions here in StackOverflow, the mysql documentation and forums, without understanding the problem. One of the things that puzzles me is that one of the tables does not block any resource according to innodb status.
Here is the result of SHOW INNODB STATUS :
*** (1) TRANSACTION: TRANSACTION 0 2326105503, ACTIVE 0 sec, process no 18871, OS thread id 1078532416 inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216, undo log entries 1 MySQL thread id 225129, query id 126720476 192.168.999.999 the-user update insert into the_table (creation_date, expiration_date, iid, ma_c, ma_cid, ma_ed, ma_lat, ma_long, ma_ln, ma_sd, ma_sid, uid, id) values ('2011-08-30 16:54:18.0', '2011-09-01 23:59:59.0', 1001, '', 'US', '2011-09-01 23:59:59.0', 33.72, -92.61, 'CITY_CENTER', '2011-08-31 23:59:59.0', '', 1010, 'xxx') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 15002 page no 5749 n bits 376 index `idx_iu` of table `the_db/the_table` trx id 0 2326105503 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 0 2326105502, ACTIVE 0 sec, process no 18871, OS thread id 1243085120 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1216, undo log entries 2 MySQL thread id 223875, query id 126720499 192.168.999.999 the-user update insert into the_table (creation_date, expiration_date, iid, ma_c, ma_cid,ma_ed, ma_lat, ma_long, ma_ln,ma_sd, ma_sid, uid, id) values ('2011-08-30 16:54:18.0','2011-12-14 23:59:59.0', 2002, 'Amsterdam', 'NL', '2011-10-04 16:45:00.0', 52.37, 4.89, 'CITY_CENTER', '2011-09-06 23:59:59.0', '', 2020, 'yyy') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 15002 page no 5749 n bits 376 index `idx_iu` of table `the_db/the_table` trx id 0 2326105502 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 283 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 8; hex 80000000008b22f0; asc " ;; 1: len 4; hex 8004ab2a; asc *;; 2: len 30; hex 313331343732333235383338393330353936323430363039352020202020; asc 1314723258389305962406095 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 15002 page no 5749 n bits 376 index `idx_iu` of table `the_db/the_table` trx id 0 2326105502 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1)
The table is defined as follows:
CREATE TABLE `the_table` ( `id` char(30) NOT NULL default '', `iid` bigint(20) NOT NULL, `uid` int(11) NOT NULL, `creation_date` datetime default NULL, `expiration_date` datetime default NULL, `ma_sd` datetime default NULL, `ma_ed` datetime default NULL, `ma_c` varchar(255) default NULL, `ma_sid` varchar(3) default NULL, `ma_cid` varchar(3) default NULL, `ma_long` double default NULL, `ma_lat` double default NULL, `ma_ln` varchar(50) default NULL, PRIMARY KEY (`id`), KEY `idx_iu` (`iid`,`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Help would be greatly appreciated.