트랜잭션과 잠금 트랜잭션 처리 메커니즘을 자세히 이해한다. 트랜잭션의 종류를 파악한다.

Presentation on theme: "트랜잭션과 잠금 트랜잭션 처리 메커니즘을 자세히 이해한다. 트랜잭션의 종류를 파악한다."— Presentation transcript:

1 트랜잭션과 잠금 트랜잭션 처리 메커니즘을 자세히 이해한다. 트랜잭션의 종류를 파악한다.
명시적 트랜잭션의 조작 방법과 중첩을 이해한다. 자동 커밋 트랜잭션과 암시적 트랜잭션을 이해한다. 잠금의 개념, 종류 및 호환성을 이해한다. 실습을 통해 교착 상태의 개념을 파악한다. 교착 상태의 대비책을 강구한다.

2 1. 트랜잭션 처리 메커니즘 2. 트랜잭션 3. 잠금과 교착 상태
트랜잭션과 잠금 1. 트랜잭션 처리 메커니즘 2. 트랜잭션 3. 잠금과 교착 상태

3 1. 트랜잭션 처리 메커니즘 개요 데이터베이스를 변경하는 일련의 명령어들 간의 일관성 유지와
다른 세션 간의 충돌 방지를 위해 트랜잭션이라는 개념이 필요하다. 이는 데이터베이스에서 가장 중요한 메커니즘 중 하나다. 데이터베이스를 공부하는 사람이라면 반드시 숙지해야 할 트랜잭션 처리 메커니즘을 알아볼 것이다.

4 1. 트랜잭션 처리 메커니즘 트랜잭션의 개념 트랜잭션(transaction): 한 묶음으로 처리되어야 하는 데이터베이스
변경 명령어들의 집합 한 은행에서 다른 은행으로 계좌 이체를 하는 예를 들어 보자. [그림 10-1]에서 bank1 계좌로부터 백만 원이 인출되고 bank2 계좌에 백만 원이 입금되어야 계좌 이체가 완료된다(편의상, bank1과 bank2는 같은 데이터베이스 내의 테이블이라고 가정함). bank1 계좌로부터 백만 원이 인출되었는데, bank2 계좌에 백만 원이 입금되기 직전에 정전 등의 사고로 입금이 되지 않았다고 하자. 이때 은행이 고객의 예금을 횡령하게 되는 심각한 결과가 발생하게 된다.

5 1. 트랜잭션 처리 메커니즘 트랜잭션의 개념

6 1. 트랜잭션 처리 메커니즘 트랜잭션의 개념 이를 방지하려면 인출과 입금 명령문을 트랜잭션이라는 보자기로 묶어야 한다.
이 보자기 안에서는‘전부 또는 전무’(All or Nothing)라는 규칙이 적용된다. 즉 성공하려면 모두 성공하고, 그렇지 않으면 모두 실패해야 한다는 규칙이다. 여러 명령어들을 트랜잭션으로 묶으려면 BEGIN TRAN(SACTION) 문으로 시작하고, COMMIT TRAN(SACTION) 문으로 마무리한다 (이를 명시적 트랜잭션이라고 함).

7 1. 트랜잭션 처리 메커니즘 트랜잭션의 개념 계좌 이체 명령어들을 트랜잭션으로 묶어보자. 예제 1 1 BEGIN TRAN 2
3 UPDATE bank1 4 SET balance = balance 5 WHERE ... 6 7 UPDATE bank2 8 SET balance = balance 9 WHERE ... 10 11 COMMIT TRAN

8 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘 성공적인 시나리오 상에서의 처리 메커니즘 데이터베이스 변경 요청 접수
다양한 응용 프로그램에 의해 데이터베이스 변경 요청이 SQL 서버로 보내 져 접수된다. 이 예에서는 [예제 10-1]과 같은 트랜잭션을 구성하는 명령문 들이 쿼리 편집기를 통해 SQL 서버로 접수된다. 데이터 캐시 로드 및 변경 변경 대상이 되는 하나 또는 그 이상의 데이터 페이지가 데이터 파일로부터 메모리 상의 데이터 캐시(cache)라는 곳으로 로드된다. 이미 데이터 캐시 에 있는 페이지는 다시 로드되지 않는다.

9 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘 UPDATE bank1
그리고 접수된 명령문을 이 데이터 캐시의 페이지에 대해 실행하여, 데이터 캐시의 해당 페이지 내용을 바꾼다. - 이 책의 예에서는 다음과 같은 첫 번째 UPDATE 문이다. UPDATE bank1 SET balance = balance – WHERE…

10 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘

11 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘 로그 파일에 기록
먼저 쓰기 로그(write-ahead log): 데이터 캐시의 페이지 내용이 성공적 으로 변경되었을 경우, 데이터 파일보다 트랜잭션 로그 파일에 먼저 기록됨

