Description:
On some queries to a certain table, the query stays in 'update' state for a long time, causing queries to other myisam tables on the same database to wait with state 'Locked'.
Example:
+------+----------+------------------------------+----------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+------------------------------+----------+---------+------+--------+------------------------------------------------------------------------------------------------------+ |
| 21 | root | localhost | asterisk | Query | 49 | Locked | delete from pbx_channel where server = 'pbx1' and channel = 'agent/2820000152' |
| 23 | root | localhost | asterisk | Query | 49 | update | insert into pbx_channel(uniqueid,channel,state,callerid,calleridname,insert_date,server,hash,usernam |
| 83 | openser | 85.19.69.12:57518 | asterisk | Query | 28 | Locked | update location set expires='2008-09-04 12:44:30',q=-1.00 ,cseq=192949,flags=0,cflags=0,user_age |
Output from show create table:
CREATE TABLE `pbx_channel` (
`server` varchar(50) NOT NULL DEFAULT 'pbx1',
`uniqueid` varchar(50) NOT NULL DEFAULT '',
`channel` varchar(150) NOT NULL DEFAULT '',
`state` varchar(20) NOT NULL DEFAULT '',
`callerid` varchar(30) NOT NULL DEFAULT '',
`calleridname` varchar(150) NOT NULL DEFAULT '',
`insert_date` varchar(14) DEFAULT NULL,
`link_channel` varchar(150) DEFAULT NULL,
`link_uniqueid` varchar(50) DEFAULT NULL,
`hash` varchar(32) NOT NULL DEFAULT '',
`waitingfor` varchar(20) DEFAULT NULL,
`wait_start` varchar(14) DEFAULT NULL,
`dialednum` varchar(50) DEFAULT NULL,
`redir_by` varchar(20) DEFAULT NULL,
`username` varchar(50) DEFAULT NULL,
`ncom_id` int(11) DEFAULT NULL,
PRIMARY KEY (`hash`),
KEY `ix_uniqueid` (`server`,`uniqueid`),
KEY `ix_channel` (`server`,`channel`),
KEY `ix_waitingfor` (`waitingfor`),
KEY `ix_link_channel` (`link_channel`),
KEY `ix_link_uniqueid` (`link_uniqueid`),
KEY `ix_state` (`state`),
KEY `ix_username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `location` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL DEFAULT '',
`domain` varchar(64) DEFAULT NULL,
`contact` varchar(255) NOT NULL DEFAULT '',
`received` varchar(128) DEFAULT NULL,
`path` varchar(128) DEFAULT NULL,
`expires` datetime NOT NULL DEFAULT '2020-05-28 21:32:15',
`q` float(10,2) NOT NULL DEFAULT '1.00',
`callid` varchar(255) NOT NULL DEFAULT 'Default-Call-ID',
`cseq` int(11) NOT NULL DEFAULT '13',
`last_modified` datetime NOT NULL DEFAULT '1900-01-01 00:00:01',
`flags` int(11) NOT NULL DEFAULT '0',
`cflags` int(11) NOT NULL DEFAULT '0',
`user_agent` varchar(255) NOT NULL DEFAULT '',
`socket` varchar(64) DEFAULT NULL,
`methods` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `account_contact_idx` (`username`,`domain`,`contact`),
KEY `ix_username` (`username`),
KEY `ix_expires` (`expires`)
) ENGINE=MyISAM AUTO_INCREMENT=1311 DEFAULT CHARSET=latin1
After 10-60 seconds the query finish the server is back to normal.
I've checked the hardware and system stats. The are no problems and the server load is not very high.
The tables accessed is about 50-100 rows - it's used to keep track of active calls on an pbx, so it's changed quite often.
How to repeat:
It's not repeatable as far as I know.
