Diskussion über Aktualisierungslösungen, wenn die gleichzeitige Verarbeitung von SQL Server vorhanden ist

In diesem Artikel werden hauptsächlich 7 Lösungen für die Aktualisierung von SQL ServerParallelitätsverarbeitung besprochen, sobald sie vorhanden sind. Interessierte Freunde können darauf verweisen

Vorwort

In diesem Abschnitt werden wir über die häufigste Situation bei Parallelität sprechen: Wenn bei Parallelität ein Zeilendatensatz nicht vorhanden ist, wird er sehr einfach eingefügt auftreten, wenn es nicht richtig gehandhabt wird. In diesem Artikel stellen wir sieben Lösungen zum Aktualisieren von Zeilendatensätzen vor, wenn diese gleichzeitig vorhanden sind, und analysieren umfassend die am besten geeignete Lösung.

Besprechen Sie sieben Optionen für die Aktualisierung, falls es eine gibt

Zuerst erstellen wir eine Testtabelle

IF OBJECT_ID(&#39;Test&#39;) IS NOT NULL
 DROP TABLE Test

CREATE TABLE Test
(
 Id int,
 Name nchar(100),
 [Counter] int,primary key (Id),
 unique (Name)
);
GO

Nach dem Login kopieren

Lösung 1 (Transaktion aktivieren)

Wir erstellen eine einheitliche gespeicherte Prozedur, um die Parallelitätssituation mithilfe von SQLQueryStress zu testen.

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

Öffnen Sie 100 Threads und 200 Threads gleichzeitig und fügen Sie doppelte Schlüssel ein . Die Wahrscheinlichkeit ist relativ gering, aber immer noch vorhanden.

Lösung 2 (Reduzieren Sie die Isolationsstufe auf die niedrigste Isolationsstufe UNCOMMITED)

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

An diesem Punkt ist das Problem immer noch dasselbe als Lösung (Wenn die Ebene auf die niedrigste Isolationsstufe reduziert wird, der Zeilendatensatz leer ist und die vorherige Transaktion nicht festgeschrieben wurde, kann die aktuelle Transaktion auch lesen, dass der Zeilendatensatz leer ist. Wenn die aktuelle Transaktion eingefügt und festgeschrieben wird , die vorherige Transaktion wird angezeigt. Beim erneuten Absenden wird das Problem des Einfügens doppelter Schlüssel angezeigt Isolationsstufe auf die höchste Stufe (SERIALISIERBAR)

In diesem Fall ist es noch schlimmer und führt direkt zu einem Deadlock

Zu diesem Zeitpunkt Erhöhen Sie die Isolationsstufe auf die höchste Isolationsstufe. Dadurch wird das Problem des Einfügens doppelter Schlüssel gelöst. Aktualisierungen können jedoch exklusive Sperren ohne Festschreibung erhalten. Zu diesem Zeitpunkt führt ein anderer Prozess eine Abfrage

durch, um eine gemeinsame Sperre zu erhalten. Dies führt zu einer gegenseitigen Blockierung zwischen Prozessen und zu einem Deadlock. Daraus wissen wir, dass die höchste Isolationsstufe manchmal Parallelitätsprobleme lösen kann, aber auch Deadlock-Probleme verursachen kann.

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

Lösung 4

(Isolationsstufe erhöhen + gute Sperre)

Zu diesem Zeitpunkt werden wir Aktualisierungen hinzufügen, die auf dem Hinzufügen der Sperre mit der höchsten Isolationsstufe basieren. wie folgt:

Nach mehrmaliger Ausführung wurde keine Ausnahme gefunden. Verwenden Sie stattdessen die Aktualisierungssperre. damit Daten gelesen werden können, ohne andere Transaktionen zu blockieren, und zweitens wird auch sichergestellt, dass die Daten seit dem letzten Lesen der Daten nicht geändert wurden, wodurch das Deadlock-Problem gelöst wird. Es scheint, dass diese Lösung machbar ist, aber ich weiß nicht, ob sie machbar ist, wenn die Parallelität hoch ist.

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

Lösung 5

(Erhöhen Sie die Isolationsstufe für die Zeilenversionskontrolle SNAPSHOT)

Die obige Lösung verursacht auch das Problem beim Einfügen Doppelte Schlüssel. Nicht ratsam.

Lösung 6(Isolationsstufe erhöhen + Tabellenvariable)

ALTER DATABASE UpsertTestDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
 
ALTER DATABASE UpsertTestDatabase
SET READ_COMMITTED_SNAPSHOT ON
GO 

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

Nach mehreren Authentifizierungen gibt es keine Fehler. Es scheint machbar, es in Form von Tabellenvariablen zu implementieren.

Lösung 7

(Isolationsstufe erhöhen + Zusammenführen)

Verwenden Sie die Zusammenführungstaste, um die Existenz zu implementieren, andernfalls zu aktualisieren und gleichzeitig einzufügen Gleichzeitig sollten wir darauf achten, die Isolationsstufe auf SERIALIZABLE zu setzen, da sonst ein Problem beim Einfügen doppelter Schlüssel auftritt. Der Code lautet wie folgt:

Mehrere Authentifizierungen, unabhängig davon, ob es sich um 100 gleichzeitige Threads handelt oder 200 gleichzeitige Threads, es gibt immer noch keine Ausnahmeinformationen. Zusammenfassung

In diesem Abschnitt haben wir ausführlich besprochen, wie mit dem Problem der Aktualisierung (falls vorhanden) umgegangen werden kann, andernfalls mit dem gleichzeitigen Einfügen. Derzeit gibt es die drei oben genannten Lösungen sind machbar.

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

Lösung Eins

(Höchste Isolationsstufe + Update-Sperre)

Diese drei Lösungen fallen mir vorerst nur ein. Ich persönlich empfehle Lösung Eins und Lösung Drei. Was denken Sie? Bitte hinterlassen Sie Ihre Kommentare, ich werde sie später hinzufügen.

Lösung 2

(Höchste Isolationsstufe + Tabellenvariable)

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

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

IF OBJECT_ID(&#39;TestPro&#39;) 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

Nach dem Login kopieren

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

Das obige ist der detaillierte Inhalt vonDiskussion über Aktualisierungslösungen, wenn die gleichzeitige Verarbeitung von SQL Server vorhanden ist_MsSql. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!