Обсуждение: In certain cases, can UPDATE transactions fail rather than block waiting for “FOR UPDATE lock”?
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
On 2018-Jul-20, Praveen Kumar wrote: > 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 ? No. What it means that if you UPDATE the columns-indexed-by-unique-idx then the FOR UPDATE lock is acquired underneath. If your UPDATE modifies some other column, then a FOR NO KEY UPDATE lock is acquired instead. In both cases, concurrent transactions would be blocked rather than erroring out. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services