How to simulate FOR UPDATE lock on UPDATE on PostgreSQL?

Question

I am trying to play with PostgreSQL 14 and row level lock on update. Documentation mentions:

The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values of 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.

I created a table documents, added a field user_id with a UNIQ constraint.

Then on one session

UPDATE documents SET user_id=2 WHERE id=1 AND pg_sleep(5) IS NOT NULL;

On another session after 1 sec after hitting Enter on the first one.

UPDATE documents SET user_id=1 WHERE id=1;

The problem I see is the lock is not acquired. The second query is executed right away, then the first one overide on commit. Also the slow query do not respect the pg_sleep time (no issue when updating on a non-constrained column), query take 55-60s.

1. Why the lock is not acquired?
2. Why this slow time of updating?

Answer 1

Locks are not taken when the statement starts, but when the row is found. Since you are using pg_sleep(5) in the WHERE condition, it take five seconds before the row is found and PostgreSQL attempts to lock it. By that time your second statement has locked the row, so the first statement has to wait until the transaction that runs the second statement finishes. Also, don't forget that pg_sleep(5) will be executed for every row, so the first statement can take considerably longer than 5 seconds.

Answer 2

The same doc page states:

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update.

You can use FOR UPDATE hint with the SELECT statement, not with UPDATE. It can be used in a multi-statement (i.e. explicit) transaction to exclusively lock rows that are being read with the intention of updating them later in the same transaction. The lock is taken in exclusive mode. If you're not updating the primary key column(s), better use FOR NO KEY UPDATE mode. Or just skip all this altogether and rely on PostgreSQL's default MVCC mode of operation.

There's more info in https://www.postgresql.org/docs/current/applevel-consistency.html#NON-SERIALIZABLE-CONSISTENCY