File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Concurrent UPDATE...WHERE behaviour Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Concurrent UPDATE...WHERE behaviour" Watch "Concurrent UPDATE...WHERE behaviour" New topic

Concurrent UPDATE...WHERE behaviour

Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

I'm curious about how the following scenario might behave. If any one has any ideas please post.

I have an application running in a clustered environment. The application uses a "listener" thread which polls a table in the DB to process records in a queue-like fashion (i.e. read a record, do something then delete the record). There will obviously be a thread per JVM - so access to this one table is open to concurrency problems. To flag to a process that a record is already being processed (so I don't get the same piece of logic being run more than once for one record) what I though I'd do is set a PID field for each record which doesn't already have one and only process those records where the thread's PID matches the record PID value. To do this I do "UPDATE [table] WHERE PID = null". I think this is OK - but to complicate the issue, I can't lock the table, and as I understand most DB platforms use row level locking for update statements.

What I'm worried about is that there may be conflicts where multiple threads issue multiple "UPDATE....WHERE [condition]" statements. Suppose thread1 starts this process and there are 100 records in the table. It gets to the 70th record then thread2 starts. The condition is basically where PID = null so at the point thread2 starts its condition matches 30 records. Does anyone know if this means that basically thread1's update will be truncated or will there be deadlocks?

Any opinions welcomed.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Neither problem will occur. Individual database operations such as UPDATE are atomic with respect to each other. In other words, the first UPDATE will get all the rows fulfilling the WHERE condition at that time; the second one might get rows that have been added while the first UPDATE was executing. Do take care though that your PIDs are really unique - if they are Unix process IDs they are not.

Not sure that this is the best possible implementation, but it would work. What surprises me though is that you actually spend time writing plumbing like this. What's wrong with, say, JMS?

- Peter
[ July 22, 2004: Message edited by: Peter den Haan ]
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Cheers Peter. That's pretty much what I though - but I just needed clarification. PID's will come from our PK generation startegy - so will be unique.

Just so you know: JMS isn't an option because (in the context of our app) it would require XA, and not all the DBs/JDBC drivers the app needs to support support XA.
I agree. Here's the link:
subject: Concurrent UPDATE...WHERE behaviour
It's not a secret anymore!