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.
Just make the operation synchronized at the server and get rid of the tuple altogether. That way you won't hold up clients for any longer than strictly necessary, and you don't have to worry about the database at all.
The scenario is not that simple. The operation which is triggered is triggered per object+ in the system. (It is basically used to sync the state of the object in my application to the status of a corresponding object in the DB.) Now, I have to stop another client from triggering the operation on THAT object alone; for other objects, the operation can be allowed as long as there are no ongoing sync operations. That is why I am storing the "sync operation status" in the DB, along with the object ID on which it is called.
In case a previously triggered operation on an object "hangs" forever, I need a method to timeout and thereby remove the entry from the DB, so that any client can be in a position to trigger the operation again. So, ideally, if the DB entry could "disappear" after a timeout (if it is not already deleted by the operation completion), then this would be a straightforward solution to the problem.