트랜잭션과 잠금

초코칩·2024년 1월 29일

mysql 잠금 트랜잭션

MySQL

목록 보기

2/8

트랜잭션

MySQL의 동시성에 영향을 미치는 잠금과 트랜잭션, 트랜잭션의 격리 수준을 알아본다.

트랜잭션은 작업의 완전성을 보장해 주는 것이다. 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상을 방지한다.

잠금: 동시성을 제어하기 위한 기능
트랜잭션: 데이터의 정합성을 보장하기 위한 기능

잠금은 여러 커넥션에서 도잇에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에서 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다. 격리 수준은 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

MySQL에서의 트랜잭션

트랜잭션은 하나의 논리적인 작업 셋에 하나 또는 여러 개의 쿼리의 실행이 100%(COMMIT 실행 시) 적용되거나 아무것도 적용되지 않아야 함(ROLLBACK 또는 트랜잭션의 ROLLBACK 오류)을 보장해 주는 것이다.

다음과 같이 MyISAM, InnoDB 테이블을 만드자.

CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUES (3);

CREATE TABLE tab_myinnodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;
INSERT INTO tab_ innodb (fdpk) VALUES (3);

INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);

두 INSERT 문장 모두 PK 중복으로 실패하게 된다. 하지만 두 테이블의 레코드를 조회해보면 MyISAM의 경우는 1과 2가 남아있는 채로 실행을 종료하고, InnoDB의 경우는 트랜잭션 이전으로 롤백한다.

트랜잭션은 애플리케이션 개발에서 고민해야 할 문제를 줄여주는 필수적인 DBMS의 기능이다. 부분 업데이트 현상이 발생하면 정합성이 깨지게 되고 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 추가로 필요하게 된다.

주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.

일반적으로 DB 커넥션은 개수가 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수가 줄어든다.
- 어느 순간 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.
메일 전송이나 ftp 파일 전송 적업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 DBMS 트랜잭션 내에서 제거하는 것이 좋다.
- 프로그램 실행 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버 뿐 아니라 DBMS 서버까지 위험해진다.
성격이 다른(WRITE, SAVE, …) 트랜잭션끼리는 분리하는 것이 좋다.

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 작용을 미치지는 않는다.

글로벌 락

획득 명령어: FLUSH TABLES WITH READ LOCK;
해제 명령어: UNLOCK TABLES;
다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태로 남는다.
범위: MySQL 서버 전체
- 작업 대상 테이블이나 DB가 아니여도 동일하게 영향을 미친다.

글로벌 락은 최악의 케이스로 실행되면 MySQL 서버의 모든 테이블에 대한 INSERT, UPDATE, DELETE 쿼리가 아주 오랜 시간 동안 실행되지 못하고 기다릴 수도 있다. 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스 용으로 사용되는 MySQL 서버에서는 사용하지 않는 것이 좋다.

백업 락

FLUSH TABLES WITH READ LOCK을 이용한 글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다. 하지만 InnoDB 스토로지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요가 없다. MySQL 8.0 버전부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 더 가변운 글로벌 락의 필요성이 생겼다. MySQL 8.0 버전부터 XtraBackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위한 백업 락이 도입됐다.

획득 명령어: LOCK INSTANCE FOR BACKUP;
해제 명령어: UNLOCK INSTANCE;

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

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

하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용된다. 일반적인 MySQL 서버의 구성은 소스 서버(Source server)와 레플리카 서버(Replica server)로 구성되는데, 주로 백업은 레플리카 서버에서 실행된다. 하지만 백업이 FLUSH TABLES WITH READ LOCK 명령을 이용해 글로벌 락을 획득하면 복제는 백업 시간만큼 지연될 수밖에 없다. 레플리카 서버에서 백업을 실행하는 도중에 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있다. 물론 XtraBackup이나 Enterprise Backup 툴들은 모두 복제가 진행되는 상태에서도 일관된 백업을 만들 수 있다. 하지만 XtraBackup이나 Enterprise Backup 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패하게 된다. 6~7시간 동안 백업이 실행되고 있는데, 갑자기 DDL 명령 하나로 인해 백업이 실패하면 다시 그만큼 시간을 들여서 백업을 실행해야 한다. MySQL 서버의 백업 락은 이런 목적으로 도입됐으며, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

