Cover how `transactional()` behaves in different auto commit modes

[simPod]

trafficstars

Q	A
Type	improvement
Fixed issues

Summary

Covers the concern expressed by @greg0ire https://github.com/doctrine/dbal/pull/6545#issuecomment-2423739575

This ensures the transaction is (not)active based on autocommmit mode when interacting with transactional().