가속 데이터베이스 복구 [ADR] (1) Row Versioning

가속 데이터베이스 복구를 이해하기 위해서는 먼 SQL Server의 특징인 Row Versioning에 대한 이해가 필요하다.

 

Row Versioninig과 Version Store, 그리고 Snapshot

Row Versioninig은 SQL Server 2005 부터 도입된 낙관적 동시성 제어 모델이다.

기본적으로 RDBMS는 비관적 동시성 제어 모델을 따르는데 2개의 특징은 다음과 같다.

 

l  비관적 동시성 제어

다른 사용자에게 영향을 주는 데이터 수정은 수행할 수 없도록 하는 잠금 방식.

한 사용자가 잠금을 유발하는 동작을 수행하면 다른 사용자는 이 소유자가 잠금을 해제할 때까지 해당 잠금과 충돌하는 동작을 수행할 수 없다.

데이터에 대한 경합이 치열한 환경에 주로 사용되기 때문에 비관적 제어라고 한다.

보통의 isolation level을 설정할 때 언급되는 격리 수준에 대한 내용이다.

 

l  낙관적 동시성 제어

낙관적 동시성 제어에서는 사용자가 데이터를 읽을 때 해당 데이터를 잠그지 않는 방식

사용자가 데이터를 업데이트할 때는 다른 사용자가 해당 데이터를 읽은 후 변경하지 않았는지 검사가 진행하고 다른 사용자가 데이터를 업데이트한 경우에는 오류가 발생됨

일반적으로 오류를 수신한 사용자의 트랜잭션이 롤백되고 다시 시작된다.

데이터에 대한 경합이 낮은 환경에 주로 사용되기 때문에 낙관적 제어라고 한다.

 

이 때 낙관적 동시성 제어 모델에서 다른 사용자가 데이터를 업데이트 하는 경우를 판단하기 위해 갱신 전 데이터를 임시로 저장(Snapshot) 하고 사용하는 것을 Row Versioning 이라고 한다.

 

Row Versioning을 위한 데이터를 임시로 하는 저장을 tempdb을 사용(가속 데이터베이스 복구에서는 해당 데이터베이스를 사용) 하여 저장하는데 이 저장소를 Version Store라고 칭하였고 이전 버전의 데이터를 Snapshot이라 칭하였다.

 

Row Versioning의 작동 방식

SQL Server에서 Table 또는 Index의 행에 Update가 발생되면 Transaction Sequence number(XSN)이 발행된다.

XSN는 Transaction이 발생하면 단조롭게 증가하는 숫자로 각 인스턴스 내에서 고유하다.

행을 업데이트하면 이전 행의 데이터가 Version Store에 저장되고 새로 생성된 행이 이전 버전의 행에 대한 포인터를 가지고 있다.

아래 이미지와 같이 앞 선 행의 저장에 대한 포인터를 가지기 때문에 정리되기 이전까지의 데이터 추적이 가능한 것이다.

 

Row Versioning의 사용법 (SI 와 RCSI)

SQL Server에서 적용방법은 SI와 RCSI 두 가지 방법이 있다.

l  RCSI(Read Committed Snapshot Isolation)

다음과 같이 활성화 한다.

ALTER DATABASE AdventureWorks2019 SET READ_COMMITTED_SNAPSHOT ON

 

Database 단위로 설정이 되며 한번 설정 후 OFF 전까지 Read Committed Isolation에서 Snapshot으로 작동한다.

작업을 진행하는 Connection 외에 해당 Database에 대한 연결이 종료되길 기다렸다가 Alter Database 작업 완료 된다.

이 때 바로 RCSI를 적용할 수 있는지 여부를 확인하여 적용하는 구문은 다음과 같은데 데이터베이스에 다른 Connection이 있다면 에러를 발생 시킨다.

ALTER DATABASE AdventureWorks2019

SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

 

메시지 5070, 수준 16, 상태 2, 줄 1

다른 사용자가 데이터베이스 'AdventureWorks2019'을(를) 사용하는 중에는 데이터베이스 상태를 변경할 수 없습니다.

메시지 5069, 수준 16, 상태 1, 줄 1

ALTER DATABASE 문이 실패했습니다.

 

이 때 Alter Database를 성공시키는 방법은 Single 유저로 변경 후 작업하는 것이다.

ALTER DATABASE AdventureWorks2019

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE AdventureWorks2019

SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

ALTER DATABASE AdventureWorks2019 SET MULTI_USER

 

SELECT  name

    ,   is_read_committed_snapshot_on

