Payment Deadlock issue

Question

This is coming in an email to the client after the transaction:

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

Answer 1

This comes up frequently for high-volume stores (or perhaps just high concurrency stores) from my experience. I built a (somewhat hacky) workaround and it is available on Github.

I've also discussed in other answers that the immediate retry does not solve issues because two contentious locks will always be immediately retrying. It's up to chance at that point if both make it though - and admittedly sometimes they do make it through.

My most successful patch for the deadlock issues on Magento < 1.8CE/1.13EE have been to use the same retry pattern as proposed by the now-famous Magento Forum post and instead delay the retry by successively longer periods of time. This is known as exponential backoff strategy. In the most extreme situations the customer can be forced to wait as long as 60 seconds for order completion but I have always seen locks of this nature end with successful orders and not deadlocked sessions.

For the post that gives a demo of such code see here:

https://magento.stackexchange.com/a/380/336

For the module (already mentioned by Brent) see here: https://github.com/philwinkle/Philwinkle_DeadlockRetry

Answer 2

Good old deadlocks. We love these...

The first thing we tried after receiving these was to attempt to re-run the query as recommended by the error. To do this we modified Zend_Db_Statement_Pdo::_execute

public function _execute(array $params = null)
{
    $max_tries = 3;
    $tries = 0;
    do {
        $retry = false;
        try {
            if ($params !== null) {
                return $this->_stmt->execute($params);
            } else {
                return $this->_stmt->execute();
            }
        } catch (PDOException $e) {
            if ($tries < $max_tries && $e->getMessage()=='SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction') {
                $sql = $this->_stmt->queryString.' '.print_r($params, true);
                // we actually have some custom logging here too
                $retry = true;
                sleep(1);
            } else {
                throw new Zend_Db_Statement_Exception($e->getMessage(), (int) $e->getCode(), $e);
            }
            $tries++;
        }
    } while ($retry);
}

This produced reasonable success with regards to preventing the failure, but obviously we were still running into a lot of deadlocks. The other thing we have tried, which seemed to significantly reduce the number of deadlocks written to the log (and thus encountered) was to move one of the function calls from _afterSave to afterCommitCallback. You can actually find both of these approaches discussed in the forum. The second was recommended by Anton Makarenko.

Answer 3

I have added Phil Jacksons ext

https://github.com/philwinkle/Philwinkle_DeadlockRetry