MySQL 트랜잭션

MySQL의 동시성에는 잠금(Lock), 트랜잭션, 그리고 트랜잭션의 격리 수준(Isolation level)이 영향을 미친다. 트랜잭션은 작업의 완전성을 보장해준다. 즉, 논리적인 작업 셋을 모두 완벽하게 처리하거나 또는 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어주는 기능이다.

잠금(Lock)과 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다. 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는데 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경해버릴 수 있게 된다. 결과적으로 해당 레코드의 값은 예측할 수 없는 상태가 된다. 잠금은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다. 격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

MySQL 트랜잭션

많은 사용자들이 트랜잭션에 대해 깊이 생각하거나 고민하기 싫어서 MyISAM을 주로 선택한다는 내용의 게시물을 자주 접하곤 한다. 하지만 사실은 MyISAM이나 MEMORY 같이 트랜잭션을 지원하지 않는 스토리지 엔진의 테이블이 더 많은 고민거리를 만들어 낸다. 이번 절에서는 트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB의 처리 방식 차이를 잠깐 살펴보고자 한다. 그리고 트랜잭션을 사용할 경우 주의해야 할 사항도 함께 살펴보겠다.

1. MySQL에서의 트랜잭션

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 또는 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을 때) 함을 보장해 주는 것이다.

간단한 예제로 트랜잭션 관점에서 InnoDB 테이블과 MyISAM 테이블의 차이를 살펴보자.

mysql > CREATE TABLE tab_myisam (fdk INT NOT NULL, PRIMARY KEY (fdpk) ENGINE=MyISAM;
mysql > INSERT INTO tab_myisam (fdpk) VALUES (3);

mysql > CREATE TABLE tab_innodb (fdk INT NOT NULL, PRIMARY KEY (fdpk) ENGINE=INNODB;
mysql > INSERT INTO tab_innodb (fdpk) VALUES (3);

위와 같이 테스트용 테이블에 각각 레코드 1건씩 저장한 후, AUTO-COMMIT 모드에서 다음 쿼리 문장을 InnoDB 테이블과 MyISAM 테이블에서 각각 실행해 보자.

mysql > INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);
mysql > INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);

두 개의 스토리지 엔진에서 결과가 어떻게 다를까? 위 쿼리 문장의 테스트 결과는 다음과 같다.

mysql > INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);
ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY'

mysql > INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);
ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY'

mysql > SELECT * FROM tab_myisam;
+--------+
| fdpk   |
+--------+
| 1      |
| 2      |
| 3      |
+--------+

mysql > SELECT * FROM tab_innodb;

+--------+
| fdpk   |
+--------+
| 3      |
+--------+

두 INSERT 문장 모두 PRIMARY KEY 중복 오류로 쿼리가 실패했다. 그런데 두 테이블의 레코드를 조회해 보면 MyISAM 테이블에는 오류가 발생했으므로 "1"과 "2"는 INSERT된 상태로 남아 있는 것을 확인할 수 있다. 즉 MyISAM 테이블에 INSERT 문장이 실행되면서 차례대로 "1"과 "2"를 저장하고, 그다음 "3"을 저장하려고 하는 순간 중복 키 오류(이미 "3"이 있기 때문)가 발생한 것이다. 하지만 MyISAM에서 실행되는 쿼리는 이미 INSERT된 "1"과 "2"를 그대로 두고 쿼리 실행을 종료해 버린다.

MEMORY 스토리지 엔진이나 MERGE 스토리지 엔진을 사용하는 테이블도 MyISAM 테이블과 동일하게 작동한다. 하지만 InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원상태로 만들어 둔다는 트랜잭션의 원칙대로 INSERT 쿼리 문장을 실행하기 전 상태로 그대로 복구했다. MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial Update)라고 표현하며, 이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는 데 상당히 어려운 문제를 만들어 낸다.

어떤 사용자는 (특히 트랜잭션이 선택 사항인 MySQL의 경우) 트랜잭션을 상당히 골치 아픈 기능쯤으로 생각하지만 트랜잭션이란 그만큼 애플리케이션 개발에서 고민해야 할 문제를 줄어 주는 아주 필수적인 DBMS의 기능이라는 점을 기억해야 한다. 부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요해질 수 있다. 실행하는 쿼리가 하나 뿐이라면 재처리 작업은 간단할 것이다. 하지만 2개 이상의 쿼리가 실행되늰 쿼리라면 실패에 대한 재처리 작업은 아래 예제와 같이 상당한 고민 거리가 될 것이다.

