PDO inTransaction() returning false after database exception

Question

PDO's inTransaction() is returning false while still in a transaction if a database exception is thrown. This is probably specific to using PostgreSQL. e.g.

try {
    $pdo->beginTransaction();
    $pdo->exec('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
    // ...
    // Cause any PDO exception
    // ...
    $pdo->commit();
} catch (\Exception $e) {
    if ($pdo->inTransaction()) {
        // Never gets here
        $pdo->rollback();
    }
    throw $e;
}

The transaction has definitely not ended because if I start another one I get an exception that there is already a transaction in progress. I haven't tested every type of exception, but it's definitely happening for SQLSTATE[40001]: Serialization failure and primary key violations. Is this expected behavior or is it a bug in PHP?

It seems the only way to know to rollback is to keep a separate variable to know I'm in a transaction, making inTransaction() useless. I've noticed that some open source frameworks (like Doctrine) and applications (like Drupal) keep their own variable for transaction state. Why can't we rely on the driver or database to tell us if a transaction is in progress?

PHP 5.5.32 and PostgreSQL 9.4. Found a two year old related bug report which was closed in an older version of PHP.

See the answer to http://stackoverflow.com/questions/22743357/autorollback-in-postgres-using-pdo ..... from the looks of it the "problem" as it were is PostgreSQL not PHP and that what you are seeing is normal. — Dave, Jun 29 '16 at 15:57

score 5 · Accepted Answer · answered Jun 29 '16 at 19:33

To answer your questions:

Is this expected behavior or is it a bug in PHP?

No this is not expected behavior and must be a bug in the PDO PgSQL extension.

Why can't we rely on the driver or database to tell us if a transaction is in progress?

Because the drivers and databases are created by humans. And humans can make mistakes, when creating such a highly complex application such as a database or a driver for it. To me your problem looks like it isn't an edge case and could also cause serious integrity problems in any db. You might also want to consider to open a ticker on the php bug tracker.

Patched code analysis

However I looked into it a little bit more. Comparing the code from the patch 2 years ago (source) and the current code one (source) shows that nothing has changed since they patched that error. At least not in those direct affected functions, and it might also been a slightly different bug back then. So my guess is that there is something else which of course won't help you solve your problem.

A possible work around the current problem:

You could check if your connection has an outstanding transaction. For this you will need to create a second connection when hitting the exception. First off determine your current connection id.

SELECT pg_backend_pid();

In my case it returned the number 19339. You should save this number before you fire up the query that will cause the Exception. Now in your catch block, you need to look inside the table pg_catalog.pg_stat_activity.

Look up the old connection and see if it's status is

active, idle in transaction or idle in transaction (aborted)

with:

SELECT state FROM pg_catalog.pg_stat_activity WHERE pid=19339;

If it returns idle there is no current transaction for that old connection. If it's one of the upper three, there is a transactions still active. The manual from postgresql says:

active: The backend is executing a query.
idle: The backend is waiting for a new client command.
idle in transaction: The backend is in a transaction, but is not currently executing a query.
idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
fastpath function call: The backend is executing a fast-path function.
disabled: This state is reported if track_activities is disabled in this backend.

The last status indicates the downside to this all. It will only work if the track_activities config flag is set to true.

PDO inTransaction() returning false after database exception

1 Answers1