We've been having a whale of a time determining why, all of a sudden, we're getting SQL Deadlocks and extreme slowness in our website. I upped the max_connections to alleviate some "Out of Connections" error we had as well.
We are using ExpressionEngine and Magento, and I know Magento is a database hog and is the ONLY source of the SQL Deadlocks as it is occuring in Magento's PDO.php file when a customer update is being performed.
#0 /store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)#1 /store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)#2 /store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `magcust...', Array)#3 /store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `magcust...', Array)#4 /store/lib/Zend/Db/Adapter/Abstract.php(604): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `magcust...', Array)#5 /store/app/code/core/Mage/Eav/Model/Entity/Abstract.php(1133): Zend_Db_Adapter_Abstract->update('magcustomer_ent...', Array, 'entity_id='9170...')#6 /store/app in /srv/www/htdocs/store/lib/Zend/Db/Statement/Pdo.php on line 235
We authenticate our users through our ERP using a web service and have everyone's Magento account have the same password so that we can log them in automatically. We update their Magento record when we authenticate.
I added code to retry when a deadlock was found but that only caused the system to slow down even more. So, what do you think may be causing our issues?
Here is also the uncommented variables in my PHP .ini file:
That solution will not work for us. The deadlock is coming from a single point, the $customer->save()
in a function called mag_login_or_create() in our ExpressionEngine plugin. I have put in some logging and it seems that the call actually IS being called multiple times at once from the same "user". Not exactly sure why, though.
Steve Bink
Unfortunately, deadlocks are usually a design problem, not a configuration or implementation problem. Resolving these deadlocks will almost certainly involve debugging and modifying the code - something that would best be done by the authors. Have you tried contacting Magento for any assistance?
I'm not familiar enough with Magento to be of any real, specific help. The most I could do is point you in the right direction to isolate the cause of the lock. It sounds like you've already found it.
Perhaps some other experts with more knowledge will chime in.
Eddie Shipman
ASKER
Are you kidding, attempting to get help from them is like pulling teeth from a lion. Their "support" forums are a joke, no one uses them anymore and the Stack Overflow Magento area is not very highly trafficked.
in a function called mag_login_or_create() in our ExpressionEngine plugin. I have put in some logging and it seems that the call actually IS being called multiple times at once from the same "user". Not exactly sure why, though.