SQLWORLD.PE.KR 제 9 장 트랜잭션 INSIDE MICROSOFT SQL SERVER 2005: T-SQL PROGRAMMING

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

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

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

SQLWORLD.PE.KR 5 잠금 호환성 요청 모드 허용 모드 ISSUIXX 의도 공유 (IS) Yes No 공유 (S) Yes No 업데이트 (U) Yes No 의도 배타 (IX) YesNo YesNo 배타 (X) No

SQLWORLD.PE.KR 6 잠금에 대한 정보 동적 관리 뷰 (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

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

SQLWORLD.PE.KR 8 격리 수준 동시성과 일관성 동시성이 안 좋으면 일관성이 향상 됨 동시성이 좋으면 일관성이 나빠짐 격리 수준 (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)

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

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

SQLWORLD.PE.KR 11 격리 수준 요약 격리 수준 커밋되지 않은 읽기 업데이트 손실 반복되지 않은 읽기 팬텀 읽기 동시성 모델 업데이트 충돌 감지 커밋되지 않은 읽기 Yes 비관적 No 커밋된 읽기 NoYes 비관적 No 반복 읽기 No Yes 비관적 No 직렬화 읽기 No 비관적 No 스냅샷 No 낙관적 Yes 커밋된 읽기 스냅샷 NoYes 낙관적 No ※ 비관적 : Pessimistic / 낙관적 : Optimistic

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

SQLWORLD.PE.KR 13 저장점 (Save Points) 저장점 트랜잭션 내에서 부분 적인 작업을 실행 취소 할 수 있게 함 저장점 표시 : SAVE TRAN 작업 실행 취소 : ROLLBACK TRAN

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

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

SQLWORLD.PE.KR 16 단일 테이블에서의 교착 상태 … keycol=2, col1=102,col2=‘B’ … col1=102, CL Key=2 … CL Index on keycol NC Index on col1 Deadlock X Time:PT1 X lock on CL idx row where keycol = 2 Granted Set col1 = Time:PT2 S lock on NC idx row where col1 = 2 Granted 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 SELECT col2 FROM dbo.T1 WHERE col1 = 102 UPDATE dbo.T1 SET col1 = WHERE keycol = 2 연결 1 연결 2

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