1

A plain old "delete single record" statement froze on me. Suspecting a (dead)lock issue, I checked the process list to see what blocks the delete. The delete statement was the only running db process and still its state was Locked. It remained in this state for something like 2 minutes and then finished successfully.

Question: what can make a MySQL process Locked except another process holding a lock?

Below is the output from show processlist:

mysql> show processlist;
+-------+------+-----------------------------------+------+---------+------+--------+---------------------------------------------+
| Id    | User | Host                              | db   | Command | Time | State  | Info                                        |
+-------+------+-----------------------------------+------+---------+------+--------+---------------------------------------------+
| 79616 | htt  | gateway1.xxxxxxxxxxxxxxxxxx:42254 | foo  | Query   |  106 | Locked | delete from xbarApp_testfoo where id = 2279 |
| 79618 | htt  | gateway1.xxxxxxxxxxxxxxxxxx:41009 | foo  | Query   |    0 | NULL   | show processlist                            |
+-------+------+-----------------------------------+------+---------+------+--------+---------------------------------------------+
2 rows in set (0.35 sec)
Rafał Dowgird
  • 133
  • 1
  • 6
  • Does your server still have free disk space? – Janne Pikkarainen Jan 10 '13 at 10:29
  • I do not think it means what you think it means. – Tom O'Connor Jan 10 '13 at 10:29
  • @JannePikkarainen Yup, there are a couple dozen gigabytes free. – Rafał Dowgird Jan 10 '13 at 10:35
  • @TomO'Connor You mean it includes "waiting for system resources" and not only locks held by other processes? – Rafał Dowgird Jan 10 '13 at 10:36
  • if the 2min sound awfully long - check your indices. either id is not indexed, or too many things are indexed and delete will break a sweat constantly rebuilding indices if there are many things with id=2279 (usually "id" would be assumed to be a unique surrogate primary key by convention, but I am not in an assuming mood today :) . If nothing helps - strace it and look at what it is actually doing. – rackandboneman Jan 10 '13 at 10:44
  • @rackandboneman Id was primary. Also, would the process show as "Locked" during the time spent fetching the index? – Rafał Dowgird Jan 10 '13 at 11:01

0 Answers0