SQL Server Index 2011.09.07 SQLER Vision 1 기 강 동 운.

Presentation on theme: "SQL Server Index 2011.09.07 SQLER Vision 1 기 강 동 운."— Presentation transcript:

1 SQL Server Index 2011.09.07 SQLER Vision 1 기 강 동 운

2 목차 1. 트랜잭션 이야기 - 트랜잭션 이란 ?? - 명시적과 암시적 트랜잭션 (EXPLICIT AND IMPLICIT TRANSACTION) - SET XACT_ABORT ON - SET IMPLICIT_TRANSACTIONS ON 2. Lock 의 종류 - Shared(S lock) - Exclusive Lock(X Lock) - Update Lock(U Lock) 3. 트랜잭션 격리 수준 - READ COMMITTED - READ UNCOMMITTED - READ COMMITTED SNAPSHOT - SNAPSHOT - REPEATABLE READ - SERIALIZABLE

3 1. 트랜잭션 이야기 ( 트랜잭션이란 ??) A 통장에서 B 통장으로..100 만원을 송금하려고 한다.. 물론 송금하는 딱 한번에 100 만원이 송금되어야 하지. 두번 나 눠서 보낼 수 없다. A 통장에서 B 통장으로 돈을 보내는데, A 통장에서 돈이 보내졌 는지 C 통장에서 보내졌는지 모르면 안 된다. 100 만원 이체 중에 누군가 중간에 150 만원으로 수정하면 안 된 다. 100 만원이 이체가 완료되면, 그 후 시스템에 에러가 발생해도 100 만원이 데이터베이스에 저장이 되어야 한다. Atomicity( 원자성 ) Consistency( 일관 성 ) Isolation( 고립성 ) Durability( 영속성 )

4 1. 트랜잭션 이야기 ( 명시적 과 암시적 트랜잭션 ) 기본 테이블 생성 IF OBJECT_ID('tblx','U') IS NOT NULL DROP TABLE tblx GO CREATE TABLE tblx ( idxINTPRIMARY KEY,CharacterMoneyINT ) GO

5 1. 트랜잭션 이야기 ( 명시적 과 암시적 트랜잭션 ) Time Session 1( 명시적 트랜잭션 )Session 2( 암시적 트랜잭션 ) 1BEGIN TRAN 2INSERT INTO dbo.tblx VALUES(1,10) 3INSERT INTO dbo.tblx VALUES(2,20) 4COMMIT 5INSERT INTO dbo.tblx VALUES(3,20); INSERT INTO dbo.tblx VALUES(4,40); Time Session 2( 암시적 트랜잭션 내부 동작 방식 ) 5BEGIN TRAN INSERT INTO dbo.tblx VALUES(3,20) COMMIT BEGIN TRAN INSERT INTO dbo.tblx VALUES(4,40) COMMIT

6 1. 트랜잭션 이야기 ( 명시적 과 암시적 트랜잭션 ) Time Session 2( 암시적 트랜잭션 내부 동작 방식 ) 1BEGIN TRAN INSERT INTO dbo.tblx VALUES(3,20) COMMIT BEGIN TRAN INSERT INTO dbo.tblx VALUES(4,1000000000000000000) COMMIT --3 은 입력되게 된다 !

