[Real MySQL 5장] 트랜잭션과 잠금

정훈희·2023년 9월 8일

Real MySQL 격리 수준 넥스트 키 락 레코드 락 잠금 트랜잭션

Real MySQL

목록 보기

2/5

MySQL에서의 트랜잭션

트랜잭션 이란?

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때를 말하는 것이 아니라, 하나의 작업 세트가 일부만 적용되지 않도록 함을 보장하는 것을 말한다.

즉, 전부 다 적용되어 COMMIT 되거나, 오류 발생으로 변경된 사항을 전부 ROLLBACK 함을 보장한다.

트랜잭션 설정 범위

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.

왜냐하면 DB의 커넥션 수가 제한적이고, 커넥션을 소유하는 시간이 길어질 수록 여유 커넥션 수는 줄어들기 때문이다.

아래 예시를 통해 트랜잭션의 적절한 범위에 대해 알아보자.

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

위와 같은 과정의 경우 DBMS에 관련되지 않은 작업(2,3,4,8)도 트랜잭션에 들어가 있다.

이런 경우, 쓸데없이 커넥션만 더 오래 소유하고 있게 되므로 트랜잭션에 포함시키지 않는 것이 좋다.

또한, 위 작업은 크게 글 저장 관련 작업과 알림 메일 발송 작업으로 나뉠 수 있는데, 성격이 다른 작업의 경우는 다른 트랜잭션으로 분리하는 것이 좋다.

그리고, 단순 조회의 경우는 트랜잭션에 포함될 필요는 없으므로 아래와 같이 과정을 바꿀 수 있다.

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

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

글로벌 락

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

글로벌 락의 범위는 MySQL 서버 전체이므로 테이블이나 데이터베이스가 다르더라도 영향을 받는다.

전체 데이터의 변경 작업을 멈추어야 할 때 적용할 수 있지만, 트랜잭션을 지원하므로 그럴 필요가 없고, 글로벌 락은 서버에 큰 영향을 미치므로 사용하지 않는 것이 좋다.

MySQL 8.0 부터는 조금 더 가벼운 글로벌 락인 백업 락이 생겼다.

테이블 락

개별 테이블 단위로 설정되는 잠금을 말한다.

명시적 테이블 락
```
// 테이블 락 획득
LOCK TABLES table_name [ READ | WRITE ];

// 테이블 락 반납
UNLOCK TABLES;
```
글로벌 락과 마찬가지로 서버에 큰 영향을 미치고, 사용할 필요가 거의 없다.
묵시적 테이블 락 InnoDB는 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하므로 스키마를 변경하는 DDL 쿼리의 경우에만 테이블 락이 설정된다.

네임드 락

// mylock 이라는 문자열에 대해 잠금 획득
SELECT GET_LOCK('mylock', 2);

// mylock 이라는 문자열에 대해 잠금이 설정되어 있는지 확인
SELECT IS_FREE_LOCK('mylock');

// mylock 이라는 문자열에 대해 획득한 잠금을 반납
SELECT RELEASE_LOCK('mylock');

사용자가 지정한 문자열에 대해 잠금을 설정하는 것을 말한다.

자주 사용되진 않고, 여러 클라이언트가 상호 동기화를 처리해야 하는 경우에 사용된다.

ex) 1대의 DB서버에 여러 웹 서버가 접속하는 서비스에서 여러 웹 서버가 어떤 정보를 동기화 하는 경우

위 예시와 같이 여러 서비스가 접속해 있는 상황에서 한번에 많은 데이터를 수정하는 경우 데드락의 원인이 된다.

→ 이때 동일한 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸면 데드락을 막을 수 있다.

메타데이터 락

DB객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금을 말한다.

명시적으로는 획득이 불가능하고, RENAME TABLE table_1 TO table_2 이런 식으로 테이블의 이름을 변경할 때 자동으로 획득한다.

// 1. 두 RENAME 작업을 각각의 문장으로 실행
RENAME TABLE rank TO rank_backup;
RENAME TABLE rank_new TO rank;

// 2. 두 RENAME 작업을 하나의 문장으로 실행
RENAME TABLE rank TO rank_backup, rank_new TO rank;

1번의 경우는 짧은 시간동안 rank 테이블이 존재하지 않는 시간이 생겨 Table not found 'rank' 오류 발생하지만 2번과 같이 하나의 문장으로 실행하면 정상적으로 RENAME 작업이 실행된다.

InnoDB 스토리지 엔진의 잠금

InnoDB는 MySQL에서 제공하는 잠금과는 별개로 레코드 기반의 잠금 방식을 탑재하고 있다.

→ 동시성 처리가 매우 뛰어나다.

