[스터디] Real MySQL - 5장 트랜잭션과 잠금 (1/1)

Glen·2024년 6월 4일

mysql

RealMySQL-스터디

목록 보기

4/6

트랜잭션

MySQL에서의 트랜잭션

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아니다.

트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해주는 것이다.

간단한 예제로 트랜잭션 관점에서 InnoDB 테이블과 MyISAM 테이블의 차이를 살펴보면 다음과 같다.

MyISAM, InnoDB 스토리지 엔진을 사용한 테이블을 생성한 뒤, ID로 3을 가진 레코드를 생성한다.

create table myisam_table
(  
    id bigint not null,  
    constraint myisam_table_pk  
        primary key (id)  
) ENGINE = MyISAM;

create table innodb_table
(  
    id bigint not null,  
    constraint myisam_table_pk  
        primary key (id)  
) ENGINE = INNODB;

INSERT INTO myisam_table (id) values (3);
INSERT INTO innodb_table (id) values (3);

그 뒤 각 테이블에 해당 쿼리를 실행하고 결과를 보면 다음과 같이 나타난다.

# Duplicate entry '3' for key 오류가 발생하면 정상이다.
insert into myisam_table (id) values (1), (2), (3);  
insert into innodb_table (id) values (1), (2), (3);

select * from myisam_table;  
select * from innodb_table;

MyISAM

InnoDB

두 테이블에서 INSERT 쿼리가 PK 중복으로 인한 오류가 발생했지만, MyISAM의 경우 1, 2가 INSERT 된 상태로 남아 있는 것을 확인할 수 있다.

즉, MyISAM은 트랜잭션을 지원하지 않기 때문에 롤백이 되지 않은 것이다.

하지만 InnoDB 스토리지 엔진은 트랜잭션을 지원하기에 롤백이 된 것을 볼 수 있다.

트랜잭션은 상당히 골치 아픈 기능으로 생각하지만, 트랜잭션이란 그만큼 어플리케이션 개발에서 고민해야 할 문제를 줄여주는 아주 필수적인 DBMS의 기능이라는 점을 기억해야 한다.

만약 트랜잭션을 지원하지 않는 경우 다음과 같이 어플리케이션 레벨에서 롤백을 구현해야 한다.

boolean user1InsertResult = userDao.insert(user1);
if (user1InsertResult) {
    boolean user2InsertResult = userDao.insert(user2);
    if (user2InsertResult) {
        // 성공
    } else {
      user1DeleteResult = userDao.delete(user1);
      if (user1DeleteResult) {
          // 예외 발생하여 롤백 완료
      } else {
          // user1이 삭제되지 않은 상황 발생
          // user1을 어떻게든 삭제하는 추가적인 로직이 필요함
      }
    }
}

비즈니스 로직만 포함이 되어야 할 코드에 데이터 정합을 맞추기 위한 복잡한 분기가 들어가게 되므로 코드의 가독성이 매우 낮아지고, 이러한 조건이 여러 개가 될 경우 실수로 인해 버그가 발생할 확률이 매우 높다.

만약 트랜잭션을 사용한다면 다음과 같이 코드가 변경될 것이다.

// 물론 순수 비즈니스 로직만 보이려면, 트랜잭션 처리에 관한 코드 또한 없애야 한다.
try {
    txManager.begin();
    userDao.insert(user1);
    userDao.insert(user2);
    txManager.commit();
} catch (Exception e) {
    txManager.rollback();
}

주의사항

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

이는 어플리케이션 코드에서 트랜잭션의 범위를 최소화하라는 의미다.

Spring을 사용한 코드를 예시로 들면 다음과 같다.

@Transational
public SingupResult signup(String email) {
    Member member = new Member(email);
    memberRepository.save(member);
    
    emailClient.sendEmail(email); // @Async를 사용하지 않았다고 가정
    
    EmailHistory emailHistory = new EmailHistory(member.getId());
    emailHistoryRepository.save(emailHistory);
}

위 코드에서 트랜잭션은 메서드가 전체가 범위이다.

하지만 트랜잭션에는 DBMS의 정합을 보장하기 위한 코드만 포함되어야 하는데, 이메일 발송 코드가 포함이 되어있다.

이메일 발송은 외부 네트워크 연결이 필요한데, 네트워크 연결이 얼마나 걸릴 지 알 수 없다.

