Discussion sur les solutions de mise à jour lorsqu'un traitement simultané SQL Server existe_MsSql

Cet article aborde principalement avec vous 7 solutions pour mettre à jour SQL ServerTraitement simultanédès qu'il existe. Il a une certaine valeur de référence. Les amis intéressés peuvent s'y référer

. Avant-propos

Dans cette section, nous parlerons de la situation la plus courante en simultanéité : mettre à jour lorsqu'il existe. En simultanéité, si un enregistrement de ligne n'existe pas, il sera inséré. se produire si cela n'est pas bien géré. Dans le cas de l'insertion de clés en double, dans cet article, nous présenterons sept solutions pour mettre à jour les enregistrements de lignes lorsqu'ils existent en simultanéité et nous analyserons de manière approfondie la solution la plus appropriée.

Discutez de sept options de mise à jour s'il y en a une

Nous créons d'abord une table de test

IF OBJECT_ID('Test') IS NOT NULL DROP TABLE Test CREATE TABLE Test ( Id int, Name nchar(100), [Counter] int,primary key (Id), unique (Name) ); GO

Solution 1(Activer la transaction)

Nous créons uneprocédure stockéeunifiée pour tester la situation de concurrence en utilisant SQLQueryStress.

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) BEGIN TRANSACTION IF EXISTS ( SELECT 1 FROM Test WHERE Id = @Id ) UPDATE Test SET [Counter] = [Counter] + 1 WHERE Id = @Id; ELSE INSERT Test ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMIT GO

Ouvrez 100 fils de discussion et 200 fils de discussion en même temps et insérez des clés en double . La probabilité est relativement faible mais existe toujours.

Solution 2(Réduire le niveau d'isolement au niveau d'isolement le plus bas NON ENGAGÉ)

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION IF EXISTS ( SELECT 1 FROM Test WHERE Id = @Id ) UPDATE Test SET [Counter] = [Counter] + 1 WHERE Id = @Id; ELSE INSERT Test ( Id, Name, [Counter] ) VALUES ( @Id, @name, 1 ); COMMIT GO

