I'm curious about how the following scenario might behave. If any one has any ideas please post.
Scenario: 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?
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 ]