만약 이메일 서버가 불안정해서 연결에 10초가 걸렸다면, 해당 트랜잭션은 10초간 묶이게 된다.

그리고 이는 동시 처리 성능을 매우 떨어트리는데, 기본으로 스프링의 DB 커넥션 풀은 10개이므로 하나만 사용할 수 없어도 매우 치명적이다. (가용성 10% 감소)

또한 커넥션 10개가 모두가 묶여 커넥션 풀이 고갈 상태가 된다면, 단순한 조회 요청이라도 처리할 수 없는 답도 없는 상태가 된다.

커넥션 풀 =: 세마포어

따라서 이를 염두하고 트랜잭션을 적용할 곳과 적용하지 않을 곳을 구분해야한다.

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않는다.

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

네임드 락으로 분산 락을 사용할 수 있다. 하지만 이후 설명하겠지만 DBMS로 분산 락을 사용하는 것보다 Redis를 사용하여 분산 락을 사용하는게 더 나은 선택 같아 보인다.

글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 DML 또는 DDL을 실행하는 경우 글로벌 락이 해제될 때까지 대기된다.

글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.

여러 데이터베이스에 존재하는 MyISAM, MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

글로벌 락을 거는 FLUSH TABLES WITH READ LOCK 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 읽기 잠금을 건다.
글로벌 락이 걸리기 전 테이블이나 레코드에 쓰기 잠금을 거는 SQL이 실행됐다면, 해당 테이블에 읽기 잠금을 걸기 위해 먼저 실행된 잠금이 해제될 때 까지 기다려야 한다. (쓰기 잠금인 X락은 읽기 잠금인 S락과 공존할 수 없다.)
글로벌 락은 테이블에 읽기 잠금을 걸기 전에 테이블을 플러시해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료돼야 한다.
그래서 장시간 SELECT 쿼리가 실행되고 있을 때 FLUSH TABLES WITH READ LOCK 명령은 SELECT 쿼리가 종료될 때까지 기다려야 한다.

이러한 상황이 최악의 케이스로 실행되면 MySQL의 모든 테이블에 대한 SELECT를 제외한 DML이 아주 오래 실행되지 못하고 대기해야 할 수 있다.
따라서 일반적으로 글로벌 락은 사용하지 않는 것이 좋다. 또한 mysqldump는 알지 못하는 사이에 이 명령을 내부적으로 실행할 수 있으므로, 사용하는 옵션에 따라 자세히 확인해야 한다.
(--source-data를 사용시 글로벌 락을 획득한다고 나와있다)

글로벌 락은 위에 설명했듯, MySQL의 모든 변경 작업을 멈춘다.

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

때문에 조금 더 가벼운 글로벌 락의 필요성이 생겼고, MySQL 8.0 이후 백업 락이 도입됐다.

LOCK INSTANCE FOR BACKUP;
# 백업 수행
UNLOCK INSTANCE;

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

하지만 백업 락은 일반적인 테이블의 DML은 허용된다.

일반적으로 MySQL의 구성은 소스(마스터) 서버와 레플리카(슬레이브) 서버로 구성되는데, 이때 백업은 레플리카에서 이뤄진다.

하지만 백업에서 글로벌 락을 획득하면 복제는 백업 시간 만큼 지연될 수 밖에 없다.

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

물론 XtraBackup, Enterprise Backup 툴은 복제가 진행되는 상태에서도 일관된 백업을 만들 수 있다.

하지만 백업 도중 DDL 명령이 실행되면 백업이 실패하게 된다.

따라서 백업 락은 이러한 목적으로 도입됐으며, 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

책 내용이 약간 이해하기 어려운데, XtraBackup, Enterprise Backup에서는 글로벌 락을 사용하지 않으니, 백업 도중 DDL이 실행되면 백업이 실패한다는 것 같다. (글로벌 락을 걸면 복제를 할 수 없으니 장애가 될 수 있음)
따라서 백업 락을 걸고 백업 도중 복제에서 DDL 명령이 들어오면 락 때문에 대기 상태로 들어가니, 복제가 일시 중단되고 백업은 계속 진행된다는 것 같다.

테이블 락

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

명시적으로는 LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블의 락을 획득할 수 있다.

테이블 락은 MyISAM뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있다.

