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)?

Answer 1

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.

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

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.

Answer 4

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();
}

Answer 5

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.