3 ms·
One gotcha maybe with locks is they are connection specific AFAIK, and in most libraries you're using a pool typically. So you need to have a specific connecti
by candiddevmike 2y ago
One gotcha maybe with locks is they are connection specific AFAIK, and in most libraries you're using a pool typically. So you need to have a specific connection for locks, and ensure you're using that connection when doing periodic lock tests.
- Quekid5 2y agoWhy would locks be connection-specific? ... considering that only one operation can be in flight at a time on a single connection. (Usually, at least.)
- joatmon-snoo 2y agoDifferent DBs implement locks differently. Postgres allows obtaining advisory locks at either the session _or_ transaction level. If it's session-level, then you have, ergo, a connection-level lock. https://www.postgresql.org/docs/current/explicit-locking.html https://www.postgresql.org/docs/current/explicit-locking.htm...
- skrause 2y agoPostgreSQL has pg_advisory_xact_lock which releases the lock automatically when the transaction is over.
- m11a 2y agoBut then you’d be holding a DB connection for the entire duration of your task (which may include HTTP calls, etc). You might even do asynchronous work in parallel, which doesn’t quite work with txn locks. So the session based locks seem a bit better imo.
- eknkc 2y agoI personally do these in .NET, I obtain a connection dedicated to that operation, start a transaction, obtain lock and go crazy. Upon completion of the async workflow, the transaction closes and lock releases. I know I'm holding up a connection and putting some pressure on postgres by keeping a transaction open but session management might be harder as the underlying connection provider uses pooling and it is easier to use transactions rather than sessions here. And if you add something like pgBouncer or whatever, this should still work but a session lock would fuck things up.