Mysql 에서 동시성에 영향을 주는 잠금(lock) 과 트랜잭션, 그리고 트랜잭션의 격리수준(isolation level) 을 살펴보겠다.

잠금(lock) 과 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.

4.1 트랜잭션

4.1.1 Mysql에서의 트랜잭션

트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(commit을 실행했을 때) 또는 아무것도 적용되지 않아야 (RollBack 또는 Transection Rollback 시키는 오류가 발생했을 때) 함을 보장해 주는 것이다.

1
2
3
4
5
mysql> create table tab_myisam (fdpk int not null, primary key(fdpk)) engine=MyISAM;
mysql> insert into tab_myisam (fdpk) values (3);

mysql> create table tab_innodb (fdpk int not null, primary key (fdpk)) engine=innoDB;
mysql> insert into tab_innodb (fdpk) values (3);

위와같이 테스트 테이블을 생성 후 auto-commit 모드에서 다음 쿼리 문장을 실행해 보자

1
2
mysql> insert into tab_myisam (fdpk) values (1), (2), (3);
mysql> insert into tab_innodb (fdpk) values (1), (2), (3);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> Insert into tab_myisam (fdpk) values (1), (2), (3);
error 1062 ......

mysql> insert into tab_innodb (fdpk) values (1), (2), (3);
error 1062 ...

mysql> select * from tab_myisam;
+-------+
| fdpk  |
+-------+
|    1  |
|    2  |
|    3  |
+-------+

mysql> select * from tab_innodb;
+-------+
| fdpk  |
+-------+
|    3  |
+-------+

MyISAM의 경우 오류가 발생했음에도 1,2 가 들어가 있다. MyISAM 테이블에 insert 문장이 실행되면서 차례대로 1,2를 저장하고 그 다음 3을 저장하려는 순간 중복키 오류가 발생한 것이다.

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

MyISAM에서 발생하는 이런 현상을 부분 업데이트(partial update)라고 하며, 이것은 테이블 데이터의 정합성을 맞추는데 상당한 어려움을 유발시킨다.

4.1.2 주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 이는 프로그램 코드에서 트랜잭션의 범위를 최소화 하라는 의미다. 설명을 위해 간단한 예를 들어보자

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

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

  • 커넥션 생성을 1과 2번 사이에 구현했는데 DB의 사용은 5~9 사이에 이루어진다. DB의 커넥션은 제한적이라 커넥션 유지 부분을 짧게 작성 할 수록 좋다.
  • 8번의 메일전송이나 FTP 파일 전송작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게든 트랜잭션 내에서 제거하는 것이 좋다. 프로그램 실행 중에 메일서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생 할 것이다.
  • 위의 과정 중 5,6번 작업은 반드시 하나의 트랜잭션으로 묶어야 하며, 7번작업은 저장된 데이터의 단순 확인작업이므로 트랜잭션에 포함시킬 필요 없다. 9번작업은 이전 5,6 번 작업과 성격이 다르기 때문에 같은 트랜잭션으로 묶을 필요가 없다.

이러한 내용을 적용시켜 재설계 해보자

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