SQL Server 동시 처리가 존재하는 경우 업데이트 솔루션에 대한 토론_MsSql

이 문서에서는 SQL Server의 7가지 솔루션동시 처리업데이트에 대해 주로 설명합니다. 이는 특정 참조 값이 있으므로 관심 있는 친구가 참조할 수 있습니다.

서문

이 섹션에서 우리는 동시성에서 가장 일반적인 상황은 행 레코드가 존재하지 않으면 업데이트하는 것입니다. 7가지 옵션이 동시에 존재하는 경우 행 레코드를 업데이트하는 방법을 소개하고 가장 적합한 솔루션을 종합적으로 분석해 보겠습니다.

존재하는 즉시 업데이트할 수 있는 7가지 옵션에 대해 논의

먼저 테스트 테이블을 생성합니다

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

솔루션 1(트랜잭션 활성화)

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

중복 키를 삽입할 가능성은 상대적으로 적지만 100개 스레드와 200개 스레드를 동시에 열면 여전히 존재합니다.

해결책 2 (격리 수준을 가장 낮은 격리 수준으로 낮추기 UNCOMMITED)

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

이때에도 문제는 여전히 해결 방법과 동일합니다. (격리 수준을 가장 낮은 격리 수준으로 낮추면, 행 레코드가 비어 있으면 이전 트랜잭션이 제출되지 않은 경우 현재 트랜잭션도 행 레코드가 비어 있음을 읽을 수 있습니다. 현재 트랜잭션을 삽입하여 제출한 경우 이전 트랜잭션이 제출되면 중복 키를 삽입하는 문제가 발생합니다. 다시)

해결책 3 (격리 수준을 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

이 경우에는 더욱 악화되며, 교착 상태가 직접적으로 발생합니다

At 이번에는 격리 수준을 가장 높은 격리 수준으로 올리면 문제가 해결됩니다. 중복 키를 삽입하는 문제가 있지만 커밋하지 않고 배타적 잠금을 얻기 위한 업데이트와 이때 다른 프로세스가 공유 잠금을 얻기 위해 query를 수행하면 이렇게 됩니다. 프로세스 간 상호 차단을 유발하고 교착 상태를 유발하므로 이제부터 가장 높은 격리 수준은 때때로 동시성 문제를 해결할 수 있지만 교착 상태 문제를 일으킬 수도 있음을 알 수 있습니다.

해결책 4(격리 수준 높이기 + 잠금 좋음)

이번에는 가장 높은 격리 수준을 추가하는 기준으로 다음과 같이 업데이트 잠금을 추가하겠습니다.

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

실행 여러 번 예외가 발견되지 않았습니다. 데이터를 쿼리할 때 공유 잠금 대신 업데이트 잠금을 사용하면 첫째, 다른 트랜잭션을 차단하지 않고 데이터를 읽을 수 있습니다. 둘째, 데이터가 마지막으로 변경된 이후로 데이터가 변경되지 않았음을 보장합니다. 이렇게 하면 교착 상태 문제가 해결됩니다. 이 솔루션이 가능할 것 같지만 동시성이 높을 경우 실현 가능한지는 모르겠습니다.

해결책 5 (격리 수준을 행 버전 제어 SNAPSHOT으로 높임)

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

위 해결 방법도 중복 키 삽입 문제가 발생하므로 권장하지 않습니다.

해결책 6 (격리 수준 증가 + 테이블 변수)

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

오류가 없는 테이블 변수 형태로 구현하는 것이 가능할 것 같습니다.

해결책 7 (격리 수준 높이기 + 병합)

병합 키를 사용하여 존재 여부를 업데이트하거나 업데이트하지 않으면 동시에 격리 수준을 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 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

다중 인증 후에도 동시 스레드가 100개이든 동시 스레드가 200개이든 여전히 이상 정보가 없습니다.

요약

이 섹션에서는 업데이트 문제가 있는 경우 이를 처리하는 방법을 자세히 논의했고, 그렇지 않으면 동시 삽입이 가능합니다.

솔루션 1(최고 격리 수준 + 업데이트 잠금)

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

현재로서는 이 세 가지 솔루션만 생각납니다. 개인적으로 솔루션 1과 3을 추천합니다. 효과가 있다고 생각하시나요? , 나중에 추가하겠습니다.

해결책 2(최고 격리 수준 + 테이블 변수)

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

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

위 내용은 SQL Server 동시 처리가 존재하는 경우 업데이트 솔루션에 대한 토론_MsSql의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!