[nvilcins]

> - Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod.

I recently went from:

  * somewhat understanding the concept of transactions and combining that with a bunch of manual locking to ensure data integrity in our web-app;

to:

  * realizing how powerful modern Postgres actually is and delegating integrity concerns to it via the right configs (e.g., applying "serializable" isolation level), and removing the manual locks.

So I'm curious what situations are there that should make me reconsider controlling locks manually instead of blindly trusting Postgres capabilities.

[azurelake]

Just FYI if you didn’t already know this:

  Any transaction which is run at a transaction isolation   level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

Given that running everything at SERIALIZABLE probably isn’t practical for you, I think it’s more clear code wise to use explicit locks. That way, you can grep for what queries are related synchronization wise, vs. SERIALIZABLE being implicit.

[wongarsu]

Continuing with the FYIs:

Explicit locks can mean just calling LOCK TABLE account_balances IN SHARE ROW EXCLUSIVE MODE; early in the transaction and then doing SELECT ... FOR UPDATE; or similar configurations to enforce business rules where it matters.

https://www.postgresql.org/docs/current/sql-lock.html

[h1fra]

I think, in the using Postgres as a queue scenario, it's not fixing the problem that two processes can read the same row at the same time thus both executing the process.

If you manually SELECT FOR UPDATE SKIP LOCKED LIMIT 1, then the second process will be forced to select the next task without waiting for the lock.