We are using MULE(Anypoint Studio) as the ESB(Enterprise Service Bus) layer. We are using bitronix transaction manager. Database connection to ORACLE are made inside the transaction through EJB running on JBOSS.
We have situation in which the MULE server will be stopped without the transaction being COMMITTED OR ROLLED BACK.
When i restart the MULE server, new ejb connections are being made and the old sessions are not getting timed out.
When i query the db using :
select sid, serial# from v$session where sid in ( select session_id from dba_dml_locks where name = '<table_name>');
I can see that two sessions are still active and holding the locks to the table. When will the locks on the table be released. Should i set any property in JBOSS or MULE or ORACLE for this timeout? I can also see that manually killing those sessions, releases the locks. But this is not possible in the production environment. Wont the sessions be timed out automatically?
I can see that changing IDLE_TIME of the profile in ORACLE releases the locks after the given time.
ALTER PROFILE DEFAULT LIMIT IDLE_TIME <time>;
But the client would not accept this, because it affects the complete profile. Is there any way to timeout only the idle sessions of a particular table?