7 1. 트랜잭션 이야기 ( 명시적 과 암시적 트랜잭션 ) Time Session 1( 명시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx BEGIN TRAN 2INSERT INTO dbo.tblx VALUES(3,20) INSERT INTO dbo.tblx VALUES(4,10000000000000000) – 일부러 에러 3COMMIT 4SELECT * FROM dbo.tblx -- 결과는 ?? 명시적의 경우라면..?? 결과는 암시적과 같이 3 이 입력된다. 아니 롤백이 되야 되는데..?? 왜 안 될까 ?

8 1. 트랜잭션 이야기 ( 명시적 과 암시적 트랜잭션 ) Time Session 1( 명시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx BEGIN TRAN 2INSERT INTO dbo.tblx VALUES(3,20) INSERT INTO dbo.tblx VALUES(NULL,10) – 일부러 에러 3COMMIT 4SELECT * FROM dbo.tblx -- 결과는 ?? PRIMARY KEY 에 NULL 을 넣는다 면 ? 이 결과 역시 똑같이 3 이 입력되어 있 다. 이런 문제를 막기 위해서는 ?

9 1. 트랜잭션 이야기 (SET XACT_ABORT ON) Time Session 1( 명시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx SET XACT_ABORT ON BEGIN TRAN 2INSERT INTO dbo.tblx VALUES(3,20) INSERT INTO dbo.tblx VALUES(NULL,10) – 일부러 에러 3COMMIT 4SELECT * FROM dbo.tblx -- 결과는.. 3 이 입력되지 않는다. SET XACT_ABORT ON 이 옵션은 이럴 때 쓰는 것이다. ^^

10 1. 트랜잭션 이야기 (SET XACT_ABORT ON 단점 ) IF OBJECT_ID('usp_test2','P') IS NOT NULL DROP PROC usp_test2 GO IF OBJECT_ID('usp_test1','P') IS NOT NULL DROP PROC usp_test1 GO CREATE PROC dbo.usp_test2 AS BEGIN TRAN INSERT INTO dbo.tblx VALUES(10,1000000000000000000) --// 에러 발생 COMMIT GO CREATE PROC dbo.usp_test1 AS SET XACT_ABORT ON BEGIN TRAN EXEC dbo.usp_test2 --// 중첩으로 SP 실행 INSERT INTO dbo.tblx VALUES(10,20) COMMIT GO EXEC dbo.usp_test1 GO SELECT * FROM dbo.tblx -- 결과 없음 이 옵션은 에러 발생 시 즉각 중지 되기 때 문에 잘 써야 함 !

11 1. 트랜잭션 이야기 (XACT_ABORT 암시적 트랜잭션 ) Time Session 1( 암시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx SET XACT_ABORT ON 2INSERT INTO dbo.tblx VALUES(3,20) INSERT INTO dbo.tblx VALUES(NULL,10) – 일부러 에러 3SELECT * FROM dbo.tblx Time Session 1( 암시적 트랜잭션의 내부 작동방식 ) 1TRUNCATE TABLE dbo.tblx SET XACT_ABORT ON 2BEGIN INSERT INTO dbo.tblx VALUES(3,20) COMMIT BEGIN INSERT INTO dbo.tblx VALUES(NULL,10) – 일부러 에러 COMMIT 3SELECT * FROM dbo.tblx --//3 이 입력된다 !

12 1. 트랜잭션 이야기 (SET IMPLICIT_TRANSACTIONS ON) Time Session 1( 암시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx SET IMPLICIT_TRANSACTIONS ON 2INSERT INTO dbo.tblx VALUES(3,30) 3SELECT @@TRANCOUNT --//1 ( 현재 트랜잭션 COUNT) 4COMMIT 암시적 트랜잭션 사용시 쿼리 실행 후 무조건 COMMIT 을 하게 하려면 ? 1. 실수 방지 될 수 있다. 2. 오라클은 원래 이렇게 되 있다. 3. 좀 귀찮다.

13 1. 트랜잭션 이야기 (SET IMPLICIT_TRANSACTIONS ON) Time Session 1( 암시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx SET IMPLICIT_TRANSACTIONS ON 2SELECT * FROM dbo.tblx 3SELECT @@TRANCOUNT --//1 ( 현재 트랜잭션 COUNT) 4COMMIT SELECT 할 때도 마찬가지

14 1. 트랜잭션 이야기 (SET IMPLICIT_TRANSACTIONS ON) Time Session 1( 암시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx SET IMPLICIT_TRANSACTIONS ON 2INSERT INTO dbo.tblx VALUES(3,30) INSERT INTO dbo.tblx VALUES(4,40) 3SELECT @@TRANCOUNT --//1 ( 현재 트랜잭션 COUNT) 4COMMIT 두 개를 한번에 묶으면 ? 암시적인 쿼리를 한번에 묶어서 동 시에 실행해도 @@TRANCOUNT 는 한 개로 유지 된다.

15 1. 트랜잭션 이야기 (SET IMPLICIT_TRANSACTIONS ON) Time Session 1( 암시적 트랜잭션 ) 1TRUNCATE TABLE dbo.tblx SET IMPLICIT_TRANSACTIONS ON 2INSERT INTO dbo.tblx VALUES(3,30) 3INSERT INTO dbo.tblx VALUES(4,40) 4SELECT @@TRANCOUNT --//1 ( 현재 트랜잭션 COUNT) 5COMMIT 따로따로 실행하면 ? 한번에 묶든, 따로 실행하든 @@TRANCOUNT 는 한 개로 유지 된다. 왜냐 ? 그건 나도 모른다 ^^;

16 2. Lock( 잠금 ) 이란 무엇일까 ? 데이터도 읽거나 수정 삭제 시 잠궈야 합니다. 안 그러면 ???...

17 2. Lock( 잠금 ) 이란 무엇일까 ? 잠금의 종류는 많지만 … 1. Shared Lock( 공유 잠금 ) 2. Exclusive Lock( 베타 잠금 ) 3. Update Lock( 업데이트 잠금 ) 4. Intent Lock( 내부 잠금 ) 5. Schema Lock( 스키마 잠금 ) 6. BU( 대량 업데이트 잠금 )

18 2. Lock 의 종류 – MDF 와 LDF 이야기 0 TEST Database MDF FILE GROUP( 기본 적으로 PRIMARY) LDF P: Page, LSN: Log Sequence No 1 개의 Page 는 N 개의 Slot 으로 나뉩니다. …. Slot 4 Slot 3 Slot 2 Slot 1 Slot 0 1 Page

19 2. Lock 의 종류 – Shared(S Lock 공유 잠금 ) - 같은 공유 잠금 끼리 접근 가능. - 주로 SELECT 에 사용 됨.

20 …. Slot 4 Slot 3 Slot 2 2. Lock 의 종류 – Shared(S Lock) Slot 1 Slot 0 Page Session 1Session 2 SELECT SELECT 가능

21 2. Lock 의 종류 – Exclusive(X Lock 베타 잠금 ) - 베타 잠금이 걸릴 경우 다른 모든 잠 금이 접근하지 못하고 대기한다. ( 언제까지 ? 베타 잠금이 끝날 때 까지 ) - 다른 세션이 Shared Lock 을 걸고 있 으면 해제 할때까지 대기 ! - UPDATE 또는 DELETE 시 사용 됨. - Key Point! 단독 잠금이다 !

22 …. Slot 4 Slot 3 Slot 2 2. Lock 의 종류 – Exclusive(X Lock) Slot 1 Slot 0 Page Session 1Session 2 UPDATE SELECT 대기 UPDATE 대기

23 …. Slot 4 Slot 3 Slot 2 2. Lock 의 종류 – Exclusive(X Lock) Slot 1 Slot 0 Page Session 1Session 2 UPDATE 대기 SELECT 시도 SELECT 완료 UPDATE 완료

24 2. Lock 의 종류 – Update(U Lock 업데이트 잠금 ) - Shared Lock => Exclusive Lock 으로 전환 - 데이터를 수정 할 때, 수정할 데이터를 SELECT 한 뒤에 UPDATE 을 해야한다. 이는 Shared 한 뒤에 Excusive 으로 전환해야 한다.

25 2. Lock 의 종류 – Update(U Lock) Session 1Session 2 UPDATE 하기위한 SELECT UPDATE 하기위한 SELECT UPDATE 대기 중 UPDATE 대기 중 …. Slot 4 Slot 3 Slot 2 Slot 1 Slot 0 Page UPDATE 하기 위해서는 Shared Lock 을 Exclusive Lock(X Lock) 으로 전환해야 한다. 하지만, 한쪽이 풀어줄 때 까지 기다리게 되는데, Session1 과 2 모두 서로를 기다리는 바로 Dead Lock 상태가 되 버린다. 이 때문에 Update Lock 이 생기게 되었다 !

26 3. 트랜잭션 격리수준 종류 1. READ COMMITTED 2. READ UNCOMMITTED 3. READ COMMITTED SNAPSHOT 4. SNAPSHOT 5. REPEATABLE READ 6. SERIALIZABLE 2,3 번은 SQL Server 2005 부터 추가 ALTER DATABASE 디비명 SET READ_COMMITTED_SNAPSHOT ON ALTER DATABASE 디비명 SET ALLOW_SNAPSHOT_ISOLATION ON 다른 것 들은 … 세션 단위로 선언 가능 ! SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

27 3. 트랜잭션 격리수준 종류 기본 데이터 IF OBJECT_ID('tblx','U') IS NOT NULL DROP TABLE tblx GO CREATE TABLE tblx ( idxINTPRIMARY KEY,CharacterMoneyINT ) GO INSERT INTO tblx VALUES(1,10),(2,20),(3,30),(4,40),(5,50),(6,60); GO

28 3. 트랜잭션 격리수준 종류 – ① READ COMMITTED TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL READ COMMITTED 1BEGIN TRAN 2UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 3SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 -- 대기 4COMMIT 5 --100 으로 결과 반환 Session 1 의 Exclusive Lock 인 UPDATE 되는 데이터 (COMMIT 되지 않음 ) 를 Session 2 에서 Shared Lock 을 걸려고 하지만, 단독 잠금이기 때문에 Session 2 에 서 해당 행을 읽을 수 없다. 따라서 Session 1 에서 COMMIT 이 되어야 결과 값이 나 온다.

29 3. 트랜잭션 격리수준 종류 – ① READ COMMITTED TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL READ COMMITTED 1BEGIN TRAN 2UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 3SELECT CharacterMoney FROM dbo.tblx WHERE idx = 2 --20 반환 4SELECT CharacterMoney FROM dbo.tblx -- 대기 5COMMIT Session 1 의 UPDATE 되는 데이터는 IDX=1 번 값만 해당되기 때문에, Session 2 에 서는 IDX=2 의 값은 읽을 수 있다. 이게 바로 SQL Server ROW Lock 의 증거 만약 SELECT * FROM dbo.tblx 로 한다면 ??IDX=1 도 포함되있기 때문에 대기 !

30 3. 트랜잭션 격리수준 종류 – ① READ COMMITTED TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL READ COMMITTED 1BEGIN TRAN SELECT CharacterMoney FROM dbo.tblx WHERE IDX=1 -- 결과 10 2 --Time1 에서 SELECT 했던 Shared Lock 이 해제 됨. BEGIN TRAN UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 -- 성공 3SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --- 대기 4COMMIT 5 -- 결과 100 READ COMMITTED 는 Time 1 에서 SELECT 가 끝남과 동시에 Shared Lock 을 해 제 따라서 …. 2 번 세션에서 IDX=1 의 UPDATE 가 가능하다. ( 킹 ! 왕 ! 짱 ! 중요 )

31 3. 트랜잭션 격리수준 종류 – ② READ UNCOMMITTED TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 1BEGIN TRAN 2UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 3SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --100(Dirty Read) 4ROLLBACK 5SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --10 6 READ UNCOMMITTED 는 SELECT 하는 순간에도 Shared Lock 을 걸지 않는다. 따 라서 Session 1 에서 커밋되지 않은 데이터라도 Session 2 에서 읽을 수 있다. 커밋되지 않은 데이터를 읽었다 해서 Dirty Read 라고 한다 !

32 3. 트랜잭션 격리수준 종류 – ③ READ COMMITTED SNAPSHOT TimeSession 1Session 2 1BEGIN TRAN 2UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 3BEGIN TRAN 4SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --10 5COMMIT 6SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --100 7COMMIT 가져오고자 하는 행이 Exclusive Lock 이 걸린 경우 최종 적으로 그 행이 committed 된 데이터를 tempdb 를 참조해서 가져온다.

33 3. 트랜잭션 격리수준 종류 – ④ SNAPSHOT TimeSession 1Session 2 1BEGIN TRAN 2UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT 3BEGIN TRAN 4SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --10 5COMMIT 6SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --10 7COMMIT 8SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --100 가져오고자 하는 행이 Exclusive Lock 이 걸린 경우 최종 적으로 그 행이 committed 된 데이터를 tempdb 를 참조해서 가져온다. 하지만 해당 세션 내에서 트랜잭션이 COMMIT 되기 전에는 처음 가지고 왔던 값을 계속 참조하게 된다.( 일관성 유지 )

34 3. 트랜잭션 격리수준 종류 – ⑤ REPEATABLE READ TimeSession 1Session 2 1BEGIN TRAN 2SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --10 3BEGIN TRAN 4UPDATE dbo.tblx SET CharacterMoney = 100 WHERE idx = 1 -- 대기 5COMMIT 6 완료 ! 7SELECT CharacterMoney FROM dbo.tblx WHERE idx = 1 --100 READ COMMITTED 에서는 BEGIN TRAN 내에서도 SELECT 후 에는 Shared Lock 을 바로 해제 하지만 REPEATABLE READ 에서는 계속 유지한다.

35 3. 트랜잭션 격리수준 종류 기본 데이터 IF OBJECT_ID('tblx','U') IS NOT NULL DROP TABLE tblx GO CREATE TABLE tblx ( idxINTPRIMARY KEY,CharacterMoneyINT ) GO INSERT INTO tblx VALUES(1,10),(3,30),(5,50) GO 2 하고 4 가 없음에 주목 !!

36 3. 트랜잭션 격리수준 종류 – ⑤ REPEATABLE READ TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 1BEGIN TRAN 2SELECT CharacterMoney FROM dbo.tblx WHERE idx BETWEEN 2 AND 4 --30 3BEGIN TRAN 4INSERT INTO tblx VALUES(2,20),(4,40) 5COMMIT 6SELECT IDX FROM dbo.tblx WHERE idx BETWEEN 2 AND 4 --20,30, 40 (Phantom 팬텀 ) COMMIT Time 4 의 Session 2 에서 Shared Lock 이 걸리지 않은 다른 ROW 는 입력이 가능 ! 결국 REAPEATABLE READ 는 처음 SELECT 할 시에 결과 값에 대해서만 Shared Lock 을 걸게 된다. 그렇다면.. 팬텀을 없애는 방법은 ??

37 SERIALIZABLE 안에서 SELECT 는 COMMIT 되기 전까지 항상 같은 결과가 나와야 한다. 따라서, 팬텀의 대상인 2 와 4 의 입력을 허락하지 않는다. 3. 트랜잭션 격리수준 종류 – ⑥ SERIALIZABLE(INSERT) TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 1BEGIN TRAN 2SELECT CharacterMoney FROM dbo.tblx WHERE idx BETWEEN 2 AND 4 --30 3BEGIN TRAN 4INSERT INTO tblx VALUES(2,20),(4,40) -- 대기 5 중지 후 ROLLBACK INSERT INTO dbo.tblx VALUES(0,0) -- 입력 됨 6COMMIT 그러므로 SELECT 대상 밖인 IDX 5 의 값은 INSERT 가 가능하다.

38 3. 트랜잭션 격리수준 종류 기본 데이터 IF OBJECT_ID('tblx','U') IS NOT NULL DROP TABLE tblx GO CREATE TABLE tblx ( idxINTPRIMARY KEY,CharacterMoneyINT ) GO INSERT INTO tblx VALUES(1,10),(2,20),(3,30),(4,40),(5,50),(6,60); GO

39 3. 트랜잭션 격리수준 종류 – ⑥ SERIALIZABLE(UPDATE) TimeSession 1Session 2 0SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 1BEGIN TRAN 2SELECT CharacterMoney FROM dbo.tblx WHERE idx BETWEEN 2 AND 4 --20,30,40 3BEGIN TRAN 4UPDATE dbo.tblx SET IDX = 100 WHERE IDX = 1 5COMMIT 6 SELECT CharacterMoney FROM dbo.tblx WHERE IDX = 100 --10 SERIALIZABLE 은 범위 잠금을 해서 1~5 사이에 데이터가 들어갈 수는 없다. 하지만, Time 4 의 Session 2 에서 IDX 의 수정은 가능하다 ^^

40 참고 사이트 1. 열이님의 동시성 제어 (Concurrency Control) 와 행버전 (Row Versioning) http://www.sqler.com/431381 2. 달토끼님의 잠금 (Lock) 매커니즘 http://kuaaan.tistory.com/97 3. 제가 쓴 SQL 2011 강좌 트랜잭션의 격리수준 4 가지 http://www.sqler.com/394743 4. MSDN(Transaction) http://msdn.microsoft.com/ko-kr/library/ms190612.aspx 기타 몇 개가 더 있는데.. Msdn 접속이 안되네요 ㅠㅠ..

41 감사 합니다