0

I have a MView that, since it does involve many joins and is pretty complex, gets refreshed on demand by calling DBMS_MVIEW.REFRESH (asynchronously) from a (Spring-/Hibernate-based) Java Web-App like this:

this.getEntityManager().createNativeQuery("{call DBMS_MVIEW.REFRESH('AccountManagerView', method => 'C', PARALLELISM => 4)}");

Up to here everything is working fine.

The only issue that I have is that while the MView is beeing refreshed other callers trying to read from the MView are blocked, thus waiting until the refresh is done.

The funny thing is, that I can access the MView from SQL developer. If I do a select from there, the call does NOT wait for the refresh to be finished.

I made sure that the (read) call is done in a fresh Transaction with Oracle's default isolation level und read-only = true. Unfortunately it still blocks...

Since I can access the data from SQL Developer there should be a way to achieve the same from the Java-code....

Michael M
  • 11
  • 3
  • If you give a sample of the "other callers" it would be great. This code has something specific in it, as you should be able to access the stale data during the refresh from other clients,as you succeeded with SQL Developer. May be they use something specific.. – g00dy May 12 '17 at 12:57
  • Actually the code does not do anything special. It is just a one-liner calling a Named Query via a generic superclass of the concrete DAO-Implementation. The select is (in JPQL) select a from AccountManagerCustomerAndInstituteDto a where zipCode in (:zipCodes) – Michael M May 12 '17 at 15:34
  • Cont'd: The DAO gets a Transaction via Spring AOP which has the default Isolation Level set. Like mentioned before I tried different settings with transaction configurations without success. I even tried setting up a complete new DataSource in the application and tried it "the JDBC-way" (by getting a Connection from the DataSource, preparing a statement...) - with the same result: it waits for the MView to be refreshed. – Michael M May 12 '17 at 15:40
  • It seems to me, that the problem is in the EntityManager. Check this bug/enhancement request here -> https://bugs.eclipse.org/bugs/show_bug.cgi?id=282595. Also check this one here http://stackoverflow.com/questions/3735049/setting-persistence-context-to-read-only-in-jpa. I think that the EntityManager actually locks it for the rest of the users, because they most probably use the same method, e.g. `this.getEntityManager().createNativeQuery(...`. The MView should be accessible in a standard Oracle environment, that's why you can select from it from SQL Developer. – g00dy May 15 '17 at 05:35
  • I would suggest a scheduled refresh from within the Database to work around this "deadlock" problem. If that's not possible, then I suggest the Web-App to *not* call the refresh directly, but instead to call another pl/sql procedure, which will do that in a thread, or dbms_scheduler job. – g00dy May 15 '17 at 05:37

1 Answers1

1

Oracle 12g introduced a new refresh option for materialized views

out_of_place=>true

Using this option oracle builds a complete new table with new data in the background and when it's done switches the "pointer" of the MView to this table and drops the old one.

If I use this option when refreshing the view my Java code has access to the (possibly old) data in the old view which is the desired behaviour.

So this cool new parameter (which was new for me) solved my problem!

Michael M
  • 11
  • 3