[스터디] Real MySQL 8.0 1권 - 5장 트랜잭션과 잠금 정리

트랜잭션

트랜잭션이란?

• 데이터 정합성을 보장하는 기능
  • 정합성: (서로 다른 커넥션에서) 데이터가 서로 일치하는 상태
• 작업의 완전성을 보장해 주는 기능
  • 작업의 완전성: 논리적인 작업을 모두 완벽하게 처리 or 처리 실패 시 모두 원 상태로 복구해서 부분 업데이트를 방지하는 기능

MySQL의 트랜잭션

• 논리적인 작업 셋 자체가 (COMMIT 할 때) 100% 적용 되거나 (ROLLBACK or ROLLBACK 자체가 오류가 발생했을 때) 아무것도 적용되지 않음을 보장해주는 것
• 한 개의 쿼리에서 여러 값을 저장할 때 MyISAM과 InnoDB의 차이
  • MyISAM ❌: 트랜잭션 지원 X => 실패된 부분만 롤백 (부분 업데이트) => 찌꺼기 데이터를 별도로 지우는 코드가 필요
  • InnoDB ✅: 트랜잭션 지원 O => 모두 롤백 (정합성 보장)

트랜잭션 주의사항

• 프로그램 코드에서 트랜잭션의 범위를 최소화 하라
  • 커넥션 시간이 길어질수록 사용가능한 커넥션 개수가 적어진다. => 커넥션 문제가 발생할 가능성이 높아진다.
  • 단순 조회 작업이 변경 작업에 껴있으면 분리하라 => 트랜잭션 범위를 줄일 수 있다.
  • 트랜잭션 범위에 다른 네트워크 작업은 반드시 빼라. => 네트워크 작업에 문제가 생기면 DBMS까지 장애가 전파된다.

잠금

잠금이란?

• 동시성을 제어하기 위한 기능
  • 동시성 제어: 여러 커넥션에서 동시에 동일한 자원(레코드 또는 테이블)을 요청할 때 순서대로 한 시점에 하나의 커넥션만 변경할 수 있게 해주는 역할
• 격리 수준: 하나의 트랜잭션 또는 여러 트랜잭션 간 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 수준

MySQL 엔진의 잠금

MySQL 엔진의 잠금 ⊃ 스토리지 엔진의 잠금

글로벌 락

-- 글로벌 락 획득
FLUSH TABLES WITH READ LOCK;

• MySQL에서 제공하는 가장 큰 범위의 잠금
• MySQL 서버 전체에 잠금
  • 테이블 정도가 아니라 다른 데이터베이스까지 잠금을 건다.
  • 잠금 이전에는 실행 중인 쿼리들이 수행되면 잠금을 얻는다.
  • 잠금 이후에는 SELECT 를 제외한 대부분의 DDL 과 DML 쿼리를 대기시킨다.
• mysqldump 로 일관된 백업을 받아야 할 때 사용한다.

백업 락

-- 백업 락 획득
LOCK INSTANCE FOR BACKUP;

-- 백업 실행...

-- 백업 락 반납
UNLOCK INSTANCE;

• MySQL 8.0 부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 트랜잭션을 지원하기 때문에 모든 데이터 변경을 멈추는 글로벌 락이 필요 없어짐.
  • Xtrabackup, Enterprise Backup 같은 백업 툴들의 안정적인 실행을 위해 가벼운 글로벌 락이 필요해짐
• 백업 락을 획득하면 모든 세션에서 테이블의 스키마 변경, 사용자 인증 관련 정보 변경을 할 수 없게 된다.
  • 일반적인 `테이블의 데이터 변경`은 허용

테이블 락

• 개별 테이블 단위로 잠금을 획득
• 테이블 락을 획득하는 방법

명시적 테이블 락

-- 명시적인 테이블 락 획득
LOCK TABLES table_name [READ | WRITE];

-- 현재 세션의 모든 테이블 락 반납
UNLOCK TABLES;

• LOCK TABLES ... 로 잠금을 획득하기 전 현재 세션에서 보유한 모든 테이블 잠금을 내재적으로 해제.

묵시적 테이블 락

-- 묵시적인 테이블 락: 데이터를 변경할 때 자동 획득/반납
UPDATE table_name SET column_name = 1;

• 테이블의 데이터를 변경하는 쿼리 실행 시 자동으로 획득
• 쿼리 완료 후 자동으로 반납
• InnoDB 는 레코드 기반 잠금이기 때문에 단순 데이터 변경으로 묵시적 테이블 락이 설정되진 않음.
  • 대부분 DML 쿼리는 무시하고, DDL 쿼리에만 적용