언두 로그에 INSERT, UPDATE가 쌓여있다가 백업 완료 후에 반영하나?

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

명시적 테이블 락

획득 명령어: LOCK TABLES table_name [ READ | WRITE ];
해제 명령어: UNLOCK TABLES;
범위: MyISAM, InnoDB의 개별 테이블
명시적으로 테이블을 잠그는 작업은 온라인 작업에서 상당한 영향을 미치기에 거의 사용하지 않는다.

묵시적 테이블 락

획득 방법: MySQL 서버가 데이터 변경이 이루어지는 테이블에 잠금 설정
해제 방법: 변경 후, 즉시 잠금 해제
범위: MyISAM, MEMORY의 개별 테이블
InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 대부분의 DML 쿼리에서 묵시적인 테이블 락이 설정되지 않는다.
- 스키마를 변경하는 DDL의 경우 영향을 미친다.

네임드 락

DB 개체가 아닌 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.
획득 명령어: SELECT GET_LOCK('myString', seconds);
- 이미 잠금을 사용중이라면, seconds초 동안 대기한다.
잠근 확인 명령어: SELECT IS_FREE_LOCK('myString');
해제 명령어: SELECT RELEASE_LOCK('myString');
많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하다.
- 많은 레코드를 변경하는 배치 프로그램은 데드락의 원인이 되는데, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단히 해결할 수 있다.
MySQL 8.0 버전부터 네임드 락을 중첩해서 사용할 수 있게 되었으며, 현재 세션에서 획득한 네임드 락을 한 번에 해제할 수 있다.
- 일괄 해제 명령어: SELECT RELEASE_ALL_LOCKS();

메타데이터 락

DB 객체의 이름이나 구조 변경 시 획득하고 반납하는 잠금이다.
획득 방법: 테이블의 이름이나 구조를 변경하는 경우 자동으로 획득
- RENAME TABLE tab_a to tab_b
- tab_a, tab_b 모두 락이 걸린다.

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.

이전에는 이원화된 잠금 정보를 진단하는 것이 어려웠지만, 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다. MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 찾아 종료시킬 수도 있다. InnoDB 잠금에 대한 모니터링도 강화되면서 Performance Schema를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락, 테이블 락으로 승급되는 경우가 없다. 일반 DBMS와 달리 갭 락, 넥스트 키 락이 존재한다.

레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 한다. 다른 DBMS와의 차이점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 인덱스가 하나도 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

InnoDB에서는 보조 인덱스를 이용한 변경 작업은 넥스트 키락 또는 갭 락을 사용하지만, PK 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

왜? 이러한 동작은 MVCC와 관련이 있다. 갭 락을 사용하지 않는 것은 갭 락이 다른 트랜잭션들 간에 더 많은 간섭을 일으킬 수 있기 때문이다. 그 대신, 행 단위 락을 사용하여 변경 작업이 효율적으로 이루어질 수 있도록 한다.

갭 락

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다.

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다. STATEMENT 포맷의 바이너리 로그를 사용한 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

그런데 의외로 넥스트 키락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

STATEMENT 포맷

STATEMENT 방식의 경우 실행된 SQL 문을 그대로 바이너리 로그에 저장하는 방식이다. 다수의 데이터가 수정된 경우에도 단순히 쿼리만 기록되기에 적은 용량의 로그파일을 관리할 수 있다는 장점이 있다.

특징)

REPEATABLE READ 이상의 트랜잭션 격리 수준 사용: MySQL의 기본 격리 수준인 REPEATABLE READ 이상을 사용해야만 합니다. READ COMMITTED 방식의 경우 하나의 트랜잭션에서 실행 시점에 따라 스냅샷이 달라지기 때문에 데이터가 불일치하는 문제가 발생할 수 있다.
비확정적으로 처리되는 쿼리문 사용 불가: 소스와 레플리카의 데이터가 일치하지 않을 수 있다.
ROW 포맷보다 더 많은 Lock 사용: 데이터가 복사되는 Row 포맷과 달리 쿼리문을 호출하는 방식이기에 조건에 따라 더 많은 Lock을 걸어 복제 지연이 발생할 수 있다.

