Not safe for use concurrently from multiple async scopes

[segevfiner]

Issue Summary

If you use a single Database from multiple different async contexts, such as handling two requests concurrently in a web server, and both use transactions, the transactions will get interleaved, meaning you might end up trying to start another transaction while one is already ongoing in the connection, which will error, or you will end up throwing in a random query into an unrelated transaction started by a different request.

To use the library safely concurrently with one Database, there needs to be a mechanism to "lock" the database while one context is performing a transaction, or any other action that effects the connection state, which will block other queries/transaction from other contexts while that lock is held. (Might be possible to achieve using the builtin db lock of SQLite3, the API level one sqlite_db_mutex, not the database level one), and of course, such a lock needs to use callbacks/async to not actually block Node.js.

serialize is not enough, as other concurrent code can still use the DB, that is, end up queueing queries.

This even tripped up TypeORM https://github.com/typeorm/typeorm/issues/1884, and IMHO makes the library unusable like this without connection pooling or some other external locking mechanism.

Steps to Reproduce

Run multiple transactions concurrently, e.g. From multiple web requests.

Version

5.1.7

Node.js Version

18.19.0

How did you install the library?

pnpm 8 on macOS 14.3.1