Can SELECT … NOWAIT “deadlock”?

From: Toni Cárdenas <toni(at)tcardenas(dot)me>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Can SELECT … NOWAIT “deadlock”?
Date: 2019-08-20 10:46:30
Message-ID: CAH+FFSzvVPZ16NL8n8XST5sKb_7k89i8+d56K1f_3n3oK-WiTQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The docs say (emphasis mine):

> FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE are locking
clauses; they affect how SELECT locks rows **as they are obtained from the
table**.

> With NOWAIT, the statement reports an error, rather than waiting, if a
selected row cannot be locked immediately.

From this, I'm not sure if the following behavior is possible.

Imagine the same `SELECT ... NOWAIT` statement, which returns rows R1 and
R2, is run from concurrent connections S1 and S2.

1. S1 obtains and locks R1.
2. S2 obtains and locks R2.
3. S1 tries to obtain R2, but it's locked by S2.
4. S2 tries to obtain R1, but it's locked by S1.
5. The lock on R1 is released due to S1 failing.

The question is whether step 4 can actually happen between step 3 and 5, or
else steps 3 and 5 are performed atomically with respect to concurrent
selects.

I'm guessing it cannot happen, since without `NOWAIT` (or `SKIP LOCKED`),
this behavior would lead to deadlock (S1 waits for S2 to finish and release
R2 while S2 waits for S1 to finish and release R1), but maybe such scenario
would be resolved in some other way.

So, which are the guarantees here?

Browse pgsql-general by date

  From Date Subject
Next Message Toni Cárdenas 2019-08-20 10:53:50 Can SELECT … NOWAIT “deadlock”?
Previous Message Karl Martin Skoldebrand 2019-08-20 10:33:17 Databases and servers