뇌를 자극하는 SQL Server 2005

뇌를 자극하는 SQL Server 2005 10장. 트랜잭션과 잠금

트랜잭션 개념과 문법 • 트랜잭션 개념 • 하나의 논리적 작업단위로 수행되는 일련의 작업 • 전부 되거나, 전부 안 되거나의 의미가 포함되어 있음 • 특성 • 원자성(Atomicity) • 일관성(Consistency) • 격리성(Isolation) • 영속성(Durability) • 트랜잭션의 문법 • 구문 형식 BEGIN TRAN SQL 문… COMMIT TRAN (또는 COMMIT WORK 또는 ROLLBACK TRAN)

트랜잭션의 종류 (1) • 자동 커밋 트랜잭션 (Autocommit Transaction) • 각쿼리마다 자동적으로 BEGIN TRAN과 COMMIT TRAN이 붙여짐 • SQL Server가 디폴트로 사용함 • 사용 예 UPDATE 문 GO INSERT 문  BEGIN TRAN UPDATE 문 COMMIT TRAN GO BEGIN TRAN INSERT 문 COMMIT TRAN

트랜잭션의 종류(2) • 명시적 트랜잭션(Explicit Transaction) • 직접 BEGIN TRAN과 COMMIT TRAN을 써주는 것을 말함 • 대개는 BEGIN TRAN… END TRAN만 붙여주면 됨 • 구문 형식 BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] SQL 문... 또는 SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } …… COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] 또는 ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ]

트랜잭션의 종류(3) • 암시적 트랜잭션(Implicit Transaction) • 오라클 등과 호환을 위해서 사용될 수 있음 • BEGIN TRAN은 내부적으로 자동으로 붙여주지만, COMMIT TRAN은 직접 써줘야함 • 특별히 권장하지는 않음 • 암시적 트랜잭션을 위해서 다음과 같이 설정해 주면 됨. SET IMPLICIT_TRANSACTIONS ON • 실습 목표 • 트랜잭션의 기본적인 작동을 확인한다. • 트랜잭션을 사용하지 않았을 때의 문제점을 확인한다. <실습1> 트랜잭션을 사용하는 이유

실습 목표 • 자동커밋 트랜잭션, 명시적 트랜잭션, 암시적 트랜잭션을 실습한다. • SAVE TRAN의 사용법을 익힌다. • 사용된 SQL 예 BEGIN TRAN --- 1번트랜잭션 UPDATE #tranTest SET id = 111; SAVE TRAN [tranPoint1] BEGIN TRAN -- 2번트랜잭션 UPDATE #tranTest SET id = 222; SELECT * FROM #tranTest ROLLBACK TRAN [tranPoint1] -- 첫번째롤백 SELECT * FROM #tranTest ROLLBACK TRAN -- 두번째롤백 SELECT * FROM #tranTest <실습2> 트랜잭션의 활용

잠금(Lock)의 개념 • 개념 • 한 사용자가 데이터를 사용하고 있을 경우에, 다른 사용자가 그 데이터를 변경하지 못하도록 하는 것 • 데이터베이스의 일관성을 위해서 필요함 • 트랜잭션 격리 수준 (TransactionIsolation Level)의 종류 • READ UNCOMMITTED (커밋되지 않은 읽기) • READ COMMITTED (커밋된 읽기)  SQL Server 2005의 디폴트 값 • REPEATABLE READ (반복된 읽기) • SNAPSHOT (스냅숏) • SERIALIZABLE (직렬화 가능) • 여러사용자가 동시에 하나의 데이터에 접근해서 발생하는 문제 • Dirty Read (더티 리드, 커밋되지 않은 데이터를 읽기) • Unrepeatable Read (반복되지 않은 읽기) • Phantom Read (팬텀, 가상읽기)

