alt Hacker NewsThat’s an interesting idea but not all locks are held for the duration of the statement. A lot of them take a less intrusive lock for the whole statement and take an exclusive lock for a very short time when they finish up.
Edit: Looking this up, I’m not sure this is correct.
> whole statement
simplified you can think of a statement outside of a transaction as starting an implicit transaction just for itself
and (normal) locks are in general hold until the end of the transaction (while also allowing re-entrance from subsequent queries on the same transaction)
practically
- there are edge cases (e.g. Advisory Locks, but in general you don't want to use them)
- you normally(^1) would want to run your pg migration as a single transaction (but there are edge cases). And in turn the OPs idea of pre-acquiring locks would be for the whole transaction anyway. Plus it was just a general idea, so the end result could be more like an "expect lock" statement maybe with some scan ahead ability then an "acquire lock".
(^1): Exceptions include certain operations which need to be in different transactions, and some painful situations where too much data is touched/changed/computed and you need a lot of very careful handling you common small-ish PG DB use-case isn't exposed to (and in turn a lot of "naive but often good enough" migration setups can't handle either...)