In certain cases, can UPDATE transactions fail rather than block waiting for “FOR UPDATE lock”?

From: Praveen Kumar <pk1uuu(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: In certain cases, can UPDATE transactions fail rather than block waiting for “FOR UPDATE lock”?
Date: 2018-07-20 19:42:26
Message-ID: CAKmKwddyuSpCQ5ek828EgtEBi-Te3d2zHe3soRy5tLPbw=XoQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm using Postgres 9.6.5.

In the docs under--

[13.3. Explicit Locking][
https://www.postgresql.org/docs/9.6/static/explicit-locking.html]

"13.3.2. Row-level Locks" -> "Row-level Lock Modes" -> "FOR UPDATE":

''' FOR UPDATE causes the rows retrieved by the SELECT statement to be
locked as though for update. This prevents them from being locked, modified
or deleted by other transactions until the current transaction ends. That
is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE,
SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these
rows will be blocked until the current transaction ends; conversely, SELECT
FOR UPDATE will wait for a concurrent transaction that has run any of those
commands on the same row, and will then lock and return the updated row (or
no row, if the row was deleted). ...

The mode is also acquired by any DELETE on a row, and also by an UPDATE
that modifies the values on certain columns. Currently, the set of columns
considered for the UPDATE case are those that have a unique index on them
that can be used in a foreign key (so partial indexes and expressional
indexes are not considered), but this may change in the future. '''

Regarding UPDATEs on rows that are locked via "SELECT FOR UPDATE" in
another transaction, I read the above as follows: other transactions that
attempt UPDATE of these rows will be blocked until the current transaction
( which did "SELECT FOR UPDATE" for those rows ) ends, unless the columns
in these rows being UPDATE'ed are those that don't have a unique index on
them that can be used in a foreign key.

Is this correct ? If so, if I have a table "program" with a text column
"stage" ( this column doesn't fit "have a unique index on them that can be
used in a foreign key" ), and I have a transaction that does "SELECT FOR
UPDATE" for some rows followed by UPDATE'ing "stage" in these rows, is it
correct that other concurrent transactions doing "UPDATE" on these rows can
fail, rather than block until the former transaction ends ?
pk

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Arulalan Narayanasamy 2018-07-20 21:19:35 Postgres function with output parameters and resultset
Previous Message Adrian Klaver 2018-07-20 19:36:10 Re: User documentation vs Official Docs