What's the best practice to ensure atomic read of a database table using JDBC?

Question

I have an java application reading from a database table for jobs to process, and I may have multiple instances of this application running on different servers as each job is independent. Once a job is picked up for processing, its status will be update to "running". What I want to make sure is the retrieval of to be processed jobs from each instance to be atomic, how can I achieve this using JDBC?

Answer 1

One approach that would be completely generic^*, though perhaps slightly inefficient, would be to use a server-specific identifier to "claim" a job by first updating its status to that identifier, then retrieve the job based on that value. For example, if you were working with Windows servers on the same network then their server name would uniquely identify them. If your table looked like

JobID  JobName  Status
-----  -------  ---------
    1  Job_A    Completed
    2  Job_B
    3  Job_C

where unclaimed jobs have a Status of NULL then your application running on SERVER1 could claim a job by doing setAutoCommit(true) followed by

UPDATE Jobs SET Status='SERVER1'
WHERE JobID IN (
    SELECT TOP 1 JobID FROM Jobs 
    WHERE Status IS NULL
    ORDER BY JobID)

If ExecuteUpdate returns 0 then there are no jobs pending. If it returns 1 then you can get the row with

SELECT JobID, ... FROM Jobs WHERE Status='SERVER1'

and then update its Status to 'Running' with a parameterized query like

UPDATE Jobs SET Status='Running' WHERE JobID=?

where you supply the JobID you retrieved from the previous SELECT.

^*(i.e., not relying on any specific SQL extensions, explicit locking, or transaction handling)

Answer 2

Lock the table using whatever mechanism is supported by your database server.

For example, in Postgres it would be:

LOCK yourtable;

And it's your table for the duration of the transaction.

Other databases will have something similar.