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 une procédure stockée unifié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 une requête pour 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!