À ce stade, le problème est toujours le même comme solution (si le niveau est réduit au niveau d'isolement le plus bas, si l'enregistrement de ligne est vide et que la transaction précédente n'a pas été validée, la transaction en cours peut également lire que l'enregistrement de ligne est vide. Si la transaction en cours est insérée et validée , la transaction précédente lors de la nouvelle soumission, le problème de l'insertion de clés en double apparaîtra)

Solution 3(augmenter le niveau d'isolement au plus haut niveau SERIALIZABLE)

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION IF EXISTS ( SELECT 1 FROM dbo.Test WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1 WHERE Id = @Id; ELSE INSERT dbo.Test ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMIT GO

Dans ce cas, c'est encore pire, conduisant directement à une impasse

A ce moment, augmenter le niveau d'isolement au niveau d'isolement le plus élevé Cela résoudra le problème de l'insertion de clés en double, mais pour les mises à jour pour obtenir des verrous exclusifs sans validation, et à ce moment un autre processus effectue unerequêtepour obtenir un verrou partagé, ce qui provoquera un blocage mutuel entre les processus et provoquera un blocage. Nous savons donc que le niveau d'isolement le plus élevé peut parfois résoudre des problèmes de concurrence, mais peut également provoquer des problèmes de blocage.

Solution 4(augmenter le niveau d'isolement + bon verrouillage)

À ce stade, nous ajouterons des mises à jour basées sur l'ajout du niveau d'isolement le plus élevé, comme suit :

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION IF EXISTS ( SELECT 1 FROM dbo.Test WITH(UPDLOCK) WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1 WHERE Id = @Id; ELSE INSERT dbo.Test ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMIT GO

Aucune exception n'a été trouvée après plusieurs exécutions lors de l'interrogation des données, utilisez plutôt le verrouillage de mise à jour. afin que les données puissent être lues sans bloquer d'autres transactions, et deuxièmement, cela garantit également que les données n'ont pas été modifiées depuis la dernière lecture des données, résolvant ainsi le problème de blocage. Il semble que cette solution soit réalisable, mais je ne sais pas si elle est réalisable si la concurrence est élevée.

Solution 5(Augmenter le niveau d'isolement jusqu'à l'INSTANTANÉ du contrôle de version des lignes)

ALTER DATABASE UpsertTestDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE UpsertTestDatabase SET READ_COMMITTED_SNAPSHOT ON GO IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) BEGIN TRANSACTION IF EXISTS ( SELECT 1 FROM dbo.Test WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1 WHERE Id = @Id; ELSE INSERT dbo.Test ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMIT GO

La solution ci-dessus provoquera également le problème d'insertion clés en double. Non conseillé.

Solution 6(augmenter le niveau d'isolement + variable de table)

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) DECLARE @updated TABLE ( i INT ); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION UPDATE Test SET [Counter] = [Counter] + 1 OUTPUT DELETED.Id INTO @updated WHERE Id = @Id; IF NOT EXISTS ( SELECT i FROM @updated ) INSERT INTO Test ( Id, Name, counter ) VALUES ( @Id, @Name, 1 ); COMMIT GO

Après plusieurs authentifications, il n'y a aucune erreur. Il semble réalisable de l'implémenter sous forme de variables de table.

Solution 7(Augmenter le niveau d'isolement + Fusion)

Utilisez la touche Fusion pour implémenter l'existence, mettre à jour autrement et insérer en même temps. temps, nous devons faire attention à définir le niveau d'isolement sur SERIALIZABLE, sinon il y aura un problème d'insertion de clés en double. Le code est le suivant :

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRAN ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION MERGE Test AS [target] USING ( SELECT @Id AS Id ) AS source ON source.Id = [target].Id WHEN MATCHED THEN UPDATE SET [Counter] = [target].[Counter] + 1 WHEN NOT MATCHED THEN INSERT ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMIT GO

Authentifications multiples, qu'il s'agisse de 100 threads simultanés ou. 200 threads simultanés, il n'y a toujours aucune information d'exception.

Résumé

Dans cette section, nous avons discuté en détail de la manière de traiter le problème de mise à jour s'il existe, sinon d'insérer en simultanéité les trois solutions ci-dessus. sont réalisables.

Solution 1(Niveau d'isolement le plus élevé + verrouillage de mise à jour)

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) BEGIN TRANSACTION; UPDATE dbo.Test WITH ( UPDLOCK, HOLDLOCK ) SET [Counter] = [Counter] + 1 WHERE Id = @Id; IF ( @@ROWCOUNT = 0 ) BEGIN INSERT dbo.Test ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); END COMMIT GO

Je ne peux penser qu'à ces trois solutions pour le moment, je recommande personnellement la solution 1 et. Solution trois. Qu'en pensez-vous ? Veuillez laisser vos commentaires si possible, et je les ajouterai plus tard.

Solution 2(Niveau d'isolement le plus élevé + variable de table)

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) DECLARE @updated TABLE ( i INT ); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION UPDATE Test SET [Counter] = [Counter] + 1 OUTPUT DELETED.id INTO @updated WHERE id = @id; IF NOT EXISTS ( SELECT i FROM @updated ) INSERT INTO Test ( Id, Name, counter ) VALUES ( @Id, @Name, 1 ); COMMIT GO

解决方案三（最高隔离级别 + Merge）

IF OBJECT_ID('TestPro') IS NOT NULL DROP PROCEDURE TestPro; GO CREATE PROCEDURE TestPro ( @Id INT ) AS DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRAN ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION MERGE Test AS [target] USING ( SELECT @Id AS Id ) AS source ON source.Id = [target].Id WHEN MATCHED THEN UPDATE SET [Counter] = [target].[Counter] + 1 WHEN NOT MATCHED THEN INSERT ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMIT GO

暂时只能想到这三种解决方案，个人比较推荐方案一和方案三， 请问您有何高见，请留下您的评论若可行，我将进行后续补充。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!