• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Select and update on the same table

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic