We are using V11.0.1 with a Manually Refreshed Materialized View
If we do an ucommitted read of the View from connection A (leaving a table level schema shared lock on the view) and attempt to refresh the view from connection B (in shared mode) the refresh will hang up on connection B until the shared lock on connection A is cleared with a commit.
Usually our powerbuilder application can do uncommitted reads and leave shared locks on tables without a conflict with other conections. However we are now finding that queries utilizing the Materialized View need to have a Commit followiing to avoid locking up reports run by other users which refresh the view prior to reading the table.
Is this the expected behavior?
Does this change in V12?
Are there other overall benefits to always issuing a Commit after uncommited reads (as might be done with PowerBuilder Quieries via a datawindow)?
asked 29 Dec '11, 17:07
This is the expected behaviour; see the docs on the REFRESH MATERIALIZED VIEW statement.
During a refresh of a manually-maintained view WITH SHARE MODE, shared schema locks are acquired on the view's underlying base tables so that other transactions can continue to reference the underlying tables without blocking. However, the REFRESH statement itself acquires an exclusive table lock on the view itself, which will block any other transaction from referencing the view directly (even at isolation level 0).
The behaviour is unchanged with version 12.
answered 29 Dec '11, 17:22