Infinispan, Hibernate and direct database updates

[dzcs]

Dear All,

We looking for the opportunity to utilize Hibernate as ORM for our backend database (Oracle) and Infinispan as L2 cache for Hibernate. The challenge that we have to face is that in pretty long transition period we will still have other pieces of old software, which will hit database directly. The problems which I see are following:

1. How to invalidate cache in case of direct database updates done by other software pieces? Is there a callbacks in infinispan or hibernate which enable this or we will have to implement this using for example CQN and JMS?

2. How to enable direct replication of changes made on entities to database? The single point of truth will be still the database. 

Thank you in advance and hope for interesting discussion.

[sannegrinovero]

Hi Dmitri,

if you have other applications making direct changes to tables via SQL there is no way Hibernate can detect that; are you assuming you will be able to update the "pieces of old software" to somehow notify either Hibernate or Infinispan?

If your older software is performing only read-only SQL you would be safe, but I assume it does writes too or you wouldn't ask this question. Maybe you can identify which tables are being updated, and use caching only on the remaining ones? That would be much simpler.

Other alternatives would be to define trigger rules in the Oracle database which invoke Java code and notify Hibernate directly.

[dzcs]

Hi, thank you for you answer. Unfortunatelly it is not possible to restrict access only to read hits.

But what I have successfully tested is to send messages from Oracle using Oracle Streams back to Java application (JMS). It is possible to control changes and to invoke messaging process at Oracle site. However I wounder if it is possible then somehow to use these messages comming to jms topic to invalidate entity cache. Does anybody has already done this?

[sannegrinovero]

I don't think anybody tried this before, but I agree it's a very interesting approach.

I'm pretty sure that looking into the Infinispan second level cache implementation you could see how values are invalidated:

https://github.com/hibernate/hibernate-orm

If you could experiment with this as a proof of concept, I think it would be a great feature to contribute to Hibernate, even if obviously not all databases are able to call back into Java.

[smarlow]

Better would be a blocking (possibly with timeout for ultimate flexibility) database stored procedure call that returns an array of cache invalidation notification event type information (you need to create the datastructure).  For parameters, passing the process ids/names that should be ignored, would be ideal (to ignore invalidations that are already known to the app server environment).  Then invalidate the 2lc entries that are identified.  This would make a great wiki or blog page when your done. 

[galder.zamarreno]

If you want to invalidate the caches and you can get hold of the session factory, you'd call:

sessionfactory.getCache().evictEntityRegions();

sessionfactory.getCache().evictCollectionRegions();

sessionfactory.getCache().evictNaturalIdRegions();

sessionfactory.getCache().evictQueryRegions();

The question would be how to do this from a database trigger. You'd need to communicate somehow between the database and the app to be able to execute this.

If you can implement a trigger in java which lives within the app, then you'd just need to get hold of the session factory. If not, something like JMS would help.

[dzcs]

Thank you Galder. This is the whole point of the idea.

Well our solution will use JPA. But at the other hand it will run on JBossAS7 so Hibernate. And we will configure Inifinispan as L2 cache.

It is possible to create topic on the side of Oracle database and it is possible to track on changes (the problem is however how to separate direkt hits and hibernate hits, but still...) and then send the message in topic. There is a library from Oracle implementing JMS 1.1 so it is possible to listen on the topic.

Now what I know from the topic here https://community.jboss.org/message/763259#763259 it is not really possible to use OracleAQ (Oracle Streams) as messaging provider in JBoss. Thus as I learned here http://docs.jboss.org/hornetq/2.2.14.Final/user-manual/en/html_single/index.html it must be possible to configure the bridge - practically to bridge Oracle topic to Hornet topic in JBossAS7. Is there any other solution possible? Does any body tried this?

Now having that (actually I have not tried that approach with bridging. Sounds good and might work, but still ...) I would be able to react on topic messages in my application. The question is how to get the appropriate session factory to do eviction? And how can I invalidate entities in Infinispan if I would get a message, that the record with rowid X and primary key Y in table A was changed?

[dzcs]

Hi guys,

just to keep you informed. I created extension for JBoss which utilizes hornetq JMSBridgeImpl and AQjms to transfer messages from oracle topic to hornetq topic und vice versa. Having properly created MDB it is possible to react on messages from oracle topic.

What I found out is actually I have to habe entity class and key to evict or invalidate them in infinispan cache. From oracle I can have only schema, tablename, rowid and primary key if i want. So the next step is to build a mapping entity_class<=>schema/table. For that I created deployment processor in my extension. Using deployment processor I have the possibility to check deployment and I have access to virtual file system. The original idea is to scan the virtual file system. But this approach seems to me to be not time and cost effective.

The question which I have right now - is there a possibility to listen on JPA deployments and undeployments? To be able to get this information cheaper. Does anybody have any information to this question?

[dzcs]

Hi Guys,

as you already know I have created an extension for JBoss which incapsulates JMS bridge for oracle aq and entitiy mapper, which listens on deployments and aggregate mappings deployment-unit -> persistence-unit  -> table  -> class. In addition I have created MDB which reacts on JMS messages from oracle. So far message include set of update information. Each update information is schema, table, row id and primary key value (so far only single field Pk). In MDB i am getting reference to EmbeddedCacheManager using JNDI name "java:/jboss/infinispan/container/hibernate". In on message method I am getting the required cache:

Cache<Object, Object> cache = cacheManager.getCache(cacheName, false);

where cacheName = [deployment-unit-name]#[persistence-unit-name].[class-name]

I have tryed cache.evict(pkKeyValue), but nothing happend. For test I have tryed also with the same PK class as declared in entity. Also nothing. The cached entity was still there.

Can me somebody say how can I do it properly?

Another question is how to supply composite keys for eviction?

Thank you very much in advance.

[dzcs]

Hi Guys,

I have done it. So basically it was tricky to find out the CacheKey, so the only not nice feature is that I have to look for a CacheKey in Cache and cannot create my own cache key, because I have no access to session factory etc. But it works and I am getting entities evicted in L2C.

I am sorry, but I cannot post any sources etc in forums, because of my present contract with my customer.

Thank you for help!

[sannegrinovero]

Hi Dmitri,

that's great, congratulations

I don't need your sources on forums, can you not share it on github or similar ?

More seriously, I'd hope you could talk about this with your customer; if not thank you for all the details that's a good help in case someone else needs to make a similar thing, or some volunteer finds the time to contribute such a feature to Hibernate.