MS-SQL7.0 Implementation 강의 노트 Written by 남현주 Written by 남현주

제12장 트랜잭션 및 잠금 관리 트랜잭션 관리 (트랜잭션의 개념) (트랜잭션의 종류) 만약 문제가 생기면 전부 되거나 아예 안되도록 (All or Nothing)하는 일의 최소 단위   (트랜잭션의 종류) 암시적 트랜잭션과 명시적 트랜잭션

. 암시적(implicit) 트랜잭션(시스템 트랜잭션) 트랜잭션의 시작과 끝을 시스템이 지정하고 처리한다. 문장의 앞과 뒤에 자동으로 BEGIN TRAN과 COMMIT TRAN이 붙는다. . 명시적(explicit) 트랜잭션(사용자 트랜잭션) 트랜잭션의 시작과 끝을 사용자가 명시적으로 지정해야 한다. 예) BEGIN TRAN DELETE sales UPDATE sales SET qty = 0 COMMIT TRAN

(모든 수정에 트랜잭션을 걸 때) SET IMPLICIT_TRANSACTIONS ON 옵션을 설명해주면 모든 데이터의 수정 문장 끝에는 사용자가 꼭 COMMIT 또는 ROLLBACK해야만 완전하게 트랜잭션이 끝이 난다. 옵션이 설정되었는지 확인은 DBCC USEROPTIONS로 한다.

(@@TRANCOUNT와 중첩 트랜잭션) T-SQL 확장에서는 트랜잭션을 중첩시킬 수 있다. 그러나, ANSI 92에서는 중첩 트랜잭션이 없다. 값이 0이면 트랜잭션 밖에 있고, 1이상이면 트랜잭션 진행 중이다. COMMIT은 짝을 찾아 COMMIT 시키지만, ROLLBACK은 전부다 ROLLBACK 시킴 (SAVE TRANSACTION) SAVE TRAN은 지금까지의 트랜잭션을 임시 저장하는 것이다. SAVE TRAN은 @@TRANCOUNT를 증가시키지는 않는다.

트랜잭션과 잠금 잠금(LOCK)이 왜 필요한가? 트랜잭션의 직렬화를 가능하게 하여 오직 한 번에 한 명만이 데이터를 변경할 수 있게한다.

잠금을 걸지 않으면 나타나는 문제 ANSI레벨 문제점 설명 트랜잭션 고립수준 LEVEL 0 Dirty read 더티 페이지, 즉 commit되지 않은 값을 읽어 오도록 한다. 즉, 다른 트랜잭션이 아직 완료하지 않은 엉터리 값을 읽어올 수 있다. READ UNCOMMITTED LEVEL 1 Non-Repeatable Read 같은 값을 다시 가져왔을 때 이전에 가져온 값과 다르다. 즉, 반복적으로 읽었을 때 서로 다른 값이 나타난다. SQL Server의 디폴트 상태이다. COOMMITED LEVEL 2 Phantom Read 어떤 범위에 해당하는 값을 가져왔을 때 없던 값이 갑자기 나타나거나, 있던 값이 사라진다. REPEATABLE READ LEVEL 3   어떤 문제도 발생하지 않는다 SERIALIZABLE

잠금의 특성 종류 언제 걸리나? 언제 풀리나? 특성 공유 잠금 읽기 잠금 SELECT SELECT가 끝나면 바로 풀린다. 다른 잠금과 공유된다. 배타적 잠금은 함께 걸릴 수 없다. 배타적 잠금 쓰기 잠금 INSERT, UPDATE, DELETE 트랜잭션이 끝날 때 풀린다. 배타적이라서 다른 잠금과 함께 걸릴 수 없다.

