The pitfall of using PostgreSQL advisory locks with Go's DB connection pool(engineering.qubecinema.com)
engineering.qubecinema.com
The pitfall of using PostgreSQL advisory locks with Go's DB connection pool
https://engineering.qubecinema.com/2019/08/26/unlocking-advisory-locks.html
6 comments
Seems like if you tried to use transactions, you'd have the same problem?
A transaction executes all commands using the same connection so it would have worked. The semantics are different though where the commands issued between the lock and unlock aren't atomic if issued on the same connection but not in a tx.
For transactions You should use different advisory lock tied to transaction: pg_advisory_xact_lock. This should solve problem even for sessions reuse and connection pooling
TL;DR: PostgreSQL requires reserving a single connection for causally-related statements. Seems like a scaling problem for the C10K world, not just Go.
I ran into this same issue with python/SQLAlchemy. It's a connection pool thing
Even more generally, pretty much any ACID database is gonna be a scaling problem for C10K.