php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #80260 PDO::inTransaction() returns false when inside transaction
Submitted: 2020-10-20 03:36 UTC Modified: 2020-10-28 09:18 UTC
From: corey dot taylor dot fl at gmail dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 8.0.0RC2 OS:
Private report: No CVE-ID: None
View Developer Edit
 [2020-10-20 03:36 UTC] corey dot taylor dot fl at gmail dot com 
Description:
------------
We started seeing failures with php 8 and mysql 5.7 that seem to suggest PDO::inTransaction() is returning false when inside a transaction.

When calling PDO::beginTransaction(), sometimes we see this error:

PDOException: There is already an active transaction

This happens because we skip calling PDO::commit() if PDO::inTransaction() returns false. When skipping this check and calling commit(), the transaction succeeds. Normally, when that check returns false, commit() will fail.

I see that inTransaction() for mysql was re-written recently in this commit:
https://github.com/php/php-src/commit/6a4eeb1c47c46d8f6a1c43eb7de8e6ee8158ec99

We're not sure how to find the root cause of this. The test case in the commit checks all ways to manually start/stop a transaction, but we obviously have other queries in the transaction.

Patches

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-10-20 03:58 UTC] corey dot taylor dot fl at gmail dot com 
The environment we see this on is ubuntu 16.04 (xenial) travis-ci with default mysql 5.7 and php nightly builds. There are no custom pdo configurations.
 [2020-10-20 08:40 UTC] nikic@php.net 
Do you mix usage of PDO transaction APIs with transaction queries? Like, call PDO::beginTransaction() and then issue a manual COMMIT?

PDO's internal state could go out of sync in such a case. We should probably make PDO internally use the same mechanism as PDO::inTransaction() to determine whether it is inside a transaction.

However, I'm not sure this is really the case you're seeing, I suspect that is another problem here.
 [2020-10-20 08:44 UTC] nikic@php.net 
Okay, next guess: You are running a DDL query inside a transaction, which (for MySQL) will cause an automatic commit, even outside autocommit mode (https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html). That means inTransaction() reports false, but PDO still thinks its inside a transaction.

Does that sound plausible?
 [2020-10-20 08:50 UTC] corey dot taylor dot fl at gmail dot com 
I spent some time trying to narrow down the scenarios as you mentioned. It's difficult when it happens in a complex test harness.

Yes, the initial failure were showing up from a DDL transaction. I thought the same thing here that maybe there was a sync issue. However, the mysql instance clearly thinks the transaction is ok since it's waiting on a commit.

After working on moving the DDL that fails out of the transactions and only putting inserts into the transaction, that initial failure stopped, but the same error occurs further down our test suite. It is possible there is a completely unrelated transaction with DDL in it before this failure, but so far I haven't found it. The test suite really only runs DDL in one place.

We don't mix manual and PDO transactions. Everything be through beginTransaction, commit and rollback.
 [2020-10-20 08:54 UTC] corey dot taylor dot fl at gmail dot com 
If this turns out to be *entirely* due to DDL transactions - are you saying that PDO::inTransaction() won't support them?
 [2020-10-20 09:48 UTC] nikic@php.net 
The following pull request has been associated:

Patch Name: Fix inconsistency in PDO transaction state
On GitHub:  https://github.com/php/php-src/pull/6355
Patch:      https://github.com/php/php-src/pull/6355.patch
 [2020-10-20 10:03 UTC] corey dot taylor dot fl at gmail dot com 
I see now that the implicit commit closes the transaction on the server so there is no valid "commit" after the DDL statement. I thought it just committed the changes and left the transaction open where a commit was expected. We haven't had to inspect the mysql transaction support recently.

If the issue is entirely on the PDO side, then we can handle inTransaction() returning false (correctly) as long as beginTransaction() doesn't throw the error.
 [2020-10-20 10:04 UTC] nikic@php.net 
I've opened a PR that should fix your usage (if inTransaction commit), but may break other usages (unconditional commit/rollBack despite an implicit commit). As such, I'm not completely sure whether this will go forward.
 [2020-10-27 21:13 UTC] corey dot taylor dot fl at gmail dot com 
I can confirm the committed patch resolves the issue of PDO getting out of sync with server transaction status.
 [2020-10-28 09:18 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-10-28 09:18 UTC] nikic@php.net 
Thanks for checking! We'll have to see if this causes any other issues...
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Dec 26 11:01:30 2024 UTC