Apr 1st, 2011, 12:43 PM
Oracle AQ dequeue commit question
I have a Spring MDP deployed that listens to an Oracle AQ queue...at first I was using the DefaultMessageListenerContainer to initialize this listener. Everything fired up, I'm dequeueing things, everything's great. Then my DBAs come to me and tell me that the database session being used by this listener NEVER goes inactive. We have a website that thousands of users use day in and day out and at any given point there might be one or two 'active' db sessions being used by that application's connection pool when they refresh their monitoring application of DB sessions...but for this listener bound to this Oracle AQ queue, that session never disappears from their monitoring tools. They can't see much in the session via their monitoring other than the fact that it's definitely the session connecting to the queue on the database.
I then turn on JDBC debugging and restart the server and I see the listener's db connection, once a second, issuing the dequeue query on the DB to see if anything is available to dequeue, and even if nothing is dequeued, the db connection issues a commit. The commits are in turn causing disk writes, log file generation and system impact on performance due to this incessant commit execution.
After a little digging, I decided to change my Spring configuration to use the SimpleMessageListenerContainer instead and now, the server fires up, and watching the JDBC debugging, I still see the dequeue process happening but there's no commit and even though this dequeue query is occurring very frequently by this listener, the DBAs are now happy because from their perspective these sessions go to an inactive status and performance on the DB is back to normal.
I guess my question is, what is inherently different between the default and simple message listener container objects that would be causing this sort of behavior? Is there a way to still use the DefaultMessageListenerContainer object but NOT have the commit happen every single time?
I know this is a unique situation because the listener is listening to a database-derived queue and not a traditional queue deployed on something like ActiveMQ, so a commit in the ActiveMQ paradigm versus a database commit on an Oracle database is a different beast and I tried playing with the various attributes of the DefaultMessageListenerContainer class using the Spring APIs...concurrency, retrieveTimeout, sessionTransacted...I even started playing with my datasource configuration thinking it might have something to do with that...but there's something within the session and transaction management being done by the DefaultMessageListenerContainer that obviously is not present in the simple listener container and makes my life better but I want to understand why that is instead of just blindly accepting this is fixed...so anyone patient enough to read this and that has some insight, I would forever be grateful for an explanation.
Thanks so much!