How to remove a tuple from an SQL table after a timeout?

Question

Hi,

I am faced with a peculiar requirement which is as follows:
A network-intensive operation is triggered to a server by multiple clients, through a web-interface. However, only one operation is allowed at a time, and hence an entry(tuple) is made in an SQL table to indicate that the operation is in progress. Once the operation is complete (irrespective of success or failure), the appropriate result is displayed back to the client(s), and the corresponding tuple is removed from the SQL table.
Since the operation is network-intensive, a scenario where the operation needs to be "considered" to be cancelled, after some timeout (10 minutes) has to be introduced.
Is there ANY way the lifetime of a row in SQL be associated with a timeout value, so that is is deleted after certain time? My application is primarily written in Java 1.5 and EJB 3.0, using JPA/Hibernate to access Oracle 10g DB engine.

Thanks in advance.

Regards,
Nagendra U M

Answer 1

It sounds like you're implementing a mutex using the database, take a look at this question and see if it helps? Sounds like transactional access to a flag table will solve this for you, as long as you catch both success & failure states in your server code.

Answer 2

I don't know that Oracle has this kind of facility but I think no db engine have this.

If you want to do it at DB level,

1. you must have a datetime column, e.g.; 'CreatedDate' in table. This column will have datetime when record was created.

2. Write a procedure and put it in a schedule job. This job will run after every 10 minutes and remove the 10 minutes old records. The query will be like this.

T-SQL: Please convert it according to your db engine.

DELETE FROM yourtable WHERE CreatedDate < DATEADD(mi, -10, GETDATE())

This will delete all records older than 10 minutes from table.

This is just to give you idea of schedule job. It is in SQL Server. I don't know about Oracle

step_by_step_guide_to_add_a_sql_job_in_sql_server_2005

Answer 3

I would suggest that you try using a timestamp column containing the start time of the task.

A before trigger can be then made to delete the old column before a new one is inserted if the task timed out.

If you want to have multiple tasks with different timeouts, you can even add a column with the timeout in seconds. Just code your trigger accordingly.