Snapshot-Isolation – Durchführung von validationen in Transaktion unter Beibehaltung der Fähigkeit zum Rollback

Ich schaue in SQL server Snapshot Isolation Ebene. Für einfache Updates scheinen die Dinge einfach und man findet viele Beispiele für die Handhabung. Für die Logik, die auf datavalidierungsprüfungen am Ende der Transaktion beruht, habe ich Schwierigkeiten, mit einem Muster zu kommen, das uns noch erlaubt, ROLLBACK-Änderungen zu ändern, wenn eine validation fehlschlägt.

Nehmen wir ein Beispiel für eine grundlegende Inventartabelle, wo wir sicherstellen möchten, dass Bestandsbilanzen nicht negativ werden dürfen. Wir hätten einen Scheck am Ende der Transaktion, um sicherzustellen, dass die Waage nicht negativ ist und wenn es dann ROLLBACK ist und einen Fehler austriggers.

Unten ist ein SQL-Skriptbeispiel. HINWEIS: Ihre database muss für dieses Beispiel eine Snapshot-Isolation aktiviert haben. Es kann durch den folgenden Befehl aktiviert werden:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON; 

FENSTER 1: Erstellen Sie die Inventartabelle mit einem Element im Inventar:

 IF object_id('dbo.TEST_InventoryActivity') IS NOT NULL DROP TABLE dbo.TEST_InventoryActivity GO CREATE TABLE dbo.TEST_InventoryActivity ( activityID int not null primary key identity , itemID int not null , inOrOut char(1) not null , quantity int not null , modBy varchar(128) not null , modDate datetime not null default getdate() ) go INSERT INTO TEST_InventoryActivity(itemID, inOrOut, quantity, modBy) VALUES (1,'I',100, 'setups') ; --show all records SELECT i.* FROM TEST_InventoryActivity i ; --show inventory balances SELECT i.itemID, inventoryBalance = sum(i.quantity) FROM TEST_InventoryActivity i GROUP BY i.itemID ; 

WINDOW 2: Jetzt öffnen Sie eine neue Abfrage-windows und führen Sie die folgenden, die nicht verpflichten (dies wird verwendet, um zu simulieren 2 transactions auftreten, zur gleichen time).

 SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN; DECLARE @itemID int, @quantity int; set @itemID = 1; set @quantity = -75; insert into TEST_InventoryActivity(itemID, inOrOut, quantity, modBy) values(@itemID,'O', @quantity, 'test 1') --use Item 1 ; IF EXISTS( SELECT i.itemID, sum(i.quantity) FROM TEST_InventoryActivity i WHERE i.itemID = @itemID GROUP BY i.itemID HAVING sum(i.quantity) < 0 ) BEGIN ROLLBACK; RAISERROR(N'Not enough remaining inventory', 16, 1); RETURN; END ; 

WINDOW 3: nun ein weiteres neues windows öffnen und das folgende ausführen, das das Inventar negativ macht.

 SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN; DECLARE @itemID int, @quantity int; set @itemID = 1; set @quantity = -50; insert into TEST_InventoryActivity(itemID, inOrOut, quantity, modBy) values(@itemID,'O', @quantity, 'test 2') --use Item 1 ; IF EXISTS( SELECT i.itemID, sum(i.quantity) FROM TEST_InventoryActivity i WHERE i.itemID = @itemID GROUP BY i.itemID HAVING sum(i.quantity) < 0 ) BEGIN ROLLBACK; RAISERROR(N'Not enough remaining inventory', 16, 1); RETURN; END ; COMMIT; 

FENSTER 2: Gehen Sie nun zurück zu windows 2 und begehen Sie die Transaktion.

 COMMIT; 

Wenn du die Inventar-Balance für Item 1 überprüft hast, ist es jetzt -25, also haben unsere validationen nicht funktioniert, weil Windows 3 keinen Hinweis auf irgendetwas hatte, was in windows 2 vor sich ging.

 --show all records SELECT i.* FROM TEST_InventoryActivity i ; --show inventory balances SELECT i.itemID, inventoryBalance = sum(i.quantity) FROM TEST_InventoryActivity i GROUP BY i.itemID 

Ausgabe:

Bildbeschreibung hier eingeben

Die Bestandsbilanz für Punkt 1 ist nun negativ. Ich verstehe, warum das Inventar als negativ erscheint, weil die Transaktion in windows 2 die Transaktion in windows 3 nicht blockiert, aber ich kann keine Verweise auf die Verwendung dieser Art von validationslogik innerhalb einer Transaktion finden und trotzdem in der Lage sein, das zurückzusetzen ganze Transaktion bei der Verwendung von Snapshot-Isolation. Von dem, was ich verstehe, Oracle mit optimistischen Parallelität aus der Box, so würde ich mir vorstellen, es würde pathe um diese. Gibt es irgendwelche Muster, die anstelle von diesem verwendet werden können, die mit Snapshot-Isolationsstufe in SQL server funktionieren?

Ich verstehe das, wenn ich einfach die Isolationsstufe auf READ COMMITTED ändere, dann funktioniert alles (es sei denn, SET READ_COMMITTED_SNAPSHOT ON ist in der database aktiviert), aber ich suche optimistische Parallelitätsmuster, die die gleichen Ergebnisse liefern würden.

Versuchen Sie, den READCOMMITTED auf der EXISTS Abfrage zu verwenden …

 IF EXISTS(SELECT i.itemID, sum(i.quantity) FROM TEST_InventoryActivity i With (READCOMMITTED) WHERE i.itemID = @itemID GROUP BY i.itemID HAVING sum(i.quantity) < 0) BEGIN ROLLBACK; RAISERROR(N'Not enough remaining inventory', 16, 1); RETURN; END ELSE BEGIN COMMIT; END 

Ich habe diese Abfrage an einem Haufen von verschiedenen Orten während der Ausführung verwendet, um zu bestätigen, dass der READCOMMITTED Tabellenhinweis (Komplimente dieser Post ) nicht die Isolationsstufe der Transaktion ändert …

 SELECT CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions WHERE session_id = @@SPID