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.

Answers


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


Need Your Help

Deserialize XML file

c# xml serialization xml-serialization deserialization

I have an XML file containing 4 different strings, but I am having trouble deserializing the file. Could someone help me with this?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.