Fast recovery 와 로그 잠금
- Version : SQL Server 2005, 2008, 2008R2, 2012
SQL Server 2005 이상의 Enterprise 버전에서는 fast recovery 기능이 도입 되었다. fast recovery는 복구 단계에서도 데이터베이스를 사용 할 수 있다.
Fast recovery는 장애가 발생하였을 때 이전의 커밋되지 않은 트랜잭션들에 대해서는 장애 발생 이전으로 잠금을 획득하여 트랜잭션이 롤백 되는 동안 자신의 잠금을 사용자 간섭으로부터 보호 한다.
스토리지 엔진은 응급 복구 시 두 개의 패스로 수행 한다. 첫 번째 패스는(REDO) 로그 레코드를 읽고 두 번째 패스(UNDO)는 잠금을 확인 하고 실제 잠금을 획득 한다. Fast recovery 경우 그 시점에서 데이터베이스가 온라인 상태로 된다. 시스템 복구가 가능한 이유는 반대 작업(anti-operation)시 안전을 보장하기 위해 정확한 잠금의 위치를 알고 있기 때문이다. Fast recovery시 데이터베이스는 사용 할 수 있지만 복구 되는 동안 잠금 부분에서 일부 쿼리가 충돌 할 수 있다.
로그가 잠금 되는 환경을 살펴보고 어떻게 잠금이 이루어지는지 확인해 보자. 다음 스크립트는 데이터베이스를 생성하고 테이블을 생성한다. LOB 컬럼을 사용하여 텍스트 페이지 잠금을 확인한다. 첫 번째 데이터를 입력 한 다음 로그를 비운다.
CREATE DATABASE LockLogging; GO
USE LockLogging; GO
CREATE TABLE LockLogTest (c1 INT, c2 INT, c3 VARCHAR (MAX)); GO
EXEC sp_tableoption 'LockLogtest', 'large value types out of row', 'on'; GO
INSERT INTO LockLogTest VALUES (1, 1, 'a'); GO
ALTER DATABASE LockLogging SET RECOVERY SIMPLE; GO
CHECKPOINT; GO |
데이터를 삽입하고 fn_log를 사용하여 로그 기록을 확인한다.
INSERT INTO LockLogTest VALUES (2, 2, 'b'); GO
SELECT [Operation], [Context], [Page ID], [Slot ID], [Number of Locks] AS Locks, [Lock Information] FROM fn_dblog (NULL, NULL); GO |
HoBt 72057594039042048:ACQUIRE_LOCK_IX OBJECT: 11:245575913:0 ;ACQUIRE_LOCK_IX PAGE: 11:1:118 ;ACQUIRE_LOCK_X RID: 11:1:118:1 HoBt 72057594039042048:ACQUIRE_LOCK_IX OBJECT: 11:245575913:0 ;ACQUIRE_LOCK_IX PAGE: 11:1:120 ;ACQUIRE_LOCK_X RID: 11:1:120:1 |
여기서 LOB 입력을 위한 Page IX 와 row X 잠금을 확인 할 수 있다. 잠금 자원은 다음과 같다.
- 11:1:118 : database ID 11, file 1, page 118
- 11:1:118:1 : database ID 11, file 1, page 118, slot 1
- 11:245575913:0 : database ID 11, object ID : 245575913 (테이블 락 테스트)
중요한 부분은 LOP_BEGIN_XACT 및 LOP_COMMIT_XACT 기록 부분이다. 어떤 문제가 발생하였을 때 SQL Server는 롤백 시 이곳에 경계할 수 있도록 (암시적 트랜잭션이라고 부름) 내부적으로 하나를 시작한다.
Checkpoint를 실행하여 로그를 비우고 데이터를 업데이트 한다. 예상대로 table IX, page IX, 두 행의 row X를 확인 할 수 있다.
CHECKPOINT; GO
UPDATE LockLogTest SET c1 = 3; GO
SELECT [Operation], [Context], [Page ID], [Slot ID], [Number of Locks] AS Locks, [Lock Information] FROM fn_dblog (NULL, NULL); GO |
HoBt 72057594039042048:ACQUIRE_LOCK_IX OBJECT: 11:245575913:0 ;ACQUIRE_LOCK_IX PAGE: 11:1:120 ;ACQUIRE_LOCK_X RID: 11:1:120:0 HoBt 72057594039042048:ACQUIRE_LOCK_IX OBJECT: 11:245575913:0 ;ACQUIRE_LOCK_IX PAGE: 11:1:120 ;ACQUIRE_LOCK_X RID: 11:1:120:1 |
Truncate 작업과 같은 좀더 복잡한 작업은 어떻게 되는지 확인해 보자.
CHECKPOINT; GO
TRUNCATE TABLE LockLogTest; GO
SELECT [Operation], [Context], [Page ID], [Slot ID], [Number of Locks] AS Locks, [Lock Information] FROM fn_dblog (NULL, NULL); GO |
잠금 기록에서 Context 컬럼을 보면 할당 비트맵(LCX_IAM, LCX_PFS, LCX_SGAM, LCX_GAM)을 수정하지만 테이블 페이지 잠금을 발생시키고 자신에게 비트맵 할당을 하지 않는다. (이는 오직 래치 작업으로 테이블을 포함하는 페이지가 할당 해제 할 때 수행 된다.) 스토리지 엔진은 필요한 만큼 테이블 할당을 취소 하며 백그라운드에서 이 모든 작업을 수행한다.
[참고자료]
http://www.sqlskills.com/blogs/paul/lock-logging-and-fast-recovery/
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
