Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Write lock is highly not desirable. What's a common approach of processing tasks queues without locking?
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, it looks like a plan.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic