Fogeaters, Light The World.

14

2017-Aug

 [mssql / mysql] sql 트랜잭션(Transaction)

작성자: title: MoonBlonix IP ADRESS: *.64.228.3 조회 수: 1508

출처 :: http://sweeper.egloos.com/m/3003805
우선 트랜잭션에 대해 잘 설명된 MSSQL 관련 글이다. MYSQL과 다소 명령어가 다르니 개념이해만 하고 아래 댓글을 보자

1. Transaction

MSDN 참고 페이지 : 트랜잭션 문(T-SQL)


트랜잭션은 "하나의 논리적 작업 단위로 수행되는 일련의 작업"로 정의할 수 있다.
이 의미에는 "일련의 작업이 전부 되거나, 전부 되지 않거나"의 의미가 포함되어 있다.

단순히 이야기하면, "SQL의 묶음"이라고도 할 수 있다.

여러 SQL 쿼리문을 트랜잭션으로 묶어 (BEGIN TRAN)
트랜잭션이 성공하면 트랜잭션 동안 이루어진 일련의 작업 결과는 커밋되고 데이터베이스에 영구적으로 반영된다.
(COMMIT TRAN)
트랜잭션에 오류가 발생하여 취소되거나 롤백되면 일련의 작업 내역은 모두 취소된다.
(ROLLBACK TRAN)


트랜잭션은 아래와 같이 ACID(각 항목의 첫 글자)라고 불리는 특성을 가지고 있다.

1) Atomicity (원자성)

트랜잭션은 분리할 수 없는 하나의 단위이다.
작업이 모두 수행되거나 하나도 수행되지 않아야 한다.

2) Consistency (일관성)

트랜잭션에서 사용되는 모든 데이터는 일관되어야 한다.
이 일관성은 추후 설명할 '잠금'과 관련이 있다.

3) Isolation (격리성)

현재 트랜잭션이 접근하고 있는 데이터는 다른 트랜잭션으로부터 격리되어야 한다는 것을 의미한다.
트랜잭션이 발생하기 이전 상태나 완료된 이후 상태를 볼 수는 있지만,
트랜잭션이 진행중인 중간 데이터를 볼 수 없다.

(물론 이를 피해가는 방법도 있다)

4) Durability (영속성)

트랜잭션이 정상적으로 종료되면, 그 결과는 시스템 오류가 발생하더라도 시스템에 영구적으로 적용되어야 한다.


트랜잭션이 발생되는 SQL 문은 생각보다 적지 않다.
  • SELECT
  • INSERT/UPDATE/DELETE
  • ALTER TABLE, FETCH, REVOKE, CREATE, GRANT, TRUNCATE, TABLE, DROP, OPEN...
하지만 INSERT/UPDATE/DELETE가 트랜잭션을 발생시키는 주요 항목들이다.


2. 트랜잭션의 종류

트랜잭션은 아래 네 가지 종류로 분류될 수 있다.

1) 자동 커밋 트랜잭션

각 쿼리마다 자동적으로 BEGIN TRAN ... COMMIT TRAN이 붙여지는 것을 의미한다.
SQLServer는 '자동 커밋 트랜잭션'을 디폴트로 사용한다.

아래의 예제를 살펴 보자.

  1. -- 3번의 UPDATE문 호출
  2. UPDATE UserTable SET Addr = N'서울' WHERE ID = 'KHD'
  3. UPDATE UserTable SET Addr = N'경기' WHERE ID = 'KKJ'
  4. UPDATE UserTable SET Addr = N'강원' WHERE ID = 'KYM'

위 쿼리문에서 트랜잭션은 몇 번이나 발생했을까?

UPDATE가 연속 3개 나왔으므로, 3개를 하나로 묶어서 
트랜잭션이 한 번만 발생한 것처럼 보일 수 있으나, 사실 트랜잭션은 세 번 발생했다.
즉, 각각의 UPDATE가 트랜잭션을 각기 하나씩 발생시킨 것이다.

