Transcript SQLER_Imagine_10트랜젝션과잠금.(187)

10. 트랜잭션과 잠금
Imagine 팀
1조
신준상
1. 복습
트랜잭션
- 전부 되거나, 전부 안되거나
- 원자성
일관성
격리성
영속성
(Atomicity)
(Consistency)
(Isolation)
(Durability)
- 자동 커밋 트랜잭션 (Autocommit Transaction)
- 명시적 트랜잭션 (Explicit Transaction)
- 암시적 트랜잭션 (Implicit Transaction)
Set Implicit_Transactions On 선언
Alter TAble, Fetch, Revoke, Create, ....
* 중첩 트랜잭션
RollBack Trans : 모든 트랜잭션을 롤백함.
2. 잠금(Lock)
일관성 (Consistency)
동시에 여러 사용자가 같은 데이터에 접근시 발생하는 상황
DB 엔진 격리 수준
- Read Uncommitted (커밋되지 않은 읽기)
- Read Committed (커밋된 읽기 : Default)
- Repeatable Read (반복일기)
- Snapshot (Only Sql Server)
- Serializable (직렬화 가능)
격리수준 설정
동시성
Set Transaction IsoLation Level
Read UncommitTed
Read CommitTed
Repeatable Read
Snapshot
일관성
동시성이 일어날때 문제점
- Dirty Read : 더티 리드, 커밋되지 않은 데이터를 읽기
- Unrepeatable Read : 반복되지 않은 읽기
- Phantom Read : 팬텀, 가상읽기
Dirty Read
격리수준 : Read UncommitTed 에서 허용
(10_DIRTYPAGE_1.sql, 10_DIRTYPAGE_2.sql)
Unrepeatable Read
트랜잭션 내에서 한 번 읽은 데이터가 트랜잭션이 끝나기 전에 변경되었다면, 다시 읽었을때 새로운
값이 읽히는 것을 말함 (10_UnrepeatRead_1.sql, 10_UnrepeatRead_2.sql)
Phantom Read
Repeatable Read 격리수준에서의 트랜젝션 발생시 변경작업은 안되지만, 새로운 데이타 입력은
가능한 것을 말함
격리 수준과 동시성 부작용의 관계
동시성 부작용
Unrepeatable Read
(반복되지 않은 읽기)
Dirty Read
(커밋되지 않은 데이터 읽기)
격리 수준
Phantom Read
(팬텀 읽기, 가상 읽기)
READ UNCOMMITTED
(커밋되지 않은 읽기)
O
O
O
READ COMMITTED
(커밋된 읽기)
X
O
O
REPEATABLE READ
(반복된 읽기)
X
X
O
SNAPSHOT
(스냅숏)
X
X
X(데이터의 입력은 됨)
SERIALIZABLE
(직렬화 가능)
X
X
X(데이터의 입력 자체가 불가능)
잠금이 걸리는 리소스
- RID : 행 식별자는 단일 행을 잠그는 데 사용
- KEY : 인덱스 내의 행 잠금은 SERIALIZABLE 격리 수준에서 사용
- PAGE : 8KB 크기의 데이터페이지 또는 인덱스 페이지
- EXTENT : 8개의 페이지가 연속된 것
- HOBT : 클러스터형 인덱스가 없는 테이블에서 데이터 페이지나 인덱스를 보호하는 잠금
- TABLE
- FILE
- APPLICATION : 응용 프로그램이 지정한 리소스
- METADATA
- ALLOCATION_UNIT : 할당 단위
- DATABASE
잠금모드
- 공유 잠금 (S : Shared Lock)
- 업데이트 잠금 (U : Update Lock)
- 배타 잠금 (X : Exclusive Lock)
- 의도 잠금 (Intent Lock)
- 스키마 잠금 (Schema Lock)
- 대량 업데이트 잠금 (Bulk Update Lock)
- 키 범위 잠금
잠금의 호환성
현재 잠금
IS
S
U
IX
SIX
X
내재된 공유 (IS)
O
O
O
O
O
X
공유 (IS)
O
O
O
X
X
X
업데이트 (U)
O
O
X
X
X
X
의도 배타 (IX)
O
X
X
O
X
X
의도 배타 공유 (SIX)
O
X
X
X
X
X
배타 (X)
X
X
X
X
X
X
시도하는 잠금
잠금의 정보
- sp_lock : SQL Server 2008 이후 버전부터 사용하지 않음
- sys.dm_tran_lock
잠금의 힌트
FASTFIRSTROW, HOLDLOCK, NOLOCK, NOWAIT, PAGELOCK, READCOMMITTED,
READCOMMITTEDLOCK, READPAST, READUNCOMMITTED, REPEATABLEREAD,
ROWLOCK, SERIALIZABLE, TABLOCK, UPDLOCK, XLOCK
(10_intro.sql, 10_LockHint_S_1.sql, 10_LockHint_S_2.sql, 10_LockHint_X_1.sql, 10_LockHint_X_2.sql)
블로킹
- 어떤 개체에 트랜잭션으로 인한 잠금이 발생하면 다른 트랜잭션이 그 개체에 접근할수 없는 상황.
- Lock_TimeOut 옵션 : 일정시간 블로킹이 풀리지 않을때 강제로 풀어주는 설정 (1/1000 초)
ex ) Set Lock_TimeOut 15000 : 15초 후에 블로킹이 해제된다
(10_block_1.sql, 10_block_2.sql)
교착상태 (DeadLock)
- 두 잠금이 서로 상대방의 잠금이 풀리도록 계속 대기하는 상태.
(10_deadlock_1.sql, 10_deadlock_2.sql)
3. 분산 트랜잭션 ( Distributed Transaction )
-서로다른 두 서버에 데이터를 처리할 때 하나의 트랜잭션으로 처리하는것
- Begin Transaction => Begin Distributed Transaction
- Xact_Abort On 설정
ex) Set XACT_ABORT ON
(10_DTT_1.sql, 10_DTT_2.sql)