SQL Server 2000 트랜잭션과 잠금 데브피아 세미나 정원혁 / MCDBA, MCT http://mssql.ce.ro http://www.wtime.net http://www.gtu.co.kr http://www.inbrein.com -1-
차례1 트랜잭션 트랜잭션 기본 개념 (ACID) 트랜잭션 종류/ 암시트랜잭션 모드 중첩 트랜잭션/ save tran -2-
차례2 잠금 필요성/ 나타나는 문제 낙관적.비관적 잠금 잠금의 크기/ 크기와 동시성 잠금의 길이 잠금 호환성 정보 얻기 blocking 잠금의 세기 특수 잠금 -3-
차례3 격리 수준 잠금 힌트 블록킹과 데드락 분산 트랜잭션과 잠금 연결된 서버 set xact_abort 분산 트랜잭션 2단계 커밋 -4-
차례4 트리거와 트랜잭션 커서와 트랜잭션 인덱스와 잠금 잠금 수준 escalation 바운드 연결 -5-
트랜잭션? 계좌 이체 무지 큰 테이블에 대한 수정이나 삭제 BEGIN TRAN COMMIT | ROLLBACK -6-
트랜잭션? ACID 원자성 더 이상 분류할 수 없는 작업 단위 All or Nothing 일관성 완료된 트랜잭션의 모든 데이터는 일관적이어야 무결성 원자성 트랜잭션은 더 이상 분류할 수 없는 작업 단위여야 하며 모든 데이터 수정 작업이 수행되거나 하나도 수행되지 말아야 합니다. 일관성 완료된 트랜잭션의 모든 데이터는 일관적이어야 합니다. 관계형 데이터베이스에서는 트랜잭션 수정에 모든 규칙을 적용하여 모든 데이터 무결성을 유지해야 합니다. 트랜잭션 마지막에는 B-tree 인덱스 또는 이중 연결 목록 등 모든 내부적 데이터 구조를 반드시 수정해야 합니다. -7-
트랜잭션? ACID 격리성 다른 트랜잭션이 수정하기 전 상태의 데이터를 보거나, 두 번째 트랜잭션이 완료된 후의 데이터를 볼 수는 있지만 중간 상태는 볼 수 없다 Roll forward, Roll back : 순차성 영속성 완료되고 나면 영구적으로 시스템에 적용. 수정은 시스템에 오류가 발생한 경우에도 지속. 격리성 동시 트랜잭션에 의한 수정은 다른 동시 트랜잭션에 의한 수정과 격리되어야 합니다. 트랜잭션에서 다른 동시 트랜잭션이 수정하기 전 상태의 데이터를 보거나, 두 번째 트랜잭션이 완료된 후의 데이터를 볼 수는 있지만 중간 상태는 볼 수 없습니다. 결과적으로 시작 데이터를 다시 로드하고 일련의 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌릴 수 있는데 이를 순차성이라고 합니다. 영속성 트랜잭션이 완료되고 나면 그 영향이 영구적으로 시스템에 적용됩니다. 수정은 시스템에 오류가 발생한 경우에도 지속됩니다. -8-
트랜잭션의 종류 암시적(implicit) 트랜잭션: 서버가 알아서 UPDATRE bigtable SET… WHERE … DELETE bigtable WHERE … => BEGIN TRAN COMMIT -9-
트랜잭션의 종류 명시적(explicit) 트랜잭션: 사용자가 지정 UPDATE 입고 SET… WHERE … => BEGIN TRAN COMMIT -10-
중첩 트랜잭션 트랜잭션 안에 또 트랜잭션 begin tran select @@trancount commit -11- 소스코드 1.1 – 1.3 인쇄 -11-
중첩 트랜잭션 begin tran commit rollback -12-
save tran begin tran save tran AA rollback tran AA commit -13-
암시적 트랜잭션 모드 Delete bigtable 해 놓고 보니 where 절 빼먹었다. 아뿔싸! Begin tran이 없으면 rollback 불가능 암시적 트랜잭션 모드에서는 가능 set implicit_transactions on dbcc useroptions 신중하게 사용하자 blocking 세션 옵션 --소스 2 -14-
암시적 트랜잭션 모드 -15-
사용할 수 없는 문장 BACKUP LOG RESTORE LOG CREATE DATABASE ALTER DATABASE DROP DATABASE RECONFIGURE UPDATE STATISTICS LOAD DATABASE LOAD TRANSACTION DUMP TRANSACTION DISK INIT -16-
암시적 트랜잭션 모드 서버 수준에서 설정 가능 EM 에서 sp_configure ‘user options’, xxx reconfigure select @@options --소스 3 -17-
세션2 -18-
잠금 필요성: 문 안잠그고 응가하면? 안 걸면 다음과 같은 문제 발생 손실 업데이트(lost update) 커밋되지 않은 종속성(커밋되지 않은 읽기) (read uncommitted) 일관성 없는 분석(반복하지 않는 읽기) (non - repeatable read) 팬텀 읽기 (phantom read) -19-
잠금 - lost update 손실 업데이트(lost update) 손실 업데이트는 둘 이상의 트랜잭션이 같은 행을 선택한 다음 원래 선택한 값을 기준으로 행을 업데이트할 때 발생합니다. 이 때 각 트랜잭션은 다른 트랜잭션을 인식하지 못합니다. 마지막 업데이트가 다른 트랜잭션의 업데이트를 겹쳐쓰므로 데이터가 손실됩니다. 예를 들어, 두 명의 사용자가 같은 문서를 복사한다고 가정합니다. 각 사용자가 각자 복사본을 변경한 다음 변경된 복사본을 저장하면 원본 문서가 겹쳐써집니다. 변경된 복사본을 마지막으로 저장한 사용자는 먼저 사용자가 변경한 내용을 겹쳐씁니다. 이러한 문제를 해결하려면 다른 사용자가 변경 작업을 마칠 때까지 변경할 수 없게 해야 합니다. -20-
잠금 - read uncommitted 커밋되지 않은 종속성은 다른 트랜잭션이 업데이트 중인 행을 선택할 때 발생합니다. 두 번째 트랜잭션이 읽고 있는 데이터는 아직 커밋되지 않았지만 현재 행을 업데이트 중인 트랜잭션에 의해 변경될 수 있습니다. 예를 들어, 한 사용자가 문서를 변경 중이라고 가정합니다. 변경하는 동안 다른 사용자가 그 시점까지 변경한 내용을 모두 포함하여 문서를 복사한 다음 다른 사용자에게 문서를 배포합니다. 첫 번째 사용자가 그 때까지 변경한 내용이 잘못되었다고 판단하여 편집 내용을 지우고 문서를 저장할 수 있습니다. 배포된 문서에는 틀린 내용이 있으므로 이 내용은 무시해야 합니다. 이러한 문제를 해결하려면 첫 번째 사용자가 변경한 내용이 최종이라고 결정할 때까지 다른 사용자가 변경된 문서를 읽을 수 없게 해야 합니다. -21-
잠금 - non - repeatable read 일관성 없는 분석은 두 번째 트랜잭션이 같은 행에 액세스할 때마다 다른 데이터를 읽을 때 발생합니다. 일관성 없는 분석은 두 번째 트랜잭션이 읽고 있는 데이터를 다른 트랜잭션이 변경하고 있다는 점에서 커밋되지 않은 종속성과 비슷합니다. 그러나 일관성 없는 분석에서는 두 번째 트랜잭션이 읽은 데이터가 변경한 트랜잭션에 의해 커밋된 것이며 같은 데이터를 읽을 때마다 매번 다른 트랜잭션이 정보를 변경하는 것입니다. 이를 반복하지 않는 읽기라고 합니다. 예를 들어, 한 사용자가 같은 문서를 두 번 읽는데 각 읽기 사이에 다른 사용자가 문서를 다시 작성할 수 있습니다. 한 사용자가 같은 문서를 다시 읽으면 이 문서가 변경되어 있을 것이므로 원래의 읽기는 반복되지 않습니다. 이러한 문제를 해결하려면 한 사용자가 문서를 모두 작성한 후에만 다른 사용자가 문서를 읽게 합니다. -22-
잠금 - phantom read 팬텀 읽기는 한 트랜잭션이 읽고 있는 행 범위의 한 행에 대해 다른 트랜잭션이 삽입 또는 삭제 작업을 수행할 때 발생합니다. 다른 트랜잭션의 삭제 작업으로 인해 트랜잭션이 첫 번째 행 범위를 읽을 때 읽은 행이 다음에 읽을 때 없어질 수 있습니다. 마찬가지로 다른 트랜잭션의 삽입 결과로 처음 읽을 때 없던 행이 다음에 읽을 때 생길 수도 있습니다. 예를 들어, 문서 작성자가 제출한 문서를 편집자가 변경하는 중 생산 부서에서 문서의 마스터 복사본으로 변경 내용을 통합할 때 작성자가 편집되지 않은 새 자료를 문서에 추가할 될 수 있습니다. 이 문제를 해결하려면 편집자나 생산 부서가 원본 문서 작업을 완료할 때까지 다른 사용자가 새 자료를 문서에 추가할 수 없게 합니다. -23-
낙관적/ 비관적 동시성 낙관적 동시성 Optimistic Lock 내가 데이터 처리할 동안 설마 넘들이 건드리겠어? 비관적 동시성 Pessimistic Lock 내가 데이터 처리할 동안 넘들이 건드릴 가능성이 당연히 있지… 클라이언트 쪽에서 사용 -24-
잠금의 크기 SQL 서버가 알아서 잘 내가 강제로 지정가능 – 잠금 힌트 (별로 권장 안 함) 행 단위 잠금이 기본 내가 강제로 지정가능 – 잠금 힌트 (별로 권장 안 함) 행 단위 잠금이 기본 행 : RID, KEY 페이지: PAG 테이블: TAB 익스텐트 : EXT 데이터베이스: DB 서버 -25-
잠금 크기와 동시성 -26-
잠금의 길이 공유 잠금 select 가 끝나자 마자 풀린다 격리 수준/ 잠금힌트로 조정 가능하다 배타 잠금 트랜잭션이 끝나야 풀린다 조정 불가능하다 업데이트 잠금 트랜잭션 끝까지 -27-
잠금의 호환성 -28-
Pages(참고) Size: 8K, 96 byte header Max Row Size: 8060 Page header Size: 8K, 96 byte header Max Row Size: 8060 Max Key Size: 900 Max Number of Columns: 1024 Performance Improvements Rows only compacted when necessary Slot array used for binary search Torn Page Detection Torn bits: 011011000….. Row A Row C Row B Offset Slot array 460 200 100 -29-
익스텐트(참고) 균일(Uniform) Contain 8 pages from a single object 혼합(Mixed) Can contain pages from up to 8 objects 8K page Extent (8 pages = 64K) T1 T2 Mixed Extent T3 T4 -30-
페이지 훔쳐보기(참고) DBCC TRACEON (3604) DBCC PAGE (dbname, file번호, page번호, 옵션) DBCC TRACEOFF (3604) 예) dbcc page (pubs, 1, 205, 1) SELECT first FROM SYSINDEXES WHERE ID = object_id('titles') SELECT convert(int, 0xcd) 옵션 0: 헤더만 1: 행 단위로 2: 페이지 그대로 3: 행 / 그리고 컬럼 값 -31-
세션3 -32-
잠금 정보 얻기 sp_lock --소스 4 -33-
잠금 정보 얻기 sp_lock select @@spid select db_name( ) , object_name( ) file # , page #, rid # index id 0: heap 1: clustered 2> : nonclustered 255: image/ text -34-
잠금 정보 얻기 sp_lock --소스5 -35-
Blocking 앞에서 잠금 걸고 막고 있는 넘 sp_who , sp_who2 block by sp_lock wait / grant dbcc inputbuffer( spid ) Real소스\SQLERBlk.exe -36-
세션4 -37-
잠금의 세기 쉽게 생각하면 공유 잠금 배타(적) 잠금 (단독 잠금) 어렵게 생각하면 인텐트(내재) 잠금 스키마 잠금 대량 업데이트 잠금 업데이트 잠금 -38-
특수 잠금 - 인텐트 잠금 잠금 계층 구조를 만드는 데 사용 내재된 공유(IS) 잠금 내재된 단독(IX) 잠금 study! -39-
특수 잠금 – 스키마 스키마 수정(Sch-M) 잠금은 열을 추가하거나 테이블을 삭제하는 등 테이블 데이터 정의 언어(DDL) 작업이 수행 중일 때 사용됩니다. 스키마 안정성(Sch-S) 잠금은 쿼리를 컴파일할 때 사용됩니다. 스키마 안정성(Sch-S) 잠금은 단독(X) 잠금 등 다른 트랜잭션 잠금을 차단하지 않습니다. 따라서 쿼리가 컴파일되는 동안 테이블에 대한 단독(X) 잠금을 포함하여 다른 트랜잭션을 계속 실행할 수 있습니다. 그러나 테이블에서 DDL 작업은 수행할 수 없습니다. --소스 6 -40-
특수 잠금 – 대량 업데이트 테이블로 데이터를 대량 복사하고 TABLOCK 힌트가 지정되거나 sp_tableoption을 사용하여 table lock on bulk load 테이블 옵션이 설정될 때 여러 프로세스가 데이터를 동시에 같은 테이블로 대량 복사할 수는 있지만, 데이터를 대량 복사하지 않는 다른 프로세스가 테이블에 액세스하는 것은 금지 -41-
참고) 대량 업데이트 속도차이 -42-
특수 잠금 – 업데이트 잠금 update 문에서만 발생? S lock 과 X lock 의 bybrid 잠금 자동적으로 U > X 로 전환 변환 교착을 해결할 방법으로 사용 (잠금 힌트) -43-
특수 잠금 – 키 범위 잠금 SERIALIZABLE 격리 수준에서 9가지 형식 rangeS-S / rangeS-U/ rangeX-X …. 특정 범위 -44-
세션5 -45-
격리 수준 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE --- 소스 격리수준 -46-
잠금 힌트 readUncommitted (nolock) readCommitted repeatableRead serializable (holdlock) rowLock pagLock tabLock tabLockX -47-
잠금 힌트 updLock readpast -48-
잠금 힌트 예 select title, au_lname from titles (TABLOCK) join titleauthor on titles.title_id = titleauthor.title_id join authors (PAGLOCK, HOLDLOCK, INDEX(1)) on authors.au_id = titleauthor.au_id -49-
Blocking 얼마나 오래 막고 있을까? set lock_timeout EM / 현재동작 lock hint: readpast @@trancount 클라이언트 버그 중첩 트랜잭션 처리 오류 > @@trancount 확인 dbcc inputbuffer sp_block kill --소스 sp_block2000 -50-
Deadlock 서로 물린 blocking SQL 서버가 해결해 준다 순환 교착 변환 교착 오류 1205 보통, 클라이언트에서 확인 안 한다 그래서 서버에서 DBA 가 확인해 보아야 한다 dbcc traceon (3605) -- errorlog dbcc traceon (1204) -- deadlock dbcc traceon (1200) -- 상세 잠금 정보 dbcc tracestatus(-1) ---------------------- --소스 deadlock -51-
순환 교착 서로 다른 개체를 서로 blocking 할 때 -52-
변환 교착 같은 대상에 대해 잠금을 변경하려고 할 때 해결책? updlock readcomitted set transaction isolation level repeatable read begin tran select * from titles where title_id = 'bu1032' waitfor delay '0:00:05' update titles set price = price * 2 where title_id = 'bu1032' commit 해결책? updlock readcomitted --변환 교착 -53-
Deadlock 최소화 일방통행 짧게 폭좁게 set lock_timeout read uncommitted set deadlock_priority low 길을 넓힌다 주기적 모니터 -54-
세션6 -55-
연결된 서버 sp_addlinkedserver -56-
openrowset SELECT * FROM OPENROWSET('SQLOLEDB', 'sqlinst';'sa';'', 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') -57-
set xact_abort on begin tran 성공 if 실패 commit 결과는? -58- ---------------------- --소스XACT_ABORT -58-
set xact_abort on if @@error goto on_error: 주로 신경 쓸 오류 515: not NULL 오류 544: identity insert 547: 제약 위반 550: view WITH CHECK OPTION -59-
분산 트랜잭션 MSDTC on linked server 등록 (또는 openrowset) set xact_abort on begin distributed tran server1에서 작업 server2에서 작업 commit -60-
2 Phase commit 준비 단계 커밋 단계 트랜잭션 관리자가 커밋 요청을 수신 관련된 모든 리소스 관리자에게 준비 명령 트랜잭션에 대한 로그를 디스크로 기록 각 리소스 관리자가 준비 단계를 완료하면 준비 성공 또는 실패 여부를 트랜잭션 관리자에게 반환 커밋 단계 각 리소스 관리자에게 커밋 명령 각 리소스 관리자가 커밋을 완료 모든 리소스 관리자가 성공적인 커밋을 보고 트랜잭션 관리자가 응용 프로그램에 성공을 알림 준비 실패를 보고한 리소스 관리자가 있으면 트랜잭션 관리자가 각 리소스 관리자에게 롤백 명령을 보낸다 -61-
세션7 -62-
트리거와 트랜잭션 INSERT … => BEGIN TRAN INSERT… COMMIT 따라서 트리거 내에서 ROLLBACK 사용가능 -63-
트리거와 트랜잭션 트리거rollback.sql create trigger trA1ins on a1 for insert as print 'rollback' rollback go begin tran insert a1 values ('a') commit -- 동작 할까? select * from a1 -64-
중첩 트리거 insert trigger update trigger A B C rollback -65-
트리거와 트랜잭션 트랜잭션 안에서 수행 따라서 불필요한 작업들을 트리거 안에 두지 말자 rollback 도 꼭 필요한 경우 아니면 사용말자 update trigger의 이중 부하 -66-
잠금과 인덱스 관계 인덱스 전혀 없을 때 넌클러스터 인덱스만 클러스터도 인덱스 없는 컬럼의 수정 인덱스 안에서의 잠금 sp_indexoption index n lock.sql -67-
커서와 잠금 기본적으로 다음 fetch 까지 잠금 유지 디폴트 update 가능 커서 optimistic lock 오류 16934:최적 병행성 검사에 실패했습니다. 이 커서 밖에서 행을 수정했습니다 where primaryKey = … where current of SCROLL_LOCKS option -68-
잠금 수준 escaltion 한 트랜잭션의 lock count > 1250 or 잠금 관리자가 메모리 검사 전체 메모리의 40% 이상을 잠금에 사용한다면 RID/ KEY/ PAG lock TAB lock -69-
syslockinfo select * from master..syslockinfo -70-
sysprocesses open_tran & sleeping -71-
바운드 연결 둘 이상의 연결이 같은 트랜잭션과 잠금을 공유 sp_getbindtoken | srv_getbindtoken (ODS) sp_bindsession ---------------------- --소스 bound connection -72-
Application lock 잠금 정보 관리 되지 않는 잠금의 검사와 사용 SQL 서버는 이미 알고있는 잠금 항목(rid, key, page, …)에 대해서만 잠금 처리 예를 들어 procA는 늘 단독으로만 사용되어야 한다면? sp_getapplock sp_releaseapplock -73-
성능 모니터 perfmon -74-