12 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘
[그림 10-3]에서 TR#n은 트랜잭션 식별자고, LSN#n은 로그 일련번호다. 방금 첫 번째 UPDATE 문이 데이터 캐시 페이지에 성공적으로 적용되었으 므로, 즉시 로그 파일에 기록된다(참고로, BEGIN TRAN 문 역시 로그 파일에 기록된다). 이어서 두 번째 UPDATE 문이 첫 번째 UPDATE 문의 경우와 거의 비슷하게 데이터 캐시 페이지에 적용되고, 마찬가지로 로그 파일에 기록된다.

13 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘
마지막으로 COMMIT TRAN 문이 로그 파일에 기록되는데, [그림 10-4]에 여기까지의 상황을 나타냈다.

14 1. 트랜잭션 처리 메커니즘 트랜잭션 처리 메커니즘 데이터 파일을 수정
[그림 10-4]에서 검사점(checkpoint)을 확인할 수 있다. 여기까지의 트랜잭션 처리를 완료하고, 데이터 파일까지 수정했다는 확인 도장이라 생각하면 된다. 자동 검사점 프로세스(automatic checkpoint process)에 의해 새로운 검사점이 설정된다. [그림 10-4]와 같은 경우에는 완료된 트랜잭션 기록인 TR#2 LSN#14 직후에 검사점이 추가로 설정된다. 이와 동시에, 이 트랜잭션(TR#2)과 관련된 모든 데이터 캐시 페이지들이 데이터 파일(.mdf)을 덮어쓴다. 이를 페이지 플러시라고 한다.

15 1. 트랜잭션 처리 메커니즘 사례 연구(1): 롤백 지금까지 정상적인 시나리오에 의한 트랜잭션의 처리 과정을 보았다.
이제, 비정상적으로 중단된 트랜잭션이 어떻게 롤백(rollback) 되는지를 순서대로 살펴보자. [그림 10-5]에서 첫 번째 UPDATE 문까지 로그 파일에 기록된 직후에 정전이 되었다고 가정하자.

16 1. 트랜잭션 처리 메커니즘 사례 연구(1): 롤백

17 1. 트랜잭션 처리 메커니즘 사례 연구(1): 롤백 정전 시점의 상황은 다음과 같다.
데이터 파일(.mdf) : 트랜잭션 시작 시점과 같으며, 전혀 변경되지 않았다. 로그파일(.ldf) : 첫 번째 UPDATE 문까지만 기록되어 있다. 데이터 캐시 : 로드되고 변경되었던 bank1 테이블의 페이지가 손실되었다. 정전이 복구되어 컴퓨터가 부팅되고 SQL 서버 서비스가 시작되면서 자동 복구 프로세스(automatic recovery process)가 실행된다. 로그 파일의 마지막 검사점 이후의 커밋(COMMIT)되지 않은 트랜잭션의 로그 항목들은 모두 롤백시킨다. 즉 BEGIN TRAN 문과 UPDATE bank1... 문이 롤백되어 폐기되는것이다. 결과적으로, 트랜잭션은 전부 또는 전무 규칙에서 전무가 되는 것이다.

18 1. 트랜잭션 처리 메커니즘 사례 연구(2): 롤 포워드 다음은 COMMIT TRAN 명령문까지 로그 파일에 기록된 트랜잭션이
어떻게 롤 포워드(roll forward)되는지를 순서대로 살펴보자. [그림 10-6]에서 TR#2 LSN#14(COMMIT TRAN)까지 로그 파일에 기록된 직후에 정전이 되었다고 가정하자.

19 1. 트랜잭션 처리 메커니즘 사례 연구(2): 롤 포워드

20 1. 트랜잭션 처리 메커니즘 사례 연구(2): 롤 포워드 정전 시점의 상황은 다음과 같다.
데이터 파일(.mdf) : 트랜잭션 시작 시점과 같으며, 전혀 변경되지 않았다. 로그파일(.ldf) : 트랜잭션의 처음부터 끝까지 완전히 기록되어 있다. 데이터 캐시 : 로드되고 변경되었던 bank1과 bank2 테이블의 페이지들이 모두 손실되었다. 정전이 복구되어 컴퓨터가 부팅되고 SQL 서버 서비스가 시작되면서 자동 복구 프로세스가 실행된다.