네임드 락

• 여러 클라이언트에서 서로 동기화를 처리할 때 네임드 락을 사용하면 쉽게 동기화 문제를 해결할 수 있다.

-- "mylock" 이라는 문자열에 대해 잠금을 획득 (성공 시 1, 실패 시 0)
-- 이미 잠금을 사용 중이면 2초 동안만 대기 (2초 후 자동 잠금 해제)
SELECT GET_LOCK('mylock', 2);

-- "mylock" 문자열에 대해 잠금이 설정돼 있는지 확인 (성공 시 1, 실패 시 0)
SELECT IS_FREE_LOCK('mylock');

-- 잠금 반납(해제) (성공 시 1, 실패 시 0)
SELECT RELEASE_LOCK('mylock');

-- 모든 잠금 해제
SELECT RELEASE_ALL_LOCKS();

• 한꺼번에 많은 레코드를 변경하는 배치 프로그램에서 자주 데드락 발생
  • 동일한 데이터를 변경(또는 참조)하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 쉽게 해결할 수 있다.
• MySQL 8.0 부터 네임드 락을 중첩해서 사용할 수 있게 됐다.

메타데이터 락

-- table_a 에서 table_b 로 테이블 이름 변경
RENAME TABLE table_a TO table_b;

• 데이터베이스 객체(테이블 또는 뷰)의 이름이나 구조를 변경할 때 획득하는 잠금 (DDL)
• 명시적으로 획득할 수 없다.
  • 예를 들어, 테이블 이름 변경 시 자동으로 획득
• 원본 이름과 변경할 이름 모두 한꺼번에 잠금
• 이름 변경 작업을 둘로 나눠서 실행하면 아주 짧은 순간에 변경할 테이블 이름이 존재하지 않아 오류가 발생할 수 있다.

-- 한 번에 실행하는 건 괜찮다
RENAME TABLE rank TO rank_backup, rank_new TO rank;

-- 두 개로 나눠서 실행하면 위험하다
RENAME TABLE rank TO rank_backup;
RENAME TABLE rank_new TO rank;

• INSERT INTO ... SELECT * FROM ...; 같이 INSERT 를 활용하는 구문에서 발생할 수 있는 문제
  • MySQL 서버는 DDL 을 단일 스레드로 처리한다 => 테이블 사이즈가 크면 시간이 오래 걸린다
  • 시간이 너무 오래 걸리면 언두 로그 증가와 Online DDL 버퍼의 크기가 문제가 된다.
  • 해결법:
    • 새 테이블 구조를 DDL 로 생성한 뒤, ... WHERE id >= 0 AND id < 10000;, ... WHERE id >= 10000 AND id < 20000; 와 같이 최근 데이터(1시간 직전 또는 하루 전)까지는 pk 값을 범위 별로 나눠서 여러 스레드로 빠르게 복사한다.
    • 나머지 값들은 테이블 락을 명시적으로 획득한 후 마저 복사한다.
    • 테이블 이름을 변경한 후, 사용하지 않게 된 테이블을 DROP 문으로 제거한다.

InnoDB 스토리지 엔진 잠금

• 레코드 기반의 잠금 방식 덕분에 MyISAM 보다 뛰어난 동시성 처리를 제공한다.
• MySQL 8.0 부터는 Performance Schema 를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마 포어)에 대한 모니터링도 할 수 있다.
• 락 에스컬레이션이 되는 경우는 없다.
  • 락 에스컬레이션: 레코드 락 => 페이지 락 또는 테이블 락으로 레벨업 되는 것

레코드 락

• 레코드 자체만을 잠근다.
  • InnoDB 는 인덱스의 레코드를 잠근다.
    • 인덱스가 없어도 내부적으로 자동으로 생성된 클러스터링 인덱스를 이용해 잠근다.

인덱스의 레코드를 잠그는 것 vs 레코드를 잠그는 것

• 인덱스가 없는 컬럼의 값을 변경할 때
  • 인덱스를 잠그는 방식: 인덱스에 해당하는 레코드만 잠근다.
    • 인덱스가 하나도 없으면?: 풀스캔 후 전부 잠근다 => InnoDB 에서 인덱스 설계의 중요성
  • 레코드를 잠그는 방식: 인덱스가 없기 때문에 관련 레코드를 모두 잠근다.

