File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Select and update on the same table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select and update on the same table" Watch "Select and update on the same table" New topic
Author

Select and update on the same table

pat ker
Greenhorn

Joined: Apr 25, 2010
Posts: 1
Hello

I am facing a probelm that I can not find any "nice" solution.

I have one select (that can read thousands of rows), in the loop where I read my resultset, I make some checks, some addition, and in some cases, I want to run an update. The update is on the same table as the selct. The update changes values on the last row and also on a few rows that were read before in the select.

At first, I did it using hibernate with scrollableresult for the select, and an update using hql. It was working good but as my application is multi-users, if someone else do another query on the same table at the same time, it seems the table is locked by the select/update. Because of that, all my application crashes when this happens.

Then I wanted to change the select/update and write it fully in SQL. But when the update launch, it close the resultset of the select and my server crashes...

So actually I have no idea how I should do this "select/update in some cases".
I could maybe write the select in a table and run them when th select is done but this is definitively not nice way of programming...

Please help.

thanks
kv ruby
Ranch Hand

Joined: Jul 23, 2009
Posts: 38
Just verifiying.

are you using different resultsets for both the operations?

Actually i have done a similar case in my project and am using diffrent connection and resultset objects and closing it after each operation is performed and commiting of each operation is also prformed.

Handle each operation in separate try/catch block and rollback if any such error occurs so even if one fails it should not effect the other operations.


Regards, ruby kv
rubyshiv.blogspot.com
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30138
    
150

Pat,
Welcome to JavaRanch!

pat ker wrote:It was working good but as my application is multi-users, if someone else do another query on the same table at the same time, it seems the table is locked by the select/update.

This is a design problem and not a technology problem. If you really need to lock the whole table (or thousands of rows), you are going to have issues with multiple users regardless of whether using Hibernate or JDBC. (A stored procedure might help as it would at least cut down on the network traffic on transferring thousands of rows.)

What is your application doing locally with those thousands of rows? Is it something that can be done on the database? Can the transaction be made shorter?


[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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Select and update on the same table
 
Similar Threads
Update based on Row id
Table backup - programmatically using JDBC
JDBC connection
JDBC connection query
Problems Processing Large ResultSet