Does UPDATE FROM VALUES lock rows in the order they are listed?

Question

I'm bulk updating some rows using UPDATE ... FROM (values ... as described in this answer.

Will the rows will be locked in the order that they appear in the values? Or do I need to do an explicit SELECT FOR UPDATE first?

Here's an example statement

UPDATE stats as t set "individualCount" = new_values."individualCount"
  FROM (values (6::int,7::int),(3::int,15::int))
  as new_values("individualCount","id") 
  WHERE new_values."id" = t."id"

(id is the primary key of the table)

Answer 1

The order in which rows are locked is not guaranteed. You could use EXPLAIN to see the execution plan, which will reveal what the database does. But don't rely on an execution plan: changes in the data can lead to PostgreSQL choosing a different execution plan.

The only reliable way to lock rows in a certain order is to run SELECT ... FOR NO KEY UPDATE NOWAIT. NO KEY is only to avoid taking a stronger lock than you need, and NOWAIT is important to make sure that the order is correct: without it, rows that are concurrently modified may be returned in a different order.

You can use a CTE to have the SELECT ... FOR NO KEY UPDATE be part of the UPDATE statement.