아래 구문은 여러 SQL문을 하나의 트랜잭션 단위로 묶는 방법이다.

  1. -- TRANSACTION 대신 TRAN으로 쇼트닝이 가능하다.
  2. -- CHAR/NCHAR, VARCHAR/NVARCHAR의 문자열 형식의Named TRANSACTION이 가능하다.
  3.  
  4. [DECLARE @tranName NVARCHAR(16) = N'SampleTran']
  5.  
  6. BEGIN TRANSACTION [@tranName]       -- 트랜잭션 블록 시작 명시
  7.     일련의 SQL 문들...
  8. COMMIT TRANSACTION [@tranName]      -- 트랜잭션 블록 종료 명시 (성공, DB에 반영)
  9. or
  10. ROLLBACK TRANSACTION [@tranName]    -- 트랜잭션 블록 종료 명시 (실패, DB에 반영 X)

    그런데, 위에서 SQLServer가 기본적으로 "자동 커밋 모드" 방식을 사용한다고 하였다.
    따라서 위 세 번의 UPDATE 예제는 결국 아래와 동일한 것이다.

    1. -- SQLServer는 자동 커밋 트랜잭션에 의해 쿼리의 각 문장마다
    2. -- BEGIN TRAN ... COMMIT TRAN을 붙여준다.
    3.  
    4. BEGIN TRAN
    5. UPDATE UserTable SET Addr = N'서울' WHERE ID = 'KHD'
    6. COMMIT TRAN
    7.  
    8. BEGIN TRAN
    9. UPDATE UserTable SET Addr = N'경기' WHERE ID = 'KKJ'
    10. COMMIT TRAN
    11.  
    12. BEGIN TRAN
    13. UPDATE UserTable SET Addr = N'강원' WHERE ID = 'KYM'
    14. COMMIT TRAN

    2) 명시적 트랜잭션

    직접 BEGIN TRAN문과 COMMIT TRAN 또는 ROLLBACK TRAN문을 써 주는 것을 말한다.

    위 자동 커밋 트랜잭션의 예제를 명시적 트랜잭션으로 수정하려면, 다음과 같이 해주면 된다.

    1. -- 이렇게 명시적으로 BEGIN TRAN ... COMMIT TRAN으로 묶어줌으로써
    2. -- 아래 세 개의 UPDATE 문은 하나의 트랜잭션으로 처리된다
    3.  
    4. BEGIN TRAN
    5. UPDATE UserTable SET Addr = N'서울' WHERE ID = 'KHD'
    6. UPDATE UserTable SET Addr = N'경기' WHERE ID = 'KKJ'
    7. UPDATE UserTable SET Addr = N'강원' WHERE ID = 'KYM'
    8. COMMIT TRAN

    3) 암시적 트랜잭션

    오라클과의 호환성 문제가 아니라면, 암시적 트랜잭션을 쓸 일이 있을까 싶지만, 정리는 해 둔다.

    어떤 쿼리가 나오면 자동으로 내부적으로 BEGIN TRAN을 붙여 주지만,
    COMMIT TRAN 또는 ROLLBACK TRAN은 직접 써 줘야 한다.
    (오라클이 이러한 방식을 사용한다)

    암시적 트랜잭션을 사용하려면 아래와 같이 설정해 주어야 한다.

    SET IMPLICIT_TRANSACTIONS ON

    위와 같이 설정한 이후부터는 쿼리가 실행되면 동시에 트랜잭션이 시작되는 것이다.
    직접 COMMIT TRAN 또는 ROLLBACK TRAN을 수행하기 전까지는 트랜잭션이 계속 진행중인 상태가 된다.

    아래 예제를 살펴보자.

    1. -- 암시적 트랜잭션을 설정
    2. SET IMPLICIT_TRANSACTIONS ON
    3.  
    4. USE master
    5. GO
    6.  
    7. -- tranDB 데이터베이스를 생성한다
    8. CREATE DATABASE tranDB
    9. GO
    10.  
    11. USE tranDB
    12. -- tranTbl 테이블을 생성한다
    13. -- 이 순간에 이미 트랜잭션은 시작되었다.
    14. CREATE TABLE tranTbl (id INT)
    15. GO
    16.  
    17. INSERT INTO tranTbl VALUES (1)
    18. INSERT INTO tranTbl VALUES (2)
    19.  
    20. SELECT * FROM tranTbl

    위와 같이 쿼리창에서 tranDB - tranTbl을 만들고 2개의 INSERT를 수행하였다.
    이 상태에서, 새로운 쿼리창을 열고 아래와 같이 작성후 수행하면...

    1. USE tranDB
    2. GO
    3.  
    4. SELECT * FROM tranTbl

    먼저 쿼리창에서 시작된 트랜잭션이 커밋되지도 롤백되지도 않았기에,
    (즉, 트랜잭션이 완료되지 않았기에)
    잠금이 발생하였고 이로 인해 두번째 쿼리창의 SELECT ... FROM 문은 하염없이 결과를 기다리는 상태가 된다.

    4) 일괄 처리 범위의 트랜잭션

    MARS(Multiple Active Result Sets)에만 해당되며, 
    MARS 세션에서 시작되는 T-SQL 명시적 또는 암시적 트랜잭션이 일괄 처리 범위 트랜잭션이 된다.

    일괄 처리가 완료될 때 커밋되거나 롤백되지 않은 일괄 처리 범위의 트랜잭션은 SQLServer에서 자동으로 롤백한다.


    3. 트랜잭션 처리 과정과 트랜잭션 로그 파일

    데이터베이스의 기본 구조 문서의 트랜잭션 로그 파일 챕터에서도 작성하였듯이,
    트랜잭션은 우선 트랜잭션 로그 파일(*.ldf)에 저장된다.

    즉, 위 예제의 세 개의 UPDATE문은 차례대로 로그 파일에 기록된 이후에 실제 데이터 파일(*.mdf)에 변경이 적용된다.

    위 예제보다 더 예제를 단순화 시켜서 트랜잭션 처리 과정을 살펴보도록 하자.

    1. -- 아래 문법은 신경쓰지 말자
    2.  
    3. BEGIN TRAN
    4.  
    5. UPDATE 1->11
    6. UPDATE 2->22
    7. UPDATE 3->33
    8.  
    9. END TRAN

    1. BEGIN TRAN 과정

    BEGIN TRAN 문장을 실행하면 지금부터 트랜잭션을 시작하는 것으로 인식된다.

    그리고, BEGIN TRAN 문장이 로그 일련 번호, 트랜잭션 아이디와 함께 
    로그 파일의 처음이나 마지막 체크 포인트 이후에 바로 이어서 기록된다.


    2. UPDATE 1->11 과정

    UPDATE 1->11의 과정은 아래와 같이 진행된다.

    참고로, 설명을 쉽게 하기 위해 그림에는 한 페이지(8KB)당 하나씩 데이터를 넣었지만,
    실제로는 대개 한 페이지에 여러 개의 행이 들어간다.

    그리고 로딩되는 단위는 페이지 단위이므로, 해당되는 데이터가 들어있다면 한 페이지가 모두 로딩된다.

    이러한 내용은 Windows OS의 가상 메모리 페이지와 개념이 동일하다.

    1. UPDATE 1->11이 실행되면
    2. 데이터 파일에 실제로 존재하는 데이터 '1'이 데이터 캐쉬로 로딩된다.
    3. 데이터 캐쉬의 '1'의 값을 '11'로 변경하고,
    4. "UPDATE 1->11"과 로그 일련 번호, 트랜잭션 아이디를 함께 로그 파일에 기록한다.
    아직 COMMIT TRAN을 실행하지 않았으므로, 로그 파일(*.ldf)에만 UPDATE문이 기록되었을 뿐,
    데이터 파일(*.mdf)에는 아무런 변화가 없음을 잘 살펴 보아야 한다.

    3. UPDATE 2->22 / UPDATE 3->33 과정

    UPDATE 1->11과 동일한 과정을 거친다.
    역시 COMMIT TRAN이 실행되지 않았기에, 데이터 캐쉬, 트랜잭션 로그 파일에만 변경이 발생한다.


    4. COMMIT TRAN 과정

    1. COMMIT TRAN을 실행하면
    2. COMMIT TRAN 문장을 로그 일련번호, 트랜잭션 아이디와 함께 로그 파일에 기록한다
    3. 데이터 캐쉬의 변경된 데이터(1->11, 2->22, 3->33)을 차례로 데이터 파일(*.mdf)에 적용시킨다.
    4. 데이터 변경이 완료되면
    5. #5 : COMMIT TRAN 바로 다음으로 새로운 체크포인트를 설정해 둔다.

    지금까지의 모든 과정(BEGIN TRAN ... COMMIT TRAN)이 데이터의 무결성을 보장하는 처리 과정이다.
    데이터의 무결성이란 '전부 되거나 전부 안되거나'를 의미한다.

    즉, 하나의 트랜잭션에 속한 쿼리문들이 전부 적용이 되거나 전부 적용이 안 되어야지,
    일부만 적용된다면 데이터의 무결성을 보장할 수 없다.

    이 이야기의 사례는 다시 한번 데이터베이스의 기본 구조 문서의 트랜잭션 로그 파일 챕터를 살펴보길 바란다.


    4. 더티페이지와 체크포인트

    더티 페이지(Dirty page)란? 
    데이터 캐시에서는 변경되었지만, 데이터 파일(*.mdf)에서는 아직 변경되지 않은 데이터(페이지)를 의미한다.

    이 더티 페이지가 데이터 파일에 성공적으로 적용된 직후에 체크포인트가 설정된다.
    체크포인트는 SQLServer가 새로 시작될 때 복구 지점으로써 사용된다.
    (자세한 내용은 아래 챕터에서...)

    만약, 체크포인트를 강제로 설정하려면 CHECKPOINT 문을 실행시키면 된다.
    (자세한 내용은 MSDN의 CHECKPOINT 문서를 참고하자)

    CHECKPOINT 문을 실행하면 더티 페이지의 데이터가 모두 데이터 파일에 적용되고, 체크포인트 역시 재설정된다.

    이 외에도 체크포인트가 새로 설정되는 대표적인 경우들은 아래와 같다.
    • ALTER DATABASE를 수행한 경우
    • 정상적으로 SQLServer가 종료되는 경우
    • 각 데이터베이스에서 체크포인트를 주기적으로 재설정하는 경우
    • 데이터베이스를 백업하는 경우


    5. 정전이나 하드웨어 이슈로 인한 재부팅시 처리 과정

    PC가 재부팅되고 SQLServer가 재시작 되었으므로, SQLServer의 데이터 캐시에 있던 내용은 당연히 모두 없어졌다.

    이후의 처리 과정은 로그 파일의 마지막 체크포인트 이후 
    "COMMIT TRAN"의 존재 여부에 따라 아래와 같이 나뉘어 진다.
    • ROLLBACK
    • ROLLFORWARD

    1. ROLLBACK

    롤백은 로그 파일 중에서 아직 커밋이 되지 않은(즉, 데이터 파일에 적용되지 않은) 내용을
    로그 파일의 처음이나 마지막 체크포인트가 설정된 지점까지 취소/삭제시키는 과정을 말한다.

    즉, 위 예제등에서 UPDATE 1->11, 2->22, 3->33이 로그 파일에만 기록이 남아있고,
    COMMIT TRAN이 실행되지 않은 채 SQLServer가 재가동 되었다면,
    로그 파일에 남아있는 1->11 ... 3->33의 로그는 로그 파일에서 삭제된다.

    2. ROLLFOWARD

    롤포워드는 로그 파일의 처음이나 마지막 체크포인트 이후의 COMMIT TRAN들에 대해
    BEGIN TRAN ... COMMIT TRAN의 과정을 다시 수행하는 것을 의미한다.

    이렇게 함으로써, SQLServer가 성공적으로 접수했던, 
    BEGIN TRAN ... COMMIT TRAN의 과정에 대해선 SQLServer가 책임지고 온전히 수행함을 보장하는 것을 의미한다.


    SQLServer가 재시작될 때의 ROLLBACK / ROLLFORWARD는 SQLServer가 자동으로 처리를 해준다.


    6. SAVE TRAN / ROLLBACK TRAN

    1. SAVE TRAN

    SAVE TRAN은 트랜잭션 내 저장점을 생성한다..

    기본 구문은 아래와 같다.

    SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }

    저장점이란, 트랜잭션의 일부가 조건에 따라 취소될 경우에 트랜잭션이 되돌아갈 수 있는 위치를 의미한다.

    중복된 저장점 이름을 트랜잭션에서 사용할 수 있지만, 
    저장점 이름을 지정하는 ROLLBACK TRANSACTION 문은 
    해당 이름을 사용하는 최신 SAVE TRANSACTION으로만 트랜잭션을 롤백한다.

    자세한 내용은 MSDN의 SAVE TRANSACTION 페이지를 참고하기 바란다.
    또한, 위 페이지에 포함되어 있는 ROLLBACK TRAN과 SAVE TRAN의 예제는 꼭 한번 살펴보기 바란다.


    2. ROLLBACK TRAN

    ROLLBACK TRAN [@tranName]은 
    트랜잭션의 시작점이나, 트랜잭션의 저장점으로 트랜잭션을 명시적으로 롤백시킨다.

    주로, 트랜잭션의 예외 처리에 사용된다.
    (BEGIN TRY/END TRY ... BEGIN CATCH/END CATCH)

    기본 구문은 아래와 같다.

    1. ROLLBACK { TRAN | TRANSACTION }
    2.      [ transaction_name | @tran_name_variable
    3.      | savepoint_name | @savepoint_variable ]

    ROLLBACK TRAN시 뒤에 transaction_name이나 savepoint_name이 지정되지 않으면, 트랜잭션의 처음으로 롤백한다.
    즉, 모든 트랜잭션을 롤백해 버린다.
    이 경우 아래 챕터에서 다시 설명할 @@TRANCOUNT가 0으로 감소된다.
    또한, 트랜잭션을 중첩할 경우 이 문에서는 모든 내부 트랜잭션을 가장 바깥쪽 BEGIN TRANSACTION 문으로 롤백한다.

    특정한 지점으로 롤백하려 한다면, ROLLBACK TRAN savepoint_name을 사용해야 한다.
    덧붙여 ROLLBACK TRAN savepoint_name은 @@TRANCOUNT를 감소시키지 않으니 헤깔리지 말아야 한다.

    자세한 내용은 MSDN의 ROLLBACK TRANSACTION 페이지를 참고하기 바란다.
    profile
    목록
    profile

    title: MoonBlonix

    2017.08.15 12:16
    *.64.228.3

    MySQL 은 아래와 같다.


    트랜잭션 도구

    START TRANSACTION;

    // COMMIT, ROLLBACK이 나올 때까지 실행되는 모든 SQL 추적

    COMMIT;

    // 모든 코드를 실행(문제가 없을 경우에)

    ROLLBACK;

    // START TRANSACTION 실행 전 상태로 되돌림(문제 생기면)



    ex)

    START TRANSACTION;                                    //트랜잭션 시작

    SELECT * FROM my_table;                                 //초기상태 보여줌

    DELETE FROM my_table WHERE gender = 'M';    //데이터의 수정

    SELECT * FROM my_table;                                 //수정 후의 상태 보여줌

    ROLLBACK;                                               //트랜잭션이 선언되기 전 상태로 되돌아감

    SELECT * FROM my_table;                                //수정 전의 초기 상태를 보여줌

    START TRANSACTION;                                      //다시 트랜잭션 시작

    DELETE FROM my_table WHERE gender = 'M';    //수정

    COMMIT;                                                        //트랜잭션 이후 모든 동작을 적용

    SELECT * FROM my_table;                                //적용된 결과 보여줌



    출처: http://iamzombie.tistory.com/38 [가늘고길게]

    List of Articles
    번호 제목 글쓴이 날짜 조회 수
    공지 [Web] 클라우드 IDE + 2 title: MoonBlonix 2017-06-25 15128
    112 [jQuery] Ajax 옵션 설명 title: MoonBlonix 2017-09-15 1442
    111 [jQuery] 페이지 부분 새로고침 + 1 title: MoonBlonix 2017-09-13 1129
    110 정규표현식 분석 + 1 title: MoonBlonix 2017-09-12 1091
    109 [php/mysql] 모든 uft-8 한글 인코딩 문제 title: MoonBlonix 2017-09-12 1500
    108 [php] AES / mcrypt 양방향 대칭키 암호화/복호화 + 3 title: MoonBlonix 2017-08-28 1242
    107 [php/jQuery/Ajax] 파일 업로드 구현 title: MoonBlonix 2017-08-28 1463
    106 [jQuery] ajax 사용 기초 title: MoonBlonix 2017-08-19 1463
    105 [php] 서버 용량 구하기 title: MoonBlonix 2017-08-15 1355
    104 [jQuery] 레이어 팝업 title: MoonBlonix 2017-08-15 1491
    103 [php/jQuery] 선택된 다수의 체크박스 값 넘기기 / 체크박스 제어 + 2 title: MoonBlonix 2017-08-15 1477
    102 [mysql] 저장 프로시저 / 저장 함수 / 트리거 title: MoonBlonix 2017-08-15 1512
    101 [mysql] 저장엔진 title: MoonBlonix 2017-08-15 1473
    » [mssql / mysql] sql 트랜잭션(Transaction) + 1 title: MoonBlonix 2017-08-14 1508
    99 [mysql] mysqli_fetch_row() mysqli_fetch_assoc() mysqli_fetch_array() 차이 title: MoonBlonix 2017-08-08 1485
    98 [javascript/jQuery] Ajax의 흐름과 예제 title: MoonBlonix 2017-08-05 1541
    97 [web] html 게시글 에디터 title: MoonBlonix 2017-08-05 1459
    96 [mysql] JOIN 쿼리 사용하기 title: MoonBlonix 2017-08-05 1460
    95 [mysql] 인덱스 (Index) 사용 방법 및 설명 + 2 title: MoonBlonix 2017-08-05 1718
    94 [mysql] TEXT 타입, INT 타입 title: MoonBlonix 2017-08-05 1518
    93 [mysql] 성능 향상 전략 + 4 title: MoonBlonix 2017-08-05 1470
    목록 검색
    쓰기