Korrekte Art und Weise der Erzeugung von Bestellnummern in SQL Server

Question

Diese Frage gilt sicherlich für einen viel größeren Bereich, aber hier ist sie.

Ich habe eine grundlegende E-Commerce-App, wo Benutzer, natürlich genug, Bestellungen aufgeben können. Besagte Aufträge müssen eine eindeutige Nummer haben, die ich versuche, gerade jetzt zu generieren.

Jede Bestellung ist anbieterspezifisch. Im Grunde habe ich eine OrderNumberInfo (VendorID, OrderNumber) Tisch. Nun muss ich jedes Mal, wenn ein Kunde eine Bestellung aufgibt, die Zahl OrderNumber für einen bestimmten Anbieter und geben diesen Wert zurück. Natürlich möchte ich nicht, dass andere Prozesse mich dabei stören, also muss ich diese Zeile irgendwie exklusiv sperren:

begin tranaction

    declare @n int
    select @n = OrderNumber 
      from OrderNumberInfo 
      where VendorID = @vendorID

    update OrderNumberInfo 
      set OrderNumber = @n + 1 
      where OrderNumber = @n and VendorID = @vendorID

commit transaction

Nun, ich habe gelesen, dass select ... with (updlock rowlock) Ich habe mich mit der Frage beschäftigt, ob ich nicht doch noch ein pessimistisches Schloss finden könnte, aber ich kann das alles nicht in ein kohärentes Bild einordnen:

• Wie wirken sich diese Hinweise auf die Snapshot-Isolation von SQL Server 2008 aus?
• Führen sie Sperren auf Zeilenebene, Seitenebene oder sogar auf Tabellenebene durch?
• Wie wird es toleriert, dass mehrere Benutzer versuchen, Zahlen für einen einzigen Anbieter zu generieren?
• Welche Isolationsstufen sind hier angemessen?
• Und überhaupt - wie kann man solche Dinge tun?

EDITAR

Nur um einige Dinge klarer zu machen:

• Die Leistung in dieser speziellen Ecke der Anwendung ist absolut kein Problem: Bestellungen werden relativ selten aufgegeben und erfordern einen teuren Aufruf des Webdienstes des Anbieters, so dass eine Verzögerung von 1 Sekunde durchaus tolerierbar ist.
• Wir realmente die Bestellnummern der einzelnen Lieferanten müssen unabhängig und fortlaufend sein

Answer 1

Sie könnten eine OUTPUT Klausel. Damit sollte alles atomar ablaufen, ohne dass eine Transaktion erforderlich ist.

-- either return the order number directly as a single column resultset
UPDATE OrderNumberInfo 
SET OrderNumber = OrderNumber + 1
    OUTPUT DELETED.OrderNumber
WHERE VendorID = @vendorID

-- or use an intermediate table variable to get the order number into @n
DECLARE @n INT
DECLARE @temp TABLE ( OrderNumber INT )

UPDATE OrderNumberInfo 
SET OrderNumber = OrderNumber + 1
    OUTPUT DELETED.OrderNumber
    INTO @temp ( OrderNumber )
WHERE VendorID = @vendorID

SET @n = (SELECT TOP 1 OrderNumber FROM @temp)

Die obigen Beispiele gehen davon aus, dass die VendorID Spalte hat eine eindeutige Einschränkung, oder zumindest, dass es nur eine Zeile pro Anbieter-ID sein wird. Wenn das nicht der Fall ist, werden Sie möglicherweise mehrere Zeilen aktualisieren und/oder zurückgeben, was keine gute Idee zu sein scheint!

Answer 2

Ihre Lösung wird einen potenziellen Leistungsengpass bei OrderNumberInfo Tisch.

Gibt es einen bestimmten Grund, warum die Aufträge nicht einfach eine Identitätsspalte sein können, der möglicherweise eine Hersteller-ID auf der Anwendungsseite vorangestellt ist (z. B. MSFT-232323)?

Der einzige Nachteil dieses Ansatzes ist, dass die Bestellungen pro Lieferant nicht nach dem Muster "Add-1-to-get-next-order-#" erfolgen, aber ich kenne keine technischen oder geschäftlichen Überlegungen, warum das ein Problem darstellen sollte, obwohl es die Bearbeitung von Bestellungen in der Reihenfolge etwas komplizierter machen könnte.

Sie würden immer noch inkrementiert werden und pro Lieferant eindeutig sein, was die einzige wirkliche Anforderung an eine Bestell-ID ist.

Dies hat natürlich den zusätzlichen Vorteil, dass es sehr einfach ist, eine herstellerunabhängige Logik zu erstellen (vorausgesetzt, Sie haben überhaupt eine) - wie z. B. eine anwendungsweite Qualitätskontrolle/Berichterstattung.

Answer 3

Normalerweise verwende ich etwas in dieser Art:

update OrderNumberInfo with (rowlock)
set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1
where VendorID = @VendorID

Sie muss nicht in eine Transaktion verpackt werden. Wenn Sie sie in eine Transaktion einbinden, hält SQL Server Sperren für die Tabelle und verlangsamt alles. Wenn ich so etwas in einem Webdienst tun muss, führe ich es immer in einer separaten Datenbankverbindung außerhalb einer Transaktion aus, die zu diesem Zeitpunkt geöffnet sein könnte, nur um sicherzugehen.

Ich glaube (habe es aber nicht bewiesen), dass SQL Server einen Latch statt einer Transaktion verwendet, um es atomar zu machen, was effizienter sein sollte.

Wenn Ihre Tabelle so aufgebaut ist, dass die Verkäuferzeile bei Bedarf erstellt werden muss, wenn sie nicht vorhanden ist, dann verwenden Sie stattdessen diese Logik:

declare @error int, @rowcount int

-- Attempt to read and update the number.
update OrderNumberInfo with (rowlock)
set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1
where VendorID = @VendorID

select @error = @@error, @rowcount = @@rowcount
if @error <> 0 begin
    return @error
end

-- If the update succeeded then exit now.
if @rowcount > 0 begin
    return 0
end

-- Insert the row if it doesn't exist yet.
insert into OrderNumberInfo (VendorID, OrderNumber)
select VendorID, 1
where not exists (select null from OrderNumberInfo where VendorID = @VendorID)

select @error = @@error
if @error <> 0 begin
    return @error
end

-- Attempt to read and update the number.
update OrderNumberInfo with (rowlock)
set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1
where VendorID = @VendorID

select @error = @@error
if @error <> 0 begin
    return @error
end

Dieser Code erfordert immer noch keine Transaktion, da jede atomare Anweisung erfolgreich ist, unabhängig davon, wie viele andere Verbindungen den Code gleichzeitig ausführen.

Haftungsausschluss: Ich habe dies ohne Probleme auf SQL Server 7-2005 verwendet. Zum Verhalten in 2008 kann ich noch nichts sagen.

Answer 4

Der Weg, dies zu tun, um die Konsistenz zu erhalten:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
declare @n int
select @n = OrderNumber 
  from OrderNumberInfo 
  where VendorID = @vendorID

update OrderNumberInfo 
  set OrderNumber = @n + 1 
  where OrderNumber = @n and VendorID = @vendorID

COMMIT TRANSACTION

Dies ist die strengste Form der Isolierung, die sicherstellt, dass keine komischen Dinge passieren.

Answer 5

Hier ist es:

deklarieren @C int=0; update Table set Code=@C, @C=@C+1