Transcript SQL 강의4일차

MS-SQL7.0 Implementation
강의 노트
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
NonRepeatable
Read
READ
COOMMITED
LEVEL 2
Phantom
Read
같은 값을 다시 가져왔을 때 이전
에 가져온 값과 다르다. 즉, 반복
적으로 읽었을 때 서로 다른 값이
나타난다. SQL Server의 디폴트
상태이다.
어떤 범위에 해당하는 값을 가져
왔을 때 없던 값이 갑자기 나타나
거나, 있던 값이 사라진다.
어떤 문제도 발생하지 않는다
SERIALIZABLE
LEVEL 3
REPEATABLE
READ
잠금의 특성
종류
언제 걸리나?
언제 풀리나?
특성
공유 잠금
읽기 잠금
SELECT
SELECT가 끝나면 바로 풀린다.
다른 잠금과 공유된다.
배타적 잠금은 함께 걸
릴 수 없다.
배타적 잠
금
쓰기 잠금
INSERT,
UPDATE,
DELETE
트랜잭션이 끝날 때 풀린다.
배타적이라서 다른 잠
금과 함께 걸릴 수 없다.
잠금의 범위
종류
설명
RID 행 번호
(row ID)
클러스터 색인이 없을 경우 한 행에 대한 잠금이 걸릴 때 사용된다.
Key 색인의 키
색인에 대해 잠금이 걸릴 때 사용되며 seralizable을 처리하기 위해 사용
된다.
page
티이블이 아니라 한 페이지(8KB)를 통째로 잠근다.
Extent
8개의 페이지가 모인 익스텐트에 대해 잠금다. 새로운 페이지가 필요할
때 주로 걸린다.
Table
전체 테이블에 대해 잠근다.
Database
전체 데이터베이스를 잠금다. 리스토어를 할 때 발샐한다.
Server
실제로 서버 전체를 잠그는 방법은 없다. 개념적인 것이다. Single user
mode로 서버를 시작하면 된다.
공유잠금(S)
배타적 잠금(X)
공유 잠금(S)
O
X
배타적 잠금(X)
X
X
(트랜잭션과 잠금에 대한 정보 알아내기)
sp_lock : 서버에 걸린 모든 잠금에 대한 정보를 보
여준다.
Sp_lock@@spid : 실행 시킨 세션에서 걸고 있는 잠금
에 대한 정보를 보여준다.
EM에서 [Management] – [Current Activity]를 통해 :
그래픽하게 볼 수 있다.
(트랜잭션 고립화 수준:
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
예)
SELECT * FROM a (READPAST)
WHERE id BETWEEN 1 AND 10
(블로킹(Blocking)과 데드락(Deadlock))
블로킹:
tx1이 잠금을 걸고 있고, tx2가 같은 데이터에 대해
잠금을 걸려고 대기 중인 상태를 블로킹이라고 한다.
흔히 오해하고 있는 부분으로, 블로킹은 데드 락이
아니고 라이브 락이다.
블로킹 상태는 사용자가 일부러 일으킨 상태이기 때
문에 (예를 들어 commit되지 않은 상태) SQL서버는
자동으로 kill하거나 커밋하지 않는다. 따라서 수시
로 모니터링이 필요하다.
SET LOCK_TEIMOUT은 블로킹을 직접 해결하기 위한 방
법은 아니지만, 블로킹 해제를 무한정 기다려야 하는
것은 피할 수 있다.
데드락:
라이브 락의 반대되는 개념으로 둘 이상의 트랜잭션
이 서로 순환적으로 잠금을 일으켜 무한 루프에 빠진
것이다. 2개의 트랜잭션이 자신들이 사용하는 각 객
체에 잠금을 유지하고 있으며넛 서로 상대방의 객체
에도 잠금을 요구할 때 발생한다.
SQL 서버의 교착상태의 해결
1.
2.
3.
4.
교착상태 희생자가 된 트랜잭션을 롤백한다.
교착상태 희생자의 어플리케이션에 메시지 번호
1205를 띄워 알려준다.
교착 상태 희생자의 현재 요구를 취소한다.
다른 트랜잭션은 계속 수행한다.
데드락을 최소화하기 위한 방법
1.
2.
3.
4.
5.
같은 방향으로 트랜잭션을 진행시킨다.
트랜잭션을 짧게가져간다.
주기적인 모니터링
SET LOCK_TIMEOUT, SET옵션 사용
READ UNCOMMITTED 고립 수준을 적절하게 사용
(분산 트랜잭션과 DTC)
분산 데이터를 하나의 트랜잭션으로 처리하려면
BEGIN DISTRIBUTED TRAN
SERVER1에서의 작업
SERVER2에서의 작업
SERVER1에서의 작업
COMMIT TRAN
이 트랜잭션 동안 양쪽 서버의 해당 페이지가 모든
잠금이 되므로, 어느 한 쪽 서버의 해당 페이지가 꺼
져있다면 다른 쪽 서버도 롤 백 될때까지 부하가 심
하게 걸린다. 또한 다른 서버에서 변경하려는 페이지
가 잠겨 있을 때도 마찬가지이다. 따라서 양쪽 모두
수정하는 상황이 아니라, 어느 한쪽 서버에서만 수정
되고 다른 쪽 서버는 데이터를 받기만 하는 상황이라
면 복제를 사용하거나, 트랜잭션이 꼭 필요한 상황이
아니라면 그냥 원격 스토어드 프로시저 정도로 구현
하는 것이 보다 나은 방법이다.
제14장 저장프로시저 구현하기

트랜잭션 관리
(트랜잭션의 개념)
만약 문제가 생기면 전부 되거나 아예 안되도록 (All
or Nothing)하는 일의 최소 단위
(트랜잭션의 종류)
암시적 트랜잭션과 명시적 트랜잭션
제15 트리거 구현하기

트랜잭션 관리
(트랜잭션의 개념)
만약 문제가 생기면 전부 되거나 아예 안되도록 (All
or Nothing)하는 일의 최소 단위
(트랜잭션의 종류)
암시적 트랜잭션과 명시적 트랜잭션