ROW 포맷

이 포맷에서는 변경된 행의 실제 데이터가 로그에 기록된다. 따라서 어떤 행이 변경되었는지에 대한 정보가 로그에 저장된다.

특징)

변경된 데이터가 바로 적용되기에 더 적은 Lock을 점유
모든 트랜잭션에서 사용이 가능
쿼리 결과에 의한 값을 전달하기에 변경된 데이터가 많을 수록 바이너리 로그 파일 크기가 커진다.
MySQL 8.0버전 부터 기본 설정이다.

자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다. AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 겨우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 일련번호 값을 가져야 한다. InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락(Auto Increment Lock)이라고 하는 테이블 수준의 잠금을 사용한다.

획득 방법: 트랜잭션과 관계 없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간 락 획득
해제 방법: 락 획득 후, 즉시 해제
AUTO_INCREMENT는 테이블에 단 하나만 존재하기에 하나의 쿼리가 락을 획득하면 나머지는 대기해야 된다.

`innodb_autoinc_lock_mode` = 0

MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 락 증가를 사용한다.

`innodb_autoinc_lock_mode` = 1 (연속 모드)

래치(뮤텍스)

MySQL가 INDERT되는 레코드의 수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고, 훨씬 빠른 래치(뮤텍스)를 이용한다. 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 해제된다.

연속된 자동 증가 락

하지만 MySQL 서버가 건수를 예측할 수 없을 때는 MySQL 5.0과 같이 자동 증가 락을 사용한다. 이때는 INSERT 문장이 완료되기 전까지는 자동 증가 락은 해제되지 않기 때문에 다른 커넥션에서는 INSERT를 실행하지 못하고 대기하게 된다. 이럴 때 InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한 번에 할당 받아서 INSERT되는 레코드에 사용한다. 그래서 대량 INSERT되는 레코드는 자동 증가 값이 누락되지 않고 연속되게 INSERT된다. 하지만 한 번에 할당 자동 증가 값이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장의 실행 이후에 INSERT되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다. 이 설정에서는 최소한 하나의 INSERT 문장으로 INSERT 되는 레코드는 연속된 자동 증가 값을 가지게 된다. 그래서 이 설정 모드를 연속 모드(Consecutive mode)라고도 한다.

`innodb_autoinc_lock_mode` = 2

innodb_autoinc_lock_mode가 2로 설정되면 InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지는 않는다. 그래서 이 설정 모드를 인터리빙 모드(Interleaved mode)라고도 한다. 이 설정 모드에서는 INSERT SELECT와 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다. 하지만 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장한다. STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증 가 값이 달라질 수도 있기 때문에 주의해야 한다.

별로 관계없는 것 같지만, 자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유가 AUTO_INCREMENT 잠금을 최소화하기 위해서다. 설령 INSERT 쿼리가 실패했더라도 한 번 중가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.

MySQL 5.7 버전까지는 innodb_autoinc_lock mode의 기본값이 1이었지만, MySQL 8.0 버전부터는 innodb_autoinc_lock_mode의 기본값이 2로 바뀌었다. 이는 MySQL 8.0부터 바이너리 로그 포맷이 STATEMENT가 아니라 ROW 포맷이 기본값이 됐기 때문이다. MySQL 8.0에서 ROW 포맷이 아니라 STATEMENT 포맷의 바이너리 로그를 사용한다면 innodb_autoinc_lock_mode를 2가 아닌 1로 변경해서 사용할 것을 권장한다.

인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있기 때문에 다시 한번 더 자세히 살펴보자. "레코드 락"을 소개하면서 잠깐 언급했듯이 InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

인덱스가 준비되어 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다.

위 테이블에 인덱스가 하나도 없다면, 테이블을 풀스캔하면서 UPDATE 작업을 하는데, 이 과정에서 테이블에 있는 모든 레코드를 잠그게 될 것이다.

레코드 수준의 잠금 확인 및 해제

InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡하다. 테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있다. 하지만 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.

예전 버전의 MySQL 서버에서는 레코드 잠금에 대한 메타 정보(딕셔너리 테이블)를 제공하지 않기 때문에 더더욱 어려운 부분이다. 하지만 MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 잠금과 잠금 대기를 바로 확인할 수 있다. 그럼 버전별로 레코드 잠금과 잠금을 대기하는 클라이언트의 정보를 확인하는 방법을 알아보자. 강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제로 종료하면 된다.

각 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금을 어떤 트랜잭선이 가지고 있는지를 쉽게 메타 정보를 통해 조회할 수 있다. 우선 MySQL 5.1부터는 information_schema라는 DB에 INNODB_TRX라는 테이블과 INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 통해 확인이 가능했다. 하지만 MySQL 8.0 버전부터는 information_schema의 정보들은 조금씩 제거(Deprecated)되고 있으며, 그 대신 performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다.

MySQL의 격리 수준

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE로 나뉜다. 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지며, 동시 처리 성능도 떨어진다.

격리 수준	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
`READ UNCOMMITTED`	발생	발생	발생
`READ COMMITTED`	없음	발생	발생
`REPEATABLE READ`	없음	없음	발생(InnoDB는 없음)
`SERIALIZABLE`	없음	없음	없음

InnoDB의 독특한 특성 때문에 REPEATABLE READ 격리 수준에서 PHANTOM READ가 발생하지 않는다.

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 COMMIT과 ROLLBACK에 상관 없이 다른 트랜잭션에게 보여진다.

사용자 A가 emp_no가 500000이고 first_name이 "Lara"인 새로운 직원을 INSERT한다.
사용자 B는 커밋하기 전에 emp_no=500000
인 사원 검색에 성공한다.
사용자 A의 트랜잭션에 문제가 발생하여 롤백된다.
사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 처리한다.

Dirty Read

사용자 A는 emp_no=500000이고 first_name이 "Lara"인 새로운 사원을 INSERT한다.
사용자 B가 변경된 내용을 커밋하기도 전에 emp_no=500000인 사원을 검색한다.
사용자 B는 사용자 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 조회한다.
사용자 A의 문제로 인해 INSERT된 내용을 롤백되었다.
여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 계속 처리한다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다. 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 정합성에 문제가 많은 격리 수준이다.

READ COMMITTED

READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가 장 많이 선택되는 격리 수준이다. 이 레벨에서는 위에서 언급한 더티 리드(Dirty read) 같은 현상은 발생하지 않는다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에 서 조회할 수 있기 때문이다.

사용자 A는 emp_no=500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경했다.
새로운 값인 "Toto"는 employees 테이블에 즉시 기록되고 이전 값인 "Lara"는 언두 영역으로 백업된다.
사용자 A가 커밋을 수행하기 전에 사용자 B가 emp_no=500000인 사원을 SELECT하면 조회된 결과의 first_name 칼럼의 값은 "Toto"가 아니라 "Lara"로 조회된다.

여기서 사용자 B의 SELECT 퀴리 결과는 employees 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다. READ COMMITTED 격리 수준에서는 어떤 트랜잭선에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭선에서 그러한 변경 내역을 조회할 수 없기 때문이다. 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백업된 언두 레코드("Lara")가 아니라 새롭게 변경된 "Toto"라는 값을 참조할 수 있게 된다.

NON-REPEATABLE READ