잠금의 범위 종류 설명 RID 행 번호 (row ID) 클러스터 색인이 없을 경우 한 행에 대한 잠금이 걸릴 때 사용된다. Key 색인의 키 색인에 대해 잠금이 걸릴 때 사용되며 seralizable을 처리하기 위해 사용된다. page 티이블이 아니라 한 페이지(8KB)를 통째로 잠근다. Extent 8개의 페이지가 모인 익스텐트에 대해 잠금다. 새로운 페이지가 필요할 때 주로 걸린다. Table 전체 테이블에 대해 잠근다. Database 전체 데이터베이스를 잠금다. 리스토어를 할 때 발샐한다. Server 실제로 서버 전체를 잠그는 방법은 없다. 개념적인 것이다. Single user mode로 서버를 시작하면 된다.

(트랜잭션과 잠금에 대한 정보 알아내기) sp_lock : 서버에 걸린 모든 잠금에 대한 정보를 보여준다.   공유잠금(S) 배타적 잠금(X) 공유 잠금(S) O X (트랜잭션과 잠금에 대한 정보 알아내기) sp_lock : 서버에 걸린 모든 잠금에 대한 정보를 보여준다.  Sp_lock@@spid : 실행 시킨 세션에서 걸고 있는 잠금에 대한 정보를 보여준다.     EM에서 [Management] – [Current Activity]를 통해 : 그래픽하게 볼 수 있다.

Transaction Isolation Level) (트랜잭션 고립화 수준: Transaction Isolation Level) 트랜잭션 고립 수준이란 트랜잭션을 처리할 때 잠금의 수준을 어떻게 결정할 것 인지를 지정하는 것이다. 트랜잭션 고립 수준은 네 가지 단계가 있다. 1.        READ COMMITTED (level 1)  default COMMIT되지 않은 모든 트랜잭션에 대해서는 잠금이 걸리고, 읽어갈 수 없게 된다. 즉, dirty read를 금지하게 된다. 2.        READ UNCOMMITTED (level 0) Dirty read를 하겠다고 선언하는 것이다.(no lock)

3.        REPEATABLE READ(level 2) 트랜잭션이 끝날 때까지 잠금을 걸도록 해 주어서, 반복적으로 그 값을 다시 읽었을 때도 값은 값이 되돌려 질 것을 보장한다. 4.        SERIALIZABLE SERIALIZABLE에서는 한번 SELECT하여 가져간 모든 데이터에 공유 잠금을 걸 뿐만 아니라, 그 사이에 있는 모든 키 값에 대해서도 잠금을 걸게 된다. 5.        SET LOCK_TIMEOUT 트랜잭션 고립화 수준은 아니지만, LOCK_TIMEOUT은 잠금이 걸린 데이터에 대해 처리를 할 때 지정된 시간만큼 기다린 후 timeout 처리를 하는 명령이다. 세션 수준으로 처리되며, 단위는 ms이다. 필요에 따라 적절히 사용하면 무한정 기다리는 것(블로킹 blocking)을 피할 수 있어 성능향상에 도움이 된다.

(잠금 힌트 Lock Option) 잠금을 수동으로 지정하는 방법이다. 잠금 힌트는 트랜잭션 고립화 수준보다 우선된다. 꼭 필요한 경우를 제외하고는 SQL 서버가 자동처리 하도록 두는 것이 좋다. 예) Repeatable Read를 처리를 하고자 할 때(SELECT 를 수행한 후에도 잠금을 풀지 않을 때) BEGIN TRAN SELECT … FROM pubs (HOLDLOCK) COMMIT TRAN

옵션 설명 NOLOCK SELECT문에만 사용되며, 잠금을 걸지 않는다. READ UNCOMMITTED와 같다. READUNCOMMITTED   READCOMMITED REPEATABLE READ SERIALIZABLE HOLDLOCK SERIALIZABLE과 동일하다. ROWLOCK 행 단위 잠금을 건다. PAGLOCK 페이지 단위 잠금을 건다. TABLOCK 테이블 단위 잠금을 건다. TABLOCKX 테이블 단위 배타적 잠금을 건다. UPDLOCK SELELCT문에서 공유 잠금 대신 업데이트 잠금을 건다. 뿐만 아니라 HOLDLOCK도 함께 걸려 트랜잭션의 끝까지 잠금을 풀지 않는다. READPAST 행 단위 잠금이 걸린 것을 건너 뛴다.

