슬라이드 1

Size: px

Start display at page:

Download "슬라이드 1"

연지 승
5 years ago
Views:

1 12. 트랜잭션과잠금 여러개의데이터수정이하나의단위로수행되어야하는경우가많다. 그래서이중하나라도문제가발생한다면모든처리가다취소되어야한다. 모두다처리가되던지아니면아예하나도처리가안되든지... 이것이트랜잭션이다.

2 2 사랑차만드는법 사랑차준비물 1. 성냄과불평은뿌리를잘라내고잘게다진다. 2. 교만과자존심은속을빼낸후깨끗이씻어말린다. 3. 짜증은껍질을벗기고반으로토막을낸후에넓은맘으로절여둔다. 사랑차끓이는방법 1. 주전자에실망과미움을한컵씩붓고, 씨를잘빼낸다음불만을넣고푹끓인다. 2. 미리준비한재료를인내와기도를첨가하여재료가다녹고쓴맛이없어지기까지충분히달인다. 3. 기쁨과감사로잘젓고, 미소를몇개띄운후깨끗한믿음으로잔에부어서따뜻하게마신다.

3 트랜잭션과잠금 (Transactions and Locks) 트랜잭션 (Transactions) 트랜잭션은여러데이터수정이하나의단위로처리되게함 트랜잭션은잠금을사용하여완료되지않은트랜잭션의데이터를다른사용자가변경또는읽을수없게함 다중사용자시스템을위한온라인트랜잭션처리 (OLTP) 에는잠금이필요함 다중트랜잭션을관리할수있도록 SQL Server는트랜잭션처리를지원함 잠금 (Locks) 잠금은업데이트충돌을방지함 특정사용자는다른사용자가변경중인데이터를읽거나수정할수없음 3

4 4 트랜잭션시작 트랜잭션제어 (Controlling Transactions) 명시적트랜잭션 (Explicit Transactions) BEGIN TRANSACTION 으로트랜잭션시작 자동커밋트랜잭션 (Auto Commit Transactions) MS SQL 서버의기본모드 각 Transact-SQL 문은완료시자동으로커밋됨 암시적트랜잭션 (Implicit Transactions) SET IMPLICIT_TRANSACTION ON 으로설정 트랜잭션완료 COMMIT 모든트랜잭션에의한수정이데이터베이스에영구적으로적용됨 ROLLBACK 데이터를트랜잭션시작전상태로되돌려놓음

5 5 암시적트랜잭션옵션 (Implicit Transactions Option) 암시적트랜잭션모드가설정되면특정문수행시자동으로트랜잭션이시작됨 중첩된트랜잭션은허용되지않음 COMMIT 또는 ROLLBACK TRANSACTION 을통해트랜잭션이명시적으로커밋되거나롤백되어야함 이옵션은기본적으로 OFF 로설정되어있음 SET IMPLICIT_TRANSACTIONS ON

6 DEMO 트랜잭션의사용예를살펴봄

7 7 중첩트랜잭션 (1) (Nested Transactions) BEGIN TRAN SELECT -- 1 BEGIN TRAN -- 2 BEGIN TRAN -- 3 COMMIT -- 2 COMMIT -- 1 COMMIT -- 0

8 8 트랜잭션제어 (2) (Nested Transactions) BEGIN TRAN SELECT -- 1 BEGIN TRAN -- 2 BEGIN TRAN -- 3 ROLLBACK -- 0 COMMIT -- 0 COMMIT -- 0

9 9 포인트설정 (Save Point) BEGIN TRAN SELECT -- 1 BEGIN TRAN SAVE TRAN Tran_A -- 2 BEGIN TRAN -- 3 ROLLBACK Tran_A -- 3 COMMIT -- 2 COMMIT -- 1

10 DEMO 트랜잭션의중첩과 Save Point 에대해살펴봄

11 11 트랜잭션지원을위한속성 (ACID) 속성 원자성 (Atomicity) 일관성 (Consistency) 고립성 (Isolation) solation) 영속성 (Durability) 설명 트랜잭션의모든연산들이데이터베이스에모두적용되던지또는모두적용되지않아야한다. 데이터에대한무결성이유지되기위해서는데이터베이스내의모든규칙이트랜잭션에적용되어야한다. 즉규칙에위반되는트랜잭션은무결성을위해취소되어야만데이터베이스에일관성을유지할수있게된다. 현재의트랜잭션에의해변경되고있는데이터영역은다른사용자의트랜잭션영역으로부터분리되어야한다. 트랜잭션이정상적으로종료된후에는시스템오류가발생하더라도데이터를안정적으로저장시켜야한다.

