3

I recently upgraded our magento 1.7 database server from MySQL 5.4 to Percona 5.6 in the interest of performance.

As expected, the site and general performance of magento has increased vastly, but at the cost of these errors occurring more frequently than before:

Lock wait timeout exceeded; try restarting transaction

So, I begun a research into this error & issue and it has been frequently discussed. One point that interests me are the following:

https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

By default, percona uses REPEATABLE READ isolation level. However, during my research there has been numerous recommendation (for e.g. https://stackoverflow.com/a/23799980/2332336) that favours READ_COMMITED isolation level combined with binlog-format = ROW over the default.

I wanted to ask; if I were to change the percona server's isolation level as per recommendation, are there any serious implications? Like, will it work? Will it help? Will it break magento?

The changes I am considering for the percona56 are the following:

  • isolation-level = READ_COMMITED
  • binlog-format = ROW
  • innodb_lock_wait_timeout = 120 (currently default value 50)

Should I also be considering other changes to prevent these lock wait timeout errors?

P.S. the magento has a cron job that runs every 15 minutes to load stock level/product updates from a FTP server (third party warehouse software) and imports it into magento, I believe this is what's causing the timeout (and sometimes deadlocks) to occur.

  • Hello from the future :-) I'm dealing with the same problem with Magento 1.9 and Percona 5.7. I'm also reasearching side-effects of isolation-level = READ_COMMITED and binlog-format = ROW. Did you try it? any problem? actually fixed or not? thanks! – Dr. Gianluigi Zane Zanettini Mar 28 '17 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.