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?
Best Answer
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 theSELECT
controls what will be subsequentlyUPDATEd
,DELETEd
, etc, then do you haveFOR UPDATE
on theSELECT
?You must check for errors after each and every query in the transaction. If a deadlock occurs, start over, as mentioned above.
What happened
What was needed
ROLLBACK
and jump back to theSTART
.(The explicit
ROLLBACK
is probably redundant but harmless. However, branching back to theSTART
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.