MySQL 트랜잭션 및 잠금 동작 원리

이전 글: https://gojs.tistory.com/38

MySQL 스토리지 엔진 아키텍처

 

트랜잭션

논리적인 작업이 모두 적용 되거나 아예 적용되지 않아야 함을 보장해주는 것이다. (원자성)
트랜잭션을 미지원하는 경우 여러 작업이 동시에 수행될 때 각 작업의 성공, 실패에 대해 모두 대응해야한다.
 

주의사항

트랜잭션 범위는 최소화하는 것이 좋다.

1) 처리 시작
-- 트랜잭션 시작(BEGIN)
2) 사용자 로그인 여부 확인
3) 사용자 글쓰기 내용 오류 확인
4) 첨부로 업로드된 파일 확인 및 저장
# INSERT
5) 사용자 입력 내용 DBMS 저장
# INSERT
6) 첨부파일 정보 DBMS 저장
# SELECT
7) 저장된 내용 DBMS에서 조회
# NETWORK
8) 게시물 등록에 대한 알림 메일 발송
# INSERT
9) 알림 발송 이력 DBMS 저장
-- 트랜잭션 종료(COMMIT)
10) 처리 완료

커넥션을 맺는 시점과 끝나는 시점에 트랜잭션을 시작하고 종료하는 위와 같은 프로세스는 많은 문제를 가지고 있다.

1. 트랜잭션을 오래 가진다.
   • 2, 3, 4 작업 중 DBMS에는 어떠한 작업도 수행되지 않는다.
   • 트랜잭션 시간이 길어지면 더 많은 커넥션이 필요하기에 시스템 비용이 증가한다.
2. 네트워크 통신 작업이 트랜잭션 사이에 발생한다.
   • 통신이 실패하는 경우 DBMS에 영향을 끼친다.
3. 원자성이 보장되지 않아도 되는 작업이 하나의 트랜잭션으로 처리된다.
   • 5, 6 작업은 하나의 기능에 발생하는 두 가지 작업이다.
   • 7 작업은 원자성이 꼭 보장되어야 하는 작업은 아니다.
   • 9 작업은 별도의 기능에 발생하는 작업이다.

이와 같은 문제를 해결한 개선된 아래의 프로세스를 보자.

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

 

MySQL 엔진 잠금

잠금은 크게 MySQL 엔진 잠금, 스토리지 엔진 잠금으로 구분할 수 있다.
MySQL 엔진 잠금은 모든 스토리지에 영향을 미치지만 스토리지 엔진 잠금은 다른 스토리지에 영향은 없다.

 

글로벌 락

글로벌 락을 걸면 해제 될 때까지 MySQL 서버의 모든 변경 작업을 멈춘다.
FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있다.
→ 조금 더 가벼운 글로벌 락이 필요하여 백업 락이 도입되었다.
 

테이블 락

테이블 단위로 설정되는 잠금이다.
LOCK TABLES table_name [READ | WRITE] 명령으로 획득할 수 있다.
MyISAM이나 MEMORY 스토리지 엔진의 경우 데이터 변경 쿼리를 실행하면 발생한다. (테이블 락 → 데이터 변경 → 테이블 락 해제)
InnoDB 스토리지 엔진의 경우 레코드 기반 잠금이 수행된다. (테이블 락이 DML에는 무시)
 

네임드 락

네임드 락은 특정 문자열에 대해 설정되는 잠금이다.
데이터베이스 객체가 아닌 사용자가 임의로 사용하는 문자열에 대한 잠금이다.

mylock이라는 문자열에 대한 잠금을 획득했다.
 

mylock 문자열에 대해 잠금을 해제했다.
동시에 여러 작업을 수행하는 경우 작업별 데이터에 네임드 락을 걸게 되면 데드락을 최소화 할 수 있다.
 

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금이다.
RENAME TABLE tab_a TO tab_b 명령을 수행하는 경우에 자동으로 두 개 테이블의 이름에 대해 잠금을 획득한다.
 
만약 특정 테이블을 백업하기 위해서 아래와 같이 쿼리를 실행시켰다고 가정해보자.