12 12 트랜잭션처리 (How the Transaction Log Works) 1 사용자가데이터베이스변경작업을수행함 Buffer Cache 3 변경된내용을Disk에있는데이터베이스의트랜잭션로그파일에기록함 Disk 2 관련된데이터페이지가 Cache 에있는지확인하여 Cache 에있다면그내용을수정하고, Cache 에없다면 Disk 에서 Cache 로가져온후수정 4 Disk Checkpoint 라불리는프로세스가변경된페이지 (Dirty Pages) 를 Cache 로부터 Disk 에있는데이터베이스의데이터부분으로밀어냄

13 13 트랜잭션로그로부터정보를얻어처리함 SQL Server 시작시처리프로세스 롤포워드와롤백 (Roll Forward and Roll Back) 모든데이터베이스에대해서트랜잭션로그에기록된가장최근의 Checkpoint 시점을확인 이 Checkpoint 이후에 Commit된액션에대해롤포워드됨 (Redo Phase) 트랜잭션로그에 COMMIT TRAN 기록이없는모든오픈된트랜잭션은롤백됨 (Undo Phase) 롤포워드와롤백이모두수행된후데이터베이스접근이가능해짐 단 SQL Server 2005는 Redo 이후접근가능

14 14 트랜잭션복구및검사점 (Transaction Recovery Works) Transaction Recovery 1 2 Action Required None Roll forward 3 Roll back 4 Roll forward 5 (Roll back) 체크포인트 (Checkpoint) 시스템오류

15 15 트랜잭션을짧게유지 트랜잭션사용을위한고려사항 (Considerations for Using Transactions) WHILE 문과같은특정 Transact-SQL 문사용시주의 트랜잭션중사용자입력을요구하지않음 INSERT, UPDATE, DELETE 문이최소한의행수에영향을주도록함 데이터검색하는동안은가능하면트랜잭션을열지않음 트랜잭션중가능한최소양의데이터액세스 중첩트랜잭션 (Nesting Transactions) 트랜잭션은중첩될수는있지만권하지는않음

16 16 공유잠금 (Shared Lock) : S 데이터를읽을때자동으로공유잠금을얻음 여러프로세스가동일한데이터에대한공유잠금유지가능 공유잠금이있는데이터에서단독잠금을얻을수없음 일반적으로데이터를읽자마자공유잠금해제됨 잠금모드 (1) (Lock Mode) 쿼리힌트, 트랜잭션격리수준을이용해공유잠금해제를제어할수있음 단독잠금 (Exclusive Lock) : X 데이터가삽입, 업데이트, 삭제에의해변경될때단독잠금을얻음 한번에한프로세스만이단독잠금을유지할수있음 트랜잭션이커밋되거나롤백될때까지다른프로세스가변경된데이터를사용할수없음 힌트를사용함으로써다른프로세스들이단독잠금이유지된데이터를읽을수있음

17 17 업데이트잠금 (Update Lock) : U 잠금모드 (2) (Lock Mode) 변경될리소스를찾기위해테이블을검색할필요가있을때업데이트잠금이얻어짐 데이터를읽은후로데이터가변경되지않았다는것을확신하면서나중에데이터를변경할수있게함 나중에단독잠금을요청할수있게하는관문역할 한프로세스만이업데이트잠금을유지할수있음 특정프로세스가업데이트잠금을유지하는한다른프로세스가이자원에대해업데이트잠금이나단독잠금을얻을수없음 내재된잠금 (Intent Lock) : IS, IU, IX 리소스의구성요소가이미잠겨있음을알려주는잠금 내재된공유잠금 내재된단독잠금 내재된업데이트잠금

18 18 잠금모드 (3) 스키마안정성잠금 (Schema Stability Lock) : Sch-S (Lock Mode) 쿼리가컴파일될때다른프로세스들이스키마수정잠금을얻지못하도록함 스키마수정잠금 (Schema Modification Lock) : Sch-M 개체에대해 DDL 문이수행될때얻어짐 대량업데이트잠금 (Bulk Update Lock) : BU 다음중한가지가설정된상태에서대량복사작업이수행되는경우얻어짐 TABLOCK 힌트설정 table lock on bulk load 테이블옵션이설정된경우 키범위잠금 (Key-range Lock) : Range... Serializable 격리수준에서데이터범위를잠그려고할때얻어짐

19 DEMO 잠금모드의실제발생사례를살펴보고잠금정보가표시하는내용의의미를파악함

20 20 잠금호환성 (Lock Compatibility) 요청된모드 (Requested Mode) 기존에허용된모드 (Granted Mode) IS S U IX X Intent shared (IS) O O O O X Shared (S) O O O X X Update (U) O O X X X Intent exclusive (IX) O X X O X Exclusive (X) O X X X X

21 21 잠금으로방지되는동시성문제 (What Concurrency Problems Are Prevented by Locks?) 손실된업데이트 (Lost Update) 특정트랜잭션이다른트랜잭션의변경내용을덮어쓸경우업데이트가손실됨 커밋되지않은데이터읽기 (Dirty Read) 특정트랜잭션이다른트랜잭션의커밋되지않은데이터를읽은경우발생 일관성없는분석 (Inconsistent Analysis) 특정트랜잭션이같은행을두번이상읽을경우읽기사이에다른트랜잭션이해당행을수정하면일관성없는분석발생 팬텀읽기 (Phantom Reads) 트랜잭션이서로격리되지않을경우발생

22 22 트랜잭션격리수준 (Transaction Isolation Level) READ UNCOMMITTED NOLOCK을설정하는것과같아데이터를수정하는다른세션과충돌없음 Dirty Read를포함해앞페이지에서나열한동시성문제가발생함 다른격리수준에비해일관성을가장떨어지지만동시성은가장뛰어남 READ COMMITTED ( 기본값 ) 데이터를읽을때는공유잠금이유지되도록해서커밋되지않은데이터읽기가이루어지지않도록지정함 즉 Dirty Read 가발생하지않음 트랜잭션이끝나기전에데이터가변경되어반복하지않는읽기또는팬텀데이터가만들어질수있음 REPEATABLE READ 공유잠금을트랜잭션이종료할때까지유지하여다른사용자가데이터를업데이트할수없도록함 다른사용자가데이터집합에새허위행을삽입해현재트랜잭션의이후읽기에포함될수있음, 즉팬텀데이터존재

23 23 SERIALIZABLE 데이터집합에범위잠금을배치함 트랜잭션격리수준 (Transaction Isolation Level) 트랜잭션이완료될때까지다른사용자가행을업데이트하거나데이터집합에삽입할수없도록함 팬텀데이터가발생하지않음 SQL Server 2005 에추가된격리수준 Snapshot Read Committed Snapshot

24 DEMO 트랜잭션격리수준의네가지예를살펴봄으로써각각의특성을파악함

25 25 격리수준요약 (Summary of Isolation Levels) 격리수준 Dirty Reads Lost Updates Nonrepeatable Reads Phantoms Read Uncommitted Read Committed Repeatable Read O X X O O O O O O X X O Serializable X X X X

26 26 교착상태의희생자 (Victim) 교착상태 (Deadlocks) 메시지 1205, 수준 13, 상태 51, 줄 2 트랜잭션 ( 프로세스 ID 54) 이잠금리소스에서다른로세스와의교착상태가발생하여실행이중지되었습니다. 트랜잭션을다시실행하십시오. SET DEADLOCK_PRIOROTY LOW NORMAL (default) HIGH 인덱스가없는경우교착상태발생가능 하나의테이블에서도교착상태발생가능

27 DEMO 교착상태가발생할수있는경우에대해살펴봄

28 Time:PT1 X lock on CL idx row where keycol = 2 Granted Set col1 = <newval> UPDATE dbo.t1 SET col1 = <newval> WHERE keycol = 2 하나의테이블에서교착상태 (Deadlock with a Single Table) SELECT col2 FROM dbo.t1 WHERE col1 = 102 Connection 1 Connection 2 Time:PT2 S lock on NC idx row where col1 = 2 Granted CL Index on keycol NC Index on col1 keycol=2, col1=102,col2= B Time:PT3 Need to update NC idx row where col1 = 102 Request X lock Waiting Deadlock X Time:PT4 Need col2 from CL idx row Request S lock Waiting col1=102, CL Key=2 28

29 29 잠금정보표시 (View Locking Information) EM 의현재동작창 (Current Activity Window) sp_lock 시스템저장프로시저사용 SQL 프로필러 (SQL Profiler) 사용 윈도우의시스템모니터 (System Monitor) 사용 syslockinfo 와같은시스템테이블이용

30 정리

Microsoft PowerPoint - ch10_회복과 병행 제어.pptx

Microsoft PowerPoint - ch10_회복과 병행 제어.pptx 13-01 트랜잭션 장애와회복 병행제어 병행수행과병행제어 병행수행 (concurrency) 여러사용자가데이터베이스를동시공유할수있도록여러개의트랜잭션을동시에수행하는것을의미 여러트랜잭션들이차례로번갈아수행되는인터리빙 (interleaving) 방식으로진행됨 병행제어 (concurrency control) 또는동시성제어 병행수행시같은데이터에접근하여연산을실행해도문제가발생하지않고정확한수행결과를얻을수있도록트랜잭션의수행을제어하는것을의미