SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Question

Running on Magento 1.8

We are experiencing this error while creating product in our backend. It hinders the process of creating products and we have to keep trying in order to have the one product created.

Answer 1

I've got to clear up a few comments, misinformation in Magento is rife.

1. Do not increase timeouts, that will only make you wait longer before being given the same message.

2. Do not install a 3rd party extension to hide the issue, it will simply mean the locks will occur randomly throughout the day, rather than when you save a product.

3. Do not kill queries that hold locks. By doing so, you are effectively causing inconsistencies in your database. If a query holds a lock, it's because it needs to, killing it means killing the reindex process it was trying to complete.

The issue is that Magento needs to lock some tables and rows in others, in order to rebuild product indexes. There's nothing you can do to change this behaviour other than have it happen at a less critical time (ie. outside of production hours).

The simplest solution is to merely set all indexes to manual, then run a single large reindex when you are complete.

Answer 2

TRUNCATE `log_customer`;
TRUNCATE `log_quote`;
TRUNCATE `log_summary`;
TRUNCATE `log_summary_type`;
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;

Truncating these tables worked for me.