Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Best way to mark a job as 'underway'?

Author  Topic  spoddy Starting Member 5 Posts Posted - 2001-06-14 : 12:05:41 This is my first post here, so please forgive any bonehead mistakes ;-) SQL7 (SP3) on W2K 4*CPU 3.5Gb RAM I have a table containing (amongst other things) the JobID and a status flag tinyint, which is set to 0 to signal that this job needs to be performed. A priority column allows me to boost order in which the jobs get done. The idea is that an automated client selects the next job to be completed, marks the job as active (by setting the status tinyint to 2) goes away and completes the job and returns to signal that the job is complete (by setting the status tinyint to 1) The client also does some additional logging so that I can work out who is doing what - and I've discovered that the job is being allocated multiple times (sometimes as many as ten times). Sample code: DECLARE @JobID int SELECT TOP 1 @JobID = JobID FROM tblJobList WHERE StausID = 0 ORDER BY Priority DESC IF @@RowCount = 1 BEGIN -- Mark it as having been allocated UPDATE tblJobList SET StatusID = 2, WHERE JobID= @JobID -- Other data is returned to the client at this point RETURN END ELSE --There are no more jobs to be performed --So do nothing - time to relax with a beer! We have 6 of the automatic clients performing the tasks (VB clients) We're hitting the DB quite frequently so I assume that the clients are making simultaneous requests and that it performs several of the SELECTS before the UPDATE achieves an update lock to prevent the job from being re-selected. My first idea was locking but having read the books-online and 'Inside SQL Server' I am now confused as to the best way to do this. This most appropriate type of lock (as I understand it) is an update lock, but this won't prevent the other processes from SELECTing the same ID, they will just queue up before the UPDATE happens. I really don't want to use a table lock as many other things need quick access to the table. My next idea was an UPDATE lock with the SELECT using a READPAST, but I didn't get the impression that you could specify both types of lock for a single transaction. What about combining the two statements into one. UPDATE tblJobList SET StatusID = 2 WHERE JobID IN ( SELECT TOP 1 JobID FROM tblJobList WITH (READPAST) ORDER BY Priority DESC ) IF @@RowCount = 1 BEGIN SELECT @JobID=@@Identity END Would that get round the problem? What I basically need is a way to prevent other SELECTs getting the same JobID before the DB has a chance to commit the UPDATE. Is locking even the right way to do this? Could I do a kind of UPDATE TOP 1 type thing and retrieve @@Identity for the jobID? I know that's quite a long post with a lot of questions but I would Greatly appreciate any thoughts and ideas people have. Thanks in advance -Adam Quick, Reliable, Cheap. - Choose two.