갭(GAP) 락

• 레코드와 바로 인접한 레코드 사이의 간격만 잠근다.
• 새로운 레코드가 생기는 것(INSERT)을 막는다.
• 그 자체보다 넥스트 키 락의 일부로 많이 사용된다.

넥스트 키 락

• 레코드 락과 갭 락을 합친 형태의 잠금
• 주목적: 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만든 결과와 동일한 결과를 만들도록 보장하는 것
• 넥스트 키 락 때문에 데드락이 발생하거나, 다른 트랜잭션을 기다리게 만드는 일이 자주 발생
  • 바이너리 로그 포맷을 ROW 포맷으로 바꿔서 넥스트 키락이나 갭 락을 줄이는 것이 좋다. (MySQL 8.0 에서는 기본값으로 ROW 포맷)

자동 증가 락

• INSERT, REPLACE 쿼리 문장같이 새로운 레코드를 저장하는 쿼리에만 락을 건다.
  • UPDATE, DELETE 쿼리에는 걸리지 않는다.
• auto_increment 락은 테이블에 단 하나만 존재한다.
  • 여러 INSERT 쿼리가 동시에 실행되면 한 쿼리에서 락을 얻기 때문에 나머지 쿼리는 해당 락을 기다려야 한다.
    • 명시적으로 값을 설정해도 자동 증가 락을 건다.
• 명시적으로 락을 획득하거나 해제할 수 없다.
• MySQL 5.1 이상부터 innodb_autoinc_lock_mode 시스템 변수로 자동 증가 락 방식을 변경할 수 있다.
  • MySQL 8.0 부터는 기본값이 2이다. (이전엔 1)
  • 자동 증가 락을 절대 걸지 않고, 경량화된 래치(뮤텍스)를 사용한다. => 동시성 처리 우수, but STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있기 때문에 주의!

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

• 레코드 각각에 걸리는 잠금은 잠긴 레코드가 자주 사용되지 않으면 오랜 시간 잠긴 상태로 남겨지며, 잘 발견되지 않다가 나중에 문제를 일으킬 수 있다.
• MySQL 8.0 부터 performance_schema 의 data_locks, data_lock_waits 테이블을 살펴보면 된다.

MySQL의 격리 수준

READ UNCOMMITED

• 커밋되지 않은 레코드도 읽을 수 있다.
  • dirty read 문제가 발생한다.
  • non-repeatable read 문제가 발생한다.
  • phantom read 문제가 발생한다.
  • 당연히 정합성 문제가 발생한다.

READ COMMITED

• dirty read 문제는 발생하지 않는다.
• non-repeatable read 문제가 발생한다.
• phantom read 문제가 발생한다.
• 언두 영역의 가장 최신 버전에 해당하는 레코드를 가져온다.
  • 트랜잭션 없이 실행되기 때문에 다른 트랜잭션에서 변경 사항이 반영된다.
  • phantom read 문제가 발생할 수 있다 = 정합성이 어긋날 수 있다.

REPEATABLE READ

• 트랜잭션 범위에서만 실행된다. => 동일한 트랜잭션 범위에선 하루종일 동일한 레코드에 대해 같은 값만 가져온다.
• InnoDB 스토리지 엔진의 갭 락, 넥스트 키 락 덕분에 pantom read 문제가 발생하지 않는다.
• 언두 영역: 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경 전 레코드를 저장하는 언두(Undo) 공간
• 언두 영역의 변경 전 데이터를 가져온다.
  • MVCC 적용: 자신의 트랜잭션 번호보다 작은 번호의 언두 레코드 중 가장 최근 데이터를 가져온다.
  • 트랜잭션을 종료하지 않으면 언두 영역이 무한정 커져 MySQL 서버의 처리 성능이 떨어진다.
  • SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 한다.
    • 하지만 언두 레코드에는 잠금이 불가능하다.
    • 따라서 현재 레코드의 값을 가져온다. => phantom read 문제가 발생
• 언두 영역은 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제된다.
• 바이너리 로그 포맷을 사용하는 MySQL 서버는 최소 REPEATABLE READ 격리 수준을 사용해야 한다.

SERIALIZABLE

• 읽기 작업 마저 공유 잠금(읽기 잠금)을 얻어야 하고, 다른 트랜잭션은 해당 레코드를 변경하지 못한다.
• InnoDB 스토리지 엔진에선 이미 phantom read 문제가 발생하지 않기 때문에 굳이 사용하지 않는다.