Re: Select for update / deadlock possibility?

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Tue, Jan 2, 2018 at 3:22 AM, Durumdara <durumdara(at)gmail(dot)com> wrote:
>> Is "select for update" atomic (as transactions) or it isn't?

> It is atomic, but you do have to worry about deadlocks.

I think by "atomic" the OP intends "all the row locks are magically
acquired at the same instant". Which they aren't, they're taken one
at a time. So if you have different transactions trying to lock
overlapping sets of rows, there's a risk of deadlock. Which will
be detected and one transaction will fail, but still you might wish
to avoid that.

The usual rule for that is "be sure all transactions acquire locks
in the same order". So just throw an "order by id" type of clause
into the SELECT FOR UPDATE, and you should be fine.

Personally, I'd still code the application to retry on deadlock
failures, just for robustness.

regards, tom lane