Ist eine einzelne SQL Server-Anweisung atomar und konsistent?
Ist eine Anweisung in SQL ServerACID?
Bei einer einzelnen T-SQL-Anweisung, die nicht in einBEGIN TRANSACTION / COMMIT TRANSACTION, sind die Aktionen dieser Aussage:
Ich habe eine einzelne Anweisung in einem Live-System, die die Regeln der Abfrage zu verletzen scheint.
Tatsächlich lautet meine T-SQL-Anweisung:
--If there are any slots available,
--then find the earliest unbooked transaction and mark it booked
UPDATE Transactions
SET Booked = 1
WHERE TransactionID = (
SELECT TOP 1 TransactionID
FROM Slots
INNER JOIN Transactions t2
ON Slots.SlotDate = t2.TransactionDate
WHERE t2.Booked = 0 --only book it if it's currently unbooked
AND Slots.Available > 0 --only book it if there's empty slots
ORDER BY t2.CreatedDate)
Hinweis: Aber eine einfachere konzeptionelle Variante könnte sein:
--Give away one gift, as long as we haven't given away five
UPDATE Gifts
SET GivenAway = 1
WHERE GiftID = (
SELECT TOP 1 GiftID
FROM Gifts
WHERE g2.GivenAway = 0
AND (SELECT COUNT(*) FROM Gifts g2 WHERE g2.GivenAway = 1) < 5
ORDER BY g2.GiftValue DESC
)
Beachten Sie, dass es sich bei beiden Anweisungen um einzelne Anweisungen handelt (UPDATE...SET...WHERE).
Es gibt Fälle, in denen die falsche Transaktion durchgeführt wird"gebucht"; es ist eigentlich eine Auswahlspäter Transaktion. Nachdem ich das 16 Stunden lang angestarrt habe, bin ich ratlos. Es ist, als ob SQL Server einfach gegen die Regeln verstößt.
Ich fragte mich, was wenn die Ergebnisse derSlots Ansicht ändert sich vor dem Update? Was ist, wenn SQL Server nicht hält?SHARED Schlösser auf demTransaktionen auf diesemDatum? Ist es möglich, dass eine einzelne Aussage inkonsistent sein kann?
Ich habe mich entschlossen zu prüfen, ob die Ergebnisse von Unterabfragen oder inneren Operationen inkonsistent sind. Ich habe eine einfache Tabelle mit einer einzigen erstelltint Säule:
CREATE TABLE CountingNumbers (
Value int PRIMARY KEY NOT NULL
)
Aus mehreren Verbindungen, in einer engen Schleife, nenne ich daseinzelne T-SQL-Anweisung:
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
Mit anderen Worten ist der Pseudocode:
while (true)
{
ADOConnection.Execute(sql);
}
Und innerhalb weniger Sekunden bekomme ich:
Violation of PRIMARY KEY constraint 'PK__Counting__07D9BBC343D61337'.
Cannot insert duplicate key in object 'dbo.CountingNumbers'.
The duplicate value is (1332)
Die Tatsache, dass eine einzelne Aussage nicht atomar war, lässt mich fragen, ob einzelne Aussagen atomar sind.
Oder gibt es noch mehrsubtil Definition vonAussageunterscheidet sich beispielsweise von dem, was SQL Server als Anweisung ansieht:
Bedeutet dies grundsätzlich, dass SQL Server-Anweisungen innerhalb der Grenzen einer einzelnen T-SQL-Anweisung nicht atomar sind?
Und wenn eine einzelne Aussage atomar ist, was erklärt die Schlüsselverletzung?
Aus einer gespeicherten Prozedur herausAnstelle einer Remote-Client-Öffnungn Verbindungen, ich habe es mit einer gespeicherten Prozedur versucht:
CREATE procedure [dbo].[DoCountNumbers] AS
SET NOCOUNT ON;
DECLARE @bumpedCount int
SET @bumpedCount = 0
WHILE (@bumpedCount < 500) --safety valve
BEGIN
SET @bumpedCount = @bumpedCount+1;
PRINT 'Running bump '+CAST(@bumpedCount AS varchar(50))
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
IF (@bumpedCount >= 500)
BEGIN
PRINT 'WARNING: Bumping safety limit of 500 bumps reached'
END
END
PRINT 'Done bumping process'
und öffnete 5 Tabs in SSMS, drückte jeweils F5 und beobachtete, wie sie ebenfalls gegen ACID verstießen:
Running bump 414
Msg 2627, Level 14, State 1, Procedure DoCountNumbers, Line 14
Violation of PRIMARY KEY constraint 'PK_CountingNumbers'.
Cannot insert duplicate key in object 'dbo.CountingNumbers'.
The duplicate key value is (4414).
The statement has been terminated.
Der Fehler ist also unabhängig von ADO, ADO.net oder keinem der oben genannten.
Seit 15 Jahren gehe ich davon aus, dass eine einzelne Anweisung in SQL Server konsistent ist. und das einzige
Was ist mit TRANSACTION ISOLATION LEVEL xxx?Für verschiedene Varianten des auszuführenden SQL-Stapels:
default (read commit): Schlüsselverletzung
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
Standard (read commit), explizite Transaktion: kein Fehler Schlüsselverletzung
BEGIN TRANSACTION
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
COMMIT TRANSACTION
serialisierbar: Sackgasse
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Schnappschuss (Nach dem Ändern der Datenbank, um die Snapshot-Isolation zu aktivieren): Schlüsselverletzung
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
READ COMMITTED)Es stellt sich heraus, dass jede Abfrage, die ich jemals geschrieben habe, fehlerhaft istDies ändert sicherlich die Dinge. Jede Update-Anweisung, die ich jemals geschrieben habe, ist grundsätzlich fehlerhaft. Z.B.:
--Update the user with their last invoice date
UPDATE Users
SET LastInvoiceDate = (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.uid = Users.uid)
Falscher Wert; weil eine andere Rechnung nach dem eingefügt werden könnteMAX und vor demUPDATE. Oder ein Beispiel von BOL:
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD +
(SELECT SUM(so.SubTotal)
FROM Sales.SalesOrderHeader AS so
WHERE so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS so2
WHERE so2.SalesPersonID = so.SalesPersonID)
AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID
GROUP BY so.SalesPersonID);
ohne exklusive holdlocks, dieSalesYTD ist falsch.
Wie konnte ich all die Jahre etwas tun?