3

After placing an order Magento triggers a price reindex (which sometimes takes too long) and fails placing the order (because reindex is part of the transaction):

> SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try
> restarting transaction SQL Query: INSERT INTO
> `catalog_product_index_price_tmp` SELECT
> `catalog_product_index_price`.* FROM `catalog_product_index_price`
> WHERE (entity_id IN('39074', '39075', '39076', '39077', '39078',
> '49702', '49703', '49704', '49705', '49706', '49708', '52288',
> '52289', '52291', '52292')) SQL Params: Array ( )

And, because there is an email if the transaction fails, this seems to be expected (otherwise this email wouldn't be implemented).

In order to fix this, I moved the reindex outside the transaction, but I am still puzzled why this is needed.

3

Magento EE support suggested to me that this has to do with tier price availability and catalog pricing rules.

Magento does something similar with product save - whenever a product is saved it clears the cache. If you're using Memcached, for instance, you have no keys or cache tags, so the entire index is cleared. There is a pretty dirty hack that I have seen, and used on occasion, that doesn't force the cache clear but rather marks the cache as invalidated, needing to be refreshed.

So getting back to the point, you can achieve the same result by changing the index mode to 'manual' and running it yourself or creating a cron to reindex on a schedule, preferably during your off-peak hours. An example of such a script is included below:

<?php 

class MyCompany_MyModule_Model_Cron {
    public function schedule() {
        //find dirty indexes and process
        for($i=1;$i<10;$i++){
            $_process = Mage::getSingleton('index/indexer')->getProcessById($i);
            $_status = $_process->getStatus();
            if($_status!='pending'){
                $_process->reindexEverything();
            }
        }
     }
}
  • ok, so it seems it's related to tier pricing (when the price in the product list is: starting from ...)... so unless you are using tier pricing then you don't need it. Regarding the indexing, a better way (in my opinion) is async indexing (there are a few extensions that do this). – FlorinelChis Feb 13 '13 at 11:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.