A failed PDO::commit() keeps the transaction open, unlike MySQL #576

Reproduction

$pdo = new WP_MySQL_On_SQLite( 'mysql-on-sqlite:path=/tmp/db.sqlite;dbname=wp;' );
$pdo->exec( 'CREATE TABLE p (id INT PRIMARY KEY)' );
$pdo->exec( 'CREATE TABLE c (id INT PRIMARY KEY, p_id INT, FOREIGN KEY (p_id) REFERENCES p (id))' );
$pdo->beginTransaction();
$pdo->get_sqlite_pdo()->exec( 'PRAGMA defer_foreign_keys = ON' ); // Makes the commit fail.
$pdo->exec( 'INSERT INTO c VALUES (1, 1)' );

try {
	$pdo->commit();
} catch ( PDOException $e ) {
	// The caller continues without calling rollBack().
}
$pdo->exec( 'INSERT INTO p VALUES (1)' );
// $pdo->inTransaction() is still true.
// Another connection doesn't see the row, and it can't write: "database is locked".

• MySQL: the server rolls back a transaction whose commit fails, so the next INSERT autocommits.
• Driver: the transaction stays open and keeps the write lock. The next INSERT joins it and is never committed, and other connections can't write until this one closes.

In practice, a commit can fail with "database is locked" in a non-WAL journal mode, when another connection keeps reading for longer than the busy timeout.

Reproduced on #536 (a07ac226) with PHP 8.5.12 and 8.3, and SQLite 3.54.1.

PDO MySQL behavior

After a failed commit(), PDO MySQL's inTransaction() still returns true, so calling rollBack() in a catch block works:

• PHP 8.0+ reads the transaction status from the server's last successful reply. Error replies don't carry it, so inTransaction() returns false only after the next successful query (checked with PHP 8.5 and MySQL 8.4).
• PHP 7.x uses PDO's own flag, which stays true until rollBack() or a successful commit().

With #536, a failed SQL COMMIT statement rolls back the transaction, like MySQL. PDO::commit() keeps the transaction open on purpose, so that calling rollBack() after a failed commit keeps working.

Possible fix

Report inTransaction() as of the last successful operation, like PDO MySQL on PHP 8.0+, and use SQLite's real transaction state inside the driver. PDO::commit() could then roll back like the MySQL server, and rollBack() would still work right after a failed commit.

Related: #473, #536.