21 1. 트랜잭션 처리 메커니즘 사례 연구(2): 롤 포워드
로그 파일의 마지막 검사점(TR#1 LSN#10) 이후의 트랜잭션(TR#2) 의 로그 기록들을 하나씩 재적용한다. 처음에 외부로부터 트랜잭션의 명령문들을 하나씩 적용할 때와 같은 방식이라고 보면 된다. 그 결과, 다시 로그 기록이 추가된다(이를 롤 포워드라고 한다). 최종적으로 자동 검사점 프로세스에 의해 새로운 검사점이 설정되고, 이 트랜잭션과 연관된 모든 데이터 캐시 페이지들이 데이터 파일(.mdf) 을 덮어쓴다. 결과적으로, 트랜잭션은 전부 또는 전무 규칙에서 전부가 된다.

22 1. 트랜잭션 처리 메커니즘 기타 고려 사항 실제로 하나의 데이터베이스를 여러 사용자들이 동시에 사용할 경우
에는 앞 시나리오의 경우와 달리, 트랜잭션을 구성하는 명령문들이 서로 뒤섞이게 된다(선착순으로 기록하기 때문이다). 그러나 트랜잭션 식별자나 로그 일련 번호(LSN) 등으로 서로 구별 할 수 있으므로 전혀 문제가 없다. 수작업으로 새로운 검사점 설정을 위해 CHECKPOINT 명령문을 실행할 수도 있다. 앞에서 검토해 본 시나리오 외에도 많은 시나리오가 있는데 어떤 경우 에도 트랜잭션은 안전하게 처리되는 것이 보장된다.

23 2. 트랜잭션 트랜잭션 개요 트랜잭션은 크게 명시적(explicit) 트랜잭션, 자동 커밋(auto
commit) 트랜잭션 그리고 암시적(implicit) 트랜잭션으로 나뉜다. [표 10-1]은 이에 대한 설명이다.

24 2. 트랜잭션 명시적 트랜잭션 여기서는 가장 일반적인 명시적 트랜잭션에 대해 알아본다. 먼저
트랜잭션 조작의 개념을 공부하고, 이어서 트랜잭션 중첩과 트랜 잭션 작성 지침을 배운다. 트랜잭션 조작 명시적 트랜잭션에서의 트랜잭션 조작의 개념도는 [그림 10-7]과 같다.

25 2. 트랜잭션 명시적 트랜잭션

26 2. 트랜잭션 명시적 트랜잭션 [그림 10-7]에서 화살표로 표시된 것이 제어 흐름이다(분기 문이나
루프 등이 없는 한, 계속 아래로 이동한다). BEGIN TRAN 문으로 새로운 트랜잭션을 열고, SAVE TRAN 문으로 저장점(save point)을 설정할 수 있다. ROLLBACK TRAN 문에서 저장점의 이름을 지정하면 트랜잭션 로그에서 저장점까지만 롤백한다. 그리고 제어는 ROLLBACK TRAN 문의 다음 문으로 이동하므로 마치 제어가 [그림 10-7]에서 화살표와 같이 흐르는 것처럼 보인다. 마지막에 COMMIT TRAN 문으로 트랜잭션을 커밋하여 성공적으로 마무리한다.

27 2. 트랜잭션 명시적 트랜잭션 롤백과 제어 흐름은 별개다. 롤백은 제어 흐름과는 별개의 것으로, 오직 트랜잭션 로그 안의 로그
항목에만 적용된다. 흔히 ROLLBACK TRAN 문을 실행하면 제어가 위로 이동하는 것으로 착각하는데, 이는 있을 수 없는 일이며, 제어는 결코 위로 흐르지 않는다.

28 2. 트랜잭션 명시적 트랜잭션 명시적 트랜잭션과 관련된 구문 TRANSACTION은 줄여서 TRAN으로 쓸 수 있다.
transaction_name은 트랜잭션의 이름으로, 식별자 규칙을 지키되 32자까지만 허용된다. @tran_name_variable은 트랜잭션 이름을 담고 있는 변수다.

29 2. 트랜잭션 명시적 트랜잭션 WITH MARK 옵션은 트랜잭션 로그에 트랜잭션의 이름이 표시되도록
해주며(기본적으로는 시스템이 부여한 트랜잭션의 이름이 표시됨), 나중에 데이터베이스를 복원할 때 날짜와 시각대신 트랜잭션의 이름을 사용할 수 있게 한다. WITH MARK 옵션을 지정하 transaction_name 반드시 지정해야 한다. description은 트랜잭션의 이름에 대한 설명으로, 생략할 수 있다. savepoint_name은 저장점의 이름으로, 식별자 규칙을 지키되 32자 까지만 저장점 이름을 담고 있는 변수다.

30 2. 트랜잭션 명시적 트랜잭션 ROLLBACK TRAN 문에서는 transaction_name, @tran_name_
variable, 중 하나를 지정 할 수 있다(이름을 생략할 수 있음). 여기서 transaction_name 또는 @tran_name_variable은 반드시 가장 바깥쪽의 트랜잭션 이름에 해당 되는 것이어야 한다. COMMIT TRAN 문의 은 SQL 서버에 의해 무시되지만, 가독성 향상을 위해 사용될 수 있다. ROLLBACK TRAN과 COMMIT TRAN 문에서 TRAN은 생략할 수 있지만, 일관성 유지와 가독성 향상을 위해 생략하지 않는 것이 좋다.

31 2. 트랜잭션 명시적 트랜잭션 ROLLBACK TRAN 문의 효과를 확인해보자. 예제 2 1 USE Test1DB;
2 SELECT qty AS '트랜잭션 전' 3 FROM orders 4 WHERE orders_id = 1; 5 BEGIN TRAN; 6 UPDATE orders 7 SET qty = 40 8 WHERE orders_id = 1; 9 ROLLBACK TRAN; 10 SELECT qty AS '롤백 후' FROM orders WHERE orders_id = 1; 13 UPDATE orders SET qty = 50 WHERE orders_id = 1; 16 COMMIT TRAN; 17 SELECT qty AS '커밋 후' 18 FROM orders 19 WHERE orders_id = 1;

32 2. 트랜잭션 명시적 트랜잭션

33 2. 트랜잭션 명시적 트랜잭션 저장점과 관련된 예제를 살펴보자. 예제 3 1 USE Test1DB;
2 SELECT qty AS '트랜잭션 전' 3 FROM orders 4 WHERE orders_id = 1; 5 BEGIN TRAN; 6 UPDATE orders 7 SET qty = 60 8 WHERE orders_id = 1; 9 SELECT qty AS '1차 업데이트 후' FROM orders WHERE orders_id = 1; 12 13 SAVE TRAN SavePoint1; 14 UPDATE orders SET qty = 70

34 2. 트랜잭션 명시적 트랜잭션 16 WHERE orders_id = 1; 17 SELECT qty AS '2차 업데이트 후'
FROM orders WHERE orders_id = 1; 20 21 ROLLBACK TRAN SavePoint1; 22 SELECT qty AS '롤백 후' FROM orders WHERE orders_id = 1; 25 UPDATE orders SET qty = 80 WHERE orders_id = 1; 28 SELECT qty AS ‘3차 업데이트 후' FROM orders WHERE orders_id = 1; 31 COMMIT TRAN;

35 2. 트랜잭션 명시적 트랜잭션

36 2. 트랜잭션 명시적 트랜잭션

37 2. 트랜잭션 명시적 트랜잭션 트랜잭션 내에서 오류가 발생할 때 처리를 해보자. 예제 4 1 USE Test1DB; 2
3 /* 오류정보 출력 저장 프로시저 등록 */ 4 IF OBJECT_ID ('usp_ErrorLog', 'P') IS NOT NULL 5 DROP PROCEDURE usp_ErrorLog; 6 GO 7 CREATE PROCEDURE usp_ErrorLog 8 AS 9 PRINT 'Error Number = ' + CONVERT(VARCHAR(11), ERROR_NUMBER()) + ', Severity = ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) + ', State = ' + CONVERT(VARCHAR(5), ERROR_STATE()) + ', Line = ' + CONVERT(VARCHAR(5), ERROR_LINE()); 14 PRINT ERROR_MESSAGE(); 15 PRINT ''; 16 GO 17 18 /* 트랜잭션 내 오류 처리 예 */ 19 BEGIN TRY 20 BEGIN TRANSACTION; orders 테이블에 product_name 열이 없으므로, 이 ALTER 문에서는 오류가 발생함

38 2. 트랜잭션 명시적 트랜잭션 22 ALTER TABLE orders 23 DROP COLUMN product_name;
만약 위 명령문에서 오류가 발생하지 않았다면 트랜잭션을 커밋함 25 COMMIT TRANSACTION; 26 END TRY 27 BEGIN CATCH 28 EXEC usp_ErrorLog; 29 30 IF (XACT_STATE()) = 트랜잭션을 커밋할 수 없는 상태 BEGIN PRINT '롤백합니다...' ROLLBACK TRANSACTION; END; 35 ELSE IF (XACT_STATE()) = 트랜잭션을 커밋할 수 있는 상태 BEGIN PRINT '커밋합니다...' COMMIT TRANSACTION; END; 40 ELSE PRINT '트랜잭션이 없습니다...'; -- 이때 롤백 또는 커밋하면 오류가 발생함 42 END CATCH;

39 2. 트랜잭션 명시적 트랜잭션

40 2. 트랜잭션 명시적 트랜잭션 트랜잭션 중첩(nesting): 트랜잭션 안에 또 다른 트랜잭션을 포함 시키는 것
SQL 서버에서는 실제로 단지 하나의 트랜잭션만 존재하며, 진정한 의미의 중첩 트랜잭션은 존재하지 않는다. 그러나 저장 프로시저나 트리거 등을 호출하면서 트랜잭션이 중첩된 형태로 겹쳐질 수 있는데, 이것을 처리하기 위해 마치 중첩 트랜잭션을 처리하는 것처럼 시뮬 레이션하는 것이다.

41 2. 트랜잭션 명시적 트랜잭션

42 2. 트랜잭션 명시적 트랜잭션 저장 프로시저 sp0의 트랜잭션 안에서 sp1을 호출하고, 다시 sp1의
열고 닫을 때 트랜잭션 세 개가 중첩된다. 시뮬레이션되는 중첩 트랜잭션을 관리하기 위해 SQL 서버는 스칼라 함수 제공한다. 현재의 트랜잭션 카운터 값을 돌려준다.

43 2. 트랜잭션 명시적 트랜잭션 트랜잭션 카운터 값의 변동 규칙 BEGIN TRAN 문은 트랜잭션 카운터 값을 1 증가시킨다.
COMMIT TRAN 문은 트랜잭션 카운터 값을 1 감소시킨다. ROLLBACK TRAN variable) 문은 부분적인 롤백이므로 트랜잭션 카운터 값을 변경하지 않는다. 위의 ROLLBACK TRAN을 제외한 나머지 ROLLBACK TRAN 문은 트랜잭션 카운터 값을 무조건 0으로 만든다(즉, 중첩 트랜잭션 전체를 닫는다).

