Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 3
[solved] Database deadlocks and serialization failures—fix inside
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

In order to fix this error replace the _execute() in lib/Zend/Db/Statement/Pdo.php. What it does is retries the query.

public function _execute(array $params null)
    
{
        
// begin changes
        
$tries 0;
        do 
{
            $retry 
false;
            try 
{
                
if ($params !== null{
                    
return $this->_stmt->execute($params);
                
else {
                    
return $this->_stmt->execute();
                
}
            } 
catch (PDOException $e{
                
#require_once 'Zend/Db/Statement/Exception.php';
                
if ($tries 10 and $e->getMessage()=='SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction'{
                    $retry 
true;
                
else {
                    
throw new Zend_Db_Statement_Exception($e->getMessage());
                
}
                $tries
++;
            
}
        } 
while ($retry);
        
// end changes
    
}

Also, to replace a bug in Zend that can cause an infinite loop replace raw_query() in lib/Varien/Db/Adapter/Mysqli.php with the following:

$tries 0;
        do 
{
            $retry 
false;
            try 
{
                $this
->clear_result();
                
$result $this->getConnection()->query($sql);
                
$this->clear_result();
            
}
            
catch (Exception $e{
                
if ($tries 10 and $e->getMessage()=='SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction'{
                    $retry 
true;
                
else {
                    
throw $e;
                
}
                $tries
++;
            
}
        } 
while ($retry);

        return 
$result;
    
}

The only thing changed is the above is $tries is moved outside the loop so it is not reset on every loop and it ensures an exception is thrown after 10 tries.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Junaid Q
Jr. Member
 
Total Posts:  4
Joined:  2010-02-14
 

I m keeping my fingers crossed!

 
Magento Community Magento Community
Magento Community
Magento Community
 
codegreene
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2010-02-23
 

Thank you so much… This was the only post related to the Lock Wait Timeout errors that made any difference for us. Before those two bug fixes a checkout was taking anywhere from 45 seconds to 5 minutes, Now it’s down under 10 seconds like it should be. Thank you!

 
Magento Community Magento Community
Magento Community
Magento Community
 
whaaa
Jr. Member
 
Total Posts:  9
Joined:  2010-06-12
 

Can anyone help me? I don’t see “the_execute()” anywhere in my Pdo.PHP like he mentions.

 
Magento Community Magento Community
Magento Community
Magento Community
 
MAtt*!
Jr. Member
 
Total Posts:  10
Joined:  2008-07-30
 
codegreene - 10 June 2010 02:06 PM

Thank you so much… This was the only post related to the Lock Wait Timeout errors that made any difference for us. Before those two bug fixes a checkout was taking anywhere from 45 seconds to 5 minutes, Now it’s down under 10 seconds like it should be. Thank you!

We have experienced this error on order submit 3 times now. I have just implemented the above fix for 1.4.0.1 on my site to hope to stop it.

The problem we are facing (before we try this code) is the credit card is charged twice, as the existing code does retry and errors with SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, I guess.

My question is, if the retry is up to 10 times no, does this mean the credit card will be charged up to the amount of retries until successful or failure detected?

Appreciate your response!

MAtt

* I have defiantly checked, the user didn’t hit order button twice grin *

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ben Marks
Moderator
 
Avatar
Total Posts:  452
Joined:  2008-10-09
Charleston, SC
 

@MAtt - it is wise no matter what to implement rate limiting at your gateway (Authorize.Net, etc.) - for those users who do hit that button twice wink

 
Magento Community Magento Community
Magento Community
Magento Community
 
knarz
Member
 
Total Posts:  43
Joined:  2009-07-11
 

I just applied this fix for 1.4.1.1 - will leave a message if or if it not happens again.

 
Magento Community Magento Community
Magento Community
Magento Community
 
recoba
Jr. Member
 
Total Posts:  1
Joined:  2009-12-23
 

Does It work for you knarz ???

I hope yes, cause we have no more clues for that issue !!

 
Magento Community Magento Community
Magento Community
Magento Community
 
knarz
Member
 
Total Posts:  43
Joined:  2009-07-11
 

It seems to work for me yes. At least for today!

But i can not guarantee because this error occured only sometimes, i think it was a conflict between my automatic distributor import and the nightly backup of my server system.

Just try it out smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
bcp_vps
Jr. Member
 
Total Posts:  10
Joined:  2010-12-05
 

I have seen the Lock Wait error as well as the Serialization error show up occasionally on our site in the form of transaction error emails and log messages.  I applied both these fixes today on our 1.5.0.1 install.  Hopefully it’ll fix it.  Thanks for posting this!

 
Magento Community Magento Community
Magento Community
Magento Community
 
yacker
Jr. Member
 
Total Posts:  4
Joined:  2011-07-21
 

We are also seeing many deadlocks happening during the check out process even with the fix above.  We are on 1.5.0.1 and the deadlocks come up once every few days.  I caught it happening once and saw there were 2 queries trying to save the order at the same time.  INSERT INTO SALES_FLAT_ORDER table.  Anyone have any idea how to fix it without retrying? That seems like such a hack and really it’s not working for us anyway.  Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
bcp_vps
Jr. Member
 
Total Posts:  10
Joined:  2010-12-05
 

It seems we’re still having the issue as well, although I believe it did help a bit as the errors aren’t showing up quite as frequently.  Anyone else find a true fix for this yet?

In our case, it seems that 99% or the time the error occurs when trying to walk the attributes of the Customer model before the transaction is saved.  Here’s a snippet from the log:

2012-02-19T17:44:44+00:00 ERR (3):
exception ‘Mage_Eav_Model_Entity_Attribute_Exception’ with message ‘SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction’ in /chroot/home/valuepet/valuepetsupplies.com/
html/app/code/core/Mage/Eav/Model/Entity/Abstract.php:642
Stack trace:
#0 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Eav/Model/Entity/Abstract.php(1503): Mage_Eav_Model_Entity_Abstract->walkAttributes(’backend/beforeS...’, Array)
#1 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Customer/Model/Entity/Customer.php(74): Mage_Eav_Model_Entity_Abstract->_beforeSave(Object(Mage_Customer_Model_Customer))
#2 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Eav/Model/Entity/Abstract.php(1011): Mage_Customer_Model_Entity_Customer->_beforeSave(Object(Mage_Customer_Model_Customer))
#3 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Core/Model/Abstract.php(318): Mage_Eav_Model_Entity_Abstract->save(Object(Mage_Customer_Model_Customer))
#4 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Core/Model/Resource/Transaction.php(150): Mage_Core_Model_Abstract->save()
#5 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Sales/Model/Service/Quote.php(183): Mage_Core_Model_Resource_Transaction->save()
#6 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Sales/Model/Service/Quote.php(229): Mage_Sales_Model_Service_Quote->submitOrder()
#7 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/local/TM/FireCheckout/Model/Type/Standard.php(973): Mage_Sales_Model_Service_Quote->submitAll()
#8 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/local/TM/FireCheckout/controllers/IndexController.php(570): TM_FireCheckout_Model_Type_Standard->saveOrder()
#9 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): TM_FireCheckout_IndexController->saveOrderAction()
#10 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(253): Mage_Core_Controller_Varien_Action->dispatch(’saveOrder’)
#11 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#12 /chroot/home/valuepet/valuepetsupplies.com/html/app/code/core/Mage/Core/Model/App.php(340): Mage_Core_Controller_Varien_Front->dispatch()
#13 /chroot/home/valuepet/valuepetsupplies.com/html/app/Mage.php(627): Mage_Core_Model_App->run(Array)
#14 /chroot/home/valuepet/valuepetsupplies.com/html/index.php(80): Mage::run(’’, ‘store’)
#15 /chroot/home/valuepet/valuepetsupplies.com/html/lightspeed.php(35): include_once(’/chroot/home/va...’)
#16 {main}

 
Magento Community Magento Community
Magento Community
Magento Community
 
Anton Makarenko
Magento Team
 
Avatar
Total Posts:  184
Joined:  2008-05-13
Los Angeles, CA
 
ShopGuy - 10 February 2009 01:28 PM

In order to fix this error replace the _execute() in lib/Zend/Db/Statement/Pdo.php. What it does is retries the query.
...
Also, to replace a bug in Zend that can cause an infinite loop replace raw_query() in lib/Varien/Db/Adapter/Mysqli.php with the following:

The only thing changed is the above is $tries is moved outside the loop so it is not reset on every loop and it ensures an exception is thrown after 10 tries.

This is a workaround, a very dangerous one.

A true solution is to design application in such a way, that collisions will not occur at the first place.

I have found a problem in sales model that caused deadlocks during orders placement. The issue was that it tried to write into sales_flat_order_grid table within transaction right after insert into sales_flat_order table. With concurrent queries it caused locking collisions. While making DB adapter “retry” query could mitigate the problem a bit, the real solution is to move stuff of sales_flat_order_grid out of the transaction.

The patch is attached. It applies for Magento CE v1.4.1.0 / EE 1.8.0.0 and above. (EE users, please contact support for officially supported patch version)

File Attachments
sales_grid.zip  (File Size: 2KB - Downloads: 1073)
 
Magento Community Magento Community
Magento Community
Magento Community
 
sebest
Jr. Member
 
Total Posts:  5
Joined:  2008-02-13
 

@ Anton Makarenko :

Hi, sorry for my english ...
Is someone tried your code ?
Can we change the code in Abstract.php ?
Could you give us the ‘new’ code of Abstract.php ?

Thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jvanzijl
Jr. Member
 
Total Posts:  1
Joined:  2012-01-06
 

I had the error the patch is referring to, so i want to apply the patch.

I have a question about the patch tho:

you are removing the _aftersave method and replacing it by the afterCommitCallback method. i dont know exactly how the zend framework is working, but im assuming that the calling of this method is already going good? i mean how does the calling class know that he has to call the afterCommitCallback instead of the _aftersave method. unless he overrides the methods or something?

and i think the version numbers are switched? CE 1.8.0.0?

Hope someone can answer this question, i want to apply the patch but im a bit unsure about it..

 
Magento Community Magento Community
Magento Community
Magento Community
 
CommerceStack
Jr. Member
 
Avatar
Total Posts:  4
Joined:  2010-01-27
Phoenix, AZ
 

We just spent two days tracking this exact issue down during load testing. We’d constantly get deadlocks when the system was getting in the neighborhood of 40-50 orders per minute. This patch fixed the problem 100% for us. It’s criminal that, 5 years later, it still hasn’t been merged into the code base.

I highly recommend giving it a shot.

Doug

Jvanzijl - 15 July 2012 10:33 PM

I had the error the patch is referring to, so i want to apply the patch.

I have a question about the patch tho:

you are removing the _aftersave method and replacing it by the afterCommitCallback method. i dont know exactly how the zend framework is working, but im assuming that the calling of this method is already going good? i mean how does the calling class know that he has to call the afterCommitCallback instead of the _aftersave method. unless he overrides the methods or something?

and i think the version numbers are switched? CE 1.8.0.0?

Hope someone can answer this question, i want to apply the patch but im a bit unsure about it..

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 3