How to set connection timeout on lcoked record?

Hi,

In order to achieve data accuracy for crucial data, we are locking the records at databse level for some tables while editing -using for e.g

select * from employee where employee_id = 123 for update nowait;

and unless the user who has locked the above record performs a commit / rollback , the record remains locked for other users. They get(ORA-00054: resource busy and acquire with NOWAIT specified error) which is working how we want it to work.

The problem is if the first user who has locked the record does not release the record for some reason, we want the record to be released after a certain interval (say 30 mins.)

Can anyone suggest a way to achieve this in JDBC / Weblogic server (some connection pool setting)?

Any help is appreciated.

Thanks.

I'm not sure I understand the scenario.

Is the user at a console typing this in? If so, how do they leave the connection open? Go for lunch without releasing it?

Or are you using some application that runs this query? In which case, how does it keep the connection open? Is it a Swing app with a remote connection?

Thansks Jeanne for your reply.

Yes, consider this worst case scenario where user accidently leaves the connection open. (you are right. say someone opens the record for editing and goes for lunch.) To handle such scenarios we need to implement the timeout on connection and close it automatically.

Thanks.

I see. I haven't used it, but a timeout parameter looks like it would solve the problem.