44 2. 트랜잭션 명시적 트랜잭션 시뮬레이션되는 중첩 트랜잭션이라 하더라도, 트랜잭션 카운터 값이
BEGIN TRAN과 COMMIT TRAN 또는 BEGIN TRAN과 ROLLBACK TRAN 문의 쌍을 일치시켜 트랜잭션이 끝났을 때 트랜잭션 카운터 값이 반드시 0이 되게 해야 한다. 시뮬레이션되는 중첩 트랜잭션이라 하더라도, 트랜잭션 카운터 값이 1 이상이면 트랜잭션이 닫히지 않은 상태로 각종 자원을 계속 점유 한다. 따라서 트랜잭션이 중첩될 가능성이 있을 경우에는 트랜잭션 카운터 값을 수시로 확인하고 더 이상 트랜잭션이 필요없을 때는 트랜잭션 카운터 값을 0으로 초기화시켜야 한다.

45 2. 트랜잭션 명시적 트랜잭션 단순한 형태의 중첩 트랜잭션을 살펴보자. 예제 5 1 USE Test1DB;
2 BEGIN TRAN tran1; 3 UPDATE orders 4 SET qty = 81 5 WHERE orders_id = 1; 6 SELECT AS tran1; 7 BEGIN TRAN tran2; 8 UPDATE orders SET qty = 82 WHERE orders_id = 1; SELECT AS tran2; BEGIN TRAN tran3;

