Read more

Hints for debugging MySQL InnoDB deadlocks

Tobias Kraze
December 03, 2010Software engineer at makandra GmbH

Deadlocks only occur if two transactions in separate threads compete for the same rows in the database. They usually (but not necessarily) only happen when trying to update or otherwise lock several rows in different order.

Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

Solving deadlocks is potentially complicated, so here are a few pointers:

  • MySQL should always detect the deadlock right when it happens, and will throw an error to one of the offending threads. This error states the SQL statement that this thread was currently waiting for, and that tried to acquire one of the competing locks.

  • First, always go through the exception backtrace of your application, and look out for code that might operate on data it actually shouldn't. Chances are, you are not properly scoping something.

  • To get a rough idea what was actually going on, you can run

SHOW ENGINE INNODB STATUS

This will show you the most recent deadlock, including the two competing queries and a list of the locks held by both transactions. The output is cryptic, though.

  • If you still cannot see what actually happened, you might need to get better acquainted with MySQLs locking model. While it keeps out of your way usually, it's actually pretty complex. The relevant section of the official manual Show archive.org snapshot is pretty good.

  • MySQL might lock many more rows than you might expect, especially when you do not have proper database indexes for the relevant statement. For updates without indexes, every row in a table gets locked, for example.

Posted by Tobias Kraze to makandra dev (2010-12-03 12:09)