确保使用JDBC原子读取数据库表的最佳实践是什么? [英] What's the best practice to ensure atomic read of a database table using JDBC?

问题描述

我有一个Java应用程序正在从数据库表中读取要处理的作业，并且由于每个作业都是独立的，因此我可能有该应用程序的多个实例在不同的服务器上运行.拾取作业进行处理后，其状态将更新为正在运行".我要确保的是从每个实例中检索要处理的作业都是原子的，如何使用JDBC实现呢?

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?

推荐答案

一种完全通用的方法 ^* ，尽管可能效率不高，但它会使用服务器特定的标识符来声明"作业，方法是先将其状态更新为该标识符，然后根据该值检索该作业.例如，如果您在同一网络上使用Windows服务器，则它们的服务器名称将唯一地标识它们.如果你的桌子看起来像

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

其中无人认领的作业的状态为NULL，那么您在SERVER1上运行的应用程序可以通过执行setAutoCommit(true)后跟

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)

如果ExecuteUpdate返回0，则没有任何待处理的作业.如果返回1，则可以得到带有

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=?

在其中提供从上一个SELECT检索到的JobID.

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

^* (即，不依赖于任何特定的SQL扩展，显式锁定或事务处理)

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

这篇关于确保使用JDBC原子读取数据库表的最佳实践是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！