제 9 장 트랜잭션

INSIDE MICROSOFTSQL SERVER 2005:T-SQL PROGRAMMING 제9장 트랜잭션

트랜잭션의 특성 • 원자성(Atomicity) • 작업의 단위 • 일관성(Consistency) • 데이터는 트랜잭션의 시작부터 끝까지 일관되게 유지 • 격리성(Isolation) • 호환되지 않는 작업으로부터의 격리 • 영속성(Durability) • 커밋된 트랜잭션이 영속적으로 기록 • ACID

트랜잭션은 무엇인가? • 동작의 범위를 정의 할 수 있게 해 준다 • BEGIN TRAN/COMMIT TRAN블럭 • 데이터베이스 변경 아키텍쳐 1. 영향 받은 페이지가 캐시에 존재 하는지 확인 2. 존재 하지 않으면 페이지를 캐시로 로드 3. 캐시에서 페이지 변경 4. 변경된 내용을 트랜잭션 로그에 기록 5. 캐시에서 변경된 페이지를 디스크의 데이터 부분에 기록 (CHECKPOINT) • 롤백(Roll Back)과 롤포워드(Roll Forward) • SQL Server는 특정 작업이 실패 하였을 때 트랜잭션을 자동으로 롤백 하지 않을 수 있다 • XACT_ABORT ON/OFF사용 고려

잠금 • 트랜잭션의 격리성 측면을 제공 • 세션이나 쿼리의 격리 수준 설정을 통한 동시성 수준 조절 • 잠금 형식의 선택 • 행 식별자(RID),키(Key),페이지, 익스텐트, 테이블 등 • 어떤 리소스 형식으로 잠금을 얻을 지 자동으로 선택 • 테이블 힌트(ROWLOCK, PAGLOCK, TABLOCK)로 리소스 유형 지정 • 힌트로 지정된 리소스 유형은 무시 될 수 있음 • 잠금 모드 • 배타(X) 잠금, 공유(S) 잠금 • 업데이트(U) 잠금, 의도(Intent)잠금 • 스키마 잠금: 스키마 수정(Sch-M) 잠금, 스키마 안정성(Sch-S)잠금 • 대량 업데이트(BU) 잠금, 키범위 잠금

잠금 호환성

잠금에 대한 정보 • 동적 관리 뷰(Dynamic Management View) • sys.dm_tran_locks • sys.dm_exec_connections • sys.dm_exec_sessions • sys.dm_exec_requests • 동적 관리 함수(Dynamic Management Function) • sys.dm_exec_sql_text SELECT session_id, text FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST WHERE session_id IN(52, 53); GO

잠금 에스컬레이션 • 각각의 잠금에 대한 메모리 블록 관리 • 잠금의 개수에 제한이 없다면 잠재적인 메모리 부족 발생 • 너무 많은 잠금 보유 시 미세 잠금을 테이블 잠금으로 에스칼레이션 함 • 추가적인 1,250개의 잠금을 얻을 때마다 잠금 에스칼레이션 시도 • 이로 인해 잠금의 부하를 줄여주게 됨 • 그 결과로 동시성은 줄어들게 됨

격리 수준 • 동시성과 일관성 • 동시성이 안 좋으면 일관성이 향상 됨 • 동시성이 좋으면 일관성이 나빠짐 • 격리 수준(Isolation Levels) • 커밋되지 않은 읽기(Read Uncommitted) • 커밋된 읽기(Read Committed: Default Isolation Level) • 반복 읽기(Repeatable Read) • 직렬화 읽기(Serializable) • 스냅샷(Snapshot) • 커밋된 읽기 스냅샷(Read Committed Snapshot) • 일관성과 관련된 문제(Consistency Related Problems) • 커밋되지 않은 읽기(Dirty Read) • 업데이트 손실(Lost Updates) • 반복되지 않은 읽기(Non-repeatable Reads) • 팬텀 읽기(Phantoms)

