Postgresql – Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock

concurrencydeadlockorder-bypostgresql

Consider the following when the transaction isolation level is Read Committed (the default):

UPDATE example
  SET foo = 'bar'
WHERE ...

and

DELETE FROM example
WHERE ...

Neither DELETE nor UPDATE supports ORDER BY, nor do they have a defined order. After reading the PostgreSQL docs extensively, it would seem the most simple and reliable solution to avoid deadlock in a concurrent environment is

UPDATE example
  SET foo = 'bar'
WHERE ctid IN (
  SELECT ctid
  FROM example
  WHERE ...
  ORDER BY ctid
  FOR UPDATE
)

I'm not thrilled about doing this to every UPDATE and DELETE that affects multiple rows.

  1. Is my assessment and solution correct?
  2. Is this the best solution?
  3. Is there a reason PostgreSQL doesn't already do this automatically?

Best Answer

Your assessment is spot on, and there is no better way of doing it.

As an alternative, you could do without the sort, but delete in smaller batches, each in its own transaction. That is still susceptible to deadlock, but the likelihood is smaller, and re-running the transaction doesn't hurt quite as much.

Related Question