0

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?

4
  • This does look odd. Some query forms might assist. What mysql version are you using? Can you reproduce on the command line? – danblack May 5 at 9:39
  • Turn on the General Log long enough to get the exact list of queries performed. – Rick James May 5 at 18:07
  • @RickJames - not possible. This is a production website where we encounter deadlock maybe once every two months at most. Fortunately, SHOW ENGINE INNODB STATUS; provides enough info that I'm now certain of the scenario where this comes up but still need help creating a method of dealing with it when it does. – billynoah May 5 at 20:39
  • @danblack - MySQL 8.0.21 - I'm planning to put in some hours at some point soon trying to reliably reproduce but as you can imagine, it's tricky. – billynoah May 5 at 20:40
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.

7
  • Hey Rick - No there is no FOR UPDATE on any selects - the deadlock is related to something going on in a completely different part of the app. Happens very rarely but I have been able to track down the culprit. What I haven't been able to track down is the correct way to avoid having part of my transaction executed. I thought that is the whole purpose of transaction - that it can't commit only half. – billynoah May 5 at 20:35
  • My best theory right now is that when query 3 encounters the deadlock, somehow that is rolling things back and the remaining queries get executed outside the transaction as normal queries. I'm not sure - it's difficult to reproduce but I'm working on it. – billynoah May 5 at 20:37
  • @billynoah - I added another paragraph. – Rick James May 6 at 1:30
  • Sorry but I'm having trouble making sense of what you wrote there. If a deadlock or any other error occurs, shouldn't every query in the transaction get rolled back? Isn't that the whole point of a transaction? – billynoah May 6 at 1:33
  • @billynoah - MySQL will take care of cleaning up the database; you need to take care of what to do when the deadlock happens -- abort / ignore / retry / etc. And that take client-specific code. – Rick James May 6 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.