Featured image of post 5.1 트랜잭션
Real MySQL

5.1 트랜잭션

Real MySQL 8.0

트랜잭션은 작업의 완전성을 보장해 주는 것이다.
논리적이 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어주는 기능이다.

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미있는 개념은 아니다. 트랜잭션은 하나의 논리적인 작업 셋이 100% 적용되거나(COMMIT) 아무것도 적용되지 않아야(ROLLBACK)함을 보장해 주는 것이다.

MySQL에서의 트랜잭션

MySQL에서 InnoDB와 달리 MyISAM과 MEMORY 스토리지 엔진은 트랜잭션을 제공하지않는다. 처리방식의 차이를 확인하고, 트랜잭션을 사용할 때 주의할 점을 알아야 한다.

1
2
3
4
5

CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUE (3);

CREATE TABLE tab_myInnodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;
INSERT INTO tab_myInnodb (fdpk) VALUE (3);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SET autocommit=ON;

INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
/* 
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' 
+----+
|fdpk|
+----+
|   1|
+----+
|   2|
+----+
|   3|
+----+
*/

INSERT INTO tab_myinnodb (fdpk) VALUES (1), (2), (3);
/* 
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' 
+----+
|fdpk|
+----+
|   3|
+----+
*/

두 테이블의 INSERT 문장이 프라이머리 키 중복 오류로 실패했으나, MyISAM 테이블은 INSERT 문장이 순차적으로 실행되고 즉시 반영된다. 따라서 1과 2를 저장하고 3을 저장할 때 오류가 발생하여 쿼리 실행이 종료되며, 기존에 실행되었던 INSERT로 테이블에 1, 2가 저장되었고 오류로 실패해도 그대로 유지된다. MEMORY 스토리지 엔진도 동일한 방식으로 처리된다.

반면 InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 복구한다.

MyISAM에서 발생한 이러한 현상을 부분 업데이트(Partial Update)라고 표현하며, 테이블 데이터의 정합성을 맞추기 위해 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 등 어려운 문제를 만들어 낸다.

주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게 트랜잭션의 범위를 최소화 하여, 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.

AS-IS

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

  • 데이터베이스의 커넥션 생성하고 트랜잭션을 시작하는 부분

    • 실제로 DBMS에 데이터를 저장하는 작업은 5번부터 시작되므로 2,3,4 작업이 아무리 빨리 처리된다고 하더라도 트랜잭션에 포함시킬 필요는 없다.
    • 일반적으로 데이터베이스 커넥션은 개수가 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 여유 커넥션의 개수는 줄어든다. 이에 따라 커넥션을 기다려야 하는 상황이 발생할 수 있다.

  • 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 작업

    • 8번에서 프로그램이 실행되는 동안 통신할 수 없는 상황이 발생한다면 웹 서버뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생하게 될 수 있으므로 트랜잭션 내에서 제거하는 것이 좋다.

  • 작업 단위 분리

    • 처리 되어야 하는 DBMS 작업을 (5,6), (7), (9) 총 3개로 분리해서 트랜잭션을 만들 수 있고, 7번이 단순 조회라면 트랜잭션을 사용하지 않아도 된다.

TO-BE

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

위와 같은 방식으로 개선 될 수 있다.