트랜잭션과 잠금

saewoohan·2025년 3월 10일

MySQL

목록 보기

3/9

MySQL 엔진의 잠금

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

데이터 경쟁 상태를 방지하고 동시성을 제어하기 위해 MySQL 엔진에서는 테이블 데이터 동기화를 위한 잠금 이외에도 여러 가지 잠금 메커니즘을 사용한다.

글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK명령을 통해 MySQL 전체 데이터베이스를 읽기 전용 모드로 잠그는 방식이다. 즉, SELECT를 제외한 대부분의 DDL 문장이나 DML문장은 해당 글로벌 락이 해제될 때 까지 대기 상태로 남는다.

주로 백업 수행 시 일관성을 유지하기 위해 사용되며, 서버 전체를 잠그므로 동시성이 낮아지고 성능 저하가 발생할 수 있다.

백업 락

백업 락은 조금 더 가벼운 글로벌 락이라고 보면 된다. LOCK INSTANCE FOR BACKUP 명령을 사용하여 MySQL의 백업 과정 중 데이터의 일관성을 보장한다. DDL은 차단하지만 DML은 허용하여 백업 중에도 데이터 쓰기가 가능하도록 한다.

가장 대표적인 예시가 레플리카 서버에서의 백업인데, 보통 백업은 레플리카 서버에서 실행된다. 이때 글로벌 락을 사용해서 백업을 진행한다면, 복제 지연이 길어질 수 밖에 없다. XtraBackup, Enterprise Backup 같은 백업 툴들은 복제가 진행되는 상태에서도 일관된 백업을 만들 수는 있지만, DDL 명령이 발생하면 해당 백업은 실패하게 된다. 이 경우 백업 락을 사용한다면, DDL 명령을 차단할 수 있고, 그렇기에 백업 락을 통해 정상적인 복제 및 백업 실패를 막을 수 있다.

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금으로, 명시적 또는 서버 자체에서 획득할 수 있다. 명시적으로는 LOCK TABLES table_name READ/WRITE 를 사용하여 특정 테이블을 잠글 수 있으며, MyISAM나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 서버 자체에서 잠금이 발생한다.

사실 특별한 상황이 아니면 사용할 일이 전혀 없을 뿐더러, InnoDB에서는 레코드 수준의 잠금을 사용하기 때문에 MyISAM과 MEMORY엔진에서 주로 사용되는 기능이다.

네임드 락

GET_LOCK('lock_name', timeout)과 같은 명령을 사용하여 사용자 정의 잠금을 생성하는 방식이다. 해당 잠금의 특징은 대상이 테이블이나 레코드 같은 데이터베이스의 객체가 아니라는 것이다.

어플리케이션 레벨에서 트랜잭션 동기화가 필요할 때 주로 사용되며, 이름을 가진 락을 획득 후, 해제될 때까지 다른 세션은 해당 락을 획득하지 못한다.

가장 대표적인 사용 예시는 동시에 같은 주문 요청이 들어 왔을 때의 동시성 처리이다. 같은 주문 요청이 여러개 처리 될 때, 어플리케이션 단에서 네임드 락을 획득하고 요청을 진행시켜 하나의 주문만 수행되게 할 수 있다.

-- 주문 처리 시작 (네임드 락 획득)
SELECT GET_LOCK('order_user_123', 10); 

-- 주문 등록
INSERT INTO orders (user_id, product_id, quantity, status)
VALUES (123, 456, 2, 'PENDING');

-- 락 해제
SELECT RELEASE_LOCK('order_user_123');

네임드 락 vs RedLock

단일 DB 인스턴스에 대해서만 제대로 동작하며, 만약 클러스터 또는 다중 인스턴스로 DB가 구성되어 있을 때에는 어떤 DB에 네임드 락이 걸릴지 모르기 때문에 분산 환경에서의 락을 제공하는 RedLock 같은 분산락 매커니즘을 사용하는 것이 낫다.

메타데이터 락

DDL 작업을 수행할 때 발생하는 락이며, 명시적으로 획득할 수 있는 락은 아니며 테이블의 구조를 변경하는 ALTER TABLE 같은 작업을 수행할 때 테이블을 보호하기 위해 자동으로 얻는 잠금이다.

InnoDB 스토리지

앞서 말했듯, MySQL에서는 잠금을 크게 스토리지 엔진 레벨 잠금과 MySQL 엔진 레벨 잠금으로 나눌 수 있다. 이제까지는 MySQL 엔진 레벨의 잠금을 알아보았고, 다음으로는 스토리지 엔진 레벨의 잠금에 대해서 알아보도록 하자.

InnoDB 스토리지 엔진의 잠금

