How select for update works for queries with multiple rows/results?

So given this transaction:

select * from table_a where field_a = 'A' for update;

Assuming this gives out multiple rows/results, will the database lock all the results right off the bat? Or will it lock it one row at a time.

If the later is true, does that mean running this query concurrently, can result in a deadlock?

Thus, adding an order by to maintain consistency on the order is needed to solve this problem?

Solution

The documentation explains what happens as follows:

FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

The direct answer to your question is that Postgres cannot lock all the rows "right off the bat"; it has to find them first. Remember, this is row-level locking rather than table-level locking.

The documentation includes this note:

SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

I interpret this as saying that Postgres executes the SELECT query and as it finds the rows, it marks them as locked. The lock (for a given row) starts when Postgres identifies the row. It continues until the end of the transaction.

Based on this, I think it is possible for a deadlock situation to arise using SELECT FOR UPDATE.