Possible deadlock if multiple request handled at the same time due to db connection exhausted in pool

[tung2744]

Describe the bug Currently, there are global db, app db, audit db. In a single request, we begin transactions on each db. All of these dbs shares the same connection pool. In the connection pool, the connection pool cache is using database url as the cache key. Therefore, if global db / audit db / audit db share the same database url, they will share the same pool. Therefore, it is possible that in a single request we opened multiple connections to the same database in the same database pool. And if the max connection limited of the pool was reached, it is possible to become a deadlock.

Authgear Version

• Version: fe4ea3092de0ab537dc6bfbc98e6fff3d869d855

To Reproduce Trigger multiple graphql mutations together.

Expected behavior Should not cause deadlock. Maybe we should separate the database pool of global db, app db and audit db.

Screenshots

Client Env (if applicable, please complete the following information):

Additional context

[chpapa]

if global db / audit db / audit db share the same database url, they will share the same pool.

could we consider to fix this, by showing a warning at startup, if someone use the same DB url for the 3 DBs, and update our help page / setup with different pgsql users (and permissions) for each DB url

(Ironically as someone who suffer from pgsql connection pool cannot share between users in many ocassions, this is the first time I think that’s a feature not a bug lol)