Thread: Can SELECT … NOWAIT “deadlock”?
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?
> 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?
=?UTF-8?Q?Toni_C=C3=A1rdenas?= <toni@tcardenas.me> writes:
> 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 see no reason to think that 3 and 5 would appear to be atomic.
regards, tom lane