0

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)

ChrisJ
  • 591
  • 1
  • 6
  • 22

1 Answers1

0

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.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
  • Thanks. The behaviour you describe for NOWAIT does not seem to be in the documentation? https://www.postgresql.org/docs/current/sql-select.html Based on the docs it seems purely for avoiding waiting, it doesn't mention that it will impact ordering and deadlock avoidance – ChrisJ Feb 04 '23 at 02:38
  • Oh sorry, I misread. It's not related to locking but rather the warning about READ COMMITTED mode where rows may come back in a different order – ChrisJ Feb 04 '23 at 02:39
  • Right. If you don't wait for the lock, the row cannot have changed since you took the snapshot. – Laurenz Albe Feb 04 '23 at 20:52