Unexpected behaviour with InnoDB Transaction retry

Question

This is going to be difficult to reproduce but hopefully someone can shed some light on my problem based on the logic involved. After having some intermittent issues with deadlocks during a transaction I implemented a retry strategy that goes goes something like this (PHP):

public function execute_query($sql) {
    $try     = 1;
    $log_msg = '';
    while (true) {
        $query     = $this->link->query($sql);
        $error_no  = $this->link->errno;
        $error_msg = $this->link->error;
        if (!$error_no){
            if ($try > 1) {
                $this->debug_log->write('Deadlock Succeeded after ' . $try . ' Tries ::: ' . $sql);
            }
            return $query;
        } else {
            $log_msg = 'Error: ' . $error_msg . ' ::: Error No: ' . $error_no . ' ::: ' . ($try > 1 ? $try . ' Tries ::: ' : '') . $sql;
            $this->debug_log->write($log_msg);
            if ($error_no == 1213 && $try < self::DEADLOCK_RETRY) {
                // retry when deadlock occurs
                sleep($try * 2);
                $try++;
            } else {
                throw new ErrorException($log_msg);
                exit();
            }
        }
    }
}

This seems to have had the exact opposite affect that I'd hoped for - which is that it appears the first half of my transaction gets rolled back but then the second half commits.

For example:

START TRANSACTION;
Query 1
Query 2
Query 3 (deadlock occurs, query gets retried and succeeds on 2nd attempt)
Query 4
Query 5
COMMIT;

At the end of all of this I'm left with Query 3, 4, and 5 running successfully but Query 1 and 2 do not. I don't understand how this is possible but it's happened twice now and I haven't been able to reproduce the deadlock to test or develop a working strategy.

Can anyone explain why retrying a failed query within an InnoDB transaction would cause half of the transaction to get rolled back and the other half to commit?

Answer 1

When the deadlock occurs at query 3, the deadlock may involve query 1 or 2. You must start over with the START TRANSACTION and replay all the queries.

If any of those are SELECTs and the result of the SELECT controls what will be subsequently UPDATEd, DELETEd, etc, then do you have FOR UPDATE on the SELECT?

You must check for errors after each and every query in the transaction. If a deadlock occurs, start over, as mentioned above.

What happened

1. The error occurred, so it rolled back the transaction as far as it had gone.
2. The client code ignored the error and plowed ahead.
3. Queries 4,5,6 were run and committed.

What was needed

1. The error occurred, so it rolled back the transaction as far as it had gone.
2. The client code noticed the error.
3. The client code should have code to ROLLBACK and jump back to the START.

(The explicit ROLLBACK is probably redundant but harmless. However, branching back to the START is up to the client code.)

A "deadlock" should be handled that way, but any other "error" should be handled with an abort.

And, since a deadlock can occur after virtually any query, you should test everywhere.