동시성에 대한 처리를 위한 잠금과, 데이터 정합성을 위한 트랜잭션을 MySQL에서는 어떻게 다루고 있는지 분석합니다.
MySQL 트랜잭션
트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아닙니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있는 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것입니다.
다음은 이에 대한 예시를 MyISAM과 InnoDB의 쿼리 동작 차이로 풀어낸 것입니다.
INSERT INTO tab_myisam (fdpk) VALUES (3);
INSERT INTO tab_innodb (fdpk) VALUES (3);
SET autocommit=ON;
INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);
tab_myisam과 tab_innodb는 각각 MyISAM과 InnoDB를 사용해서 만들어낸 테이블입니다. 위 쿼리에 대한 결과는 아래와 같습니다.
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
하지만 테이블의 내용을 조회해보면 그 내용이 상이함을 확인할 수 있습니다.
tab_myisam
fdpk
1
2
3
-------------
tab_innodb
fdpk
3
MyISAM은 테이블에 INSERT 문장이 실행되면서 차례대로 '1'과 '2'를 저장하고 그다음 '3'을 저장하려고 하는 순간 에러가 발생했고, 이미 처리된 '1'과 '2'가 테이블에 남아있습니다. 즉, Partial Update가 발생했습니다. InnoDB는 쿼리 중에 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구했습니다.
부분 업데이트는 데이터 정합성 측면에서 많은 어려운 문제를 발생시킵니다. 이를 코드 레벨에서 if else문을 활용해서 구현하는 것은 매우 복잡하고, 또 모든 부분에서 원하는 부분에 이렇게 처리하기도 시간적 소요가 큽니다. 트랜잭션을 활용하면 매우 간단하게 해결할 수 있습니다.
try {
START TRANSACTION;
INSERT INTO tab_a ...;
INSERT INTO tab_b ...;
COMMIT;
) catch (exception) {
ROLLBACK;
}
하지만 InnoDB 엔진 특성 상, 트랜잭션의 범위가 과하게 넓어지면 메모리 사용과 쿼리의 전반적인 성능이 낮아질 수 있기 때문에 꼭 필요한 최소의 코드에만 적용하는 것이 좋습니다. 또한 트랜잭션 시작과 동시에 DB 커넥션을 가져오게 되는데, 이 DB 커넥션을 반납하지 않은 상태로 오랜시간 지속되는 것은 DBMS 서버에 높은 부하 상태를 쉽게 유발할 수 있습니다.
아래는 트랜잭션의 범위 최소화에 대한 예시입니다.
1 ) 처리 시작
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
2) 사용자 로그인 여부 확인
3) 사용자 글쓰기 내용 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
<= 데이터베이스 커넥션 반납
10) 처리 완료
위 예시에서 보이는 처리 중에 DBMS의 작업은 사용자 입력 내용 DBMS 저장, 첨부 파일 정보 DBMS 저장, 저장된 정보를 DBMS에서 조회, 알림 메일 발송 이력 DBMS 저장으로 4개로 나눠볼 수 있습니다. 여기서 중요한 점은 Network I/O, 외부 API 등의 에러 의존성이 외부에 있는 경우에는 반드시 트랜잭션에서 제거해주어야 한다는 점입니다. 또한 데이터의 수정이 없는 단순한 DBMS 조회 기능의 경우도 트랜잭션의 범위에서 제거한다면 아래와 같이 트랜잭션 설계를 개선할 수 있습니다.
1 ) 처리 시작
2) 사용자 로그인 여부 확인
3) 사용자 글쓰기 내용 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
=> 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
<= 데이터베이스 커넥션 반납
10) 처리 완료
MySQL 격리 수준
트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것입니다. 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능이 떨어집니다만, SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않습니다.
격리 수준에 따라서 세 가지의 부정합 문제가 발생할 수 있습니다. 아래의 표는 그에 대한 내용입니다.
| DIRTY READ | NON-REPETABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | O | O | O |
| READ COMMITTED | X | O | O |
| REPETABLE READ | X | X | O (InnoDB는 X) |
| SERIALIZABLE | X | X | X |
일반적인 온라인 서비스에서는 주로 READ COMMITTED나 REPETABLE READ 중 하나를 사용합니다. MySQL에서는 REPETABLE READ를 주로 사용합니다.
READ UNCOMMITTED
각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보입니다.
여기서 사용자 B는 사용자 A가 INSERT한, COMMIT되지 않은 사원의 정보를 조회하게 됩니다. 문제는 사용자 A가 처리 도중 모종의 문제가 발생해 INSERT된 내용이 롤백되더라도 사용자 B가 해당 내용을 정상적인 사원이라고 계속해서 처리하게 된다는 점입니다.
이와 같이 어떤 트랜잭션에서 처리한 작업이 완료되지도 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라 하고, READ UNCOMMITTED에서는 더티 리드가 허용됩니다. 더티 리드를 허용하게 되면 데이터가 나타났다가 사라지는 현상을 빈번하게 초래하므로 혼란스럽고, 정합성에 많은 문제가 생깁니다. 따라서 보통의 경우 사용하지 않습니다.
READ COMMITTED
오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준입니다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있습니다. 따라서 더티 리드 현상이 없어집니다.
사용자 B가 사용자 A의 UPDATE 중에 SELECT를 진행할 경우, 언두 영역에서 데이터를 참조하여 백업된 레코드를 읽게 됩니다. 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 언두 로그를 참조하게 되기 때문입니다. 위 예제에서 사용자 A가 변경한 내용을 커밋하면 그제야 다른 트랜잭션에서도 새롭게 변경된 내용인 Toto가 참조되게 됩니다.
많이 사용되는 격리 수준이라고 해서 정합성의 문제가 없는 것이 아닙니다. 아래와 같이 NON-REPETABLE READ 현상이 발생할 수 있습니다.
사용자 B의 트랜잭션 내에서 사용자 A가 B가 참조하는 내용을 변경하고 있습니다. 이때 변경 전후로 사용자 B는 두 번의 SELECT 문장이 존재하는데 다른 결과 값을 가집니다. 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPETABLE READ 정합성에 어긋나는 것입니다.
일반적인 웹 프로그램에서는 크게 문제가 되지 않을 수도 있지만, 금전적 처리 등의 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업에서 값 하나하나에 예민한 도메인에서 NON-REPETABLE READ는 문제가 될 수 있습니다.
REPEATABLE READ
REPETABLE READ는 MySQL의 InnoDB 스토리지 엔진이 기본으로 사용하는 격리 수준입니다. InnoDB는 트랜잭션이 롤백될 가능성에 대비해 변경되기전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경합니다. 이 언두 영역에 백업된 데이터를 이용해서 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장합니다. READ COMMITTED도 이 언두 영역을 사용하기는 하지만, 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에서 차이를 보입니다.
모든 InnoDB의 트랜잭션은 코유한 트랜잭션 번호를 가집니다. 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있습니다. InnoDB는 불필요하다고 판단하는 시점에 주기적으로 언두 데이터를 삭제합니다. 여기서 REPETABLE READ 격리 수준에서는 MVCC를 보장하기 위해서 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터를 보존하는 전략을 가집니다.
제시된 그림과 같이 사용자 B가 두 번의 조회를 하는데 그 중간에 사용자 A가 수정을 했다면, 그 다음의 조회에서는 언두 로그를 조회하는데 이는 트랜잭션 번호를 기반으로 수행됩니다. 조회를 수행하는 B의 트랜잭션 번호가 10이므로 10보다 이전에 존재하는 트랜잭션 번호에서 변경한 것만 보이게 됩니다. 백업된 데이터는 얼마든지 존재할 수 있으며 트랜잭션이 종료되지 않은 상태로 장기간 지속되면 언두 로그가 무한정 커질 수도 있습니다.
아래는 한 트랜잭션의 두 번의 조회 사이에 UPDATE가 아닌 생성 및 제거가 발생했을 경우에 발생하는 부정합인 Phantom read에 대한 예시입니다.
팬텀 리드는 한 트랜잭션 내에서 동일한 쿼리를 두 번 실행했을 때, 레코드가 갑자기 나타나거나 혹은 사라지는 현상을 말합니다. 즉, UPDATE가 아닌 INSERT, DELETE가 동일 쿼리의 사이에서 발생했을 때 이와 같은 부정합 현상이 발생할 수 있습니다.
SERIALIZABLE
기본적으로 InnoDB에서 순수한 SELECT 작업은 잠금없이 처리됩니다. 여기서 SERIALIZABLE로 설정되면 읽기 또한 공유 잠금을 획득해야만 합니다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없는 격리 수준을 SERIALIZABLE이라고 합니다. InnoDB에서는 갭 락과 넥스트 키 락으로 REPETABLE READ에서 Phantom read가 발생하지 않으므로 굳이 SERIALIZABLE을 택할 필요는 없습니다.
MySQL 엔진 잠금
MySQL에서의 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진 레벨의 락(Lock)은 모든 스토리지 엔진에 영향을 주지만, 스토리지 엔진 레벨의 락은 다른 스토리지 엔진에게 영향을 미치지 않습니다.
글로벌 락
FLUSH TABLES WITH READ LOCK
MySQL에서 제공하는 락 가운데 가장 범위가 큽니다. 한 세션에서 글로벌 락을 획득하면 다른 세셔넹서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남습니다. 이는 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미칩니다. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 합니다.
mysqldump와 같은 백업 프로그램은 우리가 알지 못하는 사이에 이 글로벌 락을 내부적으로 실행하고 백업을 하는 경우도 있습니다. 이때 장시간 실행되는 쿼리와 이 락에 대한 명령이 최악의 케이스로 실행되면 MySQL 서버의 모든 테이들에 대한 CRUD 작업이 매우 긴 시간 동안 실행되지 못할 수 있습니다. 이는 글로벌 락을 얻으려면 대상이되는 모든 테이블이 플러시가 되고 락을 얻기 때문에, 쿼리가 종료되어야 하고 이를 기다려야하기 때문입니다. 따라서 웹 서비스에서 이 기능은 되도록 사용하지 않는 것이 좋고, mysqldump이 서버에 어떤 잠금을 걸게 되는지 자세히 확인해보는 것이 좋습니다.
다만 트랜잭션을 지원하는 InnoDB의 경우에는 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없습니다. 이로 인해 백업 락이 도입됐습니다.
일반적으로 MySQL은 소스 서버와 레플리카 서버로 구성되는데, 주로 백업은 레플리카 서버에서 실행됩니다. 이때 기존 글로벌 락을 이용할 경우 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야할 수도 있습니다. 여기서 백업 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패하게 되는데, 보통 백업 시간은 매우 긴데 DDL 명령 하나로 백업이 실패하게 되면 다시 백업을 처음부터 시도해야합니다. 이때 백업 락은 일반적인 테이블의 데이터 변경을 허용해서, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 합니다.
| 항목 | 글로벌 락 (FLUSH TABLES WITH READ LOCK) | 백업 락 (LOCK INSTANCE FOR BACKUP) |
| 락 범위 | 서버 전체 (모든 테이블) | 서버 전체지만 더 가벼움 |
| DML (INSERT/UPDATE/DELETE) | 거의 대부분 차단 | 허용 (데이터 변경 OK) |
| DDL (테이블 구조 변경) | 차단 | 차단 (백업 실패 방지) |
| 복제 영향 | 복제 지연 발생 가능 | DDL 발생 시 복제를 일시 중지 |
| InnoDB 백업 적합성 | 과도하게 강력 | 적합 |
테이블 락
개별 테이블 단위로 설정되는 잠금입니다. 이는 명시적 또는 묵시적으로 획득할 수 있습니다.
묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에서 데이터를 변경하는 쿼리를 실행하면 발생합니다. 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후에 즉시 잠금을 해제하는 형태로 사용됩니다. 즉, 쿼리가 실행되는 동안 있다가 완료되면 해제되는 형태입니다. 하지만 InnoDB의 경우 레코드 기반 락을 제공하기 때문에 단순한 데이터 변경으로 인해 테이블 락이 설정되지는 않습니다. InnoDB에서는 테이블락이 설정되기는 하지만 대부분의 데이터 변경 쿼리(DML)에서는 무시되고 스키마 변경 쿼리(DDL)의 경우에만 영향을 미치는 형태입니다.
네임드 락
테이블이나 레코드 또는 데이터베이스 객체가 아니라 단순히 사용자가 지정한 문자열에 대해 적용하는 잠금입니다. 여러 클라이언트가 상호 동기화를 처리할 때 네임드 락을 이용하면 쉽게 해결할 수 있고, 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션(배치 프로그램 등)에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 대해서 유용하게 사용할 수 있습니다. 이는 8.0 버전부터 중첩으로 사용이 가능해졌으며, 획득한 네임드 락을 한 번에 모두 해제할 수도 있게 됐습니다.
SLELCT GET_LOCK('mylock', 2); // 문자열에 대한 잠금 획득
SELECT IS_FREE_LOCK('mylock'); // 문자열에 잠금이 설정돼있는지 확인
SELECT RELEASE_LOCK('mylock'); // 문자열에 대한 잠금을 반납
메타데이터 락
데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 명시적으로 획득, 반납할 수는 없습니다. 메타데이터 락은 다음과 같은 배치 프로그램에서 자주 발생합니다.
// 배치 프로그램에서 별도의 임시 테이블에 서비스용 랭킹 데이터를 생성
// 배치가 완료되면 현재 데이터를 rank_backup으로 백업하고 새로 만들어진 랭킹 테이블을 서비스용으로 대체 하는 경우
RENAME TABLE rank TO rank_backup, rank_new TO rank;
이렇게 하나의 RENAME TABLE 명령어에 두 개의 RENAME 작업을 한꺼번에 실행하면 rank 테이블을 찾을 수 없는 상황을 만들지 않고 적용하는 것이 가능합니다.
이미 존재하는 테이블에 대해서 테이블 구조를 변경해야할 요건이 생겼다면 Online DDL을 이용해서 변경할 수 있습니다. 다만 시간이 너무 오래 걸리는 경우에 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 다양한 문제를 고민해야하는데, 제일 중요한 것은 DDL이 단일 스레드로 동작해서 매우 느리다는 점입니다. 즉 테이블 구조 변경 시 데이터를 새로 복사해야 하는 경우에 대부분 단일 스레드로 레코드 하나하나를 읽고 변환하고 새 테이블에 쓰는데, 이 과정이 단일 스레드 환경에서 일어나면서 매우 느리다는 문제가 있다는 것입니다.
따라서 이런 문제를 회피하기 위해서 새로운 테이블을 생성하고, 가능한 가장 최근까지의 데이터를 범위로 나눠서 병렬 스레드로 복사합니다. 그 뒤에 남은 데이터를 복사하고 RENAME TABLE로 새로운 테이블을 서비스에 투입할 수 있습니다. 이때 메타데이터 락과 InnoDB의 트랜잭션을 동시에 사용합니다.
InnoDB 스토리지 엔진 잠금
InnoDB는 MyISAM에 비해 레코드 기반의 잠금 방식으로 훨씬 뛰어난 동시성 처리를 제공합니다. 또한 InnoDB에서는 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 존재합니다. information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조회하면 확인할 수 있고, 또한 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수 있습니다. 또한 스토리지 엔진 내부 잠금(세마포어)에 대한 모니터링을 할 수도 있습니다.
스토리지 엔진의 잠금
InnoDB에서는 레코드 기반 잠금이기 때문에 상당히 작은 공간으로 락이 관리됩니다. 따라서 레코드 락이 페이지 락, 테이블 락으로 레벨업 되는 경우는 없습니다. 이 레코드 락과 더불어서 갭 락, 넥스트 키 락 등 다양한 락이 존재합니다. 보조 인덱스를 이용한 변경 작업은 갭 락, 넥스트 키 락을 이용하고, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업은 레코드 자체에 대해서만 락을 겁니다.
레코드 락
레코드 자체만을 잠그는 락입니다. 다른 상용 DBMS의 레코드 락과 동일한 역할을 하지만, InnoDB의 레코드 락은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점에서 차이를 보입니다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정합니다.
갭 락
레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미합니다. 갭 락의 역할은 레코드와 레코드 사이 간격에 새로운 레코드가 INSERT되는 것을 제어하는 것입니다. 갭 락은 주로 넥스트 키 락의 일부로 자주 사용됩니다.
넥스트 키 락
레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 합니다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적입니다. 다만 여기서 데드락이나 다른 트랜잭션의 대기를 곧 잘 발생시키므로, 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋습니다.
자동 증가 락
MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공합니다. AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 하기 때문에 InnoDB에서는 내부적으로 Auto Increment Lock이라는 테이블 수준의 잠금을 사용합니다.
이 락은 새로운 레코드를 저장하는 쿼리에서만 사용되고 수정에서는 UPDATE, DELETE 등에서는 사용되지 않습니다. Auto increment 값을 가져오는 순간만 락이 걸렸다가 즉시 해제됩니다. 또한 테이블 당 단 하나만 존재하는 락이기 때문에 두 개의 INSERT 쿼리가 동시에 실행되면 하나의 쿼리는 락을 기다려야 합니다. 명시적으로 Auto increment 칼럼에 값을 적어넣더라도 이 락은 피할수 없습니다. 또한 Auto increment 값은 한 번 증가하면 절대 줄어들지 않는데, 이는 자동 증가 락을 최소화하기 위해서 입니다.
이 락은 명시적으로 획득하고 해제할 수는 없습니다. 다만 작동 방식은 변경할 수 있습니다. 해당 옵션은 아래와 같습니다.
| innodb_autoinc_lock_mode | 작동 방식 |
| 0 | 모든 INSERT 문장에 대해 자동 증가 락을 사용합니다. |
| 8.0 이전까지 기본값 1 (Consecutive, 연속 모드) |
MySQL 서버가 INSERT되는 레코드의 건수를 정확하게 예측할 수 있을 때 자동 증가 락을 사용하지 않고 더 가볍고 빠른 래치(뮤텍스)를 이용해서 처리합니다. 이는 예측을 정확하게 할 수 있을 때에만 해당하며, INSERT ... SELECT 와 같이 쿼리를 실행하기 전에는 건수를 알수 없을 때는 자동 증가 락을 사용하게 됩니다. InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한 번에 할당받아서 INSERT되는 레코드에 사용합니다. 이때 한번에 할당받은 자동 증가 값이 남아서 사용되지 못하면 폐기되기 때문에 INSERT 실행 이후에 INSERT되는 레코드의 자동 증가 값에서는 누락이 발생할 수 있습니다. |
| 8.0부터 기본값 2 (Interleaved, 인터리빙 모드) |
절대 자동 증가 락을 사용하지 않고 경량화된 래치(뮤텍스)를 사용합니다. 이 경우에는 하나의 INSERT 문장으로 삽입된 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 않습니다. 이 모드에서는 대량의 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아집니다. 하지만 이 설정에서는 자동 증가 값이 연속됨을 보장하지는 않고 유니크함만 보장합니다. STATEMENT 포맷의 바이너리 로그 기반 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수도 있으므로 유의해야합니다. |
인덱스와 잠금
InnoDB의 잠금은 레코드가 아니라 인덱스를 잠그는 방식입니다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 합니다. 인덱스 잠금에 대한 이유를 아래와 같은 예시와 함께 생각해보겠습니다.
// first_name 칼럼만 인덱스에 존재하는 상황
SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
=> 253
SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
=> 1
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';
=> ?
UPDATE 문장이 실행되면 1건의 레코드만 업데이트가 될 것인데, 그럼 이 UPDATE를 위해 몇 개의 레코드에 락을 걸지는 생각해보아야 합니다. 이 예시에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이고 last_name='Klassen'은 인덱스에 없기 때문에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠깁니다.
위와 같은 상황과 같이 적절히 인덱스가 준비가 돼있지 않다면 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트 하지 못하고 기다려야하는 상황이 빈번해질 수 있습니다. 또한 아무런 인덱스가 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하게되고, 이 과정에서 테이블에 있는 모든 레코드를 잠그게 됩니다. 따라서 MySQL의 InnoDB에서는 인덱스 설계가 매우 중요합니다.
❓백업과 복제의 차이
백업은 나중에 데이터가 망가지면 복구하기 위해서, DB의 현재 상태를 사진 찍듯이 안전한 곳에 저장하는 것입니다. 보통 레플리카 서버에서 실행합니다. 이는 보통 소스 서버가 실시간 서비스를 담당하므로 소스 서버에 부하를 주지 않기 위함입니다.
복제는 레플리카를 최신 상태로 유지하기 위해서, 소스 서버에서 일어나는 모든 변경(INSERT, UPDATE, ...)을 실시간으로 레플리카에게 복사해서 보내는 것을 말합니다.
❓바이너리 로그의 STATEMENT 포맷 vs ROW 포맷
MySQL의 바이너리 로그는 복제와 시점 복구를 위해 데이터 변경 사항을 기록하는 로그입니다. 이 로그를 기록하는 방식(포맷)은 크게 3가지가 있습니다.
- STATEMENT
- ROW
- MIXED
STATEMENT 포맷
어떤 SQL 문장을 실행했는지 그대로 기록합니다. (ex: UPDATE users SET status = 'active' WHERE id > 1000;)
이를 레플리카에서 그대로 다시 실행합니다.
대량의 행을 한 번에 UPDATE/DELETE 하는 배치 작업이 많을 때, 바이너리 로그 크기를 최소화하고 싶을 때, 복제 지연을 최소화하고 로그 관리가 중요할 때 이 포맷을 사용합니다.
바이너리 로그 크기가 매우 작고 가독성이 좋습니다. 또한 롤백된 내용은 로그에 남지 않으며 SQL로 복구가 가능하기 때문에 시점 복구에 용이합니다. 하지만 비결정적 함수(UUID, NOW()...) 등에서 다른 값이 나오기 때문에 동일하지 않은 데이터가 저장될 수 있으며 복잡한 쿼리는 느릴 수 있습니다.
ROW 포맷
어느 행이 어떻게 바뀌었는지 실제 데이터를 기록합니다. 이는 바이너리 형태로 기록됩니다.
레플리카에서 SQL을 실행하는 대신에 변경된 행의 Before/After 이미지를 그대로 적용합니다.
데이터 일관성이 가장 중요할 때, 비결정적 함수(UUID, NOW() 등)을 자주 사용할 때, 테이블에 PK가 잘 설계돼있을 때 이 포맷을 사용합니다.
비결정적 함수에도 값을 그대로 적용하므로 문제가 없어서 복제 안정성이 매우 큽니다. 또한 SQL을 돌리는 것이 아니므로 일반적으로 더 적은 락으로 처리가 가능합니다. 또한 복제 속도가 일반적으로 빠릅니다. 하지만 변경된 모든 사항을 기록하기 때문에 많은 변경 발생 시에 바이너리 로그 크기가 매우 커질 수 있으며 바이너리 데이터 형태이기 때문에 가독성이 좋지 않습니다.
MIXED 포맷
MySQL이 안전하지 않은 문장은 ROW로, 안전하고 효율적인 문장은 STATEMENT로 자동으로 판단해서 기록합니다.
대부분의 일반 웹 서비스에서 가장 추천되는 설정입니다.
-- 현재 포맷 확인
=> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
-- 글로벌 설정 변경 (my.cnf 또는 SET GLOBAL)
=> SET GLOBAL binlog_format = 'ROW' , 또는 'STATEMENT', 'MIXED';
-- 세션 단위 설정
=> SET SESSION binlog_format = 'ROW';
❓Auto increment를 사용할 떄, 자동 증가 값에서 누락이 발생하면 안 좋은 점?
누락이 발생하는 것이 큰 기술적 문제는 아닙니다. INSERT ... SELECT, multi-row INSERT, INSERT IGNORE 등에서 정확한 건수를 미리 알 수 없을 때는 넉넉하게 값을 할당받고 실제로 사용되지 않은 값은 폐기되는데, 이는 의도된 설계이기 때문입니다. 공식 문서에서도 "gaps may occur"라고 명시돼있습니다.
다만 비즈니스 측면에서 ID에 구멍이 있으면 안 된다는 요구사항이 있을 경우에는 문제가 발생합니다. 가령 어제 100번까지 주문이 들어왔는데, 오늘은 105번부터 시작하면 그 사이의 101~104가 여러가지로 오인을 받을 수 있기 때문입니다. 따라서 이와 같은 경우에는 별도의 시퀸스 테이블이나 외부 ID 생성기(UUID나 snowflake ID)와 같은 자연스럽게 gap이 있는 ID 전략으로 전환합니다.
참고 문헌
Real MySQL 8.0
'Backend > DB' 카테고리의 다른 글
| [DB] 데이터 모델링 (0) | 2026.05.30 |
|---|---|
| [DB] 비관적 락과 낙관적 락 (0) | 2026.05.01 |
| [MySQL] InnoDB 스토리지 엔진 아키텍처 분석 (0) | 2026.03.16 |
| [MySQL] MySQL 엔진 아키텍처 분석 (0) | 2026.03.05 |