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?
Any opinions welcomed.