MySQL의 InnoDB 엔진은 트랜잭션을 지원하며 레코드 수준의 잠금 방식을 사용하여 높은 동시성을 제공한다. 하지만, 이 뿐 아니라 특이하게 레코드와 레코드 사이의 간격을 잠구는 갭 락이라는 것도 존재하며, 넥스트 키 락, 자동 증가 락이 존재한다.

레코드 락

이름 그대로 레코드 자체만을 잠그는 잠금이며, 다른 DBMS와의 구분되는 차이는 레코드 자체가 아닌 인덱스의 레코드를 잠근다는 것이다. 즉, 테이블의 모든 행을 잠그는 것이 아니라, 인덱스 레코드를 기반으로 잠금을 설정하여 동시성을 높인다. 인덱스가 하나도 없는 테이블이여도 내부적으로 자동 생성된 클러스터 인덱스를 통해서 잠금을 설정한다.

꽤나 성능 최적화의 중요한 특징이며, 동일한 레코드를 검색하는 쿼리더라도 인덱스를 사용하지 않는 쿼리라면, Full Table Scan이 발생하며 테이블 전체가 잠길 가능성이 있다.

갭 락

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

다른 트랜잭션에서 임의의 데이터가 추가되지 않도록 잠그려면 아래와 같은 쿼리를 실행해야한다. 즉, 아래 쿼리를 수행하면, 실제 레코드를 제외하고 데이터가 추가될 수 있는 범위에 갭 락이 걸리게 된다.

즉, 아직 존재하지는 않지만 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금이다. 갭 락만 단독으로 사용되기 보다는 넥스트 키 락의 일부로 자주 사용된다.

SELECT ... FOR UPDATE --- 쓰기 잠금
SELECT ... LOCK IN SHARE MODE --- 읽기 잠금

넥스트 키 락

넥스트 키 락은 레코드 락과 갭 락을 합쳐놓은 형태의 잠금으로, 특정 레코드뿐만 아니라 해당 레코드 주변의 범위까지 잠군다.

InnoDB의 디폴트 격리 수준 REPEATABLE READ에서는 phantom row를 방지하기 위해 기본적으로 next-key lock을 이용하여 락을 획득한다.

자동 증가 락

AUTO_INCREMENT 컬럼 속성이 사용된 테이블에 사용되는 락이며, 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 테이블 수준의 잠금인 자동 증가 락을 획득한다.

즉, 자동 증가 락은 INSERT와 REPLACE 처럼 새로운 레코드를 저장하는 쿼리에서만 필요하며 트랜잭션과 관계없이 락이 걸렸다 해제된다.

AUTO_INCREMENT 컬럼을 보면, 해당 컬럼 값이 한 번 증가하면 절대로 줄어들지 않는데, 트랜잭션과 무관하기 때문에 자동 증가 값은 늘어났지만 트랜잭션이 실패해서 롤백되어도 자동 증가 값은 복구되지 않고 남는다.

인덱스와 잠금

앞서 언급한 것 처럼, InnoDB에서는 인덱스 기반의 레코드 락을 수행한다. 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸며 그렇기 때문에 잠금과 인덱스는 중요한 관계를 맺고 있다.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    INDEX idx_customer_id (customer_id)
);

위와 같은 테이블에서 아래와 같은 쿼리를 수행한다고 해보자.

-- 트랜잭션 A: 특정 고객의 주문을 수정 (인덱스 사용)
SELECT * FROM orders WHERE customer_id = 1001 FOR UPDATE

해당 쿼리는 customer_id = 1001에 대한 레코드만 잠근다. 아래 쿼리를 추가적으로 확인해보자.

-- 트랜잭션 B: 인덱스를 사용하지 않고 전체 테이블을 검색
SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE;

status 컬럼에 인덱스가 없기에, 모든 행에 대한 잠금이 발생한다. (Full Table Scan 발생) 이게 바로 MySQL에서 정확한 인덱스 설계가 중요한 이유이며, MYSQL 서버를 제대로 이용하기 위해서는 위와 같은 특징을 잘 파악하고 있어야한다.

MySQL의 격리 수준

트랜잭션 격리 수준이란 단순히 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에 영향을 미칠 수 있는 수준을 결정하는 것이라고 생각하면 된다. 격리 수준에 따라 발생할 수 있는 세가지의 부정합 문제가 존재하는데, MySQL의 격리 수준에 따른 부정합 문제는 아래 표와 같다.

격리 수준	Dirty Read	Non-Repeatable Read	Phantom Read	동시성
READ UNCOMMITTED	가능	가능	가능	높음
READ COMMITTED	방지	가능	가능	중간
REPEATABLE READ	방지	방지	가능 (InnoDB에서는 방지됨)	낮음
SERIALIZABLE	방지	방지	방지	매우 낮음

