MariaDB / PDO / PHP 1020 error ("Record has changed since last read in table")

Question

How do I correctly deal with 1020 errors within a transaction in MariaDB?

I recently encounter 1020 "Record has changed since last read in table" errors on code that run without this error for 20 years. It is hard to believe that the "record has changed since last read in table" because the failing SQL query is only for records used by one user (visitor):

DELETE FROM vbshoppingcart where visitor_id = :visitor_id AND businessunitID = :businessunitID

Anyway, when this error occurs I try to catch it and run the query again, because it actually should be a temporary failure:

$this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
$this->dbh->beginTransaction();

// other queries within the same transaction will precede the query in question

$retry_attempts = 3;
$retry_wait_microseconds = 100000; // 100ms

while ($retry_attempts > 0) {
  $this->stmt = $this->dbh->prepare($this->query);
  $this->bindparams();
        
  try {

    $returnvalue = $this->stmt->execute();
    $this->clearBindvalues();

    return $returnvalue;
            
  } catch (\PDOException $e) {
    $error_code = $e->getCode();
    $error_message = $e->getMessage();

    if ($error_code === 'HY000' && strpos($error_message, '1020') !== false) {
      // Retry in case of 1020
      error_log("Retry because of error 1020 – remaining: $retry_attempts.");

      usleep($retry_wait_microseconds);
      $retry_attempts--;
      continue;
    }

    // If no possibility that the query can be successfully executed clear the bind values in order to have the object ready for additional DB queries

    $this->clearBindvalues();
        
  }
            
  throw new \Exception('PDO error occurred. Could not perform desired database interaction.');
        
}

// other queries within the same transaction will follow the query in question

try {

  // At the end the transaction will be committed:
  $result = $this->dbh->commit();

} catch (\PDOException $e) {

  error_log("Commit failed: " . $e->getMessage());
  $this->dbh->rollBack();

  throw new \Exception("Transaction commit failed: " . $e->getMessage());
}

I run this in a transaction (as shown in the code) with other queries before and after the query that sometimes causes trouble. If a 1020 error is thrown, always the first retry will already be executed successfully and the script goes on. At the end, the transaction will appear to be successfully committed (according to the logs), but in reality the entries will not be written to MariaDB.

Answer 1

You can look into innodb_snapshot_isolation.

You can try

SET innodb_snapshot_isolation = 'OFF'

but that's only a work-around. A good way to fix this in a more elegant manner is to turn to the logs see here and see what queries have been executed. If you have a backup from a recent time before the issue occurred and you know what queries were being executed, then you can import the backup into a copy and monitor the affected data as you execute the queries. The most important information from here is what has been executed after the start of the transaction and before the error. You may have a trigger that is issued just before you start the transaction and which finishes its work during the transaction.