17. [SQL기초] 트랜잭션과 실행 무결성

트랜잭션 기초 이해

전체 실행이 보장되는 하나의 실행 단위, 또는 쿼리문의 묶음을 트랜잭션이라고 합니다. 트랜잭션은 트랜잭션 세션 범위 안에 있는 모든 쿼리문이 실행되는 것을 보장하기 때문에 범위 안의 쿼리문이 한 개라도 실패해 에러가 발생하면 트랜잭션 세션 범위 시작부터 실행된 모든 쿼리문이 취소됩니다.

실행한 쿼리문을 취소할 수 있다는 것은 데이터베이스 데이터의 무결성을 보장하는데 굉장히 중요합니다.

여러 개의 테이블에 데이터를 한꺼번에 집어넣을 때 중간에 쿼리문 에러, 또는 제약조건 위반으로 에러가 발생하면 쿼리문을 그 위치에서 멈춥니다.

당연히 에러가 발생해서 멈췄지만, 이미 데이터를 저장한 테이블은 수동으로 하나씩 추가된 행을 삭제해야 합니다. 추가인 경우에는 새로 추가한 행이 있으므로 삭제하면 되지만, 테이블 행을 업데이트했거나 삭제했다면, 돌이킬 방법이 없습니다.

쇼핑몰 고객이 주문을 완료하면 주문정보를 저장하고, SMS와 이메일을 발송하는 기능에서 가져갈 내용을 각각의 테이블에 저장하고, 장바구니를 삭제하고 결제 완료 처리를 하는 과정을 거쳐서 최종적으로 주문 완료 페이지가 표시됩니다.

주문정보 저장 후 SMS를 발송할 내용을 테이블에 저장하다 에러가 발생하면 문제가 발생합니다. 주문정보는 저장되어 내 주문 내역에서 주문은 확인되지만, 주문 완료 안내는 오지 않고, 장바구니에는 상품이 그대로 남아있게 됩니다.

트랜잭션을 이용해서 고객이 주문을 완료하는 시점에 트랜잭션을 시작한 후 주문 완료 페이지를 호출하기 직전에 트랜잭션을 성공 완료하도록 하면 중간에 쿼리문 실행 중 에러가 발생하는 문제에 대응을 할 수 있습니다.

중간에 쿼리문 에러가 발생하면 주문 완료 시점 이후 발생한 쿼리문 실행이 모두 취소됩니다.

트랜잭션 세션을 시작한 후 실행된 쿼리문들은 실제 테이블에 쿼리문 내용을 반영하는 것이 아닙니다. 가상의 테이블에 반영 내역을 남겨놓거나, 별도의 영역에 테이블에 반영할 내용을 기록해 놓습니다. 실제 테이블에는 아무런 작업이 이루어지지 않습니다.

트랜잭션이 성공 완료(커밋)되면 그때서야 기록해 두었던 반영 내용을 실제 테이블에 적용합니다.

트랜잭션 사용

트랜잭션은 “START TRANSACTION;”으로 시작합니다. 이후에 실행할 쿼리문들을 순차적으로 실행합니다.

START TRANSACTION;
insert into p_order (userid, p_code, p_num, p_sell_price, p_option, dtdmoney, mobile, address, zipcode)
values (3, 2, 2, 25000, '블루', 2500, 010-1234-5678, '경기도 성남시 분당구 판교동 1', '12345');
update inventory, (select p_code, p_num from basket where userid = 3) a set stock = stock - p_num where inventory.p_code = a.p_code ;
delete from basket where userid = 3;
COMMIT;

“COMMIT” 대신 “ROLLBACK”으로 변경을 하면 앞서 실행된 3개의 쿼리문 업데이트는 모두 취소되고 “START TRANSACTION” 이전의 상태로 돌아갑니다.

커밋과 롤백

트랜잭션 시작 후 실행한 쿼리문들이 성공적으로 모두 실행되었으면 커밋(commit)을 합니다. 커밋은 트랜잭션 시작 후 실행한 쿼리문 실행 결과를 실제로 데이터베이스에 반영하고 트랜잭션을 완료합니다.

커밋 명령어는 “COMMIT;” 입니다.

쿼리문 실행 중 에러가 발생하면 트랜잭션 시작 직전 상태로 되돌려야 합니다. 이것을 롤백(rollback)이라고 합니다. 롤백 명령어는 “ROLLBACK;”입니다.

커밋 또는 롤백을 하지 않으면 트랜잭션 세션이 열린 상태로 있게 되고 실행되는 쿼리문에 대한 데이터베이스 업데이트 기록이 계속 누적됩니다. 이 상태에서 데이터베이스와의 접속이 끊어지거나, 데이터베이스 사용자가 로그아웃을 하면 트랜잭션 시작 후 업데이트된 모든 내용이 롤백됩니다.

트랜잭션 시작 후 업데이트 된 내용을 실제 데이터베이스에 반영하려면 반드시 커밋을 해서 트랜잭션을 완료해야 합니다.

트랜잭션 에러 예외 처리

트랜잭션 시작 후 쿼리문을 실행하는 도중 에러가 발생하면 롤백을 해서 트랜잭션 시작 후 반영한 내용들을 모두 취소해야 합니다. 프로그래밍 언어에서 try ~ catch() 문으로 에러 핸들링 처리를 하는 것과 같은 방법으로 트랜잭션 실행 중 발생하는 에러에 대해서 자동 커밋, 또는 롤백 처리를 할 수 있습니다.

BEGIN TRY

                 BEGIN TRANSACTION;

                 -- 쿼리문 실행

    COMMIT;

END TRY

BEGIN CATCH

                 -- 에러 핸들링

    ROLLBACK;

                 -- 에러 핸들링 메시지 표시

END CATCH

단 이 에러 핸들링 처리 코드는 SQL Server에서만 실행 가능하며 MySQL은 에러 핸들링 기능을 지원하지 않습니다. 데이터베이스 종류에 따라서 지원이 가능한지 확인하고 사용해야 합니다.

DB별 호환성

데이터베이스 별로 트랜잭션 시작, 커밋, 롤백을 하는 명령어는 다음과 같습니다.