레코드 락

레코드 자체만을 잠그는 것을 말한다.

InnoDB는 레코드 자체가 아닌 인덱스의 레코드를 잠근다. (인덱스 생성을 안했어도 자동 생성되는 클러스터 인덱스를 이용하여 잠근다.)

갭 락

레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

→ 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어할 수 있다.

갭 락 자체 보단 넥스트 키 락의 일부로 자주 사용된다.

넥스트 키 락

레코드 락과 갭락을 합친 잠금이다.

바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장한다.

→ 넥스트 키 락으로 인해 데드락을 발생하는 일이 자주 발생하면 바이너리 로그 포맷을 ROW 형태로 설정하면 좋다.

자동 증가 락

AUTO_INCREMENT 칼럼이 사용된 테이블에 여러 레코드가 INSERT될 경우 각 레코드는 중복되지 않고 순차적인 값을 가져야하는데 이를 위해 자동 증가 락을 사용한다.

시스템 변수를 통해 서버가 INSERT되는 레코드 건수를 정확히 예측할 수 있을 때는 자동 증가 락 대신 래치(뮤텍스)를 이용하여 처리하게 할 수 있다.

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아닌 인덱스를 잠그는 방식으로 처리된다.

// employees 테이블의 first_name 컬럼에만 index가 있는 상태
UPDATE employees
SET hire_date = NOW()
WHERE first_name = 'Georgi' AND last_name = 'Klassen';
// -> 1개의 row 업데이트

위 상황에서는, first_name이 Georgi인 모든 레코드를 잠그고 last_name이 Klassen인 레코드를 찾는다.

→ 인덱스가 적절히 설정되어 있지 않으면 잠금의 범위가 커져서 동시성이 떨어진다.

만약 아예 인덱스가 없다면 테이블을 풀스캔하며 UPDATE 작업을 하는데, 이 과정에서 모든 레코드를 잠궈버린다.

→ 잠금의 범위를 최소화할 수 있도록 인덱스 설계를 잘 해야한다.

MySQL의 격리 수준

트랜잭션의 격리 수준이란, 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 다루는 데이터를 볼 수 있게 허용할지를 결정하는 것을 말한다.

격리 수준의 종류와 세 가지 문제점

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O (InnoDB는 X)
SERIALIZABLE	X	X	X

4가지의 격리 수준에서 아래로 갈 수록 격리 수준이 높아지고 동시 처리 성능이 떨어진다.

READ UNCOMMITTED는 거의 사용X, SERIALIZABLE는 동시성이 중요한 경우엔 거의 사용하지 않는다.

격리 수준의 종류

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT, ROLLBACK 여부에 상관없이 다른 트랜잭션에서 조회할 수 있다.

→ 아직 COMMIT되지 않은 내용이 조회되는 DIRTY READ 문제가 발생할 수 있다.

→ 만약 오류로 인해 ROLLBACK 되더라도 이미 조회한 다른 사용자는 잘못된 정보를 가지게 된다.
READ COMMITTED

READ UNCOMMITTED 격리 수준에서는 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

새로운 데이터가 들어오면 변경 전 데이터는 언두 로그에 저장하고, COMMIT 되기전에 다른 트랜잭션에서 조회하면 언두 로그에 있는 변경 전 데이터를 반환한다.

→ 하나의 트랜잭션 안에서 동일한 SELECT 쿼리로 데이터를 요청하면 항상 같은 결과를 가져오지 못하는 문제인 NON-REPEATABLE READ 문제가 발생할 수 있다.

일반적인 웹 서비스에선 큰 문제가 아닐 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.

참고로, 오라클 DBMS의 기본 격리 수준이자, 온라인 서비스에서 가장 많이 사용되는 격리 수준이다.
REPEATABLE READ

REPEATABLE READ 격리 수준에서는 트랜잭션 Id를 기준으로 자신 이후에 발생한 트랜잭션에서의 변경사항은 읽지 않는다.

→ 동일 트랜잭션 내에서는 동일한 결과를 보여줌을 보장한다.

원래 REPEATABLE READ는 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 PHANTOM READ 현상이 발생하지만 InnoDB에선 일부 특별한 쿼리(FOR UPDATE 등)를 제외하고는 발생하지 않는다.

참고로 InnoDB에서 기본으로 사용되는 격리 수준이다.
SERIALIZABLE

SERIALIZABLE 격리 수준에선 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없다.

읽기 작업에도 읽기 잠금을 획득해야하므로 동시 처리 성능이 매우 떨어진다.

InnoDB는 갭 락과 넥스트 키 락 덕분에 PHANTOM READ가 발생하지 않기 때문에 사용할 이유가 없다.