Est une seule instruction SQL Server atomique et cohérente?
Est une instruction SQL Server ACID
?
Ce que je veux dire par la que
Reçu une seule instruction T-SQL, pas enveloppé dans un BEGIN TRANSACTION
/COMMIT TRANSACTION
, sont les actions de cette déclaration:
- Atomique: soit l'ensemble de ses données à des modifications sont effectuées, ou aucun d'eux n'est effectuée.
- Cohérente: une fois terminée, une transaction doit laisser toutes les données dans un état cohérent.
- Isolé: les Modifications apportées par les transactions simultanées doivent être isolés à partir des modifications faites par d'autres transactions simultanées.
- Durable: Après qu'une transaction est terminée, ses effets sont en permanence en place dans le système.
La raison pour laquelle je demande
J'ai une seule instruction dans un système qui semble violer les règles de la requête.
En effet mon instruction T-SQL est:
--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)
Note: Mais un simple conceptuel variante pourrait être:
--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
)
Dans ces deux états, l'avis qu'ils sont de simples déclarations (UPDATE...SET...WHERE
).
Il y a des cas où la mauvaise opération est "réservé"; c'est en fait la cueillette d'un plus tard transaction. Après avoir regarder cette pour 16 heures, je suis perplexe. C'est comme si SQL Server est simplement de violation des règles.
Je me demandais ce que si les résultats de l' Slots
vue est en train de changer avant la mise à jour se passe? Si SQL Server n'est pas tenue de SHARED
de verrous sur les transactions sur que date? Est-il possible qu'une seule instruction peut être incompatible?
J'ai donc décidé de le tester
J'ai décidé de vérifier si les résultats des sous-requêtes, ou des opérations internes, sont incompatibles. J'ai créé un tableau simple avec un seul int
colonne:
CREATE TABLE CountingNumbers (
Value int PRIMARY KEY NOT NULL
)
À partir de plusieurs connexions, dans une boucle serrée, je l'appelle le seule instruction T-SQL:
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
En d'autres termes, le pseudo-code est:
while (true)
{
ADOConnection.Execute(sql);
}
Et dans un délai de quelques secondes, j'obtiens:
Violation of PRIMARY KEY constraint 'PK__Counting__07D9BBC343D61337'.
Cannot insert duplicate key in object 'dbo.CountingNumbers'.
The duplicate value is (1332)
Sont des énoncés atomiques?
Le fait que dans un seul état n'est pas atomique, me fait me demander si les déclarations sont atomiques?
Ou est-il plus subtile définition de déclaration, qui diffère de (par exemple) ce que SQL Server considère un énoncé:
N'a fondamentalement signifie que dans les limites d'une seule instruction T-SQL, SQL Server ne sont pas des comptes atomique?
Et si une seule instruction est atomique, ce que les comptes de la violation de clé?
De l'intérieur d'une procédure stockée
Plutôt que d'une télécommande d'ouverture de client n connexions, j'ai essayé avec une procédure stockée:
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'
et ouvert 5 onglets dans SSMS, appuyé sur la touche F5 dans chaque, et regardé comme ils ont aussi violé l'ACIDE:
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.
Donc la défaillance est indépendant de ADO, ADO.net ou aucun des ci-dessus.
Pendant 15 ans, j'ai été d'exploitation en vertu de l'hypothèse qu'une seule instruction dans SQL Server est compatible, et le seul
Qu'en NIVEAU d'ISOLATION de TRANSACTION xxx?
Pour les différentes variantes du traitement SQL à exécuter:
-
par défaut (read committed): violation de clé
INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
-
par défaut (read committed), transaction explicite:
pas d'erreurde violation de cléBEGIN TRANSACTION INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers COMMIT TRANSACTION
-
serializable: impasse
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
-
instantané (après la modification de la base de données pour permettre l'isolement d'instantané): violation de clé
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
Bonus
- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
- Par défaut du niveau d'isolation de transaction (
READ COMMITTED
)
S'avère chaque requête que j'ai jamais écrit est cassé
Certes cela change les choses. Chaque instruction de mise à jour que j'ai jamais écrit est fondamentalement brisé. E. g.:
--Update the user with their last invoice date
UPDATE Users
SET LastInvoiceDate = (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.uid = Users.uid)
Valeur erronée; car un autre facture pourrait être inséré après le MAX
et avant la UPDATE
. Ou un exemple de 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);
sans exclusive holdlocks, le SalesYTD
est faux.
Comment ai-je pu faire quelque chose de toutes ces années.
- Que voulez-vous dire avec le "Il y a des cas où la mauvaise opération est "réservé"; c'est en fait de choisir un plus tard de la transaction." ?
- Pouvez-vous l'obtenir à échouer dans SÉRIALISABLE? Serais intéressé de voir le plan d'exécution d'une mise à jour réussie, comprendre l'index sur la table.
- je ne peux pas le tester sur le système "réel", parce que je serais affectant des personnes réelles. Je ne peux pas reproduire le problème dans notre système de test, probablement en raison de la faible concurrence. La conversion de mon jouet
CountingNumbers
table à utiliserSET TRANSACTION ISOLATION LEVEL SERIALIZABLE
causes de blocage. - Vous remarquerez que dans la première instruction de mise à jour, je
SELECT TOP 1 Transaction ORDER BY CreatedDate
. Dans l'environnement direct, parfois, cette transaction est passé au-dessus, ou sélectionnez par erreur une version ultérieure (par exemple, que vous mettez dans votre stock à acheter à 9:00 heures, mais quelqu'un qui a mis dans leurs acheter à 9 h 20, il reçoit). - Comment pouvez-vous être sûr que la première opération a été engagée en premier? À quel point est l'horodatage associé à la ligne décidé? Si une transaction prend une demi-seconde plus commettre, l'autre pourrait avoir commencé en premier.
- Alors l'erreur, ici, c'est que votre essai satisfait à l'Isolement d'une partie de l'ACIDE où il n'a pas vraiment? hassanszone.wordpress.com/2009/03/04/...
INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
n'est pas à l'abri sous ENGAGÉ, ENGAGÉ, REPEATABLE READ et SERIALIZABLE, car il nécessiteS
serrures. Et unS
lock est compatible avec un autreS
de verrouillage. D'autres instructions simultanées/transactions pouvez toujours lire les mêmes lignes. Pour être sûr cette approche nécessite d'isolation SERIALIZABLE plusX
de serrures ou deX
serrures + HOLDLOCK les indicateurs de table:INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers WITH(XLOCK, HOLDLOCK)
.- Vous semblez faire l'erreur de confondre atomique et de l'isolement. Atomique signifie simplement réussit ou échoue dans une unité de transaction (tous attachés ou réunis à l'arrière). Elle ne dit rien à propos de la visibilité des modifications de transactions simultanées.
- Non, il renvoie à la question du titre. Ni l'atomicité, ni la cohérence promesse de ce qui semble être assumé. Atomique: signifie Simplement réussite ou de l'échec comme une unité et Cohérence: Pas de contraintes etc violé.
- Comment le verrouillage optimiste travaille alors avec
READ COMMITTED
?UPDATE T SET F = 1, V = 2 WHERE V = 1
etUPDATE T SET F = 2, V = 2 WHERE V = 1
. Ce qui se passe si les deux états s'exécute en même temps? Ce que je comprends de la réponse à cette question, cela signifie qu'il pourrait être la perte de données dans ce cas, puisque V pourrait changer à mesure que l'instruction s'exécute, mais le changement ne sera pas de la lire? - Votre question a un fragment de phrase que je ne suis pas sûr de savoir comment je serais fixer avec un point de montage (autres que de l'enlever): "depuis 15 ans, j'ai été d'exploitation en vertu de l'hypothèse qu'une seule instruction dans SQL Server est compatible, et le seul "
Vous devez vous connecter pour publier un commentaire.
Cette hypothèse est fausse. Les deux opérations suivantes sont identiques, la sémantique de verrouillage:
Aucune différence du tout. Unique des déclarations et de l'auto s'engage à ne pas changer quoi que ce soit.
Donc la fusion de toute logique dans une instruction n'aide pas (si elle le fait, c'était par accident, parce que le plan a été changé).
Nous allons résoudre le problème à la main.
SERIALIZABLE
va corriger l'incohérence que vous voyez, car il garantit que vos transactions se comportent comme si ils ont exécuté une seule threadedly. De manière équivalente, ils se comportent comme s'ils exécutés instantanément.Vous obtiendrez les blocages. Si vous êtes ok avec une boucle de nouvelle tentative, vous avez fait à ce point.
Si vous voulez investir plus de temps, d'appliquer les conseils de verrouillage de la force d'un accès exclusif aux données pertinentes:
Maintenant, vous pouvez voir une concurrence réduite. Qui pourrait être tout à fait bien en fonction de votre charge.
La nature de votre problème rend la réalisation de la simultanéité dur. Si vous avez besoin d'une solution pour que nous aurions besoin d'appliquer plusieurs techniques invasives.
Vous pouvez simplifier la mise à JOUR un peu:
Cela se débarrasse de l'un inutile rejoindre.
Ci-dessous est un exemple d'une instruction de mise à JOUR qui ne incrémenter une valeur de compteur de atomiquement