이 문서에서는 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!