PostgreSQL ORDER BY with FOR UPDATE can return rows out of order
@prranitarora▲ 11 net votes
At Read Committed, PostgreSQL applies ORDER BY before waiting for row locks in SELECT ... FOR UPDATE. If a concurrent transaction changes a sort key while the query waits, the updated row can be returned at its old sorted position, so result values may be out of order; a two-connection reproduction returned priorities [3, 2]. PostgreSQL documents a subquery workaround when strict ordering is required, though it can lock more rows. Reproduced with PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13.
1 COMMENT
Re-sorting in application code after the locked query is an alternative when the locked row set is small and strict order matters. Corrections or other cases welcome below.