-- 기존 테이블 -> 백업 테이블
RENAME TABLE rank TO rank_backup;

-- 새로운 데이터가 담긴 테이블 -> 운영 테이블
RENAME TABLE rank_new TO rank;

이와 같이 쿼리를 실행하는 경우 두 개의 쿼리 중간에 rank 테이블을 참조하는 쿼리는 오류를 발생시킨다.
 

-- 동시에 작업 (메타데이터 락)
RENAME TABLE rank TO rank_backup, rank_new TO rank;

그러나 이렇게 실행되는 쿼리는 두 개의 작업을 한번에 수행하며 메타데이터 락이 설정된다.
따라서 두 개 작업 중간에 실행되는 쿼리는 락이 해제될 때까지 기다렸다가 위의 작업이 끝나면 정상수행되게 된다.
 

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진 잠금은 레코드 기반의 잠금 방식으로 MySQL 엔진 잠금에 비해 뛰어난 동시성 처리를 제공한다.
InnoDB 스토리지 엔진 잠금을 모니터링하기 위해서 information_schema.INNODB_TRX / INNODB_LOCKS / INNODB_LOCK_WAITS 테이블을 활용할 수 있다.
(구 버전의 경우에는 모니터링이 어렵다)
 

InnoDB 스토리지 엔진 락

 

레코드 락

레코드 락은 레코드를 잠그는 락이다. 정확하게는 테이블의 레코드가 아닌 인덱스의 레코드를 잠근다.

-- employees table의 index는 last_name_idx(last_name)이 있다고 가정하자.
UPDATE employees
SET hire_date = NOW()
WHERE first_name = "jaeseok"
AND last_name = "go";

MySQL에서 위와 같은 쿼리를 실행한다고 하자.
employees 테이블에 last_name이 go인 데이터가 300건이 있다고 가정하자.
해당 쿼리를 수행할 때 인덱스의 레코드를 잠그기 때문에, last_name이 go인 300건 데이터가 모두 잠기게 된다.
 
만약 인덱스 구조가 (last_name, first_name)이라면 어떻게 될까?
last_name이 go 이고 first_name이 jaeseok인 데이터가 3건이 있다고 가정하자.
해당 쿼리를 수행할 때 3건의 데이터만 잠기게 된다. (동시성이 증가한다)
 

인덱스는 단일 쿼리의 성능을 향상시킬 뿐만 아니라 여러 작업이 동시에 일어나는 경우의 동시성도 증가시키므로 다양한 관점에서 비용을 감소시킬 수 있다.
따라서 인덱스 설계는 매우 중요하다

 

갭 락

레코드와 레코드 사이에 새로운 값이 insert되는 것을 방지하기 위해 레코드와 바로 인접한 레코드 사이의 간격을 잠근다.
 

넥스트 키 락

레코드 락과 갭 락을 합친 형태이다.
바이너리 로그에 기록되는 쿼리가 replica server에서 실행되는 경우와 동일한 결과를 만들기 위해 사용된다.
(데드락의 원인이 되기도 하여 최대한 줄이는 것이 좋다)
 

자동 증가 락

AUTO_INCREMENT 컬럼에 중복되지 않는 일련번호를 제공하기 위해 사용되는 락이다.
5.0 버전까지는 두 개의 INSERT 쿼리가 발생했을 때, 하나의 쿼리를 기다렸다가 다음 쿼리가 실행된다.
5.1 버전 이상에서는 innodb_autoinc_lock_mode 시스템 변수를 사용하여 작동 방식을 변경할 수 있다.

• 0
  • 이전 버전과 마찬가지로, 모든 INSERT 쿼리는 자동 증가 락을 사용
• 1
  • INSERT 쿼리 중 레코드 건수를 정확하게 카운트할 수 있는 경우에는 뮤텍스를 이용해 처리 (빠름)
  • INSERT-SELECT 쿼리와 같이 건수 예측이 불가능한 경우에는 자동 증가 락 사용
  • 대량 INSERT가 수행될 때는 여러 개의 자동 증가 값 할당받아 레코드에 사용
  • 하나의 INSERT 안에서 작업된 레코드는 모두 연속된 값을 할당 → 연속 모드