46 2. 트랜잭션 명시적 트랜잭션 13 UPDATE orders 14 SET qty = 83
WHERE orders_id = 1; SELECT AS tran3; COMMIT TRAN tran3; SELECT AS tran2; 19 COMMIT TRAN tran2; 20 SELECT AS tran1; 21 COMMIT TRAN tran1; 22 SELECT qty, AS '트랜잭션 후' 23 FROM orders 24 WHERE orders_id = 1;

47 2. 트랜잭션 명시적 트랜잭션

48 2. 트랜잭션 명시적 트랜잭션

49 2. 트랜잭션 명시적 트랜잭션 다소 복잡한 형태의 중첩 트랜잭션을 살펴보자. 예제 6 1 USE Test1DB;
2 BEGIN TRAN tran1; 3 UPDATE orders 4 SET qty = 91 5 WHERE orders_id = 1; 6 SELECT AS tran1; 7 BEGIN TRAN tran2; 8 UPDATE orders SET qty = 92 WHERE orders_id = 1; SELECT AS tran2; BEGIN TRAN tran3; UPDATE orders

50 2. 트랜잭션 명시적 트랜잭션 14 SET qty = 93 15 WHERE orders_id = 1;
SELECT AS tran3; 17 ROLLBACK TRAN tran3; SELECT AS tran2; 20 COMMIT TRAN tran2; 21 SELECT AS tran1; 22 COMMIT TRAN tran1; 23 GO 24 SELECT qty, AS '트랜잭션 후' 25 FROM orders 26 WHERE orders_id = 1; 27 COMMIT TRAN;

51 2. 트랜잭션 명시적 트랜잭션

52 2. 트랜잭션 명시적 트랜잭션

