2
\$\begingroup\$

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");
    }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ You should be testing the SQLSTATE and retrying only if it's a possibly transient error (deadlock, serialization failure). Which you are, though you should handle deadlock detected too. And add a bit of a random scaled back-off delay so different runs don't conflict the same way at the same time when they retry. It's all a bit of a pain, really, but that's concurrent systems for you. \$\endgroup\$ – Craig Ringer Jun 22 '16 at 22:16
  • \$\begingroup\$ @CraigRinger "random scaled back-off delay" is a really interesting idea. So like a random sleep that gets longer with each iteration of the loop? \$\endgroup\$ – Matt S Jun 23 '16 at 13:18
  • 1
    \$\begingroup\$ @MattS If I understand it correctly you hope that random sleep different for each concurrent process so the situation is not repeating exactly the same. \$\endgroup\$ – Jan Korous Jun 28 '16 at 22:46
0
\$\begingroup\$

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");
    }
}
\$\endgroup\$

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.