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:
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.