BUG: Projection errors in nested transaction are obscured

[mhsdesign]

actual

Currently a projection error like in the content graph projection is super hard to debug and obscured like:

Failed to update and commit highest applied sequence number for subscriber "Neos\ContentGraph\DoctrineDbalAdapter\DoctrineDbalContentGraphProjection". Please run Neos\ContentRepository\Core\Infrastructure\DbalCheckpointStorage::setUp()

The pr #4971 changes the message to

Failed to update and commit checkpoint for subscriber "Neos\ContentGraph\DoctrineDbalAdapter\DoctrineDbalContentGraphProjection" because the transaction has been marked for rollback only.

expectation

but the real goal would be of course to show the actual message like:

Event Neos\ContentRepository\Core\Feature\NodeVariation\Event\NodePeerVariantWasCreated could not be applied: Source parent node not found.

The problem is transaction nesting. As described by the docs of dbal v2.13 https://www.doctrine-project.org/projects/doctrine-dbal/en/2.13/reference/transactions.html#transaction-nesting the feature is discouraged and known to be unpredictable.

The nesting and rollback behaviour is exactly as described in the docs (note to myself read them first before debugging - and also in the right version ^^)

location	transaction nesting	is rollback only
before content graph catchup	0	false
after acquireLock (transaction start)	1	false
in nested transactional closure of content graph	2	false
after an error was thrown in the transactional closure	1	true
when catchup initiates updateAndReleaseLock in finally block	1	true

The last step in updateAndReleaseLock will crash now because we are in the finally block that will be executed if an exception was thrown or not:

However, a rollback in a nested transaction block will always mark the current transaction so that the only possible outcome of the transaction is to be rolled back. That means in the above example, the rollback in the inner transaction block marks the whole transaction for rollback only. Even if the nested transaction block would not rethrow the exception, the transaction is marked for rollback only and the commit of the outer transaction would trigger an exception, leading to the final rollback. This also means that you can not successfully commit some changes in an outer transaction if an inner transaction block fails and issues a rollback, even if this would be the desired behavior (i.e. because the nested operation is optional for the purpose of the outer transaction block).

possible solutions

1.)

The version 4.0 of doctrine dbal does not include this note about transaction nesting and explains that save points will be used internally. Interestingly the save point option is (undocumented?) also optionally available in our version 2.13.
For that we would have to enable it first in the dbal client:

$this->connection->setNestTransactionsWithSavepoints(true);

2.)

Detect isRollbackOnly in the updateAndReleaseLock and just return without committing the $highestAppliedSequenceNumber -> the behat test all pass?

current hotfixes for development

as written in slack https://neos-project.slack.com/archives/C04PYL8H3/p1711794118640819 one can change \Neos\ContentGraph\DoctrineDbalAdapter\DoctrineDbalContentGraphProjection::transactional to not use an transaction but run the operation directly:

private function transactional(\Closure $operations): void
{
    $operations();
}

[mhsdesign]

@bwaidelich wrote

since each apply() call is wrapped in a transaction anyways (for the checkpoint storage) I think we can remove all these nested transactions within.
I'll take care of that when working on the syncronous catchup implementation

[bwaidelich]

I know! :D