Magento deadlocks

Question

I am using Magento 1.7.0.2 Community Edition and I have encountered a big problem - deadlocks and "Lock wait timeout exceeded" errors. Problem exists while specific CRON tasks are executed

• Importing/updating products(sizes, colors, manufacturers as well). There are around 5000 products but in 90% script gets "Lock wait timeout exceeded" errors or a deadlock error. Script is developed using Magento guidelines and it works fine if no other processes are running. For example if reindex is running, we get an error for sure. It seams that is because of table locks
• Magento puts a read lock in some cases. I have read several topics about this already and the only proper solution seams to be changing /lib/Zend/Db/Statement/Pdo.php _execute function. As we are looking forward for upgrading Magento to the latest stable version we can`t afford changing core files.

So my question - is there a way how to avoid this(whether on PHP, MySQL or server(we use nginx) level)?

Nahamas

Why not split the amount of products up in smaller chunks? – Moriarty

I already thought about that but there is also a problem with that because products are retrieved via 3rd party Soap service and they dont provide smaller chunks. Of course we can chunk it on our side but either way this update process should run almost forever to ensure most latest data from external product stock. – Nahamas

Plus chunking the data into smaller pieces would give the same result if we spawn child processes using fork mechanism. In this case each child process would write/read the same tables and would end up with an error – Nahamas

You will get a deadlock if you're reindexing at the same time you're importing. Both processes are hammering on the same files which means that timing them not to step on each other's toes is really important. – Thymic

After each product import/update I am calling re-index for this specific product. Full re-index is made only once a day. Does it could couse the problem? – Nahamas

Answer 1

I came across this issue whilst trying to import more than five or six products at once. There is more information on deadlocks available here.

To solve this problem I had to place my database queries in SERIALIZABLE transactions where possible, like so:

$adapter = Mage::getModel('core/resource')->getConnection('core_write');
// Commit any existing transactions (use with caution!)
if ($adapter->getTransactionLevel > 0) {
    $adapter->commit();
}
$adapter->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$product->save(); // etc

Transaction example:

$adapter = Mage::getModel('core/resource')->getConnection('core_write');
// Commit any existing transactions (use with caution!)
if ($adapter->getTransactionLevel > 0) {
    $adapter->commit();
}
$adapter->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$adapter->beginTransaction();
try {
    $adapter->query(/* SQL goes here */);
    $adapter->commit();
} catch (Exception $e) {
    // Rollback on fail always
    $adapter->rollBack();
    throw $e;
}

If you require any further help on this, feel free to let me know.

Apices

Does this applies using Mage::getModel() instead of simple queries? – Nahamas

Magento does make use of transactions behind the scenes (when saving the resource of a particular model). You can try the first option (set transaction isolation level) and then save/edit models as you wish. – Apices

Bump. This is an amazing fix. It's literally as easy as it sounds - get the adapter and set the isolation level ahead of your save. This fixed over a year of headaches for my team! +1 – Huygens

Interested in implementing this solution for a large Magento store - please could you advise where we need to implement this? Thanks in advance. – Coupling

@GeoffJackson philwinkle packaged this in an extension: github.com/philwinkle/Philwinkle_DeadlockRetry. You can also set the transaction isolation level in /etc/my.cnf – Apices

That's great, thanks guys. We've just installed the extension to check out. :) – Coupling

According to Wikipedia, this isolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. . Which in turn means that db performance will be impaired considerably, am I right? – Jurisconsult

This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work. By doing such be sure and keep regularly daily db backups, because if it corrupts then restoration may prove difficult. The problem stems from MySQL itself NOT Magento. #6269971 But be very cautious of the side effects: ovaistariq.net/597/… – Colon

Answer 2

We had a similar issue with deadlocks popping up a few times a day when customers were attempting to add something into their cart. Ours as well seemed to be related to an index being refreshed at that time (most likely a reindex of the catalog table). The only thing that finally took care of the issue for us was to implement asynchronous re-indexing (we ended up purchasing an extension).

Wither

Yes, we purchased it as well – Nahamas

BUt even this can introduce a new problem: they often come with "validators" that detect changes and even then reindex: when this is the case (often later) a new customer could run into the same problem – Pantheas

Answer 3

we ran into this problem as well trying to save products in parallel.

the main problem we faced was that after a product has been saved initially, the indexing processes which followed were not covered by the product-saving transaction. so whenever we ran into a deadlock it was caused by the indexer and to top it of we had an inconsistent database which caused invalid product-urls and forced us to reindex everything each time it happened.

the solution we ended up with was to include the indexer into the transaction and to retry a transaction which ended up in a deadlock. however this is not an ideal solution, its rather the best we could come up with and it works in 99% of the time.

the problem with magento is that the programming is slopy and the event-driven swiss-army-knife approach to coding results in many profound problems in the inner mechanics of magento.

our next approach will be our own interface, written from scratch, to save products in parallel and leave the database with the same result as a magento save would have. this of course will mean we can no longer add extensions without integrating them into this new concept in case they are product related.

Emersonemery

Answer 4

Here is great open source solution - https://github.com/AOEpeople/Aoe_DbRetry

This module is very simple and focus on one task. It replaces the DB adapter with an extended version that will retry queries if the connection is lost, the query cannot obtain a needed lock, or a deadlock occours. These three situations are detected via exception messages. The underlying (parent) code actually wraps at least one of these exceptions up inside another exception, so we check for that and unwrap the exception if needed.

Tartary

Answer 5

I turn my index mode to MANUAL before importing with CRON scripts.

$indexCollection = Mage::getSingleton('index/indexer')->getProcessesCollection(); 
foreach ($indexCollection as $process) {
    $process->setMode(Mage_Index_Model_Process::MODE_MANUAL)->save();
    //$process->setMode(Mage_Index_Model_Process::MODE_REAL_TIME)->save();
}

Martinmas