Oracle – Session still holding the locks

javaoracle

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?

Best Answer

My suggestion will be to add timeout if response is taking time, and if any thing fails use until successful of mule.

Related Solutions

The Database Session is automatically Getting Killed

I would hope you have some session pooling going on, or at least one database connection per thread. This is not clear from your description. In a multi-threaded design, I would not close connections in the threads unless they make their own connections (which I would consider a bad design).

I would get the process running with one thread, then add an additional thread. Once you are comfortable you have that multi-threading gives you the results you want, you can scale up.

You could turn on auditing on the Oracle side to see what is really happening.

You didn't say how long the queries take to run. If they take minutes, to run you need to ensure you have keep-alive on the network connections. Otherwise, the network connection may get dropped.

EDIT: Check the resource limits applicable to the userid you are connecting with. These might get your connections closed. You may also be getting killed off due to exceeding resource limits. I would expect the reason for disconnect to be in the SQL Error text for the connection.

Given how long these queries are taking, you may want to investigate optimizing the queries. Unless you have lots of memory and processors on the database server you may find that the queries run faster when run serially. You may be forcing data out of the buffer pool with this many long queries running in parallel.

Why does dropping a MVIEW trigger a full refresh

After running some tests in an isolated environment it seems that Raj is right: the refresh background job that I saw while waiting for the DROP wasn't triggered by the DROP. As far as I can tell this was caused by the refresh settings provided during the creation of the mview.

It seems that

refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour

together with the implicit build immediate causes the mview to be refreshed during the creation and then again immediately after the creation has finished.

Apparently start with trunc(sysdate) + 1/8 causes this. When running

create materialized view foobar 
   BUILD DEFERRED
   refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;

The create statement finishes quickly and when I then look into the active sessions I do see one background session that does the refresh.

Related Question