명시적으로 획득한 락은 UNLOCK TABLES 명령으로 잠금을 해제할 수 있다.

하지만 테이블 락의 경우 특별한 상황이 아니라면 어플리케이션에서 사용할 필요가 거의 없다.

글로벌 락과 마찬가지로 실시간 환경에 큰 영향을 미치기 때문이다.

묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리(DDL, UPDATE, SELECT FOR UPDATE 등)를 실행하면 발생한다.

MySQL에서 데이터가 변경되는 테이블에 잠금을 설정하고, 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.

즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동 해제된다.

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

책에서는 InnoDB 테이블에 테이블 락이 설정되면 DML은 무시되고 DDL만 영향을 미친다고 나와있는데, 테이블 락이 걸리면 DML 또한 대기된다!
혹시나 테이블 락이 걸려도 DML이 실행되는구나 라고 오해하면 안 된다!

네임드 락

네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대한 잠금을 설정할 수 있다.

네임드 락은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라, 그저 문자열에 대한 락을 획득하고 해제하는 것이다.

select get_lock('lock', 60); # 'lock'이라는 문자열에 대해 60초 동안 락을 획득하려고 시도한다.  

select release_lock('lock'); # 'lock'이라는 문자열에 대해 락을 해제한다.

select is_free_lock('lock'); # 'lock'이라는 문자열에 대한 락이 획득되었는지 확인한다.

# 위 함수 모두 정상으로 락을 획득하고 해제하면 1을 반환하고, 그게 아니면 0 또는 null을 반환한다.
# 0과 null이 발생하는 경우는 상황에 따라 다른데, 자세한건 공식 문서를 참고하자.

자바 코드로 비슷하게 구현하면 다음과 같다.

public class NamedLock {  
  
    private final Map<String, ReentrantLock> namedLocks = new ConcurrentHashMap<>();  
  
    public Integer getLock(String name, int timeout) throws InterruptedException {  
        ReentrantLock lock = namedLocks.getOrDefault(name, new ReentrantLock());
        try {
            if (lock.tryLock(timeout, TimeUnit.SECONDS)) {  
                return 1;  
            }  
            return 0;  
        } catch(InterruptedException e) {
            Thread.currentThread().interrupt();
            return null;
        }
    }  
  
    public int isFreeLock(String name) {  
        ReentrantLock lock = namedLocks.get(name);  
        if (lock != null && lock.isLocked()) {  
            return 0;  
        }  
        return 1;  
    }  
  
    public int releaseLock(String name) {  
        ReentrantLock lock = namedLocks.get(name);  
        if (lock == null || lock.isLocked()) {  
            return 0;  
        }  
        lock.unlock();  
        namedLocks.remove(name);
        return 1;  
    }  
}

네임드 락은 자주 사용되지는 않는다.

하지만 특수한 경우 네임드 락이 도움이 될 수 있다.

분산된 서버에서 실행하고 데이터를 변경하는 스케줄링, 배치 작업의 경우 데드락의 원인이 될 수 있는데 이때 네임드 락을 활용하면 간단히 해결할 수 있다.

MySQL 8.0 부터는 네임드 락을 중첩해서 사용할 수 있게 됐으며, 세션에서 획득한 락을 한 번에 모두 해제하는 기능도 추가됐다.

다만 스프링을 사용하며 네임드 락을 사용할 때는 주의해야 하는 것이, 트랜잭션이 끝났다고 락을 자동으로 해제시켜주지 않는다!
트랜잭션 범위에서 락을 해제하지 않으면 커넥션 풀에 커넥션이 반환되어, 해제할 수 없는 상황(데드락)이 발생할 수 있다.
따라서 네임드 락에는 최대 트랜잭션 유지 시간을 두어 데드락이 발생하지 않도록 해야한다.

또한 synchronized와 @Transactional을 사용한 것과 비슷한 문제가 발생할 수 있기에 실제 네임드 락 사용은 한정되어 보인다. (커밋 이전에 락이 해제되므로 동시성 문제 발생)
(분산 환경에서 동일 시간대 스케줄링이 돌아가는 로직이 있을 때, 타임아웃을 0으로 가져서 동시성 문제를 막는 경우에만 유용해보임)

메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블, 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니라 RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

RENAME TABLE의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다.

