PDO changed behaviour for automatic transactions with PDO in PHP 8

I recently came across some code I have not touched for a while, but wanted to run it. And it failed, although I am almost sure it used to work. So I investigated, and it turns out: it is the transaction handling that has changed from PHP 7.x to 8.x with autocommit=0. I did search PHPs changelog but could not find any indication. I was hoping anybody can clear my mind, because after some investigation I might have also misunderstood how it is supposed to work.

To the problem: in PHP 8.x when I call PDO::beginTransaction() it throws an error if any queries happened before that, supposedly because the queries before automatically opened a transaction (Message is There is already an active transaction). Prior to PHP 8, the same code used to work, as it appears to me after testing that PDO::beginTransaction() would have had committed any open transaction. I have attached code to the bottom, that reproduces this behaviour.

• The first question would be about the autocommit mode. The documentation is pretty scarce about that matter and also did not find anything about changes in PHP 8 changelogs. Any ideas where I can find more info?
• Why do my selects need (and automatically open) a transaction? There is nothing to rollback anyway. It is particularly painful if you do not want to use autocommit to have implicit write management in your code, but any library, or the sort that queries the database causes failures. Is this how it is supposed to work? Can we somehow make select transactional on their own without affecting writes?
• I do get that PHP 7.x probably had errored behaviour. I was under the impression it simply did auto commit any selects, but it seems like also writes are affected. So beginTransaction() then automatically commits even though autocommit is turned off.
• Can I somehow prevent transactions from being automatically opened? At the moment the error occurs only when I open a transaction validly, but I do not have any information where a query might have occurred that opened the transaction.

The code used for testing:

$pdo = new PDO(...);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, 'SET NAMES \'UTF8\', autocommit=0');
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false);

$sql = 'SELECT id FROM test WHERE id = 1';
$stmt = $pdo->prepare($sql);
$stmt->execute();

$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare('INSERT INTO test(`id`, `value`) VALUES(2, "Test")');
    $stmt->execute();
    $stmt = $pdo->prepare('INSERT INTO test(`value`) VALUES("Missing ID")');
    $stmt->execute();
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollback();
    echo "ROLLBACK:". $e->getMessage();
}

You need to understand what PDO::ATTR_AUTOCOMMIT (autocommit=0) does. When it is enabled, every query is immediately committed to the database. When it is turned off, as is in your case, every query waits for an explicit commit, an implicit commit from a DDL query, an implicit commit from BEGIN statement ^{(Beginning a transaction causes any pending transaction to be committed.)}, or for the autocommit to be turned back on.

beginTransaction() is a syntax sugar that does pretty much the same. It turns off autocommit. When you call commit you turn the autocommit back on.

Prior to PHP 8, PDO did not check whether you already had a transaction open (autocommit=0) and if you did, it simply did nothing when beginTransaction() was called.

To fix your mistake, you need to decide which approach you prefer, either switching PDO::ATTR_AUTOCOMMIT off and on manually or using beginTransaction() and commit(). Using the dedicated functions is the preferred and easier option.