Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Select and update on the same table

 
pat ker
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic