[Database] 트랜잭션, InnoDB 락(Lock)

서론

트랜잭션은 작업의 완전성을 보장해주는 것이다. 즉 요청을 모두 완벽하게 처리하거나, 처리하지 못한 경우는 요청을 처리하기 전 상태로 되돌려서 요청의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어준다.

잠금(Lock)과 트랜잭션은 서로 비슷한 개념 같지만, 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.

만약 하나의 데이터에 여러 개의 요청이 동시에 처리된다면 해당 데이터 값은 예측할 수 없는 상태가 된다. 잠금은 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다.

격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

트랜잭션

트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB의 스토리지 엔진들의 처리 방식 차이를 살펴보자. 또한 트랜잭션을 사용할 경우 주의할 사항도 함께 살펴보자.

MySQL에서의 트랜잭션

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해주는것이다.

간단한 예로 트랜잭션 관점에서 InnoDB 테이블과 MyISAM 테이블의 차이를 알아보자. 아래와 같이 테이블을 생성하고 데이터를 넣어주었다.

# InnoDB
mysql> create table table_innodb(
	id int not null, 
    primary key(id)
) ENGINE=InnoDB;

mysql> insert into table_innodb(id) values(3);

# MyISAM
mysql> create table table_myisam(
	id int not null,
    primary key(id)
) ENGINE=MyISAM;

mysql> insert into table_myisam(id) values (3);

위와 같이 테스트용 테이블에 레코드를 1건씩 저장한 후 AUTO-COMMIT 모드에서 다음 쿼리 문장을 InnoDB 테이블과 MyISAM 테이블에서 각각 실행해보자.

mysql> insert into table_innodb (id) values (1), (2), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'table_innodb.PRIMARY'

mysql> insert into table_myisam (id) values (1), (2), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'table_myisam.PRIMARY'

두 INSERT 쿼리 모두 기본 키 중복 오류로 쿼리가 실패했다. 그런데 신기하게도 두 테이블의 레코드를 조회해보면 MyISAM 테이블에는 오류가 발생했음에도 '1'과 '2'는 INSERT된 상태로 남아 있는 것을 확인할 수 있다.

mysql> select * from table_myisam;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

MyISAM 테이블에 INSERT 쿼리가 실행되면서 차례대로 1과 2를 저장하고, 그 다음 3을 저장하려고 하는 순간 중복 키 오류가 발생한 것이다. 하지만 MyISAM 테이블에서 실행되는 쿼리는 이미 INSERT된 1과 2를 그대로 두고 쿼리 실행을 종료해 버린다.

MEMORY 스토리지 엔진을 사용하는 테이블도 MyISAM 테이블과 동일하게 작동한다. 하지만 InnoDB 테이블은 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 쿼리를 실행하기 전 상태 그대로 복구했다.

MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial Update)라고 표현하며, 이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 낸다.

만약 InnoDB를 사용하지 않아서 부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있다. 2개 이상의 쿼리가 실행되는 경우라면 실패에 대한 재처리 작업은 다음 예와 같이 상당한 고민거리가 될 것이다.

INSERT INTO table_a..;
IF(_is_insert1_succeed) {
	INSERT INTO table_b...;
    IF(_is_insert2_succeed) {
    //처리 완료
    } ELSE {
    	DELETE FROM table_a WHERE ...;
        IF(_is_delete_succeed) {
        // 처리 실패 및 table_a, table_b 모두 원상 복구 완료
        } ELSE {
        // 해결 불가능한 심각한 상황 발생
        }
     }
  }

위 애플리케이션 코드가 장난처럼 작성한 코드 같지만 트랜잭션이 지원되지 않는 MyISAM에 레코드를 INSERT 할 때 위와 같이 하지 않으면 방법이 없다. 코드를 이렇게 작성하지 않았다면, 부분 업데이트의 결과로 쓰레기 데이터가 테이블에 남아있을 가능성이 있다.

하지만 트랜잭션이 지원되는 InnoDB 테이블에서 처리한다고 가정하면 간단한 코드로 완벽한 구현이 가능하다. 이러한 이유들로 왜 InnoDB를 더 많이 사용하는지 알 것 같다.

