Updating rows in result set, should select for update be used?

[D Rog]

I'm working on processing tasks queue stored in a table. For every task I have a flag with a status, as issued, processed, and complete. Is it right approach of processing a queue like this by executing a query like:
select task,status from task_queue where status='issued'
then do like:

while (rs.next()) {
  rs.updateString("status", "processed");
  try {
     processTask(rs.getInt("task"));
     rs.updateString("status", "complete");
  }catch(TaskException te) {
     rs.updateString("status", "error");
  }
  rs.updateRow();
}

Or I have to use something like
select task,status from task_queue where status='issued' for update of status
?
[ February 15, 2007: Message edited by: D Rog ]

[Jeanne Boyarsky]

Originally posted by D Rog:

Or I have to use something like
select task,status from task_queue where status='issued' for update of status

It depends on your database. Oracle requires "for update" to get a write lock.

You will want to keep an eye on database contention with this. If processing takes any length of time, this thread has a write lock on the rows for some time.

[D Rog]

Write lock is highly not desirable. What's a common approach of processing tasks queues without locking?

[Purushoth Thambu]

You can issue update dml after call to processTask method. Only thing you need to be careful is you should NOT read the status flag from resultset (rs) as the value will be dirty. The more cleaner way will be to select 20/n record at a time and copy into a DTO/VO collection, iterate the collection, process and update the status flag. This way you can release the connection and also don't worry about reading dirty value.

In the update DML you can filter by task and status so that you update the status only if it's still in 'issued' status.
[ February 15, 2007: Message edited by: Purushothaman Thambu ]

[D Rog]

Thanks, it looks like a plan.