0

I have a mysql DELETE query where the WHERE clause uses the primary key with IN:

delete from my_table where id in (4, 900, 700);

I am getting deadlock errors. I don't understand why this is, or how to avoid it. No other queries are selecting for these IDs, although other queries are selecting/deleting/adding from the same table. The PK id is specific for a single user so no one else is using these same IDs. I thought that using the primary key would prevent the delete statement from locking other rows.

Edit: engine is innoDB

user984003
  • 28,050
  • 64
  • 189
  • 285
  • Has this question's answer helped? http://stackoverflow.com/questions/2596005/working-around-mysql-error-deadlock-found-when-trying-to-get-lock-try-restarti – zedfoxus Dec 13 '15 at 18:26
  • No. I want to avoid Deadlock errors as much as possible, not just write try/catch around my code and keep trying, as that answer suggests. – user984003 Dec 13 '15 at 19:00
  • I'd recommend enabling general logging to capture queries. When deadlocking occurs, look up other queries that are running to understand why deadlock is happening. That might help you find ways to avoid deadlocking. Without doing a deep dive it'll be difficult to answer your question. – zedfoxus Dec 13 '15 at 19:08

1 Answers1

0

First of all, which storage engine is it ? And how many seconds does it last to give a deadlock error ?

If its myisam, the engine has table level locking, so most operations can block the table. If it lasts more than max query timeout for your mysql instance, it can throw the error.

Additionaly, you can use pt-deadlock-logger of Percona, to get some help finding the problem.

abeyaz
  • 3,034
  • 1
  • 16
  • 20
  • How much time does the query take to fail ? And does it fail for other id numbers too ? If they dont, table might be corrupted. – abeyaz Dec 13 '15 at 22:40
  • I don't know how long it takes. Or where to see that. I don't think the table is corrupted - it's only an occasional fail. There doesn't seem to be a pattern in what IDs fail. – user984003 Dec 13 '15 at 23:02
  • If you use mysql 5.6, you can activate performance-scheme to track such things. I also recommend 3rd party tools like [monyog](https://www.webyog.com/product/monyog), to analyze your queries and problems. Then, you can tune mysql variables to avoid such issues. pt-deadlock-logger might help, as well. – abeyaz Dec 14 '15 at 08:06