만약 기존 테이블을 백업하고, 새로운 테이블로 교체를 해야하는 상황에 다음과 같이 사용하면 다른 쿼리에서 Table not found ... 오류를 발생시키지 않게 할 수 있다.

RENAME TABLE rank TO rank_backup, rank_new TO rank;

만약 위 쿼리를 다음과 같이 2개로 나눈다면 Table not found ... 오류가 발생할 수 있다.

RENAME TABLE rank TO rank_backup;
# 이 사이에서 오류 발생
RENAME TABLE rank_new TO rank;

또한 메타데이터 락과 테이블 락을 사용해서 규모가 큰 테이블 구조의 변경을 대응할 수 있다.

CREATE TABLE access_log_new (...);

INSERT INTO access_log_new select * from access_log where id >= 0 and id < 10000;
INSERT INTO access_log_new select * from access_log where id >= 10000 and id < 20000;
...

SET autocommit=0;

LOCK TABLE access_log WRITE, access_log_new WRITE;

SELECT MAX(id) as @MAX_ID FROM access_log_new;
INSERT INTO access_log_new SELECT * FROM access_log where id > @MAX_ID;
COMMIT;

RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;
UNLOCK TABLES;

DROP TABLE access_log_old;

InnoDB 스토리지 엔진 잠금

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

InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있지만, 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다.

예전 버전의 MySQL에선느 InnoDB의 잠금 정보를 진단할 수 있는 도구는 lock_monitor, SHOW ENGINE INNODB STATUS 명령이 전부였지만, 이후 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.

performance_schema 데이터베이스의 data_locks 테이블을 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있다.

책에서는 information_schema 데이터베이스를 조회하라고 하지만, 해당 테이블은 innodb_trx를 제외하고 MySQL 8.0부터 삭제되었다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업(락 에스컬레이션)되는 경우는 없다.

일반 사용 DBMS와는 조금 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(Gap) 락이라는 것이 존재한다.

mysql> SELECT * FROM t;
+----+
| id |
+----+
|  5 |
| 10 |
| 42 |
+----+

--(                        the gap before the row #5

   5                       the row #5 itself

    )---(                  the gap before the row #10

         10                the row #10 itself

           )-----(         the gap before the row #42

                  42       the row #42 itself

                    )--->  the gap before infinity

출처

레코드 락

레코드 자체만을 잠그는 것을 레코드 락(Record lock)이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다.

가장 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다.

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

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

주의할 점으로 PK 또는 유니크 인덱스에 대해서 반드시 레코드 락이 걸리지는 않는다.
만약 해당 인덱스에 대한 레코드가 존재하지 않으면 갭 락이 걸린다.
자세한 것은 당근 기술 블로그 글의 Gap Lock으로 인한 Dead Lock 항목 참조

존재하지 않는 인덱스(PK)에 대해 잠금

select * from innodb_table where id = 2 for update;

존재하는 인덱스(PK)에 대해 잠금

select * from innodb_table where id = 5 for update;

LOCK_MODE에 대해서 정리해둔 목록은 해당 아티클의 하단 부분 참조

갭 락

다른 DBMS와의 또 다른 차이가 바로 갭 락이다.

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다.

갭 락은 그 자체보다는 이어서 설명할 넥스트 키 락의 일부로 자주 사용된다.

갭 락은 1. REPEATABLE READ를 보장하기 위해, 2. Replication 일관성 보장(bin log = Statement, Mixed), 3. Foreign Key 일관성 보장을 위해 사용된다고 한다.
https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

따라서 갭 락은 READ COMMITTED 이하 격리 수준에서는 적용되지 않는다.

넥스트 키 락

레코드 락과 갭 락을 합친 형태를 넥스트 키 락이라고 한다.

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL에서는 REPEATABLE READ 격리 수준을 사용해야 한다.

또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

innodb_locks_unsafe_for_binlog 변수는 MySQL 8.0 이후 삭제되었다.

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

그런데 이러한 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.

이에 대한 자세한 내용은 당근 기술 블로그 글 참조하면 좋을 것 같다.

가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

MySQL 8.0 이후 ROW 포맷의 바이너리 로그가 기본 설정으로 변경됐기에, 따로 설정할 필요는 없을 것 같다.
또한 MySQL 8.0.34 이후 binlog_format은 더 이상 사용되지 않으며, ROW 포맷의 바이너리 로그 형식만 지원한다.