53 2. 트랜잭션 명시적 트랜잭션 트랜잭션 작성 지침: 트랜잭션 처리 시간이 길어지면 잠금 시간도
같이 길어지므로 전체적인 성능과 동시성(concurrency)을 크게 떨어뜨린다. 이처럼 트랜잭션은 성능과 자원 활용에 매우 중요한 영향 을 미치므로, 처리 시간이 최소가 되도록 작성해야 한다. 트랜잭션 작성을 위한 지침 다음과 같이 장시간을 소요하는 문은 트랜잭션 내에서 사용할 수 없다. ALTER DATABASE, BACKUP LOG, CREATE DATABASE, DROP DATABASE, RECONFIGURE, RESTORE DATABASE, RESTORE LOG, UPDATE STATISTICS

54 2. 트랜잭션 명시적 트랜잭션 sp_dboption처럼 임시 테이블을 만드는 저장 프로시저는 트랜잭션 내에서 사용할 수 없다.
WHILE 문 등과 같은 루프나 DDL 문은 트랜잭션 밖으로 빼낸다. 사용자 입력은 트랜잭션을 시작하기 전에 수행한다. INSERT, UPDATE, DELETE 문이 트랜잭션의 주가 되도록 해야 한다. 또한 가능한 범위 내에서 최소의 행만 조작하도록 해야 한다. 이는 잠금 대상이 되는 행의 수를 줄여서 동시성을 개선시킨다.

55 2. 트랜잭션 명시적 트랜잭션 데이터를 조회할 때는 트랜잭션을 열지 않도록 한다. 즉 데이터 조회와
분석을 끝낸 후, 주로 데이터를 수정할 목적으로 트랜잭션을 여는 것이 바람직하다. 가능한 한 트랜잭션을 중첩시키지 않도록 한다(저장 프로시저나 트리거 에 의한 트랜잭션의 중첩은 불가피하다).

56 2. 트랜잭션 자동 커밋 트랜잭션 명시적 트랜잭션이나 뒤이어 설명할 암시적 트랜잭션이 시작되지
않았다면 기본적으로 자동 커밋(auto commit) 모드가 적용된다. 자동 커밋 모드: 데이터베이스를 변경시키는 배타 명령문(예 INSERT, UPDATE, DELETE 문)을 실행할 때 이 명령문만으로 눈에 보이지 않는 트랜잭션이 만들어지고, 명령문의 실행 결과에 따라 자동으로 커밋되거나 롤백되는 모드다. 자동 커밋 모드는 SQL 서버의 기본 트랜잭션 모드다.

57 2. 트랜잭션 자동 커밋 트랜잭션

58 2. 트랜잭션 자동 커밋 트랜잭션 자동 커밋 모드에 의해 각각의 데이터베이스를 변경시키는 명령문은
서로 독립적이다(독립적인 트랜잭션이기 때문이다). 그러나 배치(batch) 내에 구문 오류가 있을 때는 배치 내의 모든 명령문이 롤백되는 것처럼 보이는 경우가 있다. 그러나 이는 트랜잭션 사이의 간섭이 아니라 단지 배치 전체가 컴파일되지 못하여 전혀 실행 되지 않았기 때문이다. 컴파일만 성공하면 명령문들은 서로 독립적으로 실행된다([예제 7] 참고).

59 2. 트랜잭션 자동 커밋 트랜잭션 배치 내의 구문 오류를 살펴보자. 예제 7 1 USE Test1DB;
5행의 구문 오류로 인해 3~5행의 배치 전체가 롤백되는 것처럼 보이지만, 이는 컴파일이 안 되어 전혀 실행되지 않았기 때문에 그렇게 보이는 것임 예제 7 1 USE Test1DB; 2 SELECT COUNT(*) FROM jobs; 3 GO 4 INSERT jobs (min_lvl, max_lvl) VALUES(10, 15); 5 INSERT jobs VALUES DEFAULTS; --> 구문 오류! 6 GO 7 SELECT COUNT(*) FROM jobs;

60 2. 트랜잭션 자동 커밋 트랜잭션

61 2. 트랜잭션 암시적 트랜잭션 데이터베이스 연결에 IMPLICIT_TRANSACTIONS 옵션을 설정
이와 관련된 구문은 다음과 같다. 여기서 ON은 암시적 트랜잭션 모드를 시작하게 하고 OFF는 암시적 트랜잭션 모드를 마치고 자동 커밋 모드를 시작하게 한다.

62 2. 트랜잭션 암시적 트랜잭션 암시적 트랜잭션 모드에서 다음 명령문 중 하나를 실행하면 트랜 잭션이 자동으로 시작된다.
ALTERTABLE, BEGINTRANSACTION, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATETABLE, UPDATE 일단 시작된 트랜잭션은 사용자가 반드시 COMMIT TRAN 또는 ROLLBACK TRAN 문을 사용하여 종료해야 한다.

