Dear Rick,
Below is the table structure with the indexes.
CREATE TABLE IF NOT EXISTS `tblMst` (
`mstID` int(5) NOT NULL AUTO_INCREMENT,
`entID` int(5) NOT NULL,
`vID` int(5) NOT NULL,
`mstType` enum('MML','ILL') NOT NULL DEFAULT 'SLM',
`mSN` varchar(20) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`lati` double NOT NULL,
`long` double NOT NULL,
`dateTimer` datetime NOT NULL,
`deviceVolt` float NOT NULL,
`msStatus` enum('a','d','u','r','l','t') NOT NULL,
PRIMARY KEY (`mstID`),
KEY `mSN` (`mSN`),
KEY `entID` (`entID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
I need to manage to catch when the lock event occur. But below is how I saw some of the lock in waiting. What does this means "lock_mode X locks rec but not gap waiting" ?
130831 2:42:03\n*** (1) TRANSACTION:\nTRANSACTION 0 6123076, ACTIVE 28 sec, process no 8046, OS thread id 140036459042560 fetching rows\nmysql tables in use 1, locked 1\n
LOCK WAIT 5 lock struct(s), heap size 1216, 8 row lock(s), undo log entries 3\n
MySQL thread id 394324, query id 610567968
WAITING FOR THIS LOCK TO BE GRANTED:\n
RECORD LOCKS space id 95 page no 10 n bits 120 index `PRIMARY` of table `sdata`.`tblMst` trx id 0 6123076 lock_mode X locks rec but not gap waiting\nRecord lock, heap no 47 PHYSICAL RECORD: n_fields 50; compact format; info bits 0\n 0:
The total of innodb_row_lock_waits is now more then 1070 where earlier it was just 432. How monitor this locks live or putting into a log file because I can capture once I miss that event.
I also saw this error in mysqld 130914 23:08:11 InnoDB: ERROR: the age of the last checkpoint is 9433963,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row. Does this link to the lock wait time problem?