MySQL에서 제공하는 락

MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 된다.

MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락(Metadata Lock) 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named Lock)이라는 잠금 기능도 제공한다.

글로벌 락

글로벌 락(Global Lock)은 아래의 명령으로 획득할 수 있다. MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.

FLUSH TABLES WITH READ LOCK

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

글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

MySQL 서버가 업데이트되면서 MyISAM이나 MEMORY 스토리지 엔진보다는 InnoDB 스토리지 엔진의 사용이 일반화됐다. InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.

또한 MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다. 그래서 MySQL 8.0 버전부터는 Xtrabackup이나 Enterprise Backup과 같은 경량 백업 툴들이 도입되었다.

mysql> LOCK INSTANCE FOR BACKUP;
-- // 백업 실행
mysql> UNLOCK INSTANCE;

백업락과 레플리카 서버

하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용된다. 일반적인 MySQL 서버의 구성은 소스 서버(Source Server)와 레플리카 서버(Replica Server)로 구성되는데, 백업은 주로 레플리카 서버에서 실행된다.

하지만 레플리카 서버에서 백업을 실행하는 도중에 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있다.

물론 위에서 말한 백업 툴들을 사용하면, 백업이 진행되는 상태에서도 일관된 백업을 할 수 있다. 하지만 백업 툴들이 실행되는 와중에 스키마가 변경된다면 백업은 실패하게 된다.

만약 몇 시간 동안 백업이 실행하고 있었는데, 갑자기 DDL 명령 하나로 인해 백업이 실패하면 다시 그 시간만큼 백업을 다시 실행해야 할 것이다.

MySQL 서버의 백업 락은 이런 문제를 해결하기 위한 목적으로 도입되었으며, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

테이블 락

테이블 락(Table Lock)은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적으로는 아래와 같은 명령으로 특정 테이블의 락을 획득할 수 있다.

LOCK TABLES table_name [ READ 또는 WRITE]

락은 Read와 Write 중 옵션으로 획득할 수 있는데, 아래와 같은 특징이 있다.

읽기 락 - Read Lock(Shared Lock)

데이터 읽기를 위한 잠금
Insert, Update, Delete DML은 사용이 불가능
하나의 row를 여러 세션에서 접근할 수 있음

항상 동일한 데이터 조회를 위해 읽기락을 획득한 세션 전체에서 Select를 제외한 DML의 사용이 불가능하다. 그렇기에 어느 세션에서나 동일한 데이터 조회를 보장할 수 있다.

쓰기 락 - Write Lock(Exclusive Lock)

데이터 쓰기를 위한 잠금
다른 세션에서 락이 걸린 데이터를 조회하거나 수정할 수 없음
다른 세션에서 동시에 같은 테이블에 대한 읽기 락, 쓰기 락 모두 획득이 불가능

쓰기락을 획득한 세션에서 데이터가 변경될지 모르는 상태에서 다른 세션이 데이터를 조회하게 될 경우 Non_Repeatable Read 또는 팬텀 리드 현상이 발생할 수 있기 때문에 락을 획득하지 않은 세션에서는 조회가 불가능하다.

명시적으로 획득한 잠금은 아래의 명령으로 잠금을 반납(해제)할 수 있다.

UNLOCK TABLES

명시적인 테이블 락도 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다. 명시적으로 테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문이다.

묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제된다.

하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 락을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.

더 정확히는 InnoDB 테이블에도 테이블 락이 설정되긴 하지만 대부분의 DML 쿼리에서는 무시되고, DDL의 경우에만 영향을 미친다.

InnoDB에서는 DML에 대해서 묵시적인 락을 제공하지 않는데, 어떻게 데이터의 무결성을 보장할 수 있을까?

위에서 말했듯이 InnoDB 엔진은 dml 작업에 대해 묵시적인 락을 제공하지 않는다. 그러나 트랜잭션 기능을 사용하여 데이터의 무결성을 보장한다. 따라서 조회 쿼리에도 트랜잭션을 사용하는게 좋다.

트랜잭션(Isolation) 단계 : MyISAM과 MEMORY 스토리지 엔진과 다르게, 트랜잭션 격리 수준을 지원하여 동시성 문제를 해결한다. 기본적으로 InnoDB는 리피터블 리드(Repetable Read) 격리 수준을 사용하며, 트랜잭션이 실행되는 동안 데이터의 일관성을 보장한다.

리피터블 리드(REPETABLE READ) 격리 수준이란?
특정 로우를 조회시 항상 같은 데이터를 응답하는 것을 보장하는 격리 수준이다. 하지만, SERIALIZABLE과 다르게 행이 추가되는 것을 막지는 않는다. 이로 인해 팬텀 리드 현상이 발생할 수 있다. 다만, InnoDB는 특별한 구조로 인해 팬텀 리드 현상이 발생하지 않는다.

네임드 락

네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다.

네임드 락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금이다. 명확한 해당 업무 요건이나 로직이 필요할 경우 사용할 수 있지만, MySQL에서는 네임드 락 이외 대표적으로 Redis나 Zookeeper와 같은 솔루션을 통한 분산 락을 구현할 수 있기 때문이다.

예를 들어, 데이터베이스 서버 1대에 여러 개의 웹 서버가 접속해서 서비스하는 상황에서 여러개의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 쉽게 해결할 수 있다.

메타데이터 락

메타데이터 락(Metadata Lock)은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE bebe1 TO bebe2"와 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

하지만 실제 서비스에서 bebe1 테이블로 요청이 들어오는 와중에 테이블 명이 변경된다면, "해당하는 테이블이 없습니다" 같은 경고 메세지가 발생할 것이다. 이 문제를 해결하기 위해서는 하나의 rename table 쿼리에 여러개의 조건을 넣어야 한다.

RENAME TABLE bebe1 TO bebe2, bebe3 TO bebe1

당연하게도 변경되기 전의 bebe1 테이블과 bebe3 테이블은 동일해야 한다. 하지만 bebe3 테이블이 bebe1 테이블과 동기화되기 위해서는 복제(replication)가 되어야 하는데, MySQL에서 제공하는 DDL로 복제를 한다면 시간이 꽤 오래 걸릴 것이다.(MySQL에서 DDL은 싱글 스레드로 동작하기 때문)

InnoDB - 락

InnoDB에서 제공하는 락 모니터링 스키마

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 레코드 기반의 잠금 방식 때문에 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있다.

하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다.

예전 버전의 MySQL 서버에서는 InnoDB의 잠금 정보를 진단할 수 있는 도구라고는 lock_monitor와 SHOW ENGINE INNODB STATUS 명령이 전부였다. 하지만 모두 어셈블리 코드를 보는 것 같아서 이해하기 어렵다는 단점이 있다.

이러한 문제로 최신 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다. MySQL 서버의 information_scheme 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블이 있다.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

해당 테이블들을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있다.

그리고 조금씩 업그레이드되면서 InnoDB의 중요도가 높아졌고, InnoDB의 잠금에 대한 모니터링도 더 강화되면서 Performance Schema를 이용한 InnoDB 스토리지 엔진의 세마포어(내부 잠금)에 대한 모니터링 방법도 추가되었다.

InnoDB 락 종류와 개념

InnoDB에서는 MySQL에서 제공하는 락과는 별도로 락을 제공하고 있다. 총 8가지의 락을 제공하고 있으며 다음과 같다.

Shared Lock(읽기 락) / Exclusive Lock(쓰기 락)
Intention Lock(의도 락)
Record Lock(레코드 락)
Gap Lock(갭 락)
Next-Key Lock(넥스트 키 락)
Auto-Increment Lock(자동 증가 락)
Insert Intention Lock(삽입 의도 락)

Shared Lock(S-Lock) - 공유 락, 읽기 락

만약 읽기 요청을 처리할 때 팬텀 리드(Phantom Read) 같은 현상을 방지해야하는 중요한 작업이라면 S-Lock이 사용될 수 있다. MySQL에서는 리피터블 리드를 Default로 사용하고 있긴 하지만, 격리 수준을 커스텀 했다면 논 리피터블 리드 현상도 방지할 수 있다.

S-Lock은 레코드 레벨의 락이며, 해당 락을 보유하지 않은 트랜잭션도 해당 레코드를 읽을 수 있다. 다른 트랜잭션에서는 해당 레코드에 쓰기 작업을 할 수 없다.

다른 트랜잭션에서는 동일한 레코드에 S-Lock을 걸 수 있지만, X-Lock은 걸지 못한다. 또한 S-Lock 끼리는 동시에 접근이 가능하다. 즉, 하나의 Row를 여러 트랜잭션이 동시에 읽을 수 있다는 것이다.

하나의 Row를 여러 트랜잭션에서 읽을 수 있다는 것은 S-Lock의 고유한 특징이라고 보기는 어려울 것 같다. S-Lock을 적용하지 않아도 동시에 접근할 수 있으니 말이다.

공유 락은 FOR SHARE 키워드를 사용해서 걸 수 있다.

SELECT * FROM TABLE_INNODB WHERE id=1 FOR SHARE;

트랜잭션 1에서 특정 로우에 공유 락을 걸었으며, 트랜잭션 2에서 해당 로우에 접근할 때 기준 시간동안 Lock을 획득하지 못하면 타임아웃이 발생한다. 트랜잭션 1을 바로 커밋하는 경우엔 트랜잭션 2의 쿼리는 바로 실행된다.

상황 1. S-Lock이 걸린 Row 수정

상황 2. 서로 다른 S-Lock이 걸린 Row에 대한 수정

트랜잭션 각자 S-Lock이 걸린 상대의 row를 수정하면 아래와 같은 조건을 만족하면서 데드락이 발생한다.

상호 배제(Mutual Exclusion) : 한 번에 한 개의 프로세스만이 공유 자원을 사용할 수 있다.
- 하나의 자원에 대해서 여러개의 트랜잭션이 가지려 함 - 만족
점유와 대기(Hold and Wait) : 프로세스가 할당된 자원을 가진 상태에서 다른 자원을 기다린다.
- 해당 트랜잭션들은 각자가 S-Lock이 할당된 자원들을 가지고 있으며, 서로 각자의 자원들을 기다리고 있다. - 만족
비선점(No Preemption) : 프로세스가 작업을 마친 후 자원을 자발적으로 반환할 때 까지 기다림
- 각 트랜잭션들이 자발적으로 자원을 반환할 때 까지 기다리고 있다. - 만족
순환 대기(Circular Wait) : 프로세스의 자원 점유 및 점유된 자원의 요구 관계가 원형을 이루면서 대기하는 조건
- 트랜잭션 A가 트랜잭션 B를 필요로 하고, 트랜잭션 B가 트랜잭션 A를 필요로함 - 만족

논 리피터블 리드(Non Repeatable Read)

트랜잭션 내에서 같은 조회에 대해 트랜잭션 격리 수준마다 다른 값이 나오는 현상이다. T1은 3번 작업이 끝난 뒤에 커밋된다는 가정하에 아래와 같은 예를 들 수 있다.

1. T1에서 user_id = 1인 유저의 age를 19로 조회한다.
2. T2가 해당 유저의 age를 20으로 수정하고 커밋한다.
3. T1이 똑같은 쿼리를 수행했을 때, Read Uncommitted, Read Committed 레벨에서는 age를 20으로 조회하고, Repetable Read와 Serializable은 20으로 조회한다.

Exclusive Lock(X-Lock) - 배타 락, 쓰기 락

X-Lock은 특정 Row를 변경할 때 다른 트랜잭션이 해당 row에 접근해서 데이터 무결성이 깨지는 것을 방지하기 위해 사용한다. 특정 row에 X-Lock이 해제될 때 까지 다른 트랜잭션은 S-Lock, X-Lock을 모두 걸 수 없다.

X-Lock은 SELECT ~ FOR UPDATE나 UPDATE, DELETE 등의 DML을 실행할 때 걸리는 Lock이다. 배타 락은 FOR UPDATE 명령어를 통해 걸 수 있다.

