Nested transactions with multiple levels

[OliverSkroblin]

Bug Report

I've tried nesting multiple transactions. However, this does not seem to work properly from a depth of 3. I'm using MySQL Server version: 8.0.16.

Q	A
BC Break	no
Version	v2.9.2

How to reproduce

I wrote a small function to reproduce the behavior. In the function is summarized the following done:

1. start a transaction
2. execute an insert
3. maximum nesting reached?
   • Yes: Rollback
   • No: Recursive call and then commit

/** @var Connection $connection */
$connection->executeUpdate('DROP TABLE IF EXISTS `test`;');
$connection->executeUpdate('CREATE TABLE `test` ( `name` varchar(5) NOT NULL );');

try {
    insert($connection, 1, 3);
} catch (\Exception $e) {
    echo $e->getMessage() . PHP_EOL;
}

$records = $connection->fetchAll('SELECT * FROM test');

echo sprintf('records in database: %s', count($records)) . PHP_EOL;

print_r(array_column($records, 'name'));




function insert(Connection $connection, int $level, int $max)
{
    echo 'start transaction ' . $level . PHP_EOL;
    $connection->beginTransaction();

    $connection->insert('test', ['name' => 'A' . $level]);

    if ($level === $max) {
        echo 'rollback ' . $level . PHP_EOL;

        $connection->rollBack();
        return;
    }

    ++$level;

    insert($connection, $level, $max);

    $last = $level - 1;

    echo 'commit ' .  $last . PHP_EOL;
    $connection->commit();
}

output:

start transaction 1
start transaction 2
start transaction 3
rollback 3
commit 2
Transaction commit failed because the transaction has been marked for rollback only.
records in database: 3
Array
(
    [0] => A1
    [1] => A2
    [2] => A3
)

Expected behaviour

First of all, the commit call throws me an exception after the rollback: Transaction commit failed because the transaction has been marked for rollback only..

With the above source:

• I start three transactions
• Each transaction has one insert query
• Rollback one transaction
• Commit the remaining two transactions

I would expect to have at least two records inside the database.

I already had a look at the documentation under (https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/transactions.html#transaction-nesting). If I copy the sources and continue nesting the whole thing doesn't work either. Therefore I had it in a small function outsourced since it becomes otherwise very confusing

[javaDeveloperKid]

Even though this is one year old question I will answer it for descendants because I had the same problem once.

If you did a rollback when having more than one transaction opened you will never be able to perform a commit. Once a rollback is called a connection is marked for rollback only and you have to keep rolling back until there is only one opened transaction (you can do that by throwing and catching an exception up to the first try..catch block with begin/commit/rollback code) what will eventually do a real rollback on a database level and remove connection from rollback only state.

Please remember that you should distinguish what begin/commit/rollback means on the Connection class level and what it means on a database level. Please inspect this class to understand how Doctrine's Connection class handles this 3 operations and read your Database platform documentation how it handles this 3 operations. Example: My database platform is MySQL. You cannot have nested transactions in MySQL. Every next begin statement closes the previous begin. In Doctrine's Connection you can call beginTransaction() as often as you want. To understand this please investigate this class.