데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

Fast recovery 와 로그 잠금

jevida(강성욱) 2016.10.13 06:30 Views : 2018

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

No. Subject Author Date Views
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1538
» Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2018
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2242
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1637
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1638
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2239
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1477
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 4953
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1148
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1279
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1301
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1626
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1262
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1476
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 994
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1331
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1755
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1777
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1256
1911 Memory Pressure jevida(강성욱) 2016.10.12 1120





XE Login