SELECT * FROM TABLE_A WHERE id=1 FOR UPDATE;

상황 1. X-Lock이 걸린 Row를 S-Lock으로 조회

상황 2. X-Lock이 걸린 Row를 X-Lock으로 조회

유의할 점은 X-Lock을 걸었다고 해서 S-Lock이 아닌 Select도 못하는 것이 아니라, 기본적인 Select는 S-Lock이든 X-Lock이든 항상 조회할 수 있다.

Intention Lock - 의도 락

테이블과 레코드 각각에 락을 걸 수 있도록 해준다. 예를 들어 의도 락을 사용하면 다음과 같은 응용이 가능하다.

Intention Shared Lock(IS-Lock) - 의도 공유 락
- 트랜잭션이 테이블과 레코드에 모두 공유 락을 건다.

Intention Exclusive Lock(IX-Lock) - 의도 배타 락
- 트랜잭션이 테이블의 개별 레코드에 베타 락을 건다.

여기서 의문이 들 수 있다. 어떻게 테이블 락과 레코드 락이 공존할 수 있을까? 의도 공유 락을 사용할 때 테이블 레벨에 걸리는 락은 해당 테이블의 모든 레코드에 거는 락이 아닌, 테이블의 스키마 변경을 막을 수 있는 락이다. 그래서 테이블 락과 레코드 락이 공존할 수 있게 되면서 의도 락 설정이 가능한 것이다.

Record Lock - 레코드 락

레코드에 대해서 락을 거는 것이다. InnoDB의 경우 레코드 자체를 잠그는 것이 아닌, 인덱스의 레코드를 잠근다. 만약 인덱스가 없는 레코드라면 어떻게 처리할까?

InnoDB는 인덱스가 없더라도 클러스터링 키를 내부적으로 생성해줬던 것 처럼, 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

위에서 살펴봤던 문법과 같다.

# id = 1인 레코드에 대해 S-Lock
SELECT id FROM tb_1 WHERE id = 1 FOR SHARE;

# id = 1인 레코드에 대해 X-Lock
SELECT id FROM tb_1 WHERE id = 1 FOR UPDATE;

여기서 레코드 자체를 잠그느냐, 아니면 인덱스를 잠그느냐는 상당히 크고 중요한 차이를 만들어 낸다. 레코드가 아닌 인덱스의 레코드에 대해 락을 거는 이유는 다음과 같다.

InnoDB는 B+ Tree 인덱스를 통해 데이터를 저장하거나 검색하는데, 이 때 인덱스의 레코드를 탐색하여 특정 레코드를 찾아내기 때문에 인덱스의 레코드에 대한 접근이 필요하다.
인덱스에 대해서만 락을 걸기 때문에 다른 트랜잭션들이 접근하는 경우에도 충돌을 최소화하여 성능 자체에 도움이 된다.

Gap Lock - 갭 락

MySQL과 다른 DBMS와의 차이가 바로 갭 락이다. 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락은 아래의 간격에 대해서만 락을 건다.

레코드와 레코드 사이에 대한 락

갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다. 따라서 갭 락을 사용하게 되면 조회 쿼리를 여러 번 실행했을 때 다른 트랜잭션에서 수정이 발생하더라도 같은 결과가 리턴되도록 보장할 수 있다. 즉, 팬텀 리드 현상을 방지할 수 있다.

SELECT * FROM table_innodb WHERE id BETWEEN 1 AND 10;

위와 같은 쿼리가 있을 때 1~10 사이의 레코드들에 X-Lock이 걸리는 것과 동일한 효과를 낸다. 새로운 데이터가 삽입되려면 해당 트랜잭션이 커밋(Commit)될 때 까지 기다려야 한다.

Next Key Lock - 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 락을 넥스트 키 락이라고 한다. InnoDB에서는 테이블 인덱스를 검색할 때 실제 인덱스 레코드에 대해 S,X-Lock을 설정하여 락을 걸게 된다. 넥스트 키 락은 일반 갭 락보다 2가지 조건이 더 있다.

