What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?
As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY
takes an EXCLUSIVE
lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE
lock on a table "allows only concurrent ACCESS SHARE
locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE
conflicts with ... EXCLUSIVE
", meaning that another REFRESH MATERIALIZED VIEW CONCURRENTLY
statement, which requests the same EXCLUSIVE
lock, will have to wait until the earlier EXCLUSIVE
lock is released.
If you want to avoid waiting for this lock for an undefined period, you may want to set the session variable lock_timeout
to a sensible value.
As noted by mustaccio, this question overlaps significantly with Postgres Refresh Materialized View Locks.
However, while the accepted answer to that question has a link that answers this one, the answer to this question isn't directly included in that one.
So, to be specific: According to the PostgreSQL manual page on explicit locking (Link is to the current version page, for PostGres 10), REFRESH MATERIALIZED VIEW CONCURRENTLY
takes a EXCLUSIVE
lock. The EXCLUSIVE
lock appears to block all other locks except ACCESS SHARE
- that includes other EXCLUSIVE
locks.
So a second REFRESH MATERIALIZED VIEW CONCURRENTLY
request on the same view will wait for the lock obtained by the first one to be released.