근원e♡수진♡라라 일상생활

MySQL Transaction

MySQL Transaction 에 대해서 알아 보겠습니다. 트랜잭션(Transaction) 은 데이터 베이스 내의 구문을 완전하게 유지 하기 위해 사용되는 기능입니다. 예를 들어 결제 내역에 처리를 한다고 했을때 사용자가 물품 구매 내역 테이블에 결제시작에 관련한 정보를 넣고 , 결제 내역 기록 테이블에 결제 내역에 대한 기록을 하고 , 구매 내역 관리 테이블(통계 테이블)에 기록을 한다고 한다면 , 전체적으로 하나의 액션마다 하나의 기록을 합니다. 하지만 결제 부분에서 오류 발생등으로 인해 구매가 중단되면 다시 결제 관련한 기록들을 이전 상태로 돌려 놓아야 합니다. (ROLLBACK) 이때 기록했던 (INSERT) 데이터들을 다시 삭제 (DELETE)하고 구매 내역 관리 테이블에 업데이트 했던 내용을 다시 이전 상태로 돌려 놓는 과정이 필요합니다.

그러나 이 일련의 과정이 정확이 이루어 지지 않았다고 한다면 어떻게 될까요? 전체적으로 데이터의 완전성은 무너지게 됩니다. 그러므로 트랜잭션은 액션 전체를 하나의 큰 틀로 묶어 전체적으로 제어 하는것입니다. 트랜잭션의 사직과 함께 정상적으로 구문이 완료 되었을 때는 COMMIT 을 하여 데이터 베이스를 갱신하며 그렇지 않고 이전 상태로 돌린다면 ROLLBACK을 하여 이전상태로 돌려 놓는 과정입니다.

MySQL 에서 트랜잭션 지원이 가능한 테이블엔진은 BDB , InnoDB 입니다. MySQL 을 접속하여 SHOW ENGINES \G 를 입력하여 데이터 베이스 엔진의 종류를 알아 보도록 하겠습니다.

위와 같이 transactions 이 지원 된다고 나와 있습니다. 트랜잭션을 사용하려 하면 우선 시스템 설정상에 autocommit 을 OFF로 변경해야 합니다. autocommit 이 ON 으로 되어 있으면 하나의 수행마다 기록이 되므로 우선 autocommit 의 설정을 Off로 변경해보도록 하겠습니다. 트랜잭션은 커넥션 단위로 수행이 됩니다. ^^

위와 같이 autocommit 의 설정 상태를 OFF로 변경하였습니다. 이제 트랜잭션의 사용에 대해서 알아보겠습니다.

START TRANSACTION [WITH CONSISTENT SNAPSHOT];
-- 트랜잭션을 적용할 구문을 적어 줍니다.
COMMIT;
ROLLBACK;

이 예제를 보시면 아시겠지만. Transaction Start이 후 값을 변경하고 RollBack 을 하면 이전 상태로 돌아감을 볼 수 있습니다. 이처럼 여러가지 수행 을 한 후 ROLLBACK/COMMIT을 수행하여 데이터의 완전성을 지킬 수 있습니다.

InnoDB 에서는 START TRANSACTION WITH CONSISTENT SNAPSHOT; 으로 트랜잭션을 시작하면 입력을 실행한 때의 데이터를 스냅샷용으로 보존을 합니다.

트랜잭션 시에 중요한것이 있습니다. 몇가지 SQL 구문들은 자동적으로 COMMIT이 이루어 지게 되어 있습니다. ALTER , CREATE, DROP, LOAD, SET, LOCK, UNLOCK, TRUNCATE  등의 구문은 START TRANSACTION을 시작하였다 하더라도 자동으로 COMMIT이 이루어 집니다.

트랜잭션을 수행하다 데이터 베이스 접속이 끊기거나 , 트랜잭션이 중단이 될 경우에는 데이터 베이스에는 로그가 남게 되어 있습니다. 예를 들어 트랜잭션을 시작하고 여러 구문을 수행한 다음에 COMMIT이나 ROLLBACK을 하지 않고 접속이 끊어지는 상황에서는 MySQL 서버에 log 가 남아 있게 되어 있습니다. 이 버퍼의 크기는 innodb_log_buffer_size 값을 my.ini파일에서 수정하면 됩니다.

위처럼 MySQL 의 트랜잭션을 이용해 더욱더 윤택한 SQL 생활이 가능합니다 ^^