aspose file tools*
The moose likes JDBC and the fly likes Updating rows in result set, should select for update be used? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updating rows in result set, should select for update be used?" Watch "Updating rows in result set, should select for update be used?" New topic
Author

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

D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

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 ]

Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30516
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

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

Joined: May 24, 2003
Posts: 425
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

Joined: Feb 07, 2004
Posts: 472

Thanks, it looks like a plan.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Updating rows in result set, should select for update be used?