원래 REPEATABLE READ에서는 Phantom Read가 발생할 수 있다. 당연하게도 특정 row에 대한 반복적인 조회에 대해서 일관성을 보장하는 수준이기에 추가적인 행의 삽입을 막지 않기 때문이다.

하지만, InnoDB에서는 REPEATABLE READ격리 수준에 대해서도, 넥스트 키 락과 갭 락을 통해 Phantom Read를 방지한다.

READ UNCOMMITED

가장 낮은 격리 수준으로, COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다. 그렇기에 아직 커밋되지 않은 데이터를 읽을 수 있는 현상인 Dirty Read가 발생할 수 있고, 데이터 정합성에 문제가 많은 격리 수준이기에 잘 사용되지는 않는다.

-- 트랜잭션 A
START TRANSACTION;
UPDATE orders SET status = 'CANCELLED' WHERE order_id = 1;
-- 아직 COMMIT 또는 ROLLBACK 하지 않음

-- 트랜잭션 B
START TRANSACTION;
SELECT status FROM orders WHERE order_id = 1;
-- 결과: 'CANCELLED' 
-- 트랜잭션 A가 ROLLBACK하면 데이터가 사라짐 (Dirty Read 발생)

READ COMMITED

커밋된 데이터만 읽을 수 있으며, 그렇기에 Dirty Read는 발생하지 않는다. 이것이 가능한 것이 이전에 소개한 언두 로그를 통한 MVCC 덕분이며 아래 쿼리와 같은 상황에서 트랜잭션 B는 트랜잭션 A가 변경한 내용이 아니라 언두 로그에 저장되어 있는 기존 내용을 참조하여 가져온다.

-- 트랜잭션 A
START TRANSACTION;
UPDATE orders SET status = 'CANCELLED' WHERE order_id = 1;
-- 아직 COMMIT 또는 ROLLBACK 하지 않음

-- 트랜잭션 B (다른 트랜잭션에서 같은 데이터를 조회)
START TRANSACTION;
SELECT status FROM orders WHERE order_id = 1;
-- 결과: 트랜잭션 B는 커밋된 값만 볼 수 있으므로, 기존 값을 반환 (Dirty Read 방지)

하지만, 다른 트랜잭션이 새로운 데이터를 삽입하고 커밋하면 Non-Repetable Read가 발생할 수 있다. 아래의 쿼리에서는 트랜잭션 B가 커밋을 수행했기 때문에, UPDATE가 반영이 된 상태이다. 이때, READ COMMITED는 언두 로그에서 참조하는 것이 아니라 커밋된 데이터를 참조하기 때문에 같은 트랜잭션 A에서 해당 데이터에 대한 커밋 전과 커밋 후 각각 조회의 결과 값은 다를 것이다.

-- 트랜잭션 A
START TRANSACTION;
SELECT status FROM orders WHERE order_id = 1; 
-- 결과: 'PENDING'

-- 트랜잭션 B
START TRANSACTION;
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1;
COMMIT;

-- 트랜잭션 A (같은 데이터 재조회)
SELECT status FROM orders WHERE order_id = 1; 
-- 결과: 'SHIPPED' (Non-Repeatable Read)

REPEATABLE READ

REPEATABLE READ는 MySQL InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. 특히, 바이너리 로그(복제 목적으로 주로 사용)를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

Non-Repeatable Read를 방지할 수 있는 격리 수준으로, READ COMMITED 처럼 언두 로그에 백업된 레코드의 버전을 사용해서 이전 버전을 읽는 방식으로 격리 수준을 보장하는 것은 같지만 READ COMMITED 와는 다르게 REPEATABLE READ는 동일 트랜잭션 내에서는 항상 동일한 결과를 보여줄 수 있도록 보장한다.

이게 가능한 이유는 각각의 트랜잭션은 순차적으로 증가하는 고유한 트랜잭션 번호가 존재하며, 언두 로그에는 어느 트랜잭션에 의해 백업되었는지 트랜잭션 로그를 함께 저장한다. 그 이후에 불필요해지는 시점에 주기적으로 백그라운드 쓰레드를 통해 삭제한다.

-- 트랜잭션 A (trx-id=6) (조회)
START TRANSACTION;
SELECT * FROM orders WHERE id = 50;
-- 결과: 1건 조회됨

-- 트랜잭션 B (trx-id=7)
START TRANSACTION;
UPDATE orders SET status = 'SHIPPED' WHERE id = 50;
COMMIT;

-- 트랜잭션 A(trx-id=6)에서 다시 동일한 조회 수행
SELECT * FROM orders WHERE id = 50;
-- 결과: 변경되지 않고 이전 데이터 유지

