concurrent updates - how to control them ?
Hello,
I am having two threads run an update statement. The statement is
update someTable set status = 'In Progress' where ID = ? and statusDate = ? and status = ?
Java code :
int numberOfRowsUpdated = ps.executeUpdate();
if (numberOfRowsUpdates > 0) {
//This thread succesfully updated the status from 'Pending' to 'In Progress'.
// The idea is - this thread now processes this request and all other threads trying to execute the same statement on same ID will have numberOfRowsUpdated = 0 and hence will have to select some other request from the table (with different ID)
//However I saw that more than one thread ends up processing the same request !
}
Furthur explanation -
Task - There are requests placed in a table to be processed at night. Each request will have a unique ID. It will have a status of 'Pending' indicating that it needs to be processed.
There is a statusDate column which indicates when the status was changed. (So, whenever I change the status I have to change the statusDate to sysDate of oracle)
I spawn lot of threads at night to pick up the requests. As you can see no two threads should process the same request.
My logic - Instead of using 'Select for update', I wanted to do a regular select. Obviously more than one thread will end up getting the request ID (because I select the oldest request first).
-> Then I update the status to 'In Progress' using the statement above. I thought only one thread will be able to successfully update the request with an ID.
Example - 2 threads got the same ID = 1, status = 'Pending' statusDate = '06/10/07 10:15:35'
Thread 1 - update someTable set status = 'In Progress' where ID = 1 and statusDate = '06/10/07 10:15:35' and status = 'Pending'
If we assume Thread 1 executed this statment first, then by the time Thread 2 executes the same query
Thread 2 - update someTable set status = 'In Progress' where ID = 1 and statusDate = '06/10/07 10:15:35' and status = 'Pending'
It will fail because the status is no longer 'Pending', it has been changed to 'In Progress' by Thread 1.
Hence I thought, only 1 thread should be able to get a request. But, thats not my finding !
Any Help ?
