9

Does the whole table UPDATE (without specifying WHERE clause) lock a table in PostgreSQL? E.g. does it prevents rows from being deleted / inserted?

E.g. if I run UPDATE t1 SET key = 'value' Can I expect no new rows will be inserted into t1 during the UPDATE execution?

If no, can I expect UPDATE will update even the rows appeared after its start? (key has no DEFAULT 'value' in its definition)

  • If it was updating rows that where inserted after it started and there was a process that was continually inserting rows wouldn't that mean the update statement would never finish? – Joe W Sep 30 '19 at 12:27
  • good point! Yes, it is barely possible UPDATE to be implemented in such manner to update newly inserted rows. – origaminal Sep 30 '19 at 12:32
  • No, only the rows that have been changed will be locked. An UPDATE never locks the whole table. – a_horse_with_no_name Sep 30 '19 at 12:36
15

An UPDATE without a WHERE clause will lock all rows in the table, but will not lock the table itself for DML.

The rows can not be deleted from a different transaction because they are locked.

But you can insert new rows without problems (assuming they do not violate any constraints).

Any row that is inserted after the UPDATE will not be seen by the UPDATE statement and thus they won't be changed.

  • can you please share the links on docs. I didn't find much in official documentation saying exactly about UPDATE without WHERE. – origaminal Sep 30 '19 at 12:41
  • And rows UPDATED from another transaction will fail as well due to the row locks, correct? – tale852150 Oct 1 '19 at 1:32
  • 1
    @origaminal The doc in question is at postgresql.org/docs/current/explicit-locking.html , the last four are what you're calling 'table locks' -- although even permissive locks are still a form of table lock that will prevent attempts to take an exclusive lock. – SilverbackNet Oct 1 '19 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.