Efficient transaction, record locking

Question

I've got a stored procedure, which selects 1 record back. the stored procedure could be called from several different applications on different PCs. The idea is that the stored procedure brings back the next record that needs to be processed, and if two applications call the stored proc at the same time, the same record should not be brought back. My query is below, I'm trying to write the query as efficiently as possible (sql 2008). Can it get done more efficiently than this?

CREATE PROCEDURE GetNextUnprocessedRecord
AS
BEGIN
    SET NOCOUNT ON;

    --ID of record we want to select back
    DECLARE @iID BIGINT     

    -- Find the next processable record, and mark it as dispatched
    -- Must be done in a transaction to ensure no other query can get
    -- this record between the read and update
    BEGIN TRAN

     SELECT TOP 1
      @iID = [ID]
     FROM
      --Don't read locked records, only lock the specific record
      [MyRecords] WITH (READPAST, ROWLOCK)
     WHERE
      [Dispatched] is null
     ORDER BY
      [Received]

     --Mark record as picked up for processing 
     UPDATE 
      [MyRecords]
     SET
      [Dispatched] = GETDATE()
     WHERE
      [ID] = @iID  

    COMMIT TRAN

    --Select back the specific record
    SELECT 
     [ID],
     [Data]
    FROM 
     [MyRecords] WITH (NOLOCK, READPAST)
    WHERE
     [ID] = @iID

END

Answer 1

It is recommended for high throughput in a highly transactional database system, that a locking mechanism be placed in a separate table, rather than directly in the active table.

Answer 2

You can assign each picker process a unique id, and add columns pickerproc and pickstate to your records. Then

UPDATE MyRecords
SET pickerproc = myproc,
pickstate = 'I' -- for 'I'n process
WHERE Id = (SELECT MAX(Id) FROM MyRecords WHERE pickstate = 'A') -- 'A'vailable

That gets you your record in one atomic step, and you can do the rest of your processing at your leisure. Then you can set pickstate to 'C'omplete', 'E'rror, or whatever when it's resolved.

I think Mitch is referring to another good technique where you create a message-queue table and insert the Ids there. There are several SO threads - search for 'message queue table'.

Answer 3

You can keep MyRecords on a "MEMORY" table for faster processing.

Answer 4

Using the READPAST locking hint is correct and your SQL looks OK.

I'd add use XLOCK though which is also HOLDLOCK/SERIALIZABLE

...
[MyRecords] WITH (READPAST, ROWLOCK, XLOCK)
...

This means you get the ID, and exclusively lock that row while you carry on and update it.

Edit: add an index on Dispatched and Received columns to make it quicker. If [ID] (I assume it's the PK) is not clustered, INCLUDE [ID]. And filter the index too because it's SQL 2008

You could also use this construct which does it all in one go without XLOCK or HOLDLOCK

UPDATE
    MyRecords
SET
    --record the row ID
    @id = [ID],
    --flag doing stuff
    [Dispatched] = GETDATE()
WHERE
    [ID] = (SELECT TOP 1 [ID] FROM MyRecords WITH (ROWLOCK, READPAST) WHERE Dispatched IS NULL ORDER BY Received)

UPDATE, assign, set in one