MySQL Bugs: #45505: Deadlock condition with MyISAM table in version 5.1.35

Bug #45505	Deadlock condition with MyISAM table in version 5.1.35
Submitted:	15 Jun 2009 15:47	Modified:	5 Sep 2009 18:46
Reporter:	Mark Butler	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.1.35	OS:	Linux (RHEL 5.3 (CentOS))
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, myisam, replication

Description:
We have a group of replication slave hosts that are read only. I will notice that replication on a particular host is running behind.  I will look at the process list and see that there is a killed zombie SELECT query and an UPDATE query that are of similar age.  The killed query does not clear out, and any attempt to restart the instance fails.  I have to kill mysqld to clear the error. The table loks like this

CREATE TABLE `article_page` (
  `article_page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `article_id` int(10) unsigned NOT NULL DEFAULT '0',
  `number` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `name` varchar(50) NOT NULL DEFAULT '',
  `body` mediumtext NOT NULL,
  PRIMARY KEY (`article_page_id`),
  UNIQUE KEY `article_number_idx` (`article_id`,`number`),
  KEY `article_idx` (`article_id`),
  FULLTEXT KEY `page_text` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=6395444 DEFAULT CHARSET=latin1

SELECT query looks like this
SELECT article_page_id, number, name, body FROM article_page WHERE article_id = '4313548'
The update query is a simple attempt to update the article_id,body, name and number fields.

How to repeat:
The problem shows up whenever an update query tries to change a record, immediately subsequent to this update a select query is trying to read the table.  The update query seems to have the table locked implicitly. The Apache server connection times out, and the select query becomes a zombie.  We have a locally made zombie killer script that attempts to use mysqadmin -kill <process id> to kill the select query.  The query does not die (as it does in mysql 5.0) and neither does the update query in the process list.

Here is the process list from one such event.  This has happen many many times.

+---------+-------------+-------------------+-------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id      | User        | Host              | db                | Command | Time  | State                            | Info                                                                                                 |
+---------+-------------+-------------------+-------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 1       | system user |                   |                   | Connect | 66935 | Waiting for master to send event |                                                                                                      |
| 2       | system user |                   | feed              | Connect | 4431  | Locked                           | UPDATE article_page SET
              number=1,
              name='',
              body='<font si |
| 2964841 | feed_read   | 10.9.150.44:39011 | feed              | Killed  | 4431  | Sending data                     | SELECT article_page_id, number, name, body FROM article_page WHERE article_id = '4198924'            |
| 3001841 | root        | localhost         |                   | Query   | 0     |                                  | show processlist                                                                                     |
+---------+-------------+-------------------+-------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+

The zombie killer script in this case fires via crontab every 3 minutes and kills any select query that is 60 seconds old or older.  Obviously the above example shows the extreme reluctance of this SELECT query to go the way of all flesh.

Thank you for the report.

I can not repeat described behavior with generic test. Please check if it is duplicate of bug #37067. Also, please, provide your configuration file and indicate if you have query cache enabled.

config of file for 45505

Attachment: my.cnf (application/octet-stream, text), 3.98 KiB.

Thank you for the feedback.

Please try to turn query cache off and see if it solves the problem.

Will do thanks

I have the same with the following table:

CREATE TABLE `BoekenSearch` (
  `Ean` char(13) NOT NULL DEFAULT '',
  `Snelzoeken` text,
  `Titelzoeken` text,
  `Samenvatting` text,
  `Rubriek` varchar(100) DEFAULT NULL,
  `Serie` varchar(255) DEFAULT NULL,
  `Jaaruitgifte` int(11) DEFAULT NULL,
  `Aanmaakdatum` date DEFAULT NULL,
  `Recensiedatum` date DEFAULT NULL,
  `Prijs` float(8,2) DEFAULT NULL,
  `Boekstatus` int(11) DEFAULT NULL,
  `Productsoort` varchar(100) DEFAULT NULL,
  `Taal` varchar(6) DEFAULT NULL,
  `Auteur1` int(11) DEFAULT NULL,
  `Auteur2` int(11) DEFAULT NULL,
  `Auteur3` int(11) DEFAULT NULL,
  `Uitgeverid` int(11) DEFAULT NULL,
  `Achternaam` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`Ean`),
  KEY `Boekstatus` (`Boekstatus`),
  KEY `Productsoort` (`Productsoort`),
  KEY `Prijs` (`Prijs`),
  KEY `Rubriek` (`Rubriek`),
  KEY `Serie` (`Serie`),
  KEY `Jaaruitgifte` (`Jaaruitgifte`),
  KEY `Aanmaakdatum` (`Aanmaakdatum`),
  KEY `Recensiedatum` (`Recensiedatum`),
  KEY `Taal` (`Taal`),
  KEY `Achternaam` (`Achternaam`),
  KEY `Auteur1` (`Auteur1`),
  KEY `Auteur2` (`Auteur2`),
  KEY `Auteur3` (`Auteur3`),
  KEY `Uitgeverid` (`Uitgeverid`),
  FULLTEXT KEY `Snelzoeken` (`Snelzoeken`),
  FULLTEXT KEY `Samenvatting` (`Samenvatting`),
  FULLTEXT KEY `Titelzoeken` (`Titelzoeken`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

A few times a day a select with a MATCH statement will go into deadlock. It will eat 100% CPU, can't be killed in mysql and I can't restart mysql the nice way and have to 'kill -9' it manually.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sander,

thank you for the feedback.

Please be sure you use latest version of MySQL server and try to turn query cache off, then run same query again, then inform us if it solves the problem for you.

I did turn off the query cache and it corrected the issue

Thank you for the feedback.

This seems to be duplicate of bug #43758 fixed in version 5.1.36. Please upgrade (current version is 5.1.37 though) and inform us if it solved the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".