해당 포스트는
MySQL를 학습하며 필요한 내용을 정리한 포스트입니다.
🌈 5. 트랜잭션과 잠금
💻 5.1 트랜잭션
트랜잭션은 작업의 완전성을 보장해 주는 것이다.
즉 논리적인 작업 셋을 모두 완벽하게 처리 하거나, 처리하지 못할 경우에는 모두 원 상태로 복구 하는 작업의 일부분만 적용되는 현상(Partial update)이 발생하지 않도록 해주는 기능으로 트랜잭션 4가지 속성 중 Atomicity(원자성)에 해당하며 All or Nothing 형태를 의미한다.
잠금(Lock)과 트랜잭션은 서로 비슷한 개념 같지만 잠금(Lock)은 동시성 제어를 하기 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.
잠금은 여러 세션이 동시에 동일한 레코드나 테이블의 변경을 요청 할 경우 순서대로 한 시점에 하나의 세션만 변경할 수 있게 해주는 역할을 하게 된다.
격리 수준(isolation level)이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.
🍳 5.1.1 MySQL 에서의 트랜잭션
트랜잭션은 여러 개의 변경 작업을 묶어서 수행하는 경우만 의미 있는 개념은 아니다.
트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있거나 두 개 이상의 쿼리가 있거나 관계 없이 논리적인 작업 셋 자체가 100% 적용 되거나, 아무것도 적용되지 않아야 함을 보장 해주는 것이다.
InnoDB 스토리지 엔진은 사용하기 복잡하고 번거롭다고 생각할 수도 있지만, MyISAM 이나 MEMORY 와 같은 트랜잭션을 지원하지 않는 스토리지 엔진의 테이블을 사용할 경우 더 많은 고민거리를 만들어 낸다.
MyISAM 과 InnoDB 스토리지 엔진의 테이블의 트랜잭션 차이를 확인 해보자.
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
26
27
28
29
mysql> create table tb_tab_myisam(col int primary key) ENGINE=MyISAM;
mysql> insert into tb_tab_myisam values (3);
mysql> create table tb_tab_innodb(col int primary key) ENGINE=InnoDB;
mysql> insert into tb_tab_innodb values (3);
mysql> insert into tb_tab_myisam values(1),(2),(3);
Error Code: 1062. Duplicate entry '3' for key 'tb_tab_myisam.PRIMARY'
mysql> insert into tb_tab_innodb values(1),(2),(3);
Error Code: 1062. Duplicate entry '3' for key 'tb_tab_innodb.PRIMARY'
-- MyISAM
mysql> select * from tb_tab_myisam;
+-----+
| col |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
-- InnoDB
mysql> select * from tb_tab_innodb;
+-----+
| col |
+-----+
| 3 |
+-----+
Insert 과정에서 2개의 구문 모두 1062 에러(PK 중복) 으로 에러가 발생 되기는 한다.
다만 2개의 테이블을 조회해보면 그 결과가 다르다.
MyISAM 테이블은 PK가 중복이 되어서 에러가 발생되었지만 1,2는 INSERT된 상태로 남아 있다.
MyISAM은 INSERT문 실행시 차례대로 1,2를 저장하고 그 다음 3을 저장 하려는 순간 에러가 발생된 것이다. 이미 Insert 된 1,2는 그대로 두고 3에서 에러가 발생되면서 쿼리가 그대로 종료가 된다.
MyISAM 이러한 현상을 Partial Update 라고 표현하며 데이터 정합성을 맞추는데 많은 어려움을 만든다.
MyISAM 에서 이러한 현상을 방지 하기 위해서 IF..ELSE에 데이터에 대한 확인 및 클렌징 코드(Rollback)까지 준비해야 한다.
하지만 InnoDB 는 아래와 같이 간단하게 로직 구현이 가능하다.
1
2
3
4
5
6
7
8
try {
start transaction;
insert into tab_a ..;
insert into tab_b ..;
commit;
} catch(exception {
rollback;
}
🍳 5.1.2 주의사항
트랜잭션도 DBMS 커넥션과 유사하게 꼭 필요하고 최소한의 코드에만 적용하는 것이 좋다.
이 말은 프로그램 코드에서 트랜잭션의 범위를 최소화 하라는 의미
게시판에서 게시물 작성 완료 버튼을 눌렀을때 일련의 처리 로직 중 에서의 간략한 예시
1) 처리시작
- 데이터베이스 커넥션 생성
- 트랜잭션 시작
2) 사용자 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
- 트랜잭션 종료(COMMIT)
- 데이터베이스 커넥션 반납
10) 처리 완료
위의 내용에서 좋지 않은 부분은 살펴보자
실제로 DBMS에서 데이터를 저장하는 작업(트랜잭션)은 5번 부터 시작된다. 그렇기 떄문에 2~4번의 절차가 아무리 빨리 처리된다고 해도 DBMS의 트랜잭션에 포함시킬 필요는 없다.
일반적으로 데이터베이스 커넥션은 개수가 제한적이기 때문에 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다. 그리고 어느 순간에는 각 단위 프로그램에서 커넥션을 기다려야 하는 상황이 발생할 수도 있다.
더 큰 위험은 8번 작업이라고 할 수 있다.
메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생한다.
위 처리 절차에는 DBMS의 작업이 크게 4개가 있다.
- 사용자가 입력한 정보를 저장하는 5번과 6번은 반드시 하나의 트랜잭션으로 묶어야 한다.
- 7번 작업은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함할 필요는 없다. 단순 조회라고 본다면 별도로 트랜잭션을 사용하지 않아도 무방해 보인다.
- 9번 작업은 작업의 성격이 조금 다르기 때문에 이전 트랜잭션과 함께 묶지 않아도 무방하다.
위 내용을 토대로 다시 설계해보면
1) 처리시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 발생 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
- 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
- 트랜잭션 시작
5) 사용자 입력 내용을 DBMS에 자정
6) 첨부 파일 정보를 DBMS에 저장
- 트랜잭션 종료(COMMIT)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
- 트랜잭션 시작
9) 알림 메일 발송이력을 DBMS에 저장
- 트랜잭션 종료(COMMIT)
- 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10) 처리완료
이 예제가 최적의 트랜잭션 설계는 아닐 수 있다.
업무의 특성과 구현이 필요한 로직 등에 따라서 트랜잭션 설계는 여러가지 면으로 작성될 수 있겠으나, 기본적으로 생각해야 할 부분으로 애플리케이션 코드에서 데이터베이스의 커넥션을 가지고 있는 범위 그리고 트랜잭션이 활성화 되어 있는 프로그램의 범위는 최소화하고 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 한다는 것이다.
📚 레퍼런스
백은빈, and 이성욱. Real MySQL 8.0 : 개발자와 DBA를 위한 MySQL 실전 가이드 1 / 백은빈, 이성욱 지음. (2021). Web.
백은빈, and 이성욱. Real MySQL 8.0 : 개발자와 DBA를 위한 MySQL 실전 가이드. 2 / 백은빈, 이성욱 지음 (2021). Web.