Have Oracle automatically roll back abandoned sessions?

Question

Is there any way to guarantee that an application won't fail to release row locks in Oracle? If I make sure to put commit statements in finally blocks, that handles the case of unexpected errors, but what if the app process just suddenly dies before it commits (or someone kicks the power cord / lan cable out).

Is there a way to have Oracle automatically roll back idle sessions after X amount of time? Or roll back when I somehow detects that the connection was lost?

From the experiments I've done, if I terminate an app process before it commits, the rows locks stay forever until I log into the database and manually kill the session.

Thanks.

Answer 1

I am not a DBA so I am sure you can find a better solution...

but there are certain deadlock conditions that seem to happen that will not roll back on our own. My last DBA had a process that would run every minute and kill anything that had been running more than 10 minutes.

Answer 2

Try setting SQLNET.EXPIRE_TIME in your sqlnet.ora.

SQLNET.EXPIRE_TIME=10

From the documentation:

Purpose
To specify a time interval, in minutes, to send a check to verify that client/server connections are active.

Answer 3

COMMIT inside finally is probably the last thing you should do since you should (almost) never commit anything that threw an exception.