자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다.

AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.

InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

AUTO_INCREMENT 락은 INSERT, REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다.

InnoDB의 다른 잠금(레코드 락, 넥스트 키 락)과는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다.
(AUTO_INCREMENT 컬럼에 명시적으로 값을 설정하더라도 AUTO_INCREMENT 락을 건다)

AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다.

그리고 위 설명은 MySQL 5.0 이전에 사용되던 방식이고, 이후에는 innodb_autoinc_lock_mode 변수를 이용해 AUTO_INCREMENT 락의 작동 방식을 변경할 수 있다.

innodb_autoinc_lock_mode = 0

MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 AUTO_INCREMENT 락을 사용한다.

innodb_autoinc_lock_mode = 1

단순히 한 건 또는 여러 건의 레코드를 INSERT 하는 SQL 중에서 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 AUTO_INCREMENT 락을 사용하지 않고, 훨씬 가볍고 빠른 뮤텍스를 이용해 처리한다.

래치는 AUTO_INCREMENT 락과 달리 아주 짧은 시간 동안만 잠금을 걸고, 필요한 자동 증가 값을 가져오면 즉시 잠금을 해제한다.

하지만 INSERT ... SELECT와 같이 MySQL이 건수를 예측할 수 없을 때는 AUTO_INCREMENT 락을 사용한다.

이때는 INSERT 문장이 완료되기 전까지는 다른 커넥션에서 INSERT를 실행하지 못하기 때문에 동시 처리 성능이 나빠진다.

따라서 대량 INSERT가 수행될 때는 여러 개의 AUTO_INCREMENT 값을 한 번에 할당받아 INSERT 되는 레코드에 사용한다.

하지만 한 번에 할당받은 자동 증가 값이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장 이후 INSERT 되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다.

INSERT INTO my_table VALUES ('a'), ('b'), ('c'); # a == 1, b == 2, c == 3
INSERT INTO my_table VALUES ('d'); # d != 4

innodb_autoinc_lock_mode = 2

해당 설정에서 InnoDB 스토리지 엔진은 AUTO_INCREMENT 락을 사용하지 않고 뮤텍스를 사용한다.

하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 않는다.

ex) INSERT INTO my_table VALUES ('a'), ('b'); # a == 1, b != 2

대량 INSERT가 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다.

하지만 이 설정에서는 AUTO_INCREMENT 값이 유니크한 값이 생성된다는 것만 보장하기에, STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버간 부정합이 발생할 수 있기에 주의해야 한다.

MySQL 8.0부터 바이너리 로그 포맷이 ROW 포맷이 기본 값이 되었기에, innodb_autoinc_lock_mode = 2 설정이 기본으로 사용된다.
하지만 MySQL 8.0.34 이후 복제 설정에는 ROW 포맷만 사용하라고 하므로, 해당 설정을 변경할 이유는 없어보인다.

더 자세한 내용은 MySQL 공식 문서를 참고하면 좋을 것 같다.

또한 AUTO_INCREMENT 값은 잠금을 최소화하기 위해 롤백되더라도 다시 줄어들지 않는다.

인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있기 때문에 주의해야 한다.

레코드 락에서 언급했듯이, InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드에 모두 락을 걸어야 한다.

INSERT INTO innodb_table VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');

UPDATE innodb_table SET id = 6 WHERE name = 'A';

name 컬럼에 인덱스가 없는 상태에서 name 컬럼에 조건이 걸리면, 풀 테이블 스캔이 발생하므로 모든 행에 락이 걸린다.

따라서 MySQL의 InnoDB 스토리지 엔진을 사용할 때, 인덱스 설계가 중요한 이유가 이것이다.

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

InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡하다.

테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있다.

하지만 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.

이때는 performance_schema 데이터베이스의 data_locks 테이블을 조회하여, 잠금을 가지고 멈춰있는 스레드 ID를 찾아 강제 종료를 하면 된다. (mysql> KILL 17)

MySQL의 격리 수준

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

격리 수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE의 4가지로 나뉜다.

여기서 DIRTY READ가 발생하는 READ UNCOMMITTED과 동시 처리 성능을 매우 떨어트리는 SERIALIZABLE은 거의 사용되지 않는다.

4개의 격리 수준에서 순서대로 뒤로 갈수록 트랜잭션의 격리 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적이라고 볼 수 있다.

