SQL record locking (to prevent other READS?)

[BungleBonce]

I have an application which selects one record at a time from a SQL SERVER 7.0 database. After doing some business logic, it's updates the same record with an status flag change. I need to run the application on 3 different machines concurrently. When I perform my SELECT of one row, how do I lock the selected record to prevent the same record being read by each instance of the application? Thanks in advance. :) Cheers Adrian

[andyharman]

I normally do something like this:

create procedure dbo.GetNextAvailable
as begin
  declare @id int
  set nocount on

  --Start transaction so that locking works.
  begin tran

  --Get Id of next record to process.  The "holdlock" hint tells SQL
  --Server to place a lock on the row until the end of the transaction.
  --The "readpast" hint tells SQL-Server to ignore a record if it is
  --locked by one of your other two processes. Note that this only works
  --for "read committed" isolation.
  select top 1 @id = MyId from MyTable with (holdlock, readpast)
    where status = 'READY'
    order by MyId

  --Mark the record as "in-progress" so that no-one else can pick-up
  --the current record.
  update MyTable set status = 'IN PROGRESS'
    where MyId = @id
    and status = 'READY'

  --Complete the transaction.
  commit trans

  --Return the record to the front-end for processing.  If no records
  --returned then there are no records left to process.
  select * from MyTable where MyId = @id

  return(0)
end

You could potentially move the transaction outside of the stored procedure. I normally update the status to "SUCCESS" or "FAILURE" after I have completed processing of the record. If the record processing is substantial then I normally also maintain start and end times for each record. Regards Andy

[BungleBonce]

Thanks Andy, that looked like a good solution. However due to a Microsoft bug, I can't use the HOLDLOCK keyword with READPAST as I kept getting the following message: "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels." The bug is confirmed in this KB article: http://support.microsoft.com/kb/171322[^]

[andyharman]

Your error message infers that your isolation level is not "committed read" (hence my warning about this in my original response). The MS article infers that you are trying to set the isolation-level in the "GetNext" SP, after the transaction has been started. If you are using ADO then the connection's "IsolationLevel" property should be set to "adXactReadCommitted" before the transaction starts. You need to be really careful about the isolation level and the scope of your transaction otherwise you will find that your separate processes will have to wait for each over to complete - if you do it really wrong then you could find that parallel processing actually takes longer than using just a single process. The technique that I specified should work even if you don't use the "READPAST" hist specified (so long as you don't start a transaction in your client-side code until AFTER you have called my SP. Hope that helps. Andy