Effiziente Transaktion, Rekordverriegelung

Ich habe eine gespeicherte Prozedur, die 1 datasatz zurück wählt. die gespeicherte Prozedur könnte von verschiedenen Anwendungen auf verschiedenen PCs aufgerufen werden. Die Idee ist, dass die gespeicherte Prozedur den nächsten datasatz zurückbringt, der verarbeitet werden muss, und wenn zwei Anwendungen gleichzeitig den gespeicherten Proc aufrufen, sollte derselbe datasatz nicht zurückgebracht werden. Meine Abfrage ist unten, ich versuche, die Abfrage so effizient wie möglich zu schreiben (sql 2008). Kann man es noch effizienter machen?

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 

Die Verwendung des READPAST-Sperrhinweises ist korrekt und Ihr SQL sieht ok aus.

Ich würde die Verwendung von XLOCK hinzufügen, was auch HOLDLOCK / SERIALIZABLE ist

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

Das bedeutet, dass du die ID bekommst und diese Zeile exklusiv sperre, während du weitergehst und sie aktualisierst.

Bearbeiten: fügen Sie einen Index auf Dispatched und Received Spalten, um es schneller zu machen. Wenn [ID] (ich nehme an, dass es das PK ist) ist nicht gruppiert, INCLUDE [ID]. Und filtern Sie den Index auch, weil es SQL 2008 ist

Du könntest auch dieses Konstrukt benutzen, das alles in einem Zug ohne XLOCK oder HOLDLOCK macht

 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, zuordnen, einsetzen

Sie können jedem Picker-process eine eindeutige ID zuordnen und Spalten Pickerproc und Pickstate zu Ihren datasätzen hinzufügen. Dann

UPDATE MyRecords
SET pickerproc = myproc,
pickstate = 'I' – für 'I'n process
WHERE Id = (SELECT MAX (Id) von MyRecords WHERE pickstate = 'A') – 'A'vailable

Das bekommt man in einem atomaren Schritt, und du kannst den Rest deiner Verarbeitung zu deiner Freizeit machen. Dann können Sie Pickstate zu 'C'omplete', 'E'rror, oder was auch immer, wenn es getriggers ist.

Ich denke, Mitch bezieht sich auf eine andere gute Technik, wo Sie eine Message-Queue-Tabelle erstellen und die Ids dort insert. Es gibt mehrere SO-Threads – suche nach 'Message Queue Table'.

Sie können MyRecords auf einer "MEMORY" -Tabelle für eine schnellere Verarbeitung halten.