63 2. 트랜잭션 암시적 트랜잭션 만약 이를 방치하면 트랜잭션이 계속 열려 있다가 연결을 끊을 때
트랜잭션 전체가 자동으로 롤백된다. 이는 위험 부담이 매우 크므로 암시적 트랜잭션 모드에서는 트랜잭션 상태에 극도로 주의해야 한다. 암시적 트랜잭션 모드에서는 트랜잭션 중첩을 허용하지 않는다. 이미 트랜잭션이 열려 있는 상태에서 다시 위의 명령문 중 하나를 실행하면 기존의 트랜잭션 내에서 수행되고, 새로운 트랜잭션을 열지 않는다.

64 2. 트랜잭션 암시적 트랜잭션 암시적 트랜잭션 모드에서 트랜잭션을 닫으면 트랜잭션이 없는
상태가 지속되다가 위의 명령문 중 하나를 실행하면 다시 트랜 잭션을 시작한다. 암시적 트랜잭션 모드는 SQL 서버 이외의 다른 DBMS를 위해 개발된 응용 프로그램을 큰 수정 없이 실행하는 데 도움이 되는 모드로, 그 밖의 경우에는 가급적 사용하지 않는 편이 좋다(위에서 언급한 큰 위험 부담 때문이다).

65 2. 트랜잭션 암시적 트랜잭션 암시적 트랜잭션 모드를 사용해보자. 예제 8 1 BEGIN TRY
2 SET IMPLICIT_TRANSACTIONS ON; 3 USE Test1DB; 4 SELECT COUNT(*) FROM jobs; 5 6 INSERT jobs (min_lvl, max_lvl) VALUES(10, 15); 7 COMMIT TRAN; 8 9 SELECT COUNT(*) FROM jobs; 10 SET IMPLICIT_TRANSACTIONS OFF; 11 END TRY 12 BEGIN CATCH 13 ROLLBACK TRAN; 14 END CATCH;

66 2. 트랜잭션 암시적 트랜잭션

67 3. 잠금과 교착 상태 개요 일반적으로 데이터베이스는 여러 사용자가 동시에 사용한다. 따라서
여러 사용자들이 하나의 자원을 서로 점유하려고 경쟁하는 경우가 자주 발생하는데 이때 거의 필연적으로 충돌이 발생하며, 이 충돌을 원만하게 해결하지 못하면 데이터베이스에 치명적인 문제가 발생 하게 된다. 이런 문제를 해결하기 위한 잠금과 교착 상태(dead lock)에 대해 살펴볼 것이다. 다중 사용자 환경에서 동시성(concurrency)을 개선하기 위해서도 이들에 대해 잘 알아둘 필요가 있다.

68 3. 잠금과 교착 상태 잠금 잠금(lock): 데이터베이스 자원을 다른 사용자가 접근하지 못하도록 잠그는 것을 말한다.
예를 들면, A 테이블에서 특정 행의 특정 열 값을 세 명의 사용자가 동시에 다른 값으로 바꾼다면 예측할 수 없는 일이 발생하고, 이는 치명적인 문제를 야기할 것이다. 따라서 한 사용자가 업데이트를 하기 전에는 다른 사용자가 접근하지 못하도록 해당 테이블 또는 행을 잠근 후 업데이트를 수행해야 안전 하게 작업을 수행할 수 있다.

69 3. 잠금과 교착 상태 잠금

70 3. 잠금과 교착 상태 잠금 [그림 10-10]에서 종축은 시간축이다(첫째 줄에서 시작하여 시간이
지나면서 아래쪽으로 진행한다). TR1과 TR2에서 동시에 BEGIN TRAN 문을 수행하고, TR1에서 먼저 UPDATE 문을 실행한다. 이때 테이블 T는 TR1을 위해 잠긴다. 이 상태에서 TR2가 UPDATE 문을 실행하면 대기(WAIT) 상태로 들어간다(잠금 때문에 업데이트하지 못하고 기다린다). 그 후에 TR1에서 커밋하면 비로소 테이블 T의 잠금이 해제되고 TR2 를 위해 다시 잠긴다.

71 3. 잠금과 교착 상태 잠금 그리고 UPDATE 문이 실행되고 TR2에서 커밋하면 테이블 T의 모든 잠금이 해제된다.
직후에 잠금이 해제되는 것이 아니라는 점이다. 트랜잭션 내에서 잠긴 모든 자원들은 커밋(또는 롤백)과 동시에 일제히 해제된다. 트랜잭션의 수행 시간을 최소화해야 한다는 것은 바로 이런 이유 때문이다.

72 3. 잠금과 교착 상태 잠금 잠금의 종류와 호환성 세부적인 잠금의 종류는 [표 10-2]와 같다.
잠금에는 기본 잠금과 특수 잠금의 두 종류가 있다. 특수 잠금은 상황에 따라 SQL 서버가 사용하는 특수한 잠금이다. 세부적인 잠금의 종류는 [표 10-2]와 같다.