FROM    sys.databases

WHERE   name='AdventureWorks2019'

 

is_read_committed_snapshot_on 값이 1(True)로 변경되어 있다면 적용이 완료된 것이다.

다음과 같이 쿼리를 진행해서 적용을 확인해 볼 수 있다.

 

위 쿼리의 결과와 같이 3.99의 값이 Lock이 걸리지 않고 조회되었다가 다른 Session에서 Commit이 진행되면 바뀐 값이 조회 되는 것을 볼 수 있다.

DMV 조회 내용을 보면 다음과 같다.

 

다음 내용과 같이 Commit 되기 이전의 정보를 tempdb에 저장하고 있으며 Commit 후 일정 시간이 지나 다시 조회하면

다음과 같이 조회되지 않고 제거되는 것을 볼 수 있다.

해당과 같은 작업은 Version Store Cleanup 이라고 한다.

위와 같이 SELECT 작업은 진행되었으나 CUD 작업은 여전히 LOCK되어 접근할 수 없다.

 

l  SI(Snapshot Isolation)

다음과 같은 구문으로 SI 사용이 가능하도록 옵션을 변경할 수 있다.

ALTER DATABASE AdventureWorks2019 SET ALLOW_SNAPSHOT_ISOLATION ON

 

SELECT  name

    ,   snapshot_isolation_state_desc

FROM    sys.databases

WHERE   name='AdventureWorks2019'

 

해당 결과값이 ON이면 설정이 된 것이다.

RCSI와 다르게 다른 Connection들과 상관없이 바로 적용이 되는데 SI 같은 경우 Database에 전체에 적용하는 것이 아닌 Isolation level 중 Snapshot을 사용할 수 있도록 설정하는 옵션이기 때문이다.

하지만 해당 Database에 Transaction이 실행 중이라면 해당 Transaction이 완료될 때까지 기다다리는데 이 때 snapshot_isolation_state_desc 가 IN_TRANSITION_TO_ON(또는 IN_TRANSITION_TO_OFF) 상태로 표시된다.

 

다음과 같이 사용하여 확인해 볼 수 있다.

 

다음과 같이 Transaction이 완료 전 대기 상황을 가졌다가 완료 후 적용 되는 것을 볼 수 있다,.

 

SI는 다음과 같이 진행해서 작업 내역을 확인해 볼 수 있다.

 

RCSI와 다르게 현재 Session의 Transaction에서 Commit이 완료되지 않으면 Versioning Store에서 값을 조회해 오는 것을 확인할 수 있다.

 

유의점

대부분의 상황에서는 RCSI가 SI보다 권장되는데 사항은 다음과 같다.

l  RCSI는 tempdb의 용량을 SI보다 적게 사용한다.

l  RCSI는 분산 트랜잭션과 함께 사용 가능하지만 SI는 그렇지 않다.

l  RCSI는 업데이트 충돌이 없다

l  RCSI는 명시적으로 Isolation level을 변경할 필요가 없다.

하지만 SI를 사용해야 하는 고유 사항도 있어야 한다.

l  업데이트 충돌이 일어날 확률이 적어 Rollback 해야 할 가능성이 낮은 서비스

l  동시 수정 작업이 없고 반복적으로 읽는 작업이 많은 서비스

RCSI나 SI를 사용 시 몇 가지 유의사항을 인지 하고 적용해야 한다.

이전 버전의 행 데이터를 tempdb에 저장함에 따라 tempdb의 데이터 증가가 수반되고 tempdb 파일크기 증가에 대한 모니터링 추가 관리 발생된다.

대략적으로 Version Store가 작업 시 필요로 하는 디스크 공간은 다음 같다.

l  Version Store Size = 2 * 분당 생성된 Version Store 데이터 * 가장 긴 Transaction Time(분)

또한 UPDATE 발생 시 이전 버전 행 데이터를 tempdb에 저장하기 때문에 UPDATE 시 성능의 저하가 발생되고 주기적으로 tempdb의 저장된 이전 버전의 행 데이터를 가장 최신의 1개 ROW를 남기고 삭제하는 로직이 발생됨에 따른 성능저하가 발생된다.

현재의 데이터를 조회할 시에 이전 행의 정보를 알기 위한 포인터를 따라가 현재 transaction에 적절한 버전인지에 대한 확인을 진행하기 때문에 추가 비용이 발생된다.

 

참조

https://docs.microsoft.com/ko-kr/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/row-versioning-concurrency-in-sql-server/

https://docs.microsoft.com/ko-kr/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server