SQL select and update
I am trying to select 100s rows in a DB that contains 100000 rows and update those rows after processing.
the problem is I don't want to access the DB twice, as the update only marks these lines as "read".
Is there a way to do this in java using simple jdbc libraries? (hopefully without using stored procedures)
update: ok Here are some explanations.
there are several instances of the same application running on different servers, they all have to select 100 rows from the "UNREAD" rows sorted by the create_date column, read the BLOB data in it, write them to a file and ftp file to some server, (I know prehistoric, but requirements are requirements)
The read and update part is to ensure that each instance receives a different set of data. (ok, tricks like odds and evens won't work: /)
We select the data to update. transferring data over the wire (we wait and wait) and then we update it as "READ". then release the read commit. it all takes too long. By reading and updating at the same time, I would like to reduce the blocking time (from the time we use to update to the actual update) so that when multiple instances are used, the number of read rows per second increases.
Any ideas?
source to share
It seems to me that there may be more than one way to interpret this question here.
- You select lines for the sole purpose of updating them and not reading them.
- You select lines to show to someone, and mark them as read either one at a time or all as a group.
- You want to select lines and label them as read while selecting them.
Let's take option 1 first, as it seems to be the easiest. You don't need to select rows to update them, just issue the update with a WHERE clause:
update table_x
set read = 'T'
where date > sysdate-1;
Considering option 2, you want to mark them as read when the user read them (or the downstream system received it, or whatever). To do this, you will probably have to make another update. If you are querying for the primary key, in addition to the other columns you will need in the first selection, you will probably need an easier update time since the DB will not need to scan tables or indexes to find rows.
JDBC (Java) has a facility for batch updating when you are performing a set of updates at the same time. This worked well when I need to perform many updates that have the same shape.
Option 3 where you want to select and update everything in one shot. I personally don't use much for this, but that doesn't mean others don't. I believe some kind of stored procedure will reduce round trips. I'm not sure what you're working with here and can't really suggest specifics.
source to share
If you are doing SELECT in JDBC and iterating over the ResultSet to UPDATE each row, you are doing it wrong. This is a query problem (n + 1) that will never work well.
Just UPDATE with a WHERE clause that determines which of these rows should be updated. This is one round trip network.
Don't be too code-centric. Let the database do the job for which it was intended.
source to share