How to lock read sql server for scheduler in multiple machine
I have a scheduler (created with c#) running in multiple machines, accessed a single database. on the table there are list of job to do. like below:
ID JobDetail Flag 1 --- 1 2 --- 0 3 --- 0 Flag: 0: open, 1: inprogress, 2: completed
my question is what should i do to make sure that there will no scheduler execute the same job.
sorry this question should be very general, if there is a dupe please point me a link.
try something like this:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; GO DECLARE @CURR_JOBID int; SELECT @CURR_JOBID=top 1 ID FROM JOB_TABLE WHERE Flag=0 and <other condtions> BEGIN TRANSACTION UPDATE JOB_TABLE set Flag=1 where ID=@CURR_JOBID COMMIT SET TRANSACTION ISOLATION LEVEL READ COMMITED; GO
Now Use @CURR_JOBID for further processing
Once the job is finished, update the Flag to 2