|
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)? |
|
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. Thanks Glenn So this means that any report or other query which involves the view will require a Commit following to avoid locking up other user's reports/queries. I also noticed that the REFRESH itself seems to do an Implicit Commit.
(29 Dec '11, 17:30)
Glenn Barber
On further consideration, the requirement to exclusively lock the view during the refresh greatly limits the material view's use in a multiuser system where such views might be used as part of long running reports as well as transactions. Rather than refresh the view prior to reports and queries which utilize the view - requiring such reports to be run serially (because the refresh hangs until the prior report completes), are we better off Refreshing the view as a part of any transaction that updates the underlying table of the view? What would the overhead for this be and is there anyway to mimimize the impact on individual transactions?
(29 Dec '11, 18:37)
Glenn Barber
Replies hidden
There are tradeoffs. Recall that not all MVs can be immediately maintained. However, if the MV in question can be maintained incrementally, then it might make sense to update the MV in the same transaction as the updates made to the underlying base tables. Keep in mind, however, that introducing MV updates within update transactions bring with it the potential for additional blocking, possibly (even) deadlock if two updating transactions conflict on the same rows in the view.
(29 Dec '11, 21:42)
Glenn Paulley
Ideally, I would like to find a good method to Post the MV Refresh Asynchronously after the Transaction Commit, rather than tie up the Unit of Work on completeting the Refresh which could hang while waiting for a report using the view to complete. Do you have a suggestion on how I might accomplish that?
(30 Dec '11, 00:03)
Glenn Barber
Replies hidden
1
One way is to modify a table to indicate that a MV refresh is required, and then periodically poll that table with an event, which will fire on a separate, independent connection. The tradeoffs of this approach include the length of time required to re-construct the MV, and the degree of staleness that can be tolerated by other connections that refer to the underlying base tables directly (but will use the view to improve performance). Obviously, if you have a query that references the view directly then you have to account for the time the refresh request might queue, and how long it may take for the MV to be reconstructed.
(30 Dec '11, 14:47)
Glenn Paulley
|
