Efficient Transaction, Write Lock

Question

I have a stored procedure that selects 1 entry back. The stored procedure can be called from several different applications on different PCs. The idea is that the stored procedure returns the next record to be processed, and if two applications call the stored process at the same time, the same record should not be returned. My query is below, I am trying to write the query as efficiently as possible (sql 2008). Could this do 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

Using the hint to lock READPAST is correct and your SQL looks fine.

I would add the use of XLOCK, although this is also HOLDLOCK / SERIALIZABLE

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

This means that you get the identifier and exclusively block this line during its transfer and update.

Edit: Add the index to the Sent and Received columns to make it faster. If [ID] (I assume PK) is not clustered, INCLUDE [ID]. And filter the index too, because it is SQL 2008

You can also use this construct, which does everything all at once 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, install in one

Answer 2

You can assign a unique identifier to each selection process and add pickerproc and pickstate columns to your records. Then

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

This gives you your entry in one atomic step, and you can do the rest of your processing at your leisure. You can then set pickstate to "C'complete", "E'rror" or something else when it resolves.

I think Mitch refers to another good technique in which you create a message queue table and insert the identifiers there. There are several SO threads - looking for a "message queue table".

Answer 3

You can save MyRecords in the "MEMORY" table for faster processing.