PDO inTransaction () returns false after database exception

Question

PDO inTransaction() returns false during a transaction if a database exception is thrown. This is probably due to the use of PostgreSQL. eg.

 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 definitely did not end, because if I start another, I get an exception that the transaction is already in progress. I have not tested all kinds of exceptions, but this definitely happens for SQLSTATE[40001]: Serialization failure and primary key violations. Is this the expected behavior or is it a bug in PHP?

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

PHP 5.5.32 and PostgreSQL 9.4. Found a two-year report bug report , which was closed in the old version of PHP.

Answer 1

To answer your questions:

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

No, this is not the expected behavior and should be a mistake in the PgSQL PDO extension.

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

Because drivers and databases are created by people. And people can make mistakes when creating such a complex application as a database or a driver for it. For me, your problem looks like this is not the edge, and can also cause serious integrity problems in any db. You might also want to consider opening a ticker on the fleur.

Corrected Code Analysis

However, I looked at it a little more. Comparing the code with the patch 2 years ago ( source ) and the current code one ( source ) shows that nothing has changed since they fixed this error. At least not in those directly affected functions, and then this may be a slightly different error. Therefore, I assume that there is something else that, of course, will not help you solve your problem.

Possible work around the current problem:

You can check if your connection has an outstanding transaction. To do this, you will need to create a second connection when removing the exception. First determine the current connection identifier.

SELECT pg_backend_pid();

In my case, he returned the number 19339 . You must save this number before you run a query that raises an exception. Now in your catch block you need to look into the pg_catalog.pg_stat_activity table.

Look at the old connection and see if it has status

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

from:

SELECT state FROM pg_catalog.pg_stat_activity WHERE pid=19339;

If it returns idle , there is no current transaction for this old connection. If this is one of the top three, transactions are still active. The postgresql manual 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 latter status indicates a disadvantage of this. It will only work if the track_activities flag is set to true .