

As mentioned before, CREATE INDEX (without CONCURRENTLY) acquires a lock ( SHARE) that conflicts with itself, CREATE INDEX, REFRESH MATERIALIZED VIEW CONCURRENTLY, and anything that gets the ACCESS EXCLUSIVE lock, AND also the ROW EXCLUSIVE table-level lock (so cannot do concurrent INSERT/ UPDATE/ DELETE).
#POSTGRES LOCK QUEUE UPDATE#

Other statements and the table level locks they get

So for all intents and purposes, SELECT FOR UPDATE and SELECT FOR SHARE get roughly the same table-level locks as plain SELECT.Ĭonflicts with: Only the ACCESS EXCLUSIVE table level lock. Note: There’s another table level lock called ROW SHARE which is gotten by the SELECT FOR UPDATE and SELECT FOR SHARE commands, with the only difference that this lock level additionally conflicts with the lock obtained by REFRESH MATERIALIZED VIEW CONCURRENTLY, which really only applies with the SELECT FOR SHARE statement. This is the weakest and most permissive table-level lock. Note that a potentially infinite number of transactions can hold a ROW EXCLUSIVE table-level lock simultaneously, which is good and what we expect. (Do note that despite having ROW in the name, it’s a TABLE level lock, there are row-level locks that are acquired by those statements as well).Ĭonflicts with: Anything that takes an ACCESS EXCLUSIVE lock, any materialized view refresh, and regular CREATE INDEX (hint: this is why when creating an index you do it CONCURRENTLY). UPDATE, DELETE, and INSERT acquire this lock. Use REFRESH MATERIALIZED VIEW CONCURRENTLY indicator_count instead since that allows reading the materialized view while it is being written.Whats happening here is that a PostgreSQL transaction takes an ExclusiveLock on its own transaction ID when it starts. This means that one transaction is waiting for another to commit/rollback before it can proceed. REFRESH MATERIALIZED VIEW indicator_count The key thing is that its a ShareLock on the transaction.Then it re-checks the WHERE clause on the outer query to make sure it still matches (in case the row was deleted, or was. PostgreSQL sees that someone else has a lock on that row, so it waits for that lock to be released. Anything to do with partition managementĪLTER TABLE ticket DROP CONSTRAINT ticket_idx_constraint, ADD CONSTRAINT ticket_unique_description UNIQUE ( description ) Otherwise what can happen is that the inner query finds the row, returns the ID, and you try to lock the row with that ID.ALTER TABLE ticket ALTER COLUMN severity SET NOT NULL / ALTER TABLE ticket ALTER COLUMN severity DROP NOT NULL.Some examples of things you might encounter

There are some other forms of ALTER TABLE that acquire different locks but we’re not likely to encounter them.
