읽기 전용 스냅샷 VS 스냅샷 분리 수준

읽기 전용 스냅샷 VS 스냅샷 분리 수준

SQL Server에서 READ COMMITED Snapshot보다 스냅샷 분리 수준을 사용해야 하는 경우를 이해할 수 있는 사람이 있습니까?

대부분의 경우 Read Committed Snapshot이 작동하는 것은 이해하지만 언제 스냅샷을 분리해야 할지는 확실하지 않습니다.

감사해요.

READ COMMITTED SNAPSHOT낙관적 읽기 및 비관적 쓰기를 수행합니다. 그반서해에,서,SNAPSHOT낙관적 읽기 및 낙관적 쓰기를 수행합니다.

권장 Microsoft 장항READ COMMITTED SNAPSHOT행 버전 관리가 필요한 대부분의 앱에서 사용할 수 있습니다.

이 우수한 Microsoft 기사를 읽어 보십시오.행 버전 기반 분리 수준을 선택합니다.두 격리 수준의 이점과 비용에 대해 설명합니다.

그리고 여기 더 철저한 것이 있습니다: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx .

[![격리 레벨 표][2][2]

아래 예를 참조하십시오.

읽기 전용 스냅샷

아래와 같이 데이터베이스 속성 변경

ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

세션 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1

세션 2

USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 1

결과 – 현재 트랜잭션이 커밋되지 않았기 때문에 세션 2의 쿼리에 이전 값(1, 1)이 표시됩니다.이렇게 하면 차단을 방지하고 커밋된 데이터도 읽을 수 있습니다.

세션 1

COMMIT

세션 2

USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

결과 – 세션 1에서 행이 업데이트되었기 때문에 세션 2의 쿼리에 행이 표시되지 않습니다.다시 한번, 우리는 헌신적인 데이터를 보고 있습니다.

스냅샷 분리 수준

이 레벨은 SQL Server 2005 이후부터 사용할 수 있는 새로운 격리 레벨은 다음과 같습니다.이 기능을 사용하려면 응용 프로그램에서 새 분리 수준을 사용해야 하므로 변경이 필요합니다.

아래를 사용하여 데이터베이스 설정을 변경합니다.우리는 데이터베이스에 트랜잭션이 없는지 확인해야 합니다.

ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

이제 아래를 사용하여 연결의 분리 수준도 변경해야 합니다.

세션 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2

세션 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 2

결과 - 값을 10으로 변경한 경우에도 세션 2(2, 2)에서 이전 레코드를 볼 수 있습니다.

이제 세션 1에서 트랜잭션을 커밋합니다.

세션 1

COMMIT

세션 2로 돌아가서 선택을 다시 실행합니다.

세션 2

SELECT *
FROM   DemoTable
WHERE i = 2

세션 2에서 스냅샷 분리가 포함된 트랜잭션을 언급했기 때문에 레코드가 계속 표시됩니다.거래를 완료하지 않으면 최신 기록을 볼 수 없습니다.

세션 2

COMMIT
SELECT *
FROM   DemoTable
WHERE i = 2

이제 행이 이미 업데이트되었기 때문에 표시되지 않아야 합니다.

참조: SQL Authority, Safari Books Online

스냅샷에서 발생할 수 있는 무서운 "스냅샷 업데이트 충돌" 예외에 대한 논의 없이는 스냅샷과 스냅샷 읽기 커밋을 비교할 수 없습니다.

간단히 말해, 스냅샷 분리는 트랜잭션 시작 시 커밋된 데이터의 스냅샷을 검색한 다음 읽기 및 쓰기 모두에 대해 최적의 잠금을 사용합니다.트랜잭션을 커밋하려고 할 때 동일한 데이터 중 일부가 다른 것으로 변경된 것으로 판명되면 데이터베이스는 전체 트랜잭션을 롤백하고 호출 코드에서 스냅샷 업데이트 충돌 예외를 발생시키는 오류를 발생시킵니다.이는 트랜잭션의 영향을 받는 데이터 버전이 트랜잭션 종료 시점과 동일하지 않기 때문입니다.

스냅샷 읽기 커밋은 쓰기(추상적 쓰기)에 대한 잠금을 사용하고 각 문의 상태에서 커밋된 모든 데이터의 스냅샷 버전 정보를 가져오기 때문에 이 문제가 발생하지 않습니다.

스냅샷과 스냅샷 읽기 커밋되지 않은 스냅샷에서 스냅샷 업데이트 충돌이 발생할 가능성은 둘 사이에 매우 큰 차이가 있습니다.

여전히 관련이 있으며, 빌의 논평을 시작으로 더 많이 읽고 다른 사람에게 유용할 수 있는 메모를 작성했습니다.

기본적으로 단일 문(SELECT 포함)은 "커밋된" 데이터(READ COMMITED)에 대해 작동합니다. 문제는 데이터가 "유휴"될 때까지 대기하고 다른 문이 읽을 때 작동하지 않도록 하는 것입니까?

DB "속성 -> 옵션 -> 기타"를 마우스 오른쪽 단추로 클릭하여 설정:

동시성/차단: Read Committed Snapshot On [기본값 꺼짐, 켜져야 함] 여부:

• 선택(읽기)에 스냅샷을 사용하고, 다른 사용자를 기다리지 않으며, 차단하지 않습니다.
• 코드 변경 없는 효과 작업
• ALTER DATABASE <dbName> SET READ_COMMITTED_SNAPSHOT [ON|OFF]
• SELECT name, is_read_committed_snapshot_on FROM sys.databases

일관성: 스냅샷 분리 허용 [기본값 꺼짐, 토론 가능 – OK 꺼짐]:

• 클라이언트가 SQL 문(트랜잭션)에 걸쳐 스냅샷을 요청할 수 있도록 허용합니다.
• 는 "요청해야 예: " 코는 " 랜트 " 샷을 " 해야 " 청요 " 니드 " 예다 " 합잭 " 냅션 " 스: " 예야 " 코해 " " 다▁( " 랜니 "SET TRANSACTION ...)
• ALTER DATABASE <dbName> SET ALLOW_SNAPSHOT_ISOLATION [ON|OFF]
• SELECT name, snapshot_isolation_state FROM sys.databases

질문에 대해: 읽기 전용 스냅샷과 스냅샷 분리 허용 간의 차이가 아닙니다.스냅샷의 두 가지 경우로, 스냅샷 격리 허용에 대한 자세한 내용은 개별적으로 설정하거나 해제할 수 있습니다.스냅샷 분리 허용을 사용하면 코드가 스냅샷 랜드를 한 단계 더 제어할 수 있습니다.

한 행에 대해 생각해 보면 이 문제는 분명해 보입니다. 기본적으로 시스템에는 복사본이 없기 때문에 다른 사람이 글을 쓰는 경우에는 독자가 기다려야 하고, 다른 사람이 글을 읽는 경우에는 작성자도 기다려야 합니다. 즉, 행이 항상 잠겨 있어야 합니다.읽기 전용 스냅샷 설정 여부를 선택하면 DB가 "스냅샷 복사본"을 지원하여 이러한 잠금을 방지할 수 있습니다.

횡설수설...

일반 MS SQL Server 데이터베이스의 경우 "Is Read Committed Snapshot On"이 TRUE여야 하며 기본적으로 FALSE를 전송하는 것은 시기상조 최적화라고 생각합니다.

하지만 하나의 행 잠금이 악화되는 것은 테이블에 걸쳐 여러 행을 지정할 수 있기 때문만이 아니라,그러나 SQL Server에서는 행 잠금이 "블록" 수준 잠금(스토리지 근접성에 따라 임의의 행을 잠그는 것)을 사용하여 구현되고 여러 개의 잠금이 테이블 잠금을 트리거하는 임계값이 있기 때문에 사용량이 많은 데이터베이스의 문제를 차단할 위험이 있으므로 성능 최적화가 "긍정적"일 수 있습니다.

언급되지 않은 두 가지 사항에 대해 설명하겠습니다.

먼저 직관적이지 않기 때문에 두 가지 모두를 사용하는 방법을 명확하게 설명하겠습니다.

스냅샷과 READ_COMMITED_SNAPSHOT는 서로 다른 분리 레벨입니다.

스냅샷은 트랜잭션에서 일반적으로 명시적으로 사용할 수 있는 분리 레벨입니다.

begin transaction
set transaction isolation level snapshot;
-- ...
commit

READ_COMMITED_SNAPSHOT는 이렇게 사용할 수 없습니다.READ_COMMITED_SNAPSHOT는 데이터베이스 수준 옵션이자 암시적/자동 분리 수준입니다.사용하려면 전체 데이터베이스에 대해 사용 가능으로 설정해야 합니다.

alert database ... set read_committed_snapshot on;

위의 데이터베이스 설정은 다음과 같은 트랜잭션을 실행할 때마다 수행됩니다.

begin transaction
set transaction isolation level read committed;
-- ...
commit

이 옵션을 사용하면 모든 READ_COMMITED 트랜잭션이 대신 READ_COMMITED_SNAPHOT 분리 수준에서 실행됩니다.이는 자동으로 발생하며 이 설정이 ON으로 설정된 데이터베이스에 대해 실행된 모든 READ_COMMITED 트랜잭션에 영향을 미칩니다.이 수준의 모든 트랜잭션은 자동으로 READ_COMMITED_SNAPHOT로 변환되므로 READ_COMMITED 분리 수준에서 트랜잭션을 실행할 수 없습니다.

둘째, READ_COMMITED_SNAPSHOT 옵션을 맹목적으로 사용해서는 안 됩니다.

다음과 같은 간단한 이벤트 표를 사용하여 발생할 수 있는 문제를 설명합니다.

create table Events (
  id int not null identity(1, 1) primary key,
  name nvarchar(450) not null
  -- ...
)

그리고 다음과 같은 쿼리를 사용하여 주기적으로 폴링합니다.

begin transaction
set transaction isolation level read committed; -- automatically set to read committed snapshot when this setting is ON on database level 
select top 100 * from Events where id > ${lastId} order by id asc; 
commit

위 쿼리는 트랜잭션 및 명시적 분리 수준으로 묶을 필요가 없습니다.READ_COMMITED는 기본 분리 수준이며, 트랜잭션 블록으로 묶지 않고 쿼리를 호출하면 READ_COMMITED 트랜잭션에서 암시적으로 실행됩니다.

READ_COMMITED_SNAPSHOT 분리 수준에서 자동 증분 ID 값에 나중에 나타나는 공백이 있을 수 있습니다.

다음과 같은 인서트를 사용하여 쉽게 시뮬레이션할 수 있습니다.

begin transaction
insert into Events (name) values ('test 1');
waitfor delay '00:00:10'
commit

...일반 삽입을 통해 표시됩니다.

insert into Events (name) values ('test 2');

10초 이내에 호출된 폴링 기능은 ID가 2인 단일 행을 반환합니다.

lastId 업데이트 후 폴링을 수행하면 아무것도 반환되지 않습니다.ID가 1인 행은 10초 후에 나타납니다.

ID가 1인 이벤트는 효과적으로 건너뜁니다.

이 문제는 READ_COMMITED_SNAPSHOT 자동 승격 옵션 없이 READ_COMMITED를 사용하는 경우에는 발생하지 않습니다.

이 시나리오를 이해할 가치가 있습니다.아이덴티티 열이 고유성을 보장하지 않는다는 사실과 관련이 없습니다.IDITY 열이 엄격한 단조로움을 보장하지 않는다는 사실과 관련이 없습니다.고유성과 엄격한 단조로움이 모두 위반되지 않더라도 ID가 낮은 커밋을 보기 전에 ID가 높은 커밋을 볼 수 있는 가능성이라는 차이가 있습니다.

READ_COMMICTED에서는 이 문제가 존재하지 않습니다.

READ_COMMITED 아래에는 롤백된 트랜잭션별 차이도 볼 수 있습니다.하지만 그러한 격차는 영구적일 것입니다.이벤트가 다시 나타나지 않기 때문에 이벤트를 건너뛸 수 없습니다.즉, 상위 ID를 본 후에는 하위 ID가 다시 나타나지 않습니다.

READ_COMMITED_SNAPHOT를 설정하기 전에 위의 문제와 그 영향을 이해하십시오.

이 옵션의 제어권은 개발자 대 DB 관리자 책임의 회색 영역에 있습니다.관리자라면 개발자가 애플리케이션을 개발할 때 READ_COMMITED 격리 의미론에 의존했을 수 있으므로 맹목적으로 사용해서는 안 됩니다. READ_COMMITED_SNAPSHOT를 설정하면 이러한 가정을 매우 암묵적으로 위반하여 버그를 찾기가 어렵습니다.

또한 다음 사항에 유의해야 합니다.

• 도커 이미지를 사용하는 경우 이 옵션은 기본적으로 OFF입니다.
• zure 인스턴스를 사용하는 경우 이 옵션은 기본적으로 설정됩니다.

...기본적으로 배포된 환경과는 다른 기능에 대해 개발자들이 작동하고 테스트를 실행하기 때문에 어리석은 것입니다.

언급URL : https://stackoverflow.com/questions/2741016/read-committed-snapshot-vs-snapshot-isolation-level