레코드와 레코드 사이에 대한 락 (1)
인덱스의 선두 레코드의 앞에 대한 락 (2)
인덱스의 마지막 레코드 뒤에 대한 락 (3)

SELECT * FROM table_innodb WHERE 10 < id AND id < 40 FOR UPDATE;

하지만 넥스트 키 락의 경우 해당 인덱스 레코드 이전의 갭에도 영향을 미치기 때문에 하나의 트랜잭션이 특정 레코드에 S,X-Lock을 걸게 된다면 다른 트랜잭션은 해당 트랜잭션 바로 앞의 갭에도 새로운 레코드를 생성할 수 없게 된다.

Statement 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 최소 리피터블 리드 격리 수준을 사용해야 한다.

또한 리피터블 리드 격리 수준은 innodb_locks_unsafe_for_binlog 시스템 변수가 반드시 비활성화 되어야 하는데, 이를 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

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

의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 Row 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.(MySQL 8.0 버전 부터는 바이너리 로그가 기본적으로 Row 형태로 작성된다.)

Auto-Increment Lock - 자동 증가 락

MySQL에서는 자동으로 증가하는 숫자 값을 추출하기 위해 auto-increment라는 컬럼 속성을 제공한다. auto-increment 컬럼이 사용된 테이블에 동시에 여러 레코드가 삽입되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.

InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 자동 증가 락이라고 하는 테이블 수준의 잠금을 사용한다.(MySQL 8.0에서는 래치를 사용한다. 자동 증가 락은 Insert와 Replace 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, 다른 DML에서는 걸리지 않는다.

래치란?
MySQL 8.0 버전부터 바이너리 로그 포맷이 Statement에서 Row로 바뀌었기 때문에 innodb_autoinc_lock_mode의 기본 값이 2로 바뀌었다. 해당 변수가 2로 설정되면 InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 Insert 문장으로 Insert되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지는 않는다.(유니크 한 값이 생성됨은 보장함)

테이블당 단 1개의 자동 증가 락만 존재하기 때문에, 2개의 Insert문이 동시에 실행되는 경우 나머지 하나는 락이 해제될 때 까지 기다려야 한다. 명시적으로 자동 증가 락을 획득하고 해제할 수는 없다.

auto-increment의 값이 증가만하고 줄어들지 않는 이유가 자동 증가 락 때문이다. 설령 Insert 쿼리가 실패했더라도 한 번 증가된 auto-increment 값은 다시 줄어들지 않고 그대로 남는다.

Insert Intention Lock - 삽입 의도 락

삽입 의도 락은 Insert 구문이 실행될 때 묵시적으로 설정되는 일종의 갭 락이다. 여러 개의 트랜잭션이 갭 내부의 서로 다른 위치에 삽입을 진행하려고 할 때 대기 없이 실행되도록 하는 것이 목적이다.

기본적인 갭 락 쿼리라면 트랜잭션 A가 id=5를 삽입하는 과정에서 이전 레코드인 3~7 사이에 갭 락이 걸릴 것이다. 트랜잭션 A를 처리하는 중 트랜잭션 B의 요청이 들어온다면 A가 커밋될 때 까지 대기해야하는 상황이 생긴다.

따라서 갭 락 대신 삽입 의도 락을 건다면 삽입 의도 락끼리는 충돌이 가능하기 때문에 대기 시간 없이 바로 삽입이 가능하다. 동시에 처리한다는 말은 완전히 동시에 처리된다는 뜻이 아닌, 트랜잭션 A와 트랜잭션 B가 같이 처리될 수 있다는 뜻이다.

'Database' 카테고리의 다른 글

[Database] MySQL의 네임드 락을 이용한 분산 락 구현하기 (1)	2023.11.01
[DB] HikariCP 최적화 알아보기 (0)	2023.10.14
[DB] MySQL 아키텍쳐 파헤치기 (1)	2023.03.02
[DB] 가용성과 데이터의 복제 (0)	2023.02.09
[Spring] Spring Boot + Redis 제대로 활용하기(1) (0)	2023.01.18

서론