트랜잭션 격리 수준 (1) • 더티 리드(Dirty Read, 커밋되지 않은 데이터를 읽기) • 더티 페이지(Dirty Page)는 메모리에는 변경이 되었지만 디스크에는 아직 변경되지 않은 데이터를 의미하며, 더티 리드는 이 더티 페이지를 읽는 것을 말함 • 문제점은 아직 커밋되지 않은 데이터를 읽어오기 때문에 더티 페이지를 읽은 후에, 더티 페이지의 데이터가 롤백 된다면 이미 읽어온 데이터는 잘못된 데이터가 된다는 점 • 데이터의 일관성은 떨어지지만, 동시성이 좋아서 커밋을 기다리지 않고 읽을 수 있는 장점이 있음 • READ UNCOMMITTED는 더티 리드를 허용해 줌 • 실습 목표 • 트랜잭션 격리수준을 변경하는 방법을 확인한다. • 더티 리드를 이해한다. <실습3> 더티 리드

트랜잭션 격리 수준 (2) • Unrepeatable Read (반복되지 않은 읽기) • 트랜잭션내에서 한번 읽은 데이터가 트랜잭션이 끝나기 전에 변경되었다면, 다시 읽었을 경우에 새로운 값이 읽어지는 것 • READ COMMITTED 에서 발생됨 (SQL Server 2005 디폴트 값) • 공유잠금(주로 SELECT문에 의해 발생) 중에도 데이터가 변경되는 문제가 있음. • 이를 해결하기 위해서는 REPEATABLE READ로 설정 • 실습 목표 • 더티 리드를 해결하는 방법을 알아 본다. • ‘반복되지 않은 읽기’ 문제를 확인한다. • ‘반복되지 않은 읽기’ 문제를 해결하는 방법을 익힌다. <실습4> 반복되지 않은 읽기

트랜잭션 격리 수준 (3) • Phantom Read (팬텀 읽기, 가상읽기) • REPETABLE READ 격리 수준에서는 트랜잭션이 진행중인데이터에 대해서 변경작업을 할 수 없지만, 새로운 데이터의 입력작업은 가능한 것을 말함. • 이를 해결하기 위해서는 격리수준을 SERIALIZABLE 또는 SNAPSHOT,으로 설정 • 실습 목표 • 팬텀 읽기의 현상을 확인해 본다. • 팬텀 읽기의 해결책을 확인해 본다. <실습5> 팬텀 읽기

트랜잭션 격리 수준 (4) • 격리 수준과 동시성 부작용의 관계

잠금이 걸리는 리소스 • 개념 • SQL Server는 잠금을 거는 단위가 다양함. • 작은 단위의 잠금은 동시성이 좋아지지만, 잠금의 수가 많아져서 오버헤드가 증가함. • 큰 단위의 잠금은 잠금의 수가 적어서 오버헤드는 감소하지만, 동시성은 나빠짐 • SQL Server에 잠금이 걸리는 리소스 • RID : 행 식별자는 단일 행을 잠그는 데 사용 • KEY : 인덱스 내의 행 잠금은 SERIALIZABLE 격리수준에서 사용 • PAGE : 8KB 크기의 데이터페이지 또는 인덱스페이지 • EXTENT : 8개의 페이지가 연속된 것 • HOBT : 클러스터형 인덱스가 없는 테이블에서 데이터 페이지나 인덱스를 보호하는 잠금 • TABLE : 데이터와 인덱스가 포함된 전체 테이블 • FILE : 데이터베이스 파일 • APPLICATION : 응용 프로그램이 지정한 리소스 • METADATA : 메타데이터 • ALLOCATION_UNIT : 할당 단위 • DATABASE : 전체 데이터베이스

