Skip to main content
Database Administrators

Return to Question

added 25 characters in body
Source Link
Mio
Mio
  • 651
  • 1
  • 10
  • 23

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 justafter 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?

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 just after 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?

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?
edited title
Link
Mio
Mio
  • 651
  • 1
  • 10
  • 23

How to simulate FOR UPDATE lock on updateUPDATE on PosgreSQLPostgreSQL?

Source Link
Mio
Mio
  • 651
  • 1
  • 10
  • 23

How to simulate FOR UPDATE lock on update on PosgreSQL?

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 just after 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?