$driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
$this->pdo->beginTransaction();
try {
// SERIALIZABLE transactions fail if multiple of them
// even try to touch the same rows at the same time
if ($driver == "pgsql") {
$isolation = $this->pdo->prepare(
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
);
$isolation->execute();
}
$deleteStmt = $this->pdo->prepare(
"DELETE FROM $this->table WHERE $this->idCol = :id"
);
$deleteStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
$deleteStmt->execute();
$insertStmt = $this->pdo->prepare(
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)"
);
$insertStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
$insertStmt->bindParam(':data', $encoded, \PDO::PARAM_STR);
$insertStmt->bindValue(':time', time(), \PDO::PARAM_INT);
$insertStmt->execute();
$this->pdo->commit();
} catch (\PDOException $e) {
// If the error code was 40001 (serialization_failure)
// then RETRY.
if ($driver == "pgsql" && $e->getCode() == "40001") {
$this->pdo->commit();
} else {
$this->pdo->rollback();
throw $e;
}
}
Postgres' SERIALIZABLE
transaction mode makes things behave like Optimistic concurrency
if ($driver == "pgsql") {
$isolation = $this->pdo->prepare(
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
);
$isolation->execute();
}
If two (or more) transactions try touching the same rows, a SERIALIZATION_FAILURE
error (code 40001) is thrown. In the exception handling:
if ($driver == "pgsql" && $e->getCode() == "40001") {
$this->pdo->commit();
} else {
$this->pdo->rollback();
throw $e;
}
You just retry the transaction. No harm done.
Btw, your retry-logic actually does nothing. Just commiting again, will commit nothing. You need to reexecute the statements.