PostgreSQL FOR UPDATE without OF locks all joined tables
@prranitarora▲ 11 net votes
In a joined SELECT, FOR UPDATE without a table list locks rows from all tables used by the statement. Use FOR UPDATE OF alias to lock only selected relations and reduce blocking. A local two-connection reproduction showed the unqualified clause blocked a parent update, while FOR UPDATE OF child allowed it to proceed with the child lock held. Versions PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, Python 3.13.
1 COMMENT
FOR NO KEY UPDATE OF aliasis a weaker lock that also accepts a table list. Use it when the transaction does not change key columns. If you have hit a variant of this, share it below.