트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미있는 개념은 아니다. 하나의 논리적인 작업 세트에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 세트 자체가 100% 적용되거나 아무것도 적용되지 않아야함을 보장해주는 것이다.
5.1.1 주의 사항
트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.
1. 처리 시작
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
2. 사용자의 로그인 여부 확인
3. 사용자의 글쓰기 내용의 오류 여부 확인
4. 첨부로 업로드된 파일 확인 및 저장
5. 사용자의 입력 내용을 DBMS에 저장
6. 첨부 파일 정보를 DBMS에 저장
7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
8. 게시물 등록에 대한 알림 메일 발송
9. 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료(Commit)
<= 데이터베이스 커넥션 반납
10. 처리 완료
2,3,4번의 절차가 아무리 빨리 처리된다고 하더라도 DBMS의 트랜잭션에 포함시킬 필요는 없다. (커넥션 개수는 제한적)
8번과 같이 파일 전송 작업 또는 원격 서버와 통신하는 등의 작업은 트랜잭션 내에서 제거해야 한다. (메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐만 아니라 DBMS 서버까지 위험해지는 상황이 발생)
7번은 단순 조회이므로 트랜잭션에 포함할 필요가 없고, 9번 작업은 조금 성격이 다르기 때문에 하나의 트랜잭션에 묶지 않아도 무방해보인다.
5.2 MySQL 엔진의 잠금
5.2.1 글로벌 락
글로벌 락은 MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.
MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다. 따라서, 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다. 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수는 없지만, 테이블의 데이터 변경은 허용된다.
🏇 백업 락? 레플리카 서버? 레플리카 서버는 실시간으로 소스 서버의 데이터를 복제받으며, 주로 조회용이나 백업 전용으로 사용된다. 조회 트래픽을 분산시켜 소스 서버의 부하를 줄이고, 읽기 성능을 향상시키는 데에 큰 장점이 있다. 백업 시에는 '백업 락'을 걸어 레플리카 서버의 데이터를 안정적으로 덤프하며, 이 순간에는 복제를 일시 중단한다. 레플리카는 보통 바이너리 로그(binlog)를 기준으로 소스 서버의 변경 사항을 순차적으로 적용하며, 약간의 지연(Lag)이 있을 수 있다.
5.2.2 테이블 락
InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. 즉, InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다. -> 다른 요청에서 dml도 불가능
5.2.3 네임드 락
네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다. 네임드 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금으로, 자주 사용되지는 않는다. 매우 유연하고, 논리적 락이 가능하다는 장점이 있다.
5.2.4 메타데이터 락
메타데이터 락은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금으로, 명시적으로 획득하거나 해제할 수 있는 것이 아니다. -> 다른 요청에서 dml은 가능
⭐ 5.3 InnoDB 스토리지 엔진의 잠금
InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 일반 상용 DBMS와는 조금 다르게 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.
B-Tree 인덱스의 구조 인덱스는 페이지 단위로 저장되며, 인덱스 키를 바탕으로 항상 정렬된 상태를 유지한다. 정렬된 인덱스 키를 따라서 리프 노드에 도달하면 (인덱스 키, PK) 쌍으로 저장되어 있다.
다음과 같이 설계된 테이블이 있다고 하자.
CREATE TABLE employee (
emp_no INT NOT NULL AUTO_INCREMENT,
name VARCHAR(64),
PRIMARY KEY(emp_no),
INDEX idx_name (name)
) ENGINE=InnoDB;
idx_name 인덱스의 경우, name 값을 기준으로 정렬되어 있다. 그리고 데이터를 따라 리프노드에 도달하면 인덱스 키에 해당하는 레코드의 PK 값이 저장되어 있다. 테이블 영역도 인덱스와 유사한데, 리프 노드에 실제 테이블의 레코드가 저장되어 있다는 점만 다르다.
인덱스는 테이블과 독립적인 저장 공간이므로 인덱스를 통해 데이터를 조회하려면 먼저 PK를 찾아야 한다. PK로 레코드를 조회할 때는(인덱스 영역에서 테이블 영역으로 넘어가는 경우) PK가 어느 페이지에 저장되어 있는지 알 수 없으므로 랜덤 I/O가 발생한다. 이후에는 PK를 따라 리프노드에서 실제 레코드를 읽어온다.
즉, 인덱스를 통해 데이터를 조회하는 것은 아래의 2가지 작업이 수행되는 것이다.
인덱스를 통해 PK를 찾음
PK를 통해 레코드를 찾음
5.3.1 잠금의 종류
레코드 락
다른 상용 DBMS의 레코드 락과 동일한 역할을 하지만, 한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 참고로 여기서 락이 걸리는 인덱스는 클러스터 인덱스(PK) 및 논클러스터 인덱스(세컨더리 인덱스) 모두를 포함한다. 만약 PK가 없는 테이블이라면 내부적으로 자동 생성된 PK를 이용해 설정한다.
InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키락 또는 갭 락을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서 잠그지 않고 레코드 자체에 대해서만 락을 건다.
이렇듯 레코드 락은 트랜잭션이 DML 구문을 실행할 때 자동으로 거는 락이며, 레코드 락 덕분에 여러 트랜잭션이 동시에 서로 다른 레코드에 접근할 수 있는 것이다.
갭 락
갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(insert)되는 것을 제어하는 것이다. Phantom Read 방지를 위한 대표적인 수단으로, 다음 예시를 함께 살펴보자.
예를 들어 현재 성이 J로 시작하는 레코드가 Jo, Joe 2개가 있다고 하자. 그리고 언제든지 다른 데이터들 ex) Jang, Jeong, Jung 이 추가될 수 있다. 따라서 현재 트랜잭션에서 조회를 할 때, 다른 트랜잭션에서 임의의 데이터가 추가되지 않도록 잠그려면 아래와 같은 쿼리를 실행해야 한다. 여기서 SELECT … FOR UPDATE 구문은 베타적 잠금(비관적 잠금, 쓰기 잠금)을 거는 것이다. 읽기 잠검을 걸려면 LOCK IN SHARE MODE 구문을 사용해야 한다.
SELECT * FROM member WHERE last_name LIKE "J%" FOR UPDATE; // 쓰기 잠금(베타락)
SELECT * FROM member WHERE last_name LIKE "J%" LOCK IN SHARE MODE; // 읽기 잠금(공유락)
갭 락은 인덱스 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다.
넥스트 키 락
레코드 락과 갭 락을 합쳐놓은 형태의 잠금으로, 앞서 살펴본 갭 락은 단독으로 사용되기 보다는 넥스트 키 락의 일부로 함께 사용된다.
갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 리플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장해주는 것이 주목적이라고 한다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션이 기다리는 일이 자주 발생하므로, 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다고 한다.
자동 증가 락
MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT 라는 칼럼 속성을 제공한다. AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. InnoDB는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.
MySQL 8.0 버전부터는 AUTO_INCREMENT 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT 되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지는 않지만, 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다. 이 설정에서는 유니크한 값이 생성된다는 것만 보장한다.
5.3.2 인덱스와 잠금
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스는 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야한다.
--employees 테이블에는 first_name 칼럼만 멤버로 담긴 ix_firstname이라는 인덱스가 준비되어 있다.
--first_name='georgi'인 사원은 전체 253명이고, last_name='klassen' 까지인 사원은 딱 1명만 있다.
update employees set hire_dat=now() where first_name='georgi' and last_name='klassen';
위 쿼리를 실행하면 1건의 레코드가 업데이트될 것이다. 하지만 이 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까?
이 update 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='georgi'이며, last_name 칼럼은 인덱스에 없기 때문에 first_name='georgi'인 레코드가 모두 잠긴다.
인덱스를 따라가서 → 해당 레코드를 찾아서 → X Lock 을 건다.
이렇게, update를 위한 적절한 인덱스가 준비되어 있지 않다면, 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 update 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야하는 상황이 발생할 것이다.
WHERE 조건에 사용된 컬럼이 인덱스가 전혀 없다면, InnoDB는 테이블의 모든 Row를 스캔하며, 조건과 관계없이 모든 Row에 X Lock(Exclusive Lock)을 걸게 된다! 😱
5.3.2 레코드 수준의 잠금 확인 및 해제
각 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금을 어떤 트랜잭션이 가지고 있는지를 performance_schema의 data_locks 테이블을 이용해 확인 할 수 있다.
아래는 위의 update 명령 3개가 실행된 상태의 프로세스 목록을 조회한 것이다.
17번 스레드는 트랜잭션을 시작하고 update 명령이 실행 완료된 것이다. 하지만 아직 commit 을 실행하지 않은 상태이므로 업데이트한 레코드의 잠금을 그대로 가지고 있는 상태이다. 그리고 18번, 19번 스레드가 잠금 대기로 인해 아직 update 명령을 실행 중인 것으로 표시된 것이다.
이제 performant_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 잠금 대기 순서를 살펴보자.
쿼리 실행 결과를 보면 18번 스레드는 17번 스레드를 기다리고 있고, 19번 스레드는 17번과 18번 스레드를 기다리고 있다는 것을 알 수 있다.
⭐5.4 MySQL의 격리 수준
트랜잭션 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준은 크게 "READ UNCOMMITED", "READ COMMITED", "REPEATABLE READ", "SERIALIZABLE"의 4가지로 나뉜다.
"DIRTY READ"라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.
데이터베이스의 격리 수준을 이야기하면 항상 함께 언급되는 세 가지 부정합의 문제점이 있다. 이 세 가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수도 있고, 발생하지 않을 수도 있다.
5.4.1 READ UNCOMMITED
이 격리 수준에서는, 그림과 같이 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다. 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드라고 한다.
MySQL을 사용한다면 정합성에 문제가 많은 해당 격리 수준 이상을 사용할 것을 권장한다.
5.4.2 READ COMMITED
이 격리 수준에서는 어떤 트랜 잭션이 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있도록 한다(왼쪽 사진). 이는 employees 테이블이 아닌 언두 영역에 백업된 레코드에서 데이터를 조회하기 때문이다.
INSERT도 트랜잭션 도중 롤백될 수 있기 때문에, InnoDB는 INSERT된 레코드에 대해 "삭제해야 할 Undo 로그"를 남긴다.
READ COMMITED 격리 수준에서도 NON-REPEATABLE READ라는 부정합의 문제가 있다(오른쪽 사진). 이는 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋난다.
=> Non-Repeatable Read = 기존 행의 값이 바뀌었다!
5.4.3 REPEATABLE READ
MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리수준으로, 이 격리 수준에서는 "NON-REPEATABLE READ" 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다(MVCC).
READ COMMITTED 와의 차이는, MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다는 것이다. 즉, READ COMMITTED 격리 수준에서는 SELECT가 실행될 때마다 최신 커밋 데이터를 읽기 때문에, 트랜잭션 안에서 실행되든, 트랜잭션 밖에서 실행되든 큰 차이가 없지만, REPEATABLE READ는 차이가 존재한다.
사용자 A의 트랜잭션 번호는 12 였으며, 사용자 B의 트랜잭션 번호는 10이었다. 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 조회 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.(왼쪽 사진)
하지만 REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 생길 수 있다(오른쪽 사진). 이와 같이 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상은 PHANTOM READ라고 한다. (Non-Repeatable Read은 같은 row를 두 번 읽는데 값이 달라지는 경우) 하지만 MVCC 덕분에 일반적인 조회에서 유령 읽기(Phantom Read)는 발생하지 않는다. 왜냐하면 자신보다 나중에 실행된 트랜잭션이 추가한 레코드는 무시하면 되기 때문이다.
그렇다면 언제 유령 읽기가 발생하는 것일까? 바로 잠금이 사용되는 경우이다. MySQL은 다른 RDBMS와 다르게 특수한 갭 락이 존재하기 때문에, 동작이 다른 부분이 있으므로 일반적인 RDBMS 경우부터 살펴보도록 하자.
오른쪽 사진처럼 부정합이 발생하는 이유는 잠금있는 읽기는 데이터 조회가 언두 로그가 아닌 테이블에서 수행되기 때문이다. 잠금있는 읽기는 테이블에 변경이 일어나지 않도록 테이블에 잠금을 걸고 테이블에서 데이터를 조회한다. SELECT FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 따라서 SELECT FOR UPDATE 쿼리나 SELECT LOCK IN SHARE MODE로 조회하는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.
조회 방법
MVCC 사용
잠금 걸림
언두 로그 읽음
팬텀 리드 방지
일반 SELECT
✅ MVCC
❌ 없음
✅ 언두 읽음
❌ 불가
SELECT ... FOR UPDATE
❌ 현재 레코드
✅ X Lock
❌ 현재값 읽음
❌ 불완전
SELECT ... LOCK IN SHARE MODE
❌ 현재 레코드
✅ S Lock
❌ 현재값 읽음
❌ 불완전
+ Gap Lock 동반
❌ 현재 레코드
✅ + Gap Lock
❌ 현재값 읽음
✅ 가능
하지만 MySQL에는 갭 락이 존재하기 때문에 위의 상황에서 문제가 발생하지 않는다.
사용자 B가 SELECT FOR UPDATE로 데이터를 조회한 경우에 MySQL은 id가 50인 레코드에는 레코드 락, id가 50보다 큰 범위에는 갭 락으로 넥스트 키 락을 건다. 따라서 사용자 A가 id가 51인 member를 INSERT 시도한다면, B의 트랜잭션이 종료(커밋 또는 롤백)될 때 까지 기다리다가, 대기를 지나치게 오래 하면 락 타임아웃이 발생하게 된다.
😒 그럼 락을 왜 사용하는걸까? (동시성 문제는 언제 발생하는 걸까?) 1. 동시 삽입) seat_id = 101에 대한 데이터가 아직 존재하지 않기 때문에 MVCC로는 “없던 row의 등장”을 탐지할 수 없음 2. 동시 갱신) MVCC는 "읽기 일관성"만 보장하지, 동시 쓰기 충돌을 감지하지는 못함. 따라서 "동시성 갱신(Concurrent Update)" 문제를 단순히 트랜잭션만으로는 해결할 수 없다.
즉,일반 조회(락 없음)는 내가 읽는 데이터는 언두로그 기준이므로 내 트랜잭션 안에서는 일관성 100% 유지된다. 그래서 팬텀 리드조차 발생하지 않는 것처럼 보일 수 있다. 하지만, 동시성 제어는 안된다. (다른 트랜잭션이 INSERT, UPDATE 가능) 따라서, 동시성 문제 방지를 위해 락을 사용하는데 (SELECT FOR UPDATE 등) 이때, Gap Lock, Next-Key Lock 등에 의해 팬텀 리드가 방지된다.
5.4.4 SERIALIZABLE
가장 단순한 격리 수준이면서도 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다. 하지만 격리 수준이 SERIALIZABLE로 설정되면 순수한 SELECT 작업에서도 대상 레코드에 넥스트 키 락을 읽기 잠금(공유락, Shared Lock)으로 건다. 따라서 한 트랜잭션에서 넥스트 키 락이 걸린 레코드를 다른 트랜잭션에서는 절대 추가/수정/삭제할 수 없다.