(블로킹(Blocking)과 데드락(Deadlock)) 예) SELECT * FROM a (READPAST) WHERE id BETWEEN 1 AND 10  (블로킹(Blocking)과 데드락(Deadlock)) 블로킹: tx1이 잠금을 걸고 있고, tx2가 같은 데이터에 대해 잠금을 걸려고 대기 중인 상태를 블로킹이라고 한다. 흔히 오해하고 있는 부분으로, 블로킹은 데드 락이 아니고 라이브 락이다. 블로킹 상태는 사용자가 일부러 일으킨 상태이기 때문에 (예를 들어 commit되지 않은 상태) SQL서버는 자동으로 kill하거나 커밋하지 않는다. 따라서 수시로 모니터링이 필요하다. SET LOCK_TEIMOUT은 블로킹을 직접 해결하기 위한 방법은 아니지만, 블로킹 해제를 무한정 기다려야 하는 것은 피할 수 있다.

데드락: 라이브 락의 반대되는 개념으로 둘 이상의 트랜잭션이 서로 순환적으로 잠금을 일으켜 무한 루프에 빠진 것이다. 2개의 트랜잭션이 자신들이 사용하는 각 객체에 잠금을 유지하고 있으며넛 서로 상대방의 객체에도 잠금을 요구할 때 발생한다.   SQL 서버의 교착상태의 해결 1.       교착상태 희생자가 된 트랜잭션을 롤백한다. 2.       교착상태 희생자의 어플리케이션에 메시지 번호 1205를 띄워 알려준다. 3.        교착 상태 희생자의 현재 요구를 취소한다. 4.        다른 트랜잭션은 계속 수행한다.

데드락을 최소화하기 위한 방법 2. 트랜잭션을 짧게가져간다. 3. 주기적인 모니터링 1.   같은 방향으로 트랜잭션을 진행시킨다. 2.   트랜잭션을 짧게가져간다. 3.   주기적인 모니터링 4.    SET LOCK_TIMEOUT, SET옵션 사용 5.   READ UNCOMMITTED 고립 수준을 적절하게 사용 (분산 트랜잭션과 DTC) 분산 데이터를 하나의 트랜잭션으로 처리하려면 BEGIN DISTRIBUTED TRAN SERVER1에서의 작업 SERVER2에서의 작업 COMMIT TRAN

이 트랜잭션 동안 양쪽 서버의 해당 페이지가 모든 잠금이 되므로, 어느 한 쪽 서버의 해당 페이지가 꺼져있다면 다른 쪽 서버도 롤 백 될때까지 부하가 심하게 걸린다. 또한 다른 서버에서 변경하려는 페이지가 잠겨 있을 때도 마찬가지이다. 따라서 양쪽 모두 수정하는 상황이 아니라, 어느 한쪽 서버에서만 수정되고 다른 쪽 서버는 데이터를 받기만 하는 상황이라면 복제를 사용하거나, 트랜잭션이 꼭 필요한 상황이 아니라면 그냥 원격 스토어드 프로시저 정도로 구현하는 것이 보다 나은 방법이다.

제14장 저장프로시저 구현하기 트랜잭션 관리 (트랜잭션의 개념) (트랜잭션의 종류) 만약 문제가 생기면 전부 되거나 아예 안되도록 (All or Nothing)하는 일의 최소 단위   (트랜잭션의 종류) 암시적 트랜잭션과 명시적 트랜잭션

제15 트리거 구현하기 트랜잭션 관리 (트랜잭션의 개념) (트랜잭션의 종류) 만약 문제가 생기면 전부 되거나 아예 안되도록 (All or Nothing)하는 일의 최소 단위   (트랜잭션의 종류) 암시적 트랜잭션과 명시적 트랜잭션