1. 책 RealMySQL8.0의 [5.2 MySQL 엔진의 잠금] 파트를 읽었다.
MySQL에서 사용되는 잠금은 크게 2가지로 나눌 수 있다.
- MySQL 엔진 레벨의 잠금 : 모든 스토리지 엔진에 영향
- 스토리지 엔진 레벨의 잠금 : 스토리지 엔진 간 상호 영향 X
📍 글로벌 락 <- 쓸 일 거의 없다
글로벌 락
FLUSH TABLES WITH READ LOCK
- MySQL에서 제공하는 락 가운데 가장 범위가 크다.
- 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL이나 DML을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.
- 영향을 미치는 범위는 MySQL 서버 전체.
작업 대상 테이블이나 데이터베이스가 달라도 동일하게 영향 미친다. - 실행과 동시에 MySQL에 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.
- 테이블에 read lock을 걸기 전에 먼저 테이블을 flush해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료돼야 한다.
-> 장시간 SELECT 쿼리 실행되고 있을 경우 FLUSH TABLES WITH READ LOCK은 SELECT 쿼리가 종료될 때까지 기다려야 한다.
백업 락
LOCK INSTANCE FOR BACKUP;
// 백업 실행
UNLOCK INSTANCE;
- 모든 세션에서 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.
- DB및 테이블 등 모든 객체 생성 및 변경, 삭제
- REPAIR TABLE과 OPTIMIZE TABLE 명령
- 사용자 관리 및 비밀번호 변경
- 일반적인 테이블의 데이터 변경은 허용
- 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할
📍 테이블 락 <- 쓸 일 거의 없다
테이블 락 : 개별 테이블 단위로 설정되는 잠금
- 명시적으로 테이블 락을 획득하는 법
LOCK TABLES table_name [READ | WRITE];
// 작업
UNLOCK TABLES;
- 묵시적으로 테이블 락을 획득하는 법
- 테이블에 데이터를 변경하는 쿼리를 실행하면 발생
- 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금 해제
(= 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제) - InnoDB 테이블의 경우, 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공
- 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.
- 대부분의 데이터 변경(DML) 쿼리에서는 무시되고, 스키마 변경(DDL) 쿼리의 경우에만 영향
📍 네임드 락
SELECT GET_LOCK('mylock', 2); // mylock이라는 문자열에 대해 잠금을 획득한다. 이미 잠금을 사용중이면 2초간 대기
SELECT IS_FREE_LOCK('mylock'); // mylock이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
SELECT RELEASE_LOCK('mylock'); // mylock이라는 문자열에 대해 획득했던 잠금을 반납한다.
- 테이블이나 레코드, 데이터베이스 객체가 아닌 사용자가 지정한 문자열에 대해 락을 획득하고 반납하는 잠금
- 한 세션이 Lock을 획득한다면, 다른 세션은 해당 세션이 Lock을 해제한 이후 획득할 수 있다.
- Lock에 이름을 지정하여 어플리케이션 단에서 제어가 가능하다.
- 장
- MySQL만을 사용해 분산락 구현 가능 (<-> Redis를 사용하면 인프라 구축, 유지보수 비용 발생)
- 단
- Lock이 자동으로 해제되지 않음 -> 별도의 명령어로 해제를 수행해주거나 선점시간이 끝나야 해제
=> Lock의 획득, 반납에 대한 로직을 철저하게 구현해야한다 - 일시적인 락의 정보가 DB에 저장되고, 락을 획득,반납하는 과정에서 DB에 불필요한 부하가 있을 수 있다
- 락과 비즈니스 로직의 트랜잭션을 분리할 필요가 있다
- Lock이 자동으로 해제되지 않음 -> 별도의 명령어로 해제를 수행해주거나 선점시간이 끝나야 해제
분산락이란?
- 분산 환경(ex. 서버 다중화)에서 상호 배제를 구현하여 동시성 문제를 다루기 위해 등장한 방법
- 락에 대한 정보를 '어딘가'에 공통적으로 보관하고 있어야 함
- 분산 환경에서 여러대의 서버들은 공통된 ‘어딘가’를 바라보며, 자신이 임계 영역(critical section)에 접근할 수 있는지 확인
-> 분산 환경에서 원자성(atomic)을 보장할 수 있게 된다.
그 ‘어딘가’로 활용되는 기술 : MySQL의 네임드 락, Redis, Zookeeper 등
네임드락 주의점
분산락에 대해 조만간 알아봐야겠당~~
📍 메타데이터 락
- 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
- 명시적 획득 및 해제 불가
- RENAME TABLE t1 TO t2 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금
- 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금 설정
2. 책 RealMySQL8.0의 [5.3 InnoDB 스토리지 엔진의 잠금] 파트를 읽었다.
InnoDB 스토리지 엔진은
- 레코드 기반의 잠금 기능을 제공한다.
- 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락이나 테이블 락으로 레벨업되는 경우는 없다.
InnoDB에서는
- 보조 인덱스를 이용한 변경 작업 -> 넥스트 키 락 or 갭 락
- 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업 -> 레코드 락
📍 레코드 락
- 레코드 자체만을 잠그는 것
- 레코드 자체가 아니라 인덱스의 레코드를 잠근다
(인덱스가 하나도없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금 설정)
📍 갭 락
- 레코드 자체가 아니라 레코드와 인접한 레코드 사이의 간격만을 잠그는 것
- 역할 : 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어
- 갭 락은 인덱스 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다.
- 갭 락은 아직 존재하지는 않지만 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금이다.
따라서 데이터의 유일성이 보장되는 프라이머리 키(PK) 또는 유니크 인덱스에 의한 작업에서는 갭 락이 사용되지 않는다. - 갭 락은 Pantom Read(유령 읽기)를 방지하는데 도움이 된다.
- 갭락 자체보다는 넥스트 키 락의 일부로 사용
📍 넥스트 키 락
- 레코드 락 + 갭 락
- 주 목적 : 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것
바이너리 로그란?
MySQL 서버에서 Create, Drop, Alter같은 DDL과 Insert, Update, Delete같은 DML을 통해
데이터베이스, 오브젝트, 데이터에 생성,수정,업데이트를 했을 시
그 변화된 이벤트를 기록하는 이진 파일 (show나 select등 조회 문법은 제외)
바이너리 로그의 용도
1. 복제 구성에서 사용
MySQL에서는 복제라는 부하분산 기능을 제공하는데 이때 바이너리 로그를 사용
바이너리 로그는 마스터서버에서 생성되고 슬레이브서버는 마스터 서버에 접속하여 이 마스터의 바이너리 로그를 읽어와서
똑같이 그 이벤트를 실행시켜 마스터서버와 슬레이브 서버를 동일하게 만든다.
2. 특정 시점 복구에 사용
데이터베이스를 사용하다보면 데이터 삭제나 데이터베이스가 어떤 이유로 장애나 크래쉬가 발생할 시 복구를 해야할 때가 있다.
이때 특정 시점으로 돌아갈 때 바이너리 로그가 필요
📍 자동증가 락
- MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성 제공
- AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
- InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT락이라는 테이블 수준의 락을 사용
- INSERT, REPLACE처럼 새로운 레코드를 저장하는 쿼리에서만 필요 (UPDATE, DELETE등에서는 사용x)
- 트랜잭션과 관계없이 AUTO_INCREMENT값을 가져오는 순간만 락이 걸렸다가 즉시 해제
📍 인덱스와 락
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리
= 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 건다
예시)
first_name에만 인덱스가 걸린 상태.
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen'; 쿼리를 쏘면?
- UPDATE 문장이 실행되면 1건의 레코드가 업데이트될 것이다.
하지만 이 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까?- UPDATE문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi' .
last_name='Klassen'는 인덱스에 없다 - 즉, first_name='Georgi'인 레코드 253건이 모두 잠긴다.
- UPDATE문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi' .
- 해당 테이블에 인덱스가 하나도 없다면?
- 테이블을 풀 스캔하면서 UPDATE
- 즉, 테이블에 있는 30여만건의 모든 레코드를 잠근다.
=> MySQL의 InnoDB에서 인덱스 설계는 짱 중요하다!!!!!!!!!!!!!!!
3. 책 RealMySQL8.0의 [5.4 MySQL의 격리 수준] 파트를 읽었다.
트랜잭션의 격리 수준이란
여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 여부를 결정하는 것
📍 READ UNCOMMITED
- 각 트랜잭션의 변경 내용이 COMMIT or ROLLBACK 여부와 상관없이 다른 트랜잭션에서 보인다.
- DIRTY READ : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상
- 데이터가 나타났다가 사라졌다 하는 현상 초래 -> 개발자와 사용자를 혼란스럽게 함
📍 READ COMMITED
- 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
- 다른 트랜잭션에서 데이터를 변경해도 commit 되기 전까지는 언두로그에서 가져온다.
- 다른 트랜잭션에서 데이터를 변경하고 commit해야 새로운 값을 참조할 수 있다.
- NON-REPEATABLE READ : 하나의 트랜잭션 내에서 똑같이 SELECT 쿼리를 실행했는데 다른 결과를 가져오는 현상
트랜잭션 내에서 실행되는 SELECT vs 트랜잭션 없이 실행되는 SELECT
READ COMMITED 격리 수준에서는 둘 차이 별로 없다.
REPEATABLE READ에서는 SELECT도 트랜잭션 범위 내에서만 작동.
REPEATABLE READ에서는 트랜잭션 번호를 바탕으로 실제 테이블 데이터와 언두 영역의 데이터 등을 비교하며 어떤 데이터를 조회할 지 판단한다. 즉, 트랜잭션 안에서 실행되는 SELECT라면 항상 일관된 데이터를 조회하게 된다. 하지만 트랜잭션 없이 실행된다면, 데이터의 정합성이 깨지는 상황이 생길 수 있다.
📍 REPEATABLE READ
- MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준
- 바이너리 로그를 가진 MySQL서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.
- InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 Undo 공간에 백업해두고 실제 레코드 값을 변경한다. (이러한 변경방식을 MVCC라고 한다)
- REAPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장
- 해당 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.
- 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는)를 가지고 있으며,
언두 영역에 백업된 모든 레코드는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다.
- 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는)를 가지고 있으며,
- PHANTOM READ : 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상 (다른 트랜잭션에서 새로운 레코드를 추가하거나 삭제하는 경우 발생)
- REPEATABLE READ는 새로운 레코드의 추가까지는 막지 않는다
-> SELECT로 조회한 경우 트랜잭션이 끝나기 전에 다른 트랜잭션에 의해 추가된 레코드가 발견될 수 있다 - 하지만 MVCC 덕분에 일반적인 조회에서 Phantom Read는 발생하지 않는다.
(왜냐하면 자신보다 나중에 실행된 트랜잭션이 추가한 레코드는 무시하면 되기 때문) - 그럼 언제 발생? 잠금이 사용될 때
- MVCC에서는 데이터를 먼저 테이블에 반영하고, 언두 로그에 백업한다.
즉, SELECT FOR UPDATE로 잠금을 걸어도 테이블에는 반영이 되고, 언두 로그에는 다른 트랜잭션에 의한 데이터가 계속해서 쌓이는 것이다. - 만약 먼저 시작된 트랜잭션이 존재하여 작업을 하면 테이블에는 반영되고, 언두 로그에는 이전 트랜잭션의 데이터가 쌓인다.
- 그러므로 MVCC 만으로 정확한 데이터 제공이 불가능하고 언두 로그에도 잠금을 걸어야 하는데, 언두 로그는 append only 형태이므로 잠글 수 없다.
- 따라서 SELECT FOR UPDATE나 SELECT FOR SHARE로 레코드를 조회하는 경우에는 언두 영역의 데이터가 아니라 테이블의 레코드를 가져오게 되고, 이로 인해 Phaontom Read가 발생
- MVCC에서는 데이터를 먼저 테이블에 반영하고, 언두 로그에 백업한다.
- 하지만 MySQL에는 갭 락이 있기 때문에 위의 상황에서 문제 X
- 사용자 B가 SELECT FOR UPDATE로 데이터를 조회한 경우에 MySQL은 id가 50인 레코드에는 레코드 락, id가 50보다 큰 범위에는 갭 락으로 넥스트 키 락을 건다. 따라서 사용자 A가 id가 51인 member를 INSERT 시도한다면, B의 트랜잭션이 종료(커밋 또는 롤백)될 때 까지 기다리다가, 대기를 지나치게 오래 하면 락 타임아웃이 발생하게 된다.
- 일반적으로 MySQL의 REAPEATABLE READ에서는 Phantom Read가 발생하지 않는다.
- MySQL에서 Phantom Read가 발생하는 거의 유일한 케이스는 다음과 같다.
- 사용자 B는 트랜잭션을 시작하고, 잠금없는 SELECT 문으로 데이터를 조회하였다. 그리고 사용자 A는 INSERT 문을 사용해 데이터를 추가하였다. 이때 잠금이 없으므로 바로 COMMIT 된다.
하지만 사용자 B가 SELECT FOR UPDATE로 조회를 했다면, 언두 로그가 아닌 테이블로부터 레코드를 조회하므로 Phantom Read가 발생한다. - 하지만 이러한 케이스는 거의 존재하지 않으므로, MySQL의 REPEATABLE READ에서는 PHANTOM READ가 발생하지 않는다고 봐도 된다.
- 사용자 B는 트랜잭션을 시작하고, 잠금없는 SELECT 문으로 데이터를 조회하였다. 그리고 사용자 A는 INSERT 문을 사용해 데이터를 추가하였다. 이때 잠금이 없으므로 바로 COMMIT 된다.
- REPEATABLE READ는 새로운 레코드의 추가까지는 막지 않는다
아래는 MySQL 기준으로 정리된 내용이다.
- SELECT FOR UPDATE 이후 SELECT: 갭락 때문에 팬텀리드 X
- SELECT FOR UPDATE 이후 SELECT FOR UPDATE: 갭락 때문에 팬텀리드 X
- SELECT 이후 SELECT: MVCC 때문에 팬텀리드 X
- SELECT 이후 SELECT FOR UPDATE: 팬텀 리드 O
여기 어렵다... 요 글을 나중에 다시 한번 읽어보자
SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.
그래서 SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.
MVCC(Multi Version Concurrency Control)란?
MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기(Non-Locking Consistent Read)를 제공하는데에 있다.
InnoDB는 언두 로그를 이용해 이 기능을 구현한다.
여기서 멀티 버전이란 하나의 레코드에 대해 여러 버전이 동시에 관리된다는 의미이다.
격리 수준이 READ_UNCOMMITED인 경우, 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환
(데이터가 커밋됐든 아니든 변경된 상태의 데이터 반환)
격리 수준이 READ_COMMITED이나 그 이상인 경우, 언두 영역의 데이터를 읽어서 반환
=> 하나의 레코드에 대해 2가지 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조
잠금 없는 일관된 읽기(Non-Locking Consistent Read)란?
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
격리 수준이 SERIALIZABLE이 아닌 경우, INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.
(다른 트랜잭션에서 데이터를 변경하더라도 다른 사용자의 SELECT 작업을 방해하지 않는다)
MySQL에서 SELECT FOR SHARE/UPDATE는 대상 레코드에 각각 읽기/쓰기 잠금을 거는 것이다.
하지만 순수한 SELECT 작업은 아무런 레코드 잠금 없이 실행 (SERIALIZABLE 제외)
📍 SERIALIZABLE
- 가장 엄격한 격리 수준
- 동시 처리 성능 가장 떨어짐
- InnoDB에서 기본적으로 순수한 SELECT작업은 아무런 레코드 잠금도 실행하지 않고 실행된다 (Non-locking consistent read)
- 하지만 격리 수준이 SERIALIZABLE로 설정되면 순수한 SELECT 작업도 S-Lock을 획득해야하며, 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.
= 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다
InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ에서도 PHANTOM READ가 발생하지 않기 때문에 굳이 SERIALIZABLE를 사용할 필요는 없다.
엄밀하게는 SELECT ... FOR UPDATE나 SELECT ... FOR SHARE의 경우, REAPEATABLE READ 격리 수준에서 PHANTOM READ 현상이 발생할 수 있다. 하지만 레코드의 변경 이력(언두 로그)에 잠금을 걸 수는 없기 때문에, 이러한 잠금을 동반한 SELECT 쿼리를 예외적인 상황으로 볼 수 있다.
📍 정리 및 요약
격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는데, 사실 SERIALIZABLE이 아니라면 크게 성능 개선 및 저하는 발생하지 않는다. 그 이유는 결국 언두 로그를 통해 레코드를 참조하는 과정이 거의 동일하기 때문이다. 따라서 MySQL은 갭 락을 통해 Phantom Read까지 거의 발생하지 않고, READ COMMITTED보다는 동시 처리 성능은 뛰어난 REPEATABLE READ를 사용한다.
기타
트랜잭션 vs Lock
- 트랜잭션 : 데이터의 정합성을 보장하기 위해
- Lock : 동시성을 제어하기 위해
트랜잭션 주의사항
- 데이터베이스 커넥션을 가지고있는 버위를 최소화하라
- 트랜잭션의 범위를 최소화하라 (필요한 최소의 코드에만 적용)
- 네트워크를 통해 원격 서버와 통신하는 등의 작업은 트랜잭션에서 제거
락
SELECT ~ FOR UPDATE
- "데이터 수정하려고 SELECT 하는 중이야~ 다른 사람들은 데이터에 손 대지 마!"
- 동시성 제어를 위하여 특정 데이터(ROW)에 대해 배타적 LOCK을 거는 기능
- 가장 먼저 LOCK을 획득한 SESSION의 SELECT 된 ROW들이
UPDATE 쿼리후 COMMIT 되기 이전까지 다른 SESSION들은 해당 ROW들을 수정하지 못하도록 하는 기능
감정 회고
오늘은 낮부터 머리가 아파서 output보다 input을 했다 (책읽기)
근데 input이 많아서 또 머리가 아파졌다ㅋㅋㅋㅋㅋㅋㅋ
DB는 어렵구나~~