READ COMMITTED 격리 수준에서도 "NON-REPEATABLE READ"(repeatable READ"가 불가능하다)라는 부정합의 문제가 있다.

사용자 B가 BEGIN 명령으로 트랜잭션을 시작한다.
first_name이 "Toto"인 사용자를 검색했는데, 일치하는 결과가 없다.
사용자 A가 사원 번호가 500000인 사원의 이름을 "Toto"로 변경하고 커밋을 실행한다.
사용자 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 결과가 1건이 조회된다.

이는 별다른 문제가 없어 보이지만, 사실 사용자 B가 하나의 트랜잭션 내에서 똑같 은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것이다.

이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수도 있다. 예를 들어, 다른 트랜잭션에서 입금과 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해보자. 그런데 "REPEATABLE READ"가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져올 것이다. 중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지를 정확히 예측할 수 있어야 한다는 것이다. 그리고 당연히 이를 위해서는 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알아야 한다.

트랜잭션 외부,내부에서의 SELECT

가끔 사용자 중에서 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 없이 실행되는 SELECT 문장의 차이를 혼동하는 경우가 있다. READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없다.

하지만 REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다. 즉, START TRANSACTION(또는 BEGIN) 명령으로 트랜잭션을 시작한 상태에서 온종일 동일한 쿼리를 반복해서 실행해 봐도 동일한 결과만 보게 된다(아무리 다른 트랜잭션에서 그 데이터를 변경하고 COMMIT을 실행한다고 하더라도 말이다). 별로 중요하지 않은 차이처럼 보이지만 이런 문제로 데이터의 정합성이 깨지고 그로 인해 애플리케이션에 버그가 발생하면 찾아내기가 쉽지 않다.

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 "NON-REPEATABLE READ" 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다. REPEATABLE READ는 이 MVCC를 위 해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다. 사실 READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여준다. REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.

트랜잭션 번호(tid)

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번 호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니다. 더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.

employees 테이블은 번호가 6인 트랜잭션에 의해 INSERT됐다고 가정한다.
사용자 A의 트랜잭션 번호는 12였으며 사용자 B의 트랜잭션의 번호는 10이었다.
사용자 A는 사원의 이름을 "Toto"로 변경하고 커밋을 수행했다.
A 트랜잭션이 변경을 수행하고 커밋을 했지만, 사용자 B가 emp_no=500000인 사원을 A 트랜잭션의 변경 전후 각각 한 번씩 SELECT 했는데 결과는 항상 "Lara"라는 값을 가져온다.

사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10 번이라는 트랜잭션 번호를 부여받았는데, 그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다. 언두 영역에 백업된 데이터가 하나만 있는 것으로 표현했지만 사실 하나의 레코드에 대해 백업이 하나 이상 얼마든지 존재할 수 있다. 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있다. 이렇게 언두에 백업 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

Phantom Read

REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다.

사용자 B는 BEGIN 명령으로 트랜잭션을 시작한 후 SELECT를 수행한다.
사용자 B가 실행하는 두 번의 SELECT FOR UPDATE 쿼리 결과는 서로 다르다.
SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.
SELECT ... FOR UPDATE나 SELECT LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.

이렇게 다른 트랜 잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ(또는 PHANTOM ROW)라고 한다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT 작업(INSERT, SELECT, ... 또는 CREATE TABLE AS SELECT ... 가 아닌)은 아무런 레코드 잠금도 설정하지 않고 실행된다. InnoDB 매뉴얼에서 자주 나타나는 "Non-locking consistent read(잠금이 필요 없는 일관된 읽기)" 라는 말이 이를 의미하는 것이다. 하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다.

MySQL에서의 PHANTOM READ

SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 "PHANTOM READ"라는 문제가 발생하지 않는다.

InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 "PHANTOM READ"가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요가 없다. 갭 락과 넥스트 키 락이 SELECT FOR UPDATE에 범위 락이 걸리게 되어 다른 트랜잭션이

엄밀하게는 "SELECT FOR UPDATE" 또는 "SELECT FOR SHARE" 쿼리의 경우 REPEATABLE READ 격리 수준에서 PHANTOM READ 현상이 발생할 수 있다. 하지만 레코드의 변경 이력(언두 레코드)에 잠금을 걸 수는 없기 때문에, 이러한 잠금을 동반한 SELECT 쿼리는 예외적인 상황으로 볼 수 있다.

PHANTOM READ가 발생하는 한 가지의 경우

트랜잭션 B가 일반 "SELECT" 문을 실행하고 나서 "SELECT FOR UPDATE"를 실행하게 되면 앞서 "SELECT" 쿼리는 갭 락과 넥스트 키 락이 걸리지 않게 된다.

초코칩

초코칩처럼 달콤한 코드를 짜자

이전 포스트