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...
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.
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?