하지만 MySQL의 경우 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.

데이터베이스의 격리 수준을 이야기하면 항상 언급되는 세 가지 부정합의 문제점이 있다. (DIRTY READ, NON-REPEATABLE READ, PHANTOM READ)

SQL-92, SQL-99 표준에 따르면 REPEATABLE READ 격리 수준에서는 PHANTOM READ가 발생할 수 있지만, InnoDB에서는 독특한 특성(갭 락) 때문에 PHANTOM READ가 발생하지 않는다.

하지만 REPEATABLE READ에서도 특수한 경우에 PHANTOM READ가 발생한다.

READ UNCOMMITTED

가장 낮은 격리 수준

다른 트랜잭션에서 변경하고 커밋하지 않은 데이터를 읽을 수 있다.

대표적으로 Dirty Read가 발생한다.

Dirty Read

한 트랜잭션에서 커밋하지 않은 데이터를 다른 트랜잭션이 읽을 수 있는 현상

트랜잭션 A가 데이터를 수정하고, 커밋하지 않은 상태에서 트랜잭션 B가 해당 내역을 조회할 수 있다.

이때, 트랜잭션 A가 롤백하면, 해당 데이터는 반영되지 않았지만, 트랜잭션 B에서는 조회가 되었기 때문에 정합성에 문제가 발생한다.

2번 쿼리를 실행하면 다음과 같이 데이터가 조회된다.

하지만 3번 쿼리를 실행하고 다시 2번 쿼리를 실행하면 다음과 같이 커밋되지 않은 데이터가 조회된다.

READ COMMITTED

다른 트랜잭션에서 커밋한 데이터만 읽을 수 있다.

Oracle의 경우 기본 격리 수준

대표적으로 Non-Repeatable Read가 발생한다.

Non-Repeatable Read

한 트랜잭션 내에서 같은 데이터를 여러 번 읽을 때, 중간에 다른 트랜잭션에 의해 값이 변경되어 다른 값을 읽는 현상

트랜잭션 A가 값을 변경하고, 트랜잭션 B가 값을 조회했을 때, 트랜잭션 A는 아직 커밋을 하지 않았기 때문에 변경 사항이 조회되지 않는다.

하지만 트랜잭션 A가 커밋을 하면, 트랜잭션 B에서 변경 사항이 조회된다.

2번 쿼리를 실행하면 다음과 같이 데이터가 조회된다.

하지만 3번 쿼리를 커밋하고, 2번 쿼리를 실행하면 변경된 데이터가 조회된다.

REPEATABLE READ

트랜잭션 내에서 같은 데이터를 여러 번 읽을 때 항상 동일한 결과를 반환한다.

MySQL의 InnoDB 스토리지 엔진에서 기본 격리 수준

대표적으로 Phantom Read가 발생한다.

Phantom Read

한 트랜잭션 내에서 특정 조건의 데이터를 두 번 이상 읽을 때, 첫 번째 조회와 두 번째 조회 사이에 다른 트랜잭션에 의해 데이터가 추가, 삭제되어 결과가 달라지는 현상

트랜잭션 A가 값을 조회했을 때, 한 건의 데이터만 조회된다. 이때 트랜잭션 B가 새로운 값을 추가하고 커밋한 뒤, 트랜잭션 A가 다시 값을 조회했을 때 트랜잭션 B에서 추가된 값이 조회된다.

다른 트랜잭션에서 2번 쿼리를 실행하여 데이터를 조회한 뒤, 다른 트랜잭션에서 3번 쿼리를 실행 후 커밋을 해도, 다른 트랜잭션에서 다시 조회를 해도 기존의 데이터가 조회된다.

이것이 바로 MVCC를 사용하여 Phantom Read가 발생하지 않는 것이다.

2번 쿼리를 호출하는 트랜잭션이 10번, 3번 쿼리를 호출하는 트랜잭션이 11번이라고 할 때, 언두 로그에는 11번 트랜잭션에 대한 레코드가 기록된다.
이때 해당 트랜잭션 번호를 가진 트랜잭션은 자기의 번호보다 작은 트랜잭션 번호에서 변경한 것만 보게 하여 Phantom Read를 막는다.

하지만 특수한 상황에서는 Phantom Read가 발생한다.