Nested transactions deadlock

[druid8]

Describe the bug On Postgres (TransactionsContextPooled) using more then one level of nested sub-transactions cause a deadlock. All returned instances of NestedTransactionPooledContext in __aenter__ tries to acquire same lock on connection object (_trxlock)

To Reproduce On simple database setup with postgres try:

async with in_transaction():   # first level transaction, works internally on TransactionContextPooled
   async with in_transaction():   # nested transaction, works internally on NestedTransactionPooledContext, locks _trxlock
     async with in_transaction():  # deadlock, another NestedTransactionPooledContext tries to acquire same _trxlock again
        pass

Expected behavior Nested transactions should work.

Additional context I tried to get why NestedTransactionPooledContext acquires lock on aenter when just NestedTransactionContext not (thus nested transactions works on SQLite), but I have no idea. Maybe removing this lock.acquire() is a good workaround.

This case is a real use case. I'm trying to write tests, so first level transaction is created at test function level to isolate test cases. Tested code modifies bunch of models in a transaction, in some places update_or_create were used which runs internally inside their own transactions.

The only workaround I found as far is 'expanding' all update_or_create calls inline to avoid their transactions.