[BUG] Database Deadlocks

[BlairCurrey]

Bug Report

Describe the bug

Our database deadlocks in places where we open a new connection before an open transaction is resolved while at max connections.

This, basically: Vincit/objection.js#1137 (comment)

In our case, it may look something like:

await Quote.transaction(async (trx) => {
   await receiverService.get(id)
   await Quote.query(trx).insert({...})
})

We open a transaction then try to open a new connection from another db call not using the transaction (in receiverService.get). This results in the db call in receiverService.get (or similar) waiting to get a connection, which it never gets because the current connections never resolve (because its waiting on receiverService.get).

To Reproduce

• Set the knex connection settings to a min/max of 1.
• Change performance test to do 1 vu and 1 iteration
• Run test and see it never completes

That's the easiest way but it doesnt just happen under the contrived scenario of 1 min/max connection. It happens at any number of VUs >= max connections.

Expected behavior

Performance tests should pass with 1vu/1iteration with max connection size of 1.

Resolution

Note that our current system makes some queries inside transaction blocks without using the transaction. We should probably evaluate if this behavior is correct on a case-by-case basis.

But in general, we either need to move the queries not using the transaction outside of the scope of the transaction (before or after), or we need to pass the transaction into the query. We shouldnt include anything more than necessary in the transaction to reduce transaction lock time and contention.