Real MySQL - (3) 트랜잭션 개념과 주의사항

MySQL의 동시성에 영향을 미치는 잠금과 트랜잭션, 트랜잭션의 격리수준을 살펴보려한다.

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

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

• 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려 할 때
• 잠금이 없으면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 된다
• 결과적으로 해당 레코드의 값을 예측할 수 없는 상태가 된다.

잠금은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다.

격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

트랜잭션 관점에서의 InnoDB와 MyISAM 차이

• InnoDB : 트랜잭션 지원
  • 쿼리 중 일부라도 오류가 발생하면 롤백해 전체를 원 상태 복구
• MyISAM : 트랜잭션 미지원
  • 일부 오류 발생 시 발생 전까지 실행된 쿼리들을 롤백하지 않아, 부분 업데이트(Partial Update)가 발생
  • 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 내는 포인트
  • 이 경우, 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요해지고, 2개 이상의 여러 쿼리가 실행되는 경우라면 더욱 큰 고민거리가 될 것

트랜잭션 사용 시 주의사항

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

사용자가 게시판에 게시물을 작성한 후 저장 버튼을 클릭하는 시나리오

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

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

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

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

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

위의 3가지 부분을 보완해서 위의 처리 절차를 다시 설계하면 아래와 같다

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

• 결과적으로, 이 예제를 통해 설명하려는 것은 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 한다는 것이다.
• 또한, 프로그램의 코드에서 라인 수는 한두 줄이라고 하더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야한다.
• 이런 실수로 인해 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 발생한다.

출처 : Real MySQL 8.0 (1권)개발자와 DBA를 위한 MySQL 실전 가이드