잠금 모드 (종류) • 잠금의 종류 • 공유 잠금(S: Shared Lock) SELECT 문처럼 데이터를 읽는 작업에 사용한다. 공유잠금끼리는 서로 호환이 된다. 즉, 동시에 같은 데이터를 SELECT 할 수 있다는 의미이다. • 배타 잠금(X: Exclusive Lock) 데이터 수정 작업이 발생되는 INSERT/UPDATE/DELETE에서 사용된다. 여러 개의 업데이트 작업이 하나의 리소스에 동시에 수행되지 못하도록 방지한다. 배타 잠금은 다른 잠금과 호환되지 않는다. 즉, 어떤 행을 변경하고 있을 때, 동시에 같은 행을 변경할 수 없다는 의미이다. • 업데이트 잠금(U: Update Lock) 업데이트할 수 있는 리소스에 사용한다. 여러 사용자가 업데이트할 때 발생하는 교착상태를 방지한다. • 의도 잠금(Intent Lock) 잠금 계층 구조를 만드는 데 사용된다. 의도 잠금의 종류에는 의도 공유(IS), 의도 배타(IX), 의도 배타 공유(SIX)가 있다. • 스키마 잠금 (Schema Lock) 테이블의 스키마에 종속되는 작업이 실행될 때 사용된다. 스키마 잠금에는 스키마 수정(Sch-M)과 스키마 안정성(Sch-S) 잠금이 있다. • 대량 업데이트 잠금 (Bulk Update Lock) 데이터를 테이블로 대량 복사하는 경우와 TABLOCK 힌트가 지정된 경우 사용한다. • 키 범위 잠금 SERIALIZABLE 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호한다.

잠금의 호환성 • 개념 • 하나의 잠금이 일어 났을 때 다른 잠금이 일어날 수 있는지 여부

실습 목표 • 잠금의 정보를 확인하기 위한 시스템 뷰인 sys.dm_tran_locks을 확인한다. • 잠금을 강제로 지정할 수 있는 잠금 힌트의 사용법을 익힌다. • 잠금 정보 확인 예 <실습6> 잠금 힌트

블로킹 (Blocking) • 개념 • 어떤 개체에 트랜잭션이 발생되면 (= 잠금이 생성되면) 다른 트랜잭션이 그 개체에 접근할 수 없는 것 • 방지하기 위해서는 ‘LOCK_TIMEOUT’옵션을 설정 • 실습 목표 • 블로킹이 걸린 것을 확인하고 이를 해결하는 방법을 익힌다. <실습7> 블로킹 문제 해결

교착상태(Deadlock) • 개념 • 두 잠금이 서로 상대방의 잠금이 풀릴 때까지 계속 대기하는 상태. • SQL Server가 자동으로 검색해서 한쪽을 희생시키는 방법으로 해결함 • 되도록 교착상태가 일어나지 않도록 하는 것이 시스템의 성능에 바람직함. • 교착상태의 방지를 위한 권장방법 • 개체의 사용 순서를 교착상태가 발생되지 않게 설계한다. • 트랜잭션 격리 수준을 디폴트인 READ COMMITTED로 하고, 되도록 그 이상의 격리수준으로 변경하지 않는다. • 하나의 트랜잭션에 너무 많은 쿼리를 넣지 않도록 한다. • 실습 목표 • SQL Server Profiler를 이용해서 교착상태를 확인한다. <실습8> 교착 상태

분산 트랜잭션 (Distributed Transaction) • 개념 • 서로 다른 두 서버에 데이터를 처리할 때 하나의 트랜잭션으로 처리하는 것 • 개념적으로는 하나의 트랜잭션과 동일한 개념이지만, 실제로 서버가 분리되어 있다는 것만 차이가 있을 뿐 • 문법적으로는 ‘BEGIN TRANSACTION’ 대신에 ‘BEGIN DISTRIBUTED TRANSACTION’을 사용해야 하며, 커밋은 동일하게 ‘COMMIT TRANSACTION’을 사용 • 분산트랜잭션이 시작되기 전에는 꼭 ‘XACT_ABORT’옵션을ON으로 설정해야 함 • 실습 목표 • 두개의 인스턴스를 이용해서 분산 트랜잭션의 작동을 확인한다. <실습9> 분산 트랜잭션

뇌를 자극하는 SQL Server 2005