[php-src] PR #21257: ext/pdo: Add PDO::ATTR_AUTOCOMMIT_AWARE_TRANSACTIONS attribute
| From: | Crovitche-1623 | Date: | Thu, 19 Feb 2026 14:02:45 +0000 |
| Subject: | [php-src] PR #21257: ext/pdo: Add PDO::ATTR_AUTOCOMMIT_AWARE_TRANSACTIONS attribute | ||
| Groups: | php.git-pulls | ||
| Request: | Send a blank email to git-pulls+get-34959@lists.php.net to get a copy of this message | ||
Pull Request: https://github.com/php/php-src/pull/21257
Author: Crovitche-1623
## Short description
Proposal for a new opt-in PDO attribute that makes
commit() and rollBack()
aware of the logical transaction state when autocommit is disabled, eliminating the need for a
redundant BEGIN after every COMMIT.
## Problem statement
When PDO::ATTR_AUTOCOMMIT is set to false, MySQL sends SET
autocommit = 0 at connection time. In this mode, every SQL statement is implicitly part of a
transaction — there is no "auto-commit after each statement" behavior. A
COMMIT ends the current transaction, and the next statement implicitly starts a new
one.
However, MySQL's SERVER_STATUS_IN_TRANS flag — which PHP 8+'s
PDO::inTransaction() and PDO::commit() rely on — is **cleared** by
COMMIT. It is only set again when the next SQL statement executes (any statement,
including SELECT). This creates a gap:
```
1. SET autocommit = 0 → flag NOT set (no transaction yet)
2. INSERT INTO ... → flag SET (implicit transaction started)
3. COMMIT → flag CLEARED
← GAP: inTransaction()=false, commit() throws →
4. INSERT INTO ... → flag SET again
```
During this gap (between steps 3 and 4), calling PDO::commit() throws:
```
PDOException: There is no active transaction
```
This forces every framework and ORM to implement the same workaround: **immediately send
BEGIN after every COMMIT** to re-set the server flag. This
BEGIN is functionally redundant — MySQL is already ready for an implicit transaction
— but without it, the next commit() call fails.
### Concrete impact
Each level-1 commit produces **two** SQL statements instead of one:
```sql
COMMIT; -- actual commit
BEGIN; -- redundant re-sync (one extra network round-trip)
```
In a typical web request with 1–3 explicit commits, this means 1–3 wasted round-trips to MySQL
per request.
### Benchmark results
A benchmark comparing COMMIT + BEGIN (current workaround) vs COMMIT only
(proposed behavior) on MySQL 8.4 (Docker, local network). Results averaged over 2 runs of 50,000
commit cycles each (100,000 total cycles):
```
COMMIT + BEGIN (current) 35.006314 s
COMMIT only (proposed) 31.380110 s
--------------------------------------------------------
Difference 3.626204 s
Overhead 11.6%
Per-commit overhead 73 µs
Total commit cycles 50000 (per run, averaged over 2 runs)
```
The redundant BEGIN adds **~12% overhead** and **~73 µs per commit**. This is on a
local Docker network with near-zero latency — in production environments with network latency
between application and database (cloud services, separate hosts), the per-commit cost would be
higher.
<details>
<summary>Benchmark script</summary>
```php
<?php
/**
* Benchmark: measures the overhead of a redundant BEGIN after every COMMIT
* when autocommit is disabled.
*
* Compares two approaches:
* A) INSERT → COMMIT → BEGIN (current workaround)
* B) INSERT → COMMIT (proposed: no redundant BEGIN)
*
* Usage:
* php benchmark_begin_overhead.php [iterations] [runs]
*
* Examples:
* php benchmark_begin_overhead.php # 1000 iterations, 5 runs
* php benchmark_begin_overhead.php 5000 10 # 5000 iterations, 10 runs
*
* Environment variables:
* MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE
*/
$iterations = (int) ($argv[1] ?? 1000);
$runs = (int) ($argv[2] ?? 5);
$host = $_SERVER['MYSQL_HOST'] ?? 'database';
$port = (int) ($_SERVER['MYSQL_PORT'] ?? 3306);
$user = $_SERVER['MYSQL_USER'] ?? 'root';
$pass = $_SERVER['MYSQL_PASSWORD'] ?? ($_SERVER['MYSQL_ROOT_PASSWORD'] ??
'root');
$db = $_SERVER['MYSQL_DATABASE'] ?? 'database';
$dsn = \sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', $host, $port, $db);
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$pdo->exec('SET autocommit = 0');
$pdo->exec('DROP TABLE IF EXISTS _bench_begin_overhead');
$pdo->exec(
'CREATE TABLE _bench_begin_overhead ('
. 'id INT AUTO_INCREMENT PRIMARY KEY,'
. 'val INT NOT NULL'
. ') ENGINE=InnoDB',
);
$pdo->exec('COMMIT');
$stmt = $pdo->prepare('INSERT INTO _bench_begin_overhead (val) VALUES (?)');
$version = $pdo->query('SELECT VERSION()')->fetchColumn();
\printf("Benchmark: redundant BEGIN overhead after COMMIT\n");
\printf("%-24s %s\n", 'MySQL version', $version);
\printf("%-24s %s:%d\n", 'Server', $host, $port);
\printf("%-24s %d\n", 'Iterations per run', $iterations);
\printf("%-24s %d\n", 'Runs', $runs);
\printf("%s\n\n", \str_repeat('-', 56));
$timeWithBegin = 0;
$timeWithoutBegin = 0;
for ($run = 0; $run < $runs; ++$run) {
$pdo->exec('TRUNCATE TABLE _bench_begin_overhead');
$pdo->exec('COMMIT');
// Alternate execution order to reduce systematic bias
if ($run % 2 === 0) {
$pdo->exec('BEGIN');
$t = \hrtime(true);
for ($i = 0; $i < $iterations; ++$i) {
$stmt->execute([$i]);
$pdo->exec('COMMIT');
$pdo->exec('BEGIN');
}
$timeWithBegin += \hrtime(true) - $t;
$pdo->exec('COMMIT');
$pdo->exec('TRUNCATE TABLE _bench_begin_overhead');
$pdo->exec('COMMIT');
$pdo->exec('BEGIN');
$t = \hrtime(true);
for ($i = 0; $i < $iterations; ++$i) {
$stmt->execute([$i]);
$pdo->exec('COMMIT');
}
$timeWithoutBegin += \hrtime(true) - $t;
$pdo->exec('COMMIT');
} else {
$pdo->exec('BEGIN');
$t = \hrtime(true);
for ($i = 0; $i < $iterations; ++$i) {
$stmt->execute([$i]);
$pdo->exec('COMMIT');
}
$timeWithoutBegin += \hrtime(true) - $t;
$pdo->exec('COMMIT');
$pdo->exec('TRUNCATE TABLE _bench_begin_overhead');
$pdo->exec('COMMIT');
$pdo->exec('BEGIN');
$t = \hrtime(true);
for ($i = 0; $i < $iterations; ++$i) {
$stmt->execute([$i]);
$pdo->exec('COMMIT');
$pdo->exec('BEGIN');
}
$timeWithBegin += \hrtime(true) - $t;
$pdo->exec('COMMIT');
}
\printf(" run %d/%d done\n", $run + 1, $runs);
}
$totalCycles = $iterations * $runs;
$secWithBegin = $timeWithBegin / 1_000_000_000;
$secWithout = $timeWithoutBegin / 1_000_000_000;
$diff = $secWithBegin - $secWithout;
$overheadPct = ($diff / $secWithout) * 100;
$perCommitUs = ($diff / $totalCycles) * 1_000_000;
\printf("\n%s\n", \str_repeat('-', 56));
\printf("%-36s %02.6f s\n", 'COMMIT + BEGIN (current)', $secWithBegin);
\printf("%-36s %02.6f s\n", 'COMMIT only (proposed)', $secWithout);
\printf("%s\n", \str_repeat('-', 56));
\printf("%-36s %02.6f s\n", 'Difference', $diff);
\printf("%-36s %.1f%%\n", 'Overhead', $overheadPct);
\printf("%-36s %.0f µs\n", 'Per-commit overhead', $perCommitUs);
\printf("%-36s %d\n", 'Total commit cycles', $totalCycles);
\printf("%s\n", \str_repeat('-', 56));
$pdo->exec('DROP TABLE IF EXISTS _bench_begin_overhead');
$pdo->exec('COMMIT');
```
</details>
## Context: PHP 7 → PHP 8 behavioral change
### PHP 7
PDO::inTransaction() and PDO::commit() checked an internal
in_txn boolean, managed entirely client-side:
- beginTransaction() → in_txn = true
- commit() / rollBack() → in_txn = false
This flag was purely based on PDO API calls and did not reflect the actual server state. For
example, a DDL statement (CREATE TABLE) triggers an implicit commit in MySQL, but PHP
7's inTransaction() still returned true.
### PHP 8+
The MySQL PDO driver now provides pdo_mysql_in_transaction() in
[ext/pdo_mysql/mysql_driver.c][php-mysql-driver], which queries the actual server
status:
```c
/* {{{ pdo_mysql_in_transaction */
static bool pdo_mysql_in_transaction(pdo_dbh_t *dbh)
{
pdo_mysql_db_handle *H = (pdo_mysql_db_handle *)dbh->driver_data;
PDO_DBG_ENTER("pdo_mysql_in_transaction");
PDO_DBG_RETURN((pdo_mysql_get_server_status(H->server) & SERVER_STATUS_IN_TRANS) != 0);
}
/* }}} */
```
Both PDO::inTransaction() and PDO::commit() delegate to the same internal
helper [pdo_is_in_transaction()][php-pdo-dbh]:
```c
static bool pdo_is_in_transaction(pdo_dbh_t *dbh) {
if (dbh->methods->in_transaction) {
return dbh->methods->in_transaction(dbh);
}
return dbh->in_txn;
}
```
This change was a net improvement — it correctly detects implicit commits from DDL statements. But
it introduced the gap problem described above for users with autocommit = 0, because
SERVER_STATUS_IN_TRANS is cleared after COMMIT even when autocommit is
off.
## Affected projects
Multiple major PHP frameworks encountered the broader "no active transaction" problem on
PHP 8+ and implemented workarounds. The root trigger varies:
| Project | Issue | Trigger | Workaround |
|---|---|---|---|
| **Doctrine DBAL** | Custom Connection subclasses | autocommit = 0 gap |
Send BEGIN after every COMMIT (in custom wrapper) |
| **Drupal** | [#2736777][drupal-issue] | DDL implicit commit | Check inTransaction()
before commit()/rollBack() |
| **Laravel** | [#35380][laravel-issue] | DDL implicit commit | Check inTransaction()
before rollBack() |
| **Yii2** | [#18406][yii2-issue] | DDL implicit commit | Guard
commit()/rollBack() calls |
| **Doctrine Migrations** | [#1104][doctrine-migrations-issue] | DDL implicit commit | Disable
transactional migrations on MySQL |
**Important distinction:** the Drupal, Laravel, Yii2, and Doctrine Migrations issues are triggered
by DDL statements (CREATE TABLE, ALTER TABLE) that cause MySQL to
implicitly commit the current transaction — this happens regardless of the autocommit setting. The
Doctrine DBAL workaround (custom Connection subclass sending BEGIN after
every COMMIT) is the one directly caused by the autocommit = 0 gap.
This proposal specifically targets the **autocommit = 0 gap**. However, it also helps
the DDL case *when autocommit is off*: after a DDL implicit commit,
SERVER_STATUS_IN_TRANS is cleared, and commit()/rollBack()
would throw — the attribute prevents this throw.
For the DDL case with autocommit *on*, a different solution would be needed (e.g., checking
inTransaction() before calling commit(), as Drupal does).
## Proposed solution
### New attribute: PDO::ATTR_AUTOCOMMIT_AWARE_TRANSACTIONS
A new **opt-in** PDO attribute that modifies the behavior of commit() and
rollBack() when autocommit is disabled and no server-level transaction is active.
```php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_AUTOCOMMIT => false,
PDO::ATTR_AUTOCOMMIT_AWARE_TRANSACTIONS => true,
]);
```
### Behavioral change (only when both conditions are met)
When ATTR_AUTOCOMMIT_AWARE_TRANSACTIONS is true **and**
ATTR_AUTOCOMMIT is false:
| Method | Current behavior (gap) | Proposed behavior (gap) |
|---|---|---|
| commit() | Throws "no active transaction" | Returns true
silently (no-op) |
| rollBack() | Throws "no active transaction" | Returns true
silently (no-op) |
| inTransaction() | Returns false | **Unchanged** — still returns
false |
| beginTransaction() | Works normally | **Unchanged** — still works normally |
Key design decisions:
1. **inTransaction() is unchanged** — it continues to reflect the actual
SERVER_STATUS_IN_TRANS flag. This preserves backward compatibility and keeps
inTransaction() accurate for code that relies on the server-level transaction state.
2. **beginTransaction() is unchanged** — it still works in the gap, which is correct
because MySQL can accept an explicit BEGIN at any time when autocommit is off.
3. **Only commit() and rollBack() are affected** — they silently succeed
instead of throwing when there is nothing to commit/rollback. This is safe because a
COMMIT or ROLLBACK with no pending changes is a no-op from MySQL's
perspective.
### Scope
- **No driver-specific changes** — the change is in core PDO. While it technically applies to all
drivers, the primary beneficiary is the **MySQL driver** where the autocommit = 0 +
SERVER_STATUS_IN_TRANS gap is the most common pain point
- **Opt-in only** — default behavior is unchanged
- **No new SQL emitted** — this *removes* unnecessary SQL, it does not add any
## Why this matters
### Before (current state)
Every framework with autocommit = 0 must send a redundant BEGIN after
every COMMIT:
```
→ COMMIT ← OK (1 round-trip)
→ BEGIN ← OK (1 round-trip, redundant)
... next operations ...
→ COMMIT ← OK (1 round-trip)
→ BEGIN ← OK (1 round-trip, redundant)
```
### After (with the attribute)
```
→ COMMIT ← OK (1 round-trip)
... next operations ...
→ COMMIT ← OK (1 round-trip)
```
The redundant BEGIN is eliminated — each commit cycle costs one round-trip instead of
two. For applications with frequent commits, this is a measurable improvement in latency and MySQL
thread utilization.
## Cross-driver compatibility
The proposed change is in **core PDO** (pdo_dbh.c), not in a specific driver. The guard
condition !dbh->auto_commit ensures that the new behavior only activates when a
driver actually supports disabling autocommit.
| Driver | ATTR_AUTOCOMMIT support | Driver-level in_transaction() |
Change activates? | Impact |
|---|---|---|---|---|
| **pdo_mysql** | Yes | Yes (SERVER_STATUS_IN_TRANS) | **Yes** — primary target |
Eliminates redundant BEGIN |
| **pdo_pgsql** | No | Yes (PQtransactionStatus) | No — auto_commit
stays true | None |
| **pdo_sqlite** | No | Yes (sqlite3_get_autocommit) | No — auto_commit
stays true | None |
| **pdo_sqlsrv** | No (throws error) | No (uses in_txn) | No —
auto_commit stays true | None |
| **pdo_oci** | Yes | No (uses in_txn) | If user sets autocommit=false | Safe — no
gap exists with in_txn |
| **pdo_firebird** | Yes | Yes (in_manually_txn) | If user sets autocommit=false | Safe
— no-op is benign |
| **pdo_odbc** | Yes | No (uses in_txn) | If user sets autocommit=false | Safe — no
gap exists with in_txn |
**Key observations:**
- **PostgreSQL, SQLite, SQL Server** do not support ATTR_AUTOCOMMIT = false. The
auto_commit field stays at its default true value — the proposed guard
condition is never satisfied. **Zero impact.**
- **Oracle, ODBC** support ATTR_AUTOCOMMIT but rely on PDO's internal
in_txn flag (no driver-level in_transaction()). There is no gap: after
commit(), in_txn is false and stays false until
beginTransaction() is called. The no-op only fires on a double-commit, which is benign.
- **Firebird** has its own in_manually_txn flag. After commit(), the flag
is cleared and the driver restarts an autocommit transaction internally. The no-op is harmless.
- **MySQL/MariaDB** is the only driver where the gap actually exists (between COMMIT
and the next SQL statement, SERVER_STATUS_IN_TRANS is cleared). This is the primary use
case the attribute is designed for.
## Backward compatibility
**Zero BC break.** The attribute is opt-in and defaults to false. Existing code behaves
identically. Only code that explicitly enables the attribute sees the new behavior.
## Examples
### Current workaround (typical Doctrine DBAL Connection subclass)
When using Doctrine DBAL with auto_commit: false, applications must subclass
Connection and override commit() to immediately re-open a PDO-level
transaction after every commit:
```php
public function commit(): void
{
// ... nesting level management ...
if (1 === $this->getTransactionNestingLevel()) {
$driverConnection->commit();
// Without this BEGIN, the next commit() throws
// "There is no active transaction"
if (!$this->isAutoCommit()) {
$driverConnection->beginTransaction(); // ← redundant BEGIN
}
}
// ...
}
```
This pattern is used by any project combining Doctrine DBAL with autocommit = 0.
### With the proposed attribute
```php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_AUTOCOMMIT => false,
PDO::ATTR_AUTOCOMMIT_AWARE_TRANSACTIONS => true,
]);
// No workaround needed — commit() in the gap silently returns true
$pdo->exec('INSERT INTO t VALUES (1)');
$pdo->commit();
$pdo->exec('INSERT INTO t VALUES (2)');
$pdo->commit(); // ← works without a preceding BEGIN
```
## Alternatives considered
### 1. Modify pdo_mysql_in_transaction() to return true when autocommit is
off
**Rejected.** This would make beginTransaction() throw "There is already an active
transaction" whenever autocommit is off, breaking existing code. It also changes the semantics
of inTransaction() from "does the server have an active transaction" to
"are we logically in a transaction", which is a different question.
### 2. Move the BEGIN workaround into the MySQL driver's commit handler
**Rejected.** This just moves the redundant BEGIN from userland to C. The same SQL is
emitted, the same round-trip is wasted. It solves nothing.
### 3. Change the default behavior (no opt-in attribute)
**Rejected.** Existing code may rely on the exception to detect programming errors (e.g.,
double-commit). An opt-in attribute avoids any BC break.
## Test coverage
6 .phpt tests in ext/pdo_mysql/tests/, all passing against MySQL 8.4:
- pdo_mysql_autocommit_aware_commit.phpt — commit() in the gap returns
true
- pdo_mysql_autocommit_aware_rollback.phpt — rollBack() in the gap
returns true
- pdo_mysql_autocommit_aware_intransaction.phpt — inTransaction()
unchanged
- pdo_mysql_autocommit_aware_begintransaction.phpt — beginTransaction()
unchanged
- pdo_mysql_autocommit_aware_default_off.phpt — attribute defaults to off (BC
preserved)
- pdo_mysql_autocommit_aware_with_autocommit_on.phpt — no effect when autocommit on
No regressions on existing PDO / PDO MySQL tests.
## References
### PHP source code
- [ext/pdo_mysql/mysql_driver.c][php-mysql-driver] —
pdo_mysql_in_transaction()
- [ext/pdo/pdo_dbh.c][php-pdo-dbh] — pdo_is_in_transaction(),
PDO::commit(), PDO::rollBack()
- [ext/pdo_mysql/tests/pdo_mysql_inTransaction.phpt][php-intransaction-test]
- [ext/pdo_mysql/tests/pdo_mysql_attr_autocommit.phpt][php-autocommit-test]
### Existing bug reports and issues
- [PHP Bug #80260][php-bug-80260] — inTransaction() returns false inside transaction
(DDL implicit commit)
- [PHP Bug #81227][php-bug-81227] — inTransaction() reports false in SQLite
transaction
- [Drupal #2736777][drupal-issue] — "MySQL on PHP 8 now errors when committing or rolling
back when there is no active transaction"
- [Drupal #3405976][drupal-autocommit] — Transaction autocommit during shutdown
- [Doctrine Migrations #1104][doctrine-migrations-issue] — DDL migrations in transactional mode
fail
- [Laravel #35380][laravel-issue] — PDO::rollback() throws after implicit commit
- [Yii2 #18406][yii2-issue] — "There is no active transaction" on PHP 8
### MySQL documentation
- [MySQL SERVER_STATUS_IN_TRANS][mysql-server-status]
- [MySQL: autocommit, Commit, and Rollback][mysql-autocommit]
- [MySQL: Statements That Cause an Implicit Commit][mysql-implicit-commit]
[php-mysql-driver]: https://github.com/php/php-src/blob/master/ext/pdo_mysql/mysql_driver.c
[php-pdo-dbh]: https://github.com/php/php-src/blob/master/ext/pdo/pdo_dbh.c
[php-intransaction-test]: https://github.com/php/php-src/blob/master/ext/pdo_mysql/tests/pdo_mysql_inTransaction.phpt
[php-autocommit-test]: https://github.com/php/php-src/blob/master/ext/pdo_mysql/tests/pdo_mysql_attr_autocommit.phpt
[php-bug-80260]: https://bugs.php.net/bug.php?id=80260
[php-bug-81227]: https://bugs.php.net/bug.php?id=81227
[drupal-issue]: https://www.drupal.org/project/drupal/issues/2736777
[drupal-autocommit]: https://www.drupal.org/project/drupal/issues/3405976
[doctrine-migrations-issue]: https://github.com/doctrine/migrations/issues/1104
[laravel-issue]: https://github.com/laravel/framework/issues/35380
[yii2-issue]: https://github.com/yiisoft/yii2/issues/18406
[mysql-server-status]: https://dev.mysql.com/doc/dev/mysql-server/latest/mysql__com_8h.html#a1d854e841571c340f5a9b3bb8d3df829
[mysql-autocommit]: https://dev.mysql.com/doc/refman/8.4/en/innodb-autocommit-commit-rollback.html
[mysql-implicit-commit]: https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html