위와 같은 예시에서 트랜잭션 A는 트랜잭션 B가 시작하기 전에 이미 시작된 상태이며, 트랜잭션 B는 한 행에 대한 업데이트를 수행 후 커밋을 한다. 이때, REPEATABLE READ는 트랜잭션 번호를 참고하여, 자신보다 먼저 실행된 트랜잭션의 데이터만을 조회한다. 만약 테이블에 존재하는 트랜잭션 번호가 자신보다 이후의 것이라면 언두 로그를 참조해서 자신보다 이전의 트랜잭션 번호를 가진 데이터를 조회한다.

이를 통해서 트랜잭션 A는 레코드에 어떠한 변화가 있더라도 항상 같은 데이터를 얻을 수 있는 것이다. 하지만, 계속해서 언두 로그를 보존해야하기 때문에 트랜잭션이 장시간 실행된다면 데이터가 무한정 커질 수 있으며 서버의 성능이 떨어질 수 있다.

일반적인 REPEATABLE READ는 새로운 레코드의 추가까지는 막지 않기에, 한 트랜잭션 내에서 SELECT로 범위 조회 시에 다른 트랜잭션에 의해 추가된 레코드가 발생할 수 있는데 이를 Phantom Read라고 한다. 하지만 MySQL에서는 MVCC 덕분에 자신보다 나중에 실행된 트랜잭션이 실행한 레코드는 간단하게 무시할 수 있기 때문에 일반적인 조회에서 Phantom Read는 발생하지 않는다.

하지만, SELECT FOR UPDATE를 실행해서 데이터를 조회하는 경우 쓰기 잠금을 걸어야하는데 언두 로그에는 잠금을 걸 수 없기에 테이블 레코드에 쓰기 잠금을 걸고 읽어온다. 이러한 상황에서 트랜잭션 B가 id=55를 삽입하려는 시도를 한다고 한다면 일반적인 DBMS에서는 id=50에 대해서만 레코드 락이 실행되고, 트랜잭션 B의 요청은 실행될 것이다.

💡 언두 로그 잠금이 불가능한 이유

언두 로그는 단순한 Appen Only구조로 한번 기록되면 수정되지 않는 구조이기 때문

하지만, MySQL에서는 갭 락을 통해 해당 문제를 해결한다. SELECT ... FOR UPDATE시에 갭 락이 걸리기에, id=55를 삽입하려는 트랜잭션 B의 시도는 트랜잭션 A가 커밋 혹은 롤백 될 때까지 대기할 것이다.

-- 트랜잭션 A (trx-id=8) (쓰기 잠금)
START TRANSACTION;
SELECT * FROM orders WHERE id BETWEEN 50 AND 60 FOR UPDATE;

-- 트랜잭션 B (trx-id=9) (id=55 삽입 시도 일반적인 DBMS에서는 실행)
START TRANSACTION;
INSERT INTO orders (id, status) VALUES (55, 'PENDING');
-- 트랜잭션 A가 커밋될 때까지 대기 (MySQL에서는 Gap Lock 발생)

그래서 정말 대부분의 케이스에 대해서는 MySQL REPEATABLE READ는 Phantom Read가 발생하지 않는다. 하지만, 거의 유일한 케이스가 존재하는데 첫 조회는 단순 SELECT문으로 수행 했다가 추후에 SELECT FOR UPDATE로 조회했다면 이때 언두 로그가 아닌 테이블 레코드를 조회하므로 Phantom Read가 발생한다.

-- 트랜잭션 A (잠금 없는 조회)
START TRANSACTION;
SELECT * FROM orders WHERE status = 'PENDING';

-- 트랜잭션 B (새로운 데이터 삽입 후 커밋)
START TRANSACTION;
INSERT INTO orders (id, status) VALUES (70, 'PENDING');
COMMIT;

-- 트랜잭션 A (SELECT ... FOR UPDATE 수행)
SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE;
-- 결과: 새로운 레코드가 보임 (Phantom Read 발생)

SERIALIZABLE

가장 엄격한 격리 수준이며, 동시 처리 성능이 가장 떨어지는 격리 수준이다. 원래 기본적으로 단순 SELECT 작업은 MVCC 덕분에 아무런 레코드 잠금 없이 실행 될 수 있는데, SERIALIZABLE에 대해서는 단순 읽기 작업도 읽기 잠금이 필요하며, 다른 트랜잭션은 해당 레코드를 변경할 수 없다. 그렇기 때문에 Phantom Read가 발생하지 않지만, InnoDB에서는 갭 락과 넥스트 키 락이 존재하기 때문에 REPEATABLE READ에서도 Phantom Read가 발생하지 않음이 보장되기에 사용할 필요성이 떨어진다.