Real MySQL 05. 트랜잭션과 잠금

솔트커피·2022년 10월 16일

Real MySQL mysql rdb

들어가기 전에.

트랜잭션(Transaction)

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

데이터 정합성이란?

어떤 데이터들이 값이 서로 일치하는 상태.
비정규형을 사용해 이상현상이 발생하면 정합성이 지켜지지 않는다.

잠금(Lock)

동시성을 제어하기 위한 기능
여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할
- 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 됨

격리 수준(Isolation level)

하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨

5.1 트랜잭션

이번 절에서는 트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB의 처리 방식 차이를 잠깐 살펴보고자 한다. 그리고 트랜잭션을 사용할 경우 주의할 사항도 함께 살펴보겠다.

5.1.1 MySQL에서의 트랜잭션

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

p156 ~ 157
MyISAM 테이블은 InnoDB 테이블과 다르게 트랜잭션을 사용하지 않아 부분 업데이트가 일어난다.
이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 낸다.

부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있다. 실행하는 쿼리가 하나뿐이라면 재처리 작업은 간단하겠지만 그 이상의 쿼리가 실행되는 경우 상당한 고민거리가 될 것이다.

비즈니스 로직 처리로 이미 IF, ELSE로 가득한 프로그램 코드에 이런 데이터 클렌징 코드까지 넣어야 한다는 것은 정말 암담한 일일 것이다.

5.1.2 주의사항

가능하면 트랜잭션의 범위를 최소화하는 것이 좋다.

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

실제로 많은 개발자가 DB 커넥션 생성을 1과 2 사이에 구현하지만 실제로 DBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작된다는 것을 알 수 있다.
2, 3, 4번 절차가 아무리 빨리 처리된다고 해도 DBMS 트랜잭션에 포함 시킬 필요는 없다.
일반적으로 DB 커넥션은 개수가 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질 수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다.

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

또한 이 처리 절차에는 DBMS 작업이 크게 4개가 있다.

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

문제가 될만한 부분 세 가지를 보완한 처리 절차

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

5.2 MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 두 가지로 나눌 수 있다.

스토리지 엔진 레벨
- 스토리지 엔진 간 상호 영향을 미치지 않음
MySQL 엔진 레벨
- MySQL 엔진 - 스토리지 엔진을 제외한 나머지 부분
- 모든 스토리지 엔진에 영향을 미침

5.2.1 글로벌 락

FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있다.
MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
한 세션에서 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 대까지 해당 문장이 대기 상태로 남는다.
MySQL 서버 전체에 영향을 미친다. (작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미침)
MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

주의

글로벌 락은 MySQL 서버의 모든 테이블에 변경 작업을 멈추기 때문에 웹 서비스용으로 사용되는 서버에서는 가급적 사용하지 않는 것이 좋다.

InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없으므로 조금 더 가벼운 글로벌 락의 필요했다.
그래서 8.0 버전부터는 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

mysql> LOCK INSTANCE FOR BACKUP;
-- 백업 실행
mysql> UNLOCK INSTANCE

특성 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.

DB 및 테이블 등 모든 객체 생성 및 변경, 삭제
REPAIR TABLE과 OPTIMIZE TABLE 명령
사용자 관리 및 비밀번호 변경

하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용된다.

5.2.2 테이블 락

개별 테이블 단위로 설정되는 잠금이며 명시적 또는 묵시적으로 특정 테이블의 락을 획득 할 수 있다.
명시적으로는 LOCK TABLES table_name [ READ | WRITE ] 명령으로 획득할 수 있다.
명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납(해제) 할 수 있다.
명시적으로 테이블을 잠그는 작업은 글로벌 락처럼 온라인 작업에 상당한 영향을 미치기 때문에 특별한 상황이 아니면 사용할 필요가 거의 없다.
묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.
데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.
하지만 InnoDB 테이블의 경우, 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.
- 더 정확히는 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)일 경우에만 영향을 미친다.