스냅샷(Snapshot) • 스냅샷 격리 수준(Snapshot Isolation) • 트랜잭션이 시작되었을 당시에 사용 가능한 가장 최근의 일관성 있는 버전의 데이터를 가져옴 • 데이터베이스 옵션 • ALLOW_SNAPSHOT_ISOLATION ON • 행 버전(Row Version)확인 • sys.dm_tran_version_store • 격리 수준 설정 • SET TRANSACTION ISOLATION LEVEL SNAPSHOT • 업데이트 충돌(Update Conflict)

커밋된 읽기 스냅샷(Read Committed Snapshot) • 커밋된 읽기 스냅샷 격리 수준 • 문장이 시작되었을 당시에 사용 가능한 가장 최근의 일관성 있는 데이터의 버전을 가져옴 • 업데이트 충돌이 감지되지 않음 • 데이터베이스 옵션 • READ_COMMITTED_SNAPSHOT ON

격리 수준 요약 ※ 비관적: Pessimistic / 낙관적: Optimistic

저장점(Save Points) • SQL Server는 진정한 의미의 중첩된 트랜잭션을 지원하지 않음 • 트랜잭션 내에서 • ROLLBACK TRAN 문 호출 • 가장 바깥쪽 BEGIN TRAN 이후로 동작 중인 모든 작업 롤백 • BEGIN TRAN 문 호출 • 새로운 트랜잭션을 열지는 않음 • 단순히 내부 카운터를 증가 시킴 (@@trancount로 확인 가능) • COMMIT TRAN 문 호출 • 카운터가 하나씩 줄어 듬 • 카운터를 0으로 줄이는 가장 바깥쪽 COMMIT TRAN 문이 수행 될 때 실제로 트랜잭션이 커밋 됨 • BEGIN TRAN 문으로 트랜잭션을 열 수 있는 단계: 32

저장점(Save Points) • 저장점 • 트랜잭션 내에서 부분 적인 작업을 실행 취소 할 수 있게 함 • 저장점 표시: SAVE TRAN <저장점 이름> • 작업 실행 취소: ROLLBACK TRAN <저장점 이름>

교착상태(Deadlocks) • SQL Server는 교착상태를 자동으로 감지하고 해결 • 희생자(Victim) • 메시지 1205,수준 13,상태 51,줄 2 • 트랜잭션(프로세스 ID 54)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오. • 우선 순위 제어 • DEADLOCK_PRIOROTY 세션 옵션 • LOW / NORMAL (기본값) / HIGH • 누락된 인덱스로 인한 교착상태 • 단일 테이블에서의 교착 상태

문제 해결 • 추적 플래그 설정 • 플래그1204, 1222 • 교착상태 발생시 에러 로그 기록됨 • SQL Server Profiler • SQL:StmtStarting • Lock:Timeout • Lock:Deadlock Chain • Lock:Deadlock • Deadlock:Graph • SQL Server 2005의 새로운 이벤트 • 교착 상태의 정보를XML 값으로 생성해 줌

Time:PT2 S lock on NC idx row where col1 = 2 Granted Time:PT1 X lock on CL idx row where keycol = 2 Granted Set col1 = <newval> Time:PT3 Need to update NC idx row where col1 = 102 Request X lock Waiting Time:PT4 Need col2 from CL idx row Request S lock Waiting 단일 테이블에서의 교착 상태 연결1 연결2 UPDATE dbo.T1 SET col1 = <newval> WHERE keycol = 2 SELECT col2 FROM dbo.T1 WHERE col1 = 102 NC Index on col1 CL Index on keycol … keycol=2, col1=102,col2=‘B’ … Deadlock X … col1=102, CL Key=2 …

결론 • 여러 동시 사용자를 위해 응용프로그램 개발 시 고려 사항이 많음 • 동시성의 개념을 이해하는 것이 중요 • SQL Server 2005에서 새로 선보인 스냅샷 격리 수준

제 9 장 트랜잭션

제 9 장 트랜잭션

Presentation Transcript