CODEWITHSUNDEEP
postgresql
Toni Cárdenas
Toni Cárdenas

Reputation: 6848

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?

Upvotes: 4

Views: 1349

Answers (1)

clemens
clemens

Reputation: 17712

In your scenario step 3 should fail, if it tries to obtain R2 with NOWAIT option. When it fails all locks of S1 are released immediately, and S1 must be rolled back. Thus, S2 may obtain R1 in step 4. This scenario can't deadlock.

If you try to obtain R2 in step 3 without NOWAIT option, step 4 will fail because of a detected deadlock, and S2 will release all of its locks immediately.

The guarantees are the defined behavior of locks in a relational database, as described in the documentation:

To prevent the operation from waiting for other transactions to commit, use the NOWAIT option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately.

Reporting an error means that the transaction is broken and must be rolled back. A transaction is atomic. To put it simply, this means that it may only be terminated with a commit if all the steps it contains have been successfully completed.

Upvotes: 3

Related Questions