2

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?

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.