INSERT INTO tab_a ...;
IF(_is_insert1_succeed){
    INSERT INTO tab_b ...;
    IF(_is_insert2_succeed){
        // 처리 완료
    }ELSE{
        DELETE FROM tab_a WEHRE ...;
        IF(_is_delete_succeed){
            // 처리 실패 및 tab_a, tab_b 모두 원상 복구 완료
        }ELSE{
            // 해결 불가능한 심각한 상황 발생
            // 이제, 어떻게 해야 하나?
            // tab_b에 INSERT는 안 되고, 하지만 tab_a에 INSERT되어 버렸는데, 삭제는 안 되고 ...
        }
    }
}

위 애플리케이션 코드가 장난처럼 작성해 둔 코드 같지만 트랜잭션이 지원되지 않는 MyISAM에 레코드를 INSERT할 때 위와 같이 하지 않으면 방법이 없다(만약 코드를 이렇게 작성하지 않았다면 반드시 부분 업데이트의 결과로 쓰레기 데이터가 테이블에 남아 있을 가능성이 있다. 하지만 위의 코드를 트랜잭션이 지원되는 InnoDB에서 한다고 가정하면 다음과 같은 완벽한 코드로 구현이 가능하다.

try{
    START TRANSACTION;
    INSERT INTO tab_a;
    INSERT INTO tab_b;
    COMMIT;
}catch(exception){
    ROLLBACK;
}

2. 주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 이는 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미다. 다음 내용은 사용자가 게시판에 게시물을 작성한 후 저장 버튼을 클릭했을 때 서버에서 처리하는 내용을 순서대로 정리한 것이다. 물론 실제로는 이 내용보다 훨씬 복잡하고 많은 내용이 있겠지만 여기서는 설명을 단순화하기 위해 조금 간단히 나열해봤다.

1) 처리 시작
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드된 파일의 확인 및 저장
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 전송
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
<= 데이터베이스 커넥션 반납
10) 처리 완료

위 처리 절차 중에서 DBMS의 트랜잭션 처리에 좋지 않은 영향을 끼치는 부분을 나눠서 살펴보자.

• 실제로 많은 개발자가 데이터베이스의 커넥션을 생성(또는 커넥션 풀에서 가져오는)하는 코드를 1번과 2번 사이에 구현하며 그와 동시에 "START TRANSACTION" 명령으로 트랜잭션을 시작한다. 그리고 9번과 10번 사이에서 트랜잭션을 COMMIT하고 커넥션을 종료(또는 커넥션 풀로 반납)한다. 실제로 DBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작된다는 것을 알 수 있다. 그래서 2번과 3번, 그리고 4번의 절차가 아무리 빨리 처리된다 하더라도 DBMS의 트랜잭션으로 포함시킬 필요는 없다. 일반적으로 데이터베이스 커넥션은 개수가 제한적이라서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다. 그리고 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.

• 그리고 더 큰 위험은 8번 작업이라고 볼 수 있다. 메일 전송이나 FTP 파일 전송 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버 뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생할 것이다.

• 또한 이 처리 절차에서 DBMS의 작업이 크게 4개가 있다. 사용자가 입력한 정보를 저장하는 5번과 6번 작업은 반드시 하나의 트랜잭션으로 묶어야 하며, 7번 작업은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함할 필요는 없다. 그리고 9번 작업은 조금 성격이 다르기 때문에 이전 트랜잭션(5번과 6번 작업)에 함께 묶지 않아도 무방해 보인다. 이러한 작업은 별도의 트랜잭션으로 분리한느 것이 좋다. 그리고 7번 작업은 단순 조회라고 본다면 별도로 트랜잭션을 사용하지 않아도 무방해 보인다.

이러한 내용을 적용해서 위의 처리 절차를 다시 한번 설계해 보자.

1) 처리 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 발생 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
=> 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
=> 트랜잭션 시작
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
=> 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
<= 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10) 처리 완료

위에서 보여준 예제가 최적의 트랜잭션 설계는 아닐 수 있으며, 구현하고자 하는 업무의 특성에 따라 크게 달라질 수 있다. 여기서 설명하려는 바는 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 한다는 것이다. 또한 프로그램에서 라인 수는 한두 줄이라 하더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다. 이런 실수로 인해 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 나타나곤 한다.

참고

• Real MySQL