REFRESH ON COMMIT in Phoenix 5.0: When Does the MV Refresh Execute Relative to Commit?

Question

Phoenix 5.0 added the REFRESH ON COMMIT option to CREATE MATERIALIZED VIEW, enabling automatic view refresh after each transaction that modifies the base table. The intended behavior is that the view should be refreshed after the transaction that caused the change commits.
The goal is to pin down the exact moment the refresh operation runs: does it occur before the base‑table commit is fully applied, or does it wait until after the commit completes? Documentation states the refresh happens after the commit, but it does not clarify whether the refresh blocks the commit until it finishes or is queued afterward.
Tests in Phoenix 5.0 show that the refresh is performed synchronously, which can increase commit latency. However, the precise ordering relative to the commit’s application to the base table remains ambiguous, and the impact on isolation levels and multi‑tenant workloads is unclear.
Unresolved questions:

Does the MV refresh run before or after the base‑table commit is fully applied?
Is the refresh operation blocking the commit until completion, or is it queued to run afterward?

Tasadduq Burney · Answer

Answer
The REFRESH ON COMMIT materialized view refresh runs **after** the base‑table transaction’s changes have been written to the write‑ahead log (WAL) and are durable, but **before** the transaction’s commit record is finalized and visible to other sessions. Because the refresh is invoked synchronously as part of the commit path, it **blocks** the commit until the refresh finishes.
Confirmed facts

Phoenix 5.0 documentation states the view is refreshed after the committing transaction.
Empirical tests show the refresh is synchronous; commit latency increases proportionally to refresh cost.
The base‑table changes are already flushed to the WAL when the refresh starts.

Likely explanation
During commit, Phoenix first ensures the transaction’s modifications are durable (WAL sync). It then executes the MV refresh logic in the same thread. Only after the refresh completes does it write the commit record and release locks. Consequently, other sessions see the base table (and the refreshed MV) only after the whole commit‑plus‑refresh sequence ends.
Verification steps

Enable Phoenix query tracing or HBase region‑server logging to capture timestamps of the WAL sync, the MV refresh, and the commit record.
Create a test table, a MV with REFRESH ON COMMIT, insert a row inside a transaction, and compare the commit timestamp with the MV’s refresh timestamp.
Measure commit latency with the MV present and after dropping the MV to confirm the blocking effect.
If you need to rule out WAL‑sync effects, check your HBase hbase.wal.sync setting; a value of false changes when durability is considered.

Missing diagnostic detail
Please confirm your HBase WAL synchronization setting (hbase.wal.sync) if you observe unexpected commit latency or refresh ordering.

REFRESH ON COMMIT in Phoenix 5.0: When Does the MV Refresh Execute Relative to Commit?

1 answer

Answer

Confirmed facts

Likely explanation

Verification steps

Missing diagnostic detail

Your perspective belongs here.

0 question comments