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

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

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 3 / 18 트랜잭션의 종류 (1) 자동 커밋 트랜잭션 (Autocommit Transaction)  각 쿼리마다 자동적으로 BEGIN TRAN 과 COMMIT TRAN 이 붙여 짐  SQL Server 가 디폴트로 사용함 ▫ 사용 예 UPDATE 문 GO INSERT 문 BEGIN TRAN UPDATE 문 COMMIT TRAN GO BEGIN TRAN INSERT 문 COMMIT TRAN

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 4 / 18 트랜잭션의 종류 (2) 명시적 트랜잭션 (Explicit Transaction)  직접 BEGIN TRAN 과 COMMIT TRAN 을 써주는 것을 말함  대개는 BEGIN TRAN… END TRAN 만 붙여주면 됨 ▫ 구문 형식 BEGIN { TRAN | TRANSACTION } [ { transaction_name } [ WITH MARK [ 'description' ] ] ] SQL 문... 또는 SAVE { TRAN | TRANSACTION } { savepoint_name } …… COMMIT { TRAN | TRANSACTION } [ transaction_name ] ] 또는 ROLLBACK { TRAN | TRANSACTION } [ transaction_name | savepoint_name ]

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

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 6 / 18 트랜잭션의 활용 실습 목표 ▫ 자동커밋 트랜잭션, 명시적 트랜잭션, 암시적 트랜잭션을 실습한 다. ▫ SAVE TRAN 의 사용법을 익힌다. 사용된 SQL 예 BEGIN TRAN 번트랜잭션 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

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

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

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

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

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 11 / 18 트랜잭션 격리 수준 (4) 격리 수준과 동시성 부작용의 관계 동시성 부작용 격리 수준 Dirty Read ( 커밋되지 않은 데이터 읽기 ) Unrepeatable Read ( 반복되지 않은 읽기 ) Phantom Read ( 팬텀 읽기, 가상 읽기 ) READ UMCOMMITTED ( 커밋되지 않은 읽기 ) Yes READ COMMITTED ( 커밋된 읽기 ) NoYes REPEATABLE READ ( 반복된 읽기 ) No Yes SNAPSHOT ( 스냅숏 ) No ( 데이터의 입력은 됨 ) SERIALIZABLE ( 직렬화 가능 ) No ( 데이터의 입력 자체가 불가 능 )

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

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 13 / 18 잠금 모드 ( 종류 ) ▫ 잠금의 종류  공유 잠금 (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 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호한다.

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 14 / 18 잠금의 호환성 ▫ 개념  하나의 잠금이 일어 났을 때 다른 잠금이 일어날 수 있는지 여부 시도하는 잠금 기존 잠금 ISSUIXSIXX 의도 공유 (IS) YES NO 공유 (S) YES NO 업데이트 (U) YES NO 의도 배타 (IX) YESNO YESNO 의도 배타 공유 (SIX) YESNO 배타 (X) NO

뇌를 자극하는 SQL Server 장. 트랜잭션과 잠금 15 / 18 잠금 힌트 실습 목표 ▫ 잠금의 정보를 확인하기 위한 시스템 뷰인 sys.dm_tran_locks 을 확 인한다. ▫ 잠금을 강제로 지정할 수 있는 잠금 힌트의 사용법을 익힌다. 잠금 정보 확인 예

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

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

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