RFC: mysqli::in_transaction

Hi, Grégoire Paris here!

I'm a maintainer on Doctrine projects and today, I'd like to talk to you
about something we would like to propose.

We have found that PDO has a method called PDO::inTransaction() that we
would like to mimic in the API of doctrine/dbal, a database abstraction
layer many of you may already know. We would like to implement this in
driver abstraction classes, so that we can call the new method from our
existing Connection::isTransactionActive() method.

Connection::isTransactionActive() currently relies on a counter, but as
you may know, this is not foolproof because of how some RDBMS like MySQL
will implicitly commit transactions when DDL is involved. In such cases,
the method will return true when it should really return false.

Our problem is, we are missing an API similar to PDO::inTransaction()
for the mysqli driver.

We would like to contribute that API and Sergei Morozov already worked
on a patch. It is available at
https://github.com/php/php-src/compare/PHP-8.0...morozov:mysqli-in-transaction
,
and can be tested as follows:

<?php

$conn = mysqli_connect('127.0.0.1', 'root', '', 'doctrine');

if (!$conn) {
exit(1);
}

echo 'Testing commit, procedural interface:', PHP_EOL;
var_dump(mysqli_in_transaction($conn));
mysqli_begin_transaction($conn);
var_dump(mysqli_in_transaction($conn));
mysqli_commit($conn);
var_dump(mysqli_in_transaction($conn));
echo PHP_EOL;

echo 'Testing rollback, OO interface:', PHP_EOL;
var_dump($conn->in_transaction());
$conn->begin_transaction();
var_dump($conn->in_transaction());
$conn->rollback();
var_dump($conn->in_transaction());
echo PHP_EOL;

echo 'Testing DDL:', PHP_EOL;
mysqli_query($conn, 'DROP TABLE IF EXISTS foo');
mysqli_begin_transaction($conn);
var_dump(mysqli_in_transaction($conn));
mysqli_query($conn, 'CREATE TABLE foo(id INT)');
var_dump(mysqli_in_transaction($conn));
echo PHP_EOL;

Testing commit, procedural interface:
bool(false)
bool(true)
bool(false)

Testing rollback, OO interface:
bool(false)
bool(true)
bool(false)

Testing DDL:
bool(true)
bool(false)

Would it be OK for us to go ahead and contribute this?

The DBAL RFC can be found at https://github.com/doctrine/dbal/issues/4616

Best regards,

--
Grégoire Paris