MAGENTO: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying .

Getting the following error, when user purchases through paypal from our site.

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

It seems to be very common on google, but no solution. I did replace the most common solution mentioned on the google which was to reply two functions in two of the files in zend and verian but that also didnt do the trick so please dont suggest that and let me know if you have anything else in mind to fix the issue.

Also, i did truncuate the tables in db starting with log and it was also suggested somewhere in google. But no gain.

Orders from ebay are doing fine, but through paypal directly from site does not complete the transaction and users and admin dont receive any confirmation email as well. The order status in admin also becomes pending payment, while the payment is done.

Please advice.


One of the recent deadlocks detected
===================



*** (1) TRANSACTION:
TRANSACTION 0 115282368, ACTIVE 0 sec, process no 28015, OS thread id 140519808034560 inserting mysql tables in use 4, locked 4 LOCK WAIT 19 lock struct(s), heap size 3024, 13 row lock(s), undo log entries 13 MySQL thread id 5761116, query id 276606842 localhost hansab_mhsa Sending data INSERT INTO `sales_flat_order_grid` (`entity_id`, `status`, `store_id`, `customer_id`, `base_grand_total`, `base_total_paid`, `grand_total`, `total_paid`, `increment_id`, `base_currency_code`, `order_currency_code`, `store_name`, `created_at`, `updated_at`, `billing_name`, `shipping_name`) SELECT `main_table`.`entity_id`, `main_table`.`status`, `main_table`.`store_id`, `main_table`.`customer_id`, `main_table`.`base_grand_total`, `main_table`.`base_total_paid`, `main_table`.`grand_total`, `main_table`.`total_paid`, `main_table`.`increment_id`, `main_table`.`base_currency_code`, `main_table`.`order_currency_code`, `main_table`.`store_name`, `main_table`.`created_at`, `main_table`.`updated_at`, CONCAT(IFNULL(table_billing_name.firstname, ''), ' ', IFNULL(table_billing_name.lastname, '')) AS `billing_name`, CONCAT(IFNULL(table_shipping_name.firstname, ''), ' ', IFNULL(table_shipping_name.lastname, '')) AS `shipping_name` FROM `sales
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 194809 n bits 160 index `PRIMARY` of table `hansab_mhsdb`.`sales_flat_order_grid` trx id 0 115282368 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;







==================================

Please help.

 

this one i found on google...

"Findout the function _execute() from tha page “lib/Zend/Db/Statement/Pdo.php”

Replace this function with below lines


/**
* Executes a prepared statement.
*
* @param array $params OPTIONAL Values to bind to parameter placeholders.
* @return bool
* @throws Zend_Db_Statement_Exception
*/
public function _execute(array $params = null)
{
$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 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);
}

Findout the function raw_query() from the page “lib/Varien/Db/Adapter/Mysqli.php”

Replace this function with below lines


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

 

I mentioned in my question that i have tried these. I need some new solution. But thanks anyways. Please someone help me with experience, instead of google, because the specific answer is not on google search although the problem seems to be common.

 

Having exact same issue when ever I start getting ~5-10 orders/second. What I've found so far is that the problem is centered around the order grid. It constantly deadlocks with operations on the invoice and order item tables during post order creation. I've verified that the tables involved are InnoDB, so I really can't see why concurrent orders would effect each other.

I tried the core modification that people have suggested, but then I just get SQL timeout errors instead (I think deadlock happens, then it just keeps retrying until it times-out). The only work-around that has worked so far is commenting out the code for writing to the order grid (in the after save section of the sales abstract model I believe). This has an obvious downside of not having orders appear in order grid right away; I've been mass updating the order grid via SQL on a cron script as a work-around for that.

This is FAR from ideal, and I would love a real solution to this.