• 2
  • 항상 뮤텍스를 이용해 처리
  • 동시 처리 성능이 높다
  • 연속된 증가 값을 보장하지 않음 → 인터리빙 모드

 

트랜잭션 격리 레벨

트랜잭션의 격리 레벨이란 여러 트랜잭션이 동시에 처리되는 경우 다른 트랜잭션에서 이용중인 데이터를 Read하는 정책이다.

트랜잭션 격리 레벨

READ UNCOMMITTED

각 트랜잭션의 변경 내용이 COMMIT 되기 전의 데이터로 보이는 레벨이다.

READ UNCOMMITED 격리 레벨

트랜잭션 A가 해당 테이블에 값을 insert하고 커밋하지 않은 상태에서 트랜잭션 B에서 해당 값을 읽었다.
위의 사례는 정상적으로 커밋되었기 때문에 READ UNCOMMITED의 동시성이 잘 발현된 사례라고 볼 수 있다.
 
하지만 트랜잭션 A가 최종적으로 롤백되었다면 트랜잭션 B는 잘못된 값을 읽은 것이기 때문에 올바르지 않은 데이터를 읽은 것이 된다.
이를 dirty read라고 하며 이 때문에 READ UNCOMMITED는 데이터의 읽기 정합성이 중요하다면 사용하지 않는 것이 좋다.
 

READ COMMITTED

READ COMMITTED 격리 레벨은 dirty read가 발생하지 않도록 커밋이 완료된 데이터만 조회한다.

READ COMMITED 격리 레벨

MySQL은 Undo Log로 MVCC 기능을 구현한다. MVCC란 DBMS에서 동시성을 제어하기 위해 이전의 데이터의 스냅샷을 보관하는 방법이다.
따라서 커밋되지 않은 데이터는 Undo Log에 보관하고 있으며 READ COMMITED 격리 레벨에서의 읽기는 Undo Log의 데이터를 조회한다. (따라서 dirty read가 발생하지 않는다)
 

non-repeatable read 발생 케이스

그러나 READ COMMITED에서도 읽기 정합성이 깨지는 현상이 발생하기도 한다.
위와 같이 큰 트랜잭션에서 두 번의 읽기가 발생하는 경우 그 사이에 데이터가 변경된다면 서로 다른 결과를 조회하게 된다.
이와 같은 부정합을 non-repeatable read라고 한다.
 

REPEATABLE READ

InnoDB 엔진에서 기본으로 사용되는 격리 수준으로 non-repeatable read 부정합을 방지한다.

Repeatable Read 격리 레벨

InnoDB의 트랜잭션은 고유한 ID를 가지며 이 ID를 기반으로 MVCC 기능이 구현된다.
따라서 Repeatable Read 격리 레벨의 조회는 테이블에 저장된 데이터가 조회하는 트랜잭션보다 이후의 트랜잭션인지 체크한다.
이후의 트랜잭션이라면 Undo Log 영역에 있는 데이터를 조회함으로써 non-repeatable read 부정합을 방지한다.
 
그러나 Repeatable Read 격리 레벨에서도 부정합은 발생한다.
여러가지 시나리오가 존재하며 결과적으로는 처음에는 조회되지 않던 데이터가 조회되는 것으로 phantom read라고 한다.
그 중 한 가지 시나리오는 먼저 생성된 커맨드 트랜잭션이 후에 생성된 쿼리 트랜잭션의 중간에 커밋이 되는 경우이다.
 

SERIALIZABLE

가장 단순하며 가장 엄격한 격리 수준이다.
조회를 하기 위해서는 공유 잠금을 획득해야하고, 다른 트랜잭션도 그러한 레코드를 변경하지 못한다.
phantom read 부정합이 발생하지 않는다.
하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락을 활용하여 REPEATABLE READ 격리 레벨에서도 phantom read 부정합이 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없다.

 

다음 글: https://gojs.tistory.com/40

MySQL에서 옵티마이저 동작하는 원리