I have a situation where I need to use PostgreSQL's serializable isolation level for transactions.  This is for a table shared among multiple concurrent PHP processes.  If the database runs into any sort of data consistency issue PDO will throw an exception.  Makes sense since it's really up to the application to decide what to do.  In my case I effectively want to lock and wait for any other concurrent process to finish.  Based on the hints returned by PostgreSQL messages it's recommended to catch the errors and just try again.My main concern is that I'm arbitrarily retrying 5 times and hoping for no more problems.  Is there a way to write this process more resilient to concurrency issues?public function runInTransaction(callable $callable){    $success = false;    $count = 0;    $this->beginTransaction($isolationLevel);    $this->exec(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE);    try {        while (!$success && $count < 5) {            try {                $callable();                $success = $this->commit();            } catch (\PDOException $e) {                if ($e->getCode() == '40001') {                    // Serialized transaction failure. Try again.                } elseif ($e->getCode() == '25P02') {                    // In failed sql transaction. Rollback and try again.                    $this->rollback();                    $this->beginTransaction($isolationLevel);                } else {                    throw $e;                }            }            $count++;        }    } catch (\Exception $e) {        if ($this->inTransaction()) {            $this->rollback();        }        throw $e;    }    if (!$success) {        throw new \RuntimeException(Could not commit transaction);    }}
Managing PostgreSQL transactions for concurrency
php;pdo;concurrency;postgresql
Taking Craig's suggestion, I've had the following running in a test environment for one week with no issues:public function runInTransaction(callable $callable){    $success = false;    $count = 0;    $this->beginTransaction($isolationLevel);    $this->exec(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE);    try {        while (!$success && $count < 5) {            try {                $callable();                $success = $this->commit();            } catch (\PDOException $e) {                // Catch concurrency issues and retry after                // random scaled back-off delay.                if ($e->getCode() == '40001' || $e->getCode() == '40P01') {                    // Serialized transaction failure or deadlock.                    usleep(rand(100, 200) * ($count + 1));                } elseif ($e->getCode() == '25P02') {                    // In failed sql transaction.                    $this->rollback();                    usleep(rand(100, 200) * ($count + 1));                    $this->beginTransaction($isolationLevel);                } else {                    throw $e;                }            }            $count++;        }    } catch (\Exception $e) {        if ($this->inTransaction()) {            $this->rollback();        }        throw $e;    }    if (!$success) {        throw new \RuntimeException(Could not commit transaction);    }}