73 3. 잠금과 교착 상태 잠금

74 3. 잠금과 교착 상태 잠금

75 3. 잠금과 교착 상태 잠금 잠금 호환성(compatibility): 특정 자원에 한 종류의 잠금이 설정
되어 있을 때 다른 종류의 잠금이 허용되는지 여부를 결정하는 것이다. [표10-3]은 잠금의 호환성을 나타낸다.

76 3. 잠금과 교착 상태 잠금 첫째 행의 예를 들면, 특정 자원에 이미 설정된 IS, S, U, IX 또는
SIX 잠금이 있을 때 의도된 공유(IS) 잠금을 추가로 설정할 수 있다. 넷째 행에서 IX 잠금끼리 호환성이 있는 이유는 IX는 행 전체가 아니라 행들 중 일부만 업데이트할 의도(intention)를 갖고 있기 때문이다. 스키마 수정(Sch-M) 잠금은 모든 잠금에 대해 호환성이 없고, 스키마 안전성(Sch-S) 잠금은 스키마 수정(Sch-M) 잠금 이외의 모든 잠금 과 호환성이 있다.

77 3. 잠금과 교착 상태 교착 상태 교착 상태(dead lock): 둘 이상의 트랜잭션들의 잠금이 서로 얽혀서
영원히 풀리지 않는 상태를 말한다. 교착 상태의 개념도는 [그림 10-11]과 같다.

78 3. 잠금과 교착 상태 교착 상태 SQL 서버에서는 다음과 같은 절차에 의해 자동으로 교착 상태를 해소한다.
교착 상태에 있는 트랜잭션들 중 롤백 비용이 가장 작은 트랜잭션을 교착 상태 희생자(victim)로 지정한다. 교착 상태 희생자 트랜잭션을 강제로 롤백한다. 교착 상태 희생자의 응용 프로그램에 오류(오류 번호 1205)를 통지한다. 교착 상태 희생자의 현재 요청을 취소한다. 교착 상태에 있었던 다른 트랜잭션이 처리를 계속할 수 있도록 한다.

79 3. 잠금과 교착 상태 교착 상태 교착 상태 실습하기 1 CREATE DATABASE DeadLock; 2 GO
3 USE DeadLock; 4 GO 5 CREATE TABLE checking ( 6 acct_num int NOT NULL 7 , last_name char(30) NOT NULL 8 , balance money NOT NULL 9 ); 10 GO 11 CREATE TABLE savings ( 12 acct_num int NOT NULL 13 , last_name char(30) NOT NULL 14 , balance money NOT NULL 15 ); 16 GO 17 INSERT checking VALUES (1, 'Smith', $500.00); 18 INSERT checking VALUES (2, 'Jones', $300.00); 19 INSERT savings VALUES (1, 'Smith', $100.00); 20 INSERT savings VALUES (2, 'Jones', $200.00); 21 SELECT * FROM checking; 22 SELECT * FROM savings;

80 3. 잠금과 교착 상태 교착 상태

81 3. 잠금과 교착 상태 교착 상태 BEGIN TRAN; UPDATE checking
SET balance = balance + $100.00 WHERE acct_num = 1;

82 3. 잠금과 교착 상태 교착 상태 BEGIN TRAN; UPDATE savings
SET balance = balance + $100.00 WHERE acct_num = 2; UPDATE savings SET balance = balance - $100.00 WHERE acct_num = 2;

83 3. 잠금과 교착 상태 교착 상태 EXEC sp_lock

84 3. 잠금과 교착 상태 교착 상태 UPDATE checking SET balance = balance - $100.00
WHERE acct_num = 1;

85 3. 잠금과 교착 상태 교착 상태 SELECT * FROM checking; COMMIT TRAN;

86 3. 잠금과 교착 상태 교착 상태 교착 상태 대비책 비록 SQL 서버가 교착 상태를 자동으로 해소시켜주더라도 교착 상태
희생자로 지정된 응용 프로그램을 재실행해야 하는 심각한 부담이 있으 므로, 가급적 교착 상태가 발생하지 않도록 다음과 같은 대비책을 강구 해야 한다.

87 3. 잠금과 교착 상태 교착 상태 모든 트랜잭션에서 자원을 같은 순서로 액세스한다. 예를 들어, A, B, C가
다른 트랜잭션에서는 C → B → A의 순서로 액세스하면 교착 상태가 발생 할 가능성이 훨씬 커지기 때문이다. 트랜잭션을 구성하는 명령문들의 수를 최소화하여 트랜잭션 처리 시간을 줄인다. 많은 행들에 영향을 미치는 질의는 가급적 트랜잭션 내에서 사용하지 않는다.