[Real MySQL 8.0] 5장 트랜잭션과 잠금, 6장 데이터 압축 (4일차)

5장 트랜잭션과 잠금

 

어떻게보면 데이터베이스에서 가장 중요한 부분이라고 생각한다.

동시성에 영향을 미치는 잠금과 트랜잭션, 트랜잭션의 격리 수준에 대해서 살펴본다.

 

5.1 트랜잭션 

 

트랜잭션은 작업의 완전성을 보장하는 것이다. 논리적 작업 셋을 모두 완벽하게 처리하거나 실패 시 원 상태로 복구해 일관성을 유지하는 기능이다. 즉, 데이터의 정합성을 보장한다. 

 

트랜잭션은 꼭 여러 개의 쿼리가 조합되는 개념은 아니다. 논리적인 작업 셋 자체가 100% 적용되거나 0% 적용되는 것을 보장하는 것이다. 

 

데이터베이스에 PK 가 1 이 이미 존재할 경우 하나의 쿼리에서 1, 2, 3 을 삽입해보자.

• MyISAM, MEMORY 는 중복이 발생하는 1 을 제외하고 2, 3 이 삽입된다.
• InnoDB 는 중복이 발생했기에 1, 2, 3 모두 삽입되지 않는다.

MyISAM 같은 형태를 부분 업데이트라 하며 이는 정합성을 맞추는 데 상당한 문제를 만들어낸다. 

 

추가적으로 트랜잭션을 처리할 때에는 서버의 처리가 아닌 정말 DBMS 의 연결이 필요한 부분만 추출해서 트랜잭션을 생성하는 것이 중요하다. 별도의 처리가 트랜잭션에 추가되면, 데이터베이스 커넥션이 불필요하게 길어지며 다른 부분에 의해서 트랜잭션이 롤백될 수 있기 때문이다. 꼭 데이터베이스 커넥션을 가지는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화하자. 또한 네트워크 작업이 있는 경우에는 반드시 트랜잭션을 배제하자.

 

5.2 MySQL 엔진의 잠금

 

잠금은 동시성을 제어하기 위한 기능이다. 하나의 레코드에 두 개 이상의 트랜잭션이 접근 시 어떻게 동시성을 제어할 지에 관했다.

 

잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. 

 

5.2.1 글로벌 락

 

MySQL 에서 제공하는 가장 범위가 큰 잠금이다. 한 세션에서 글로벌 락을 획득하면 다른 세션에서는 SELECT 를 제외한 대부분의 DDL, DML 문장이 실행을 멈춘다. 글로벌 락은 MySQL 서버 전체에 영향을 미친다. `FLUSH TABLES WITH READ LOCK` 명령은 MySQL 서버의 모든 변경 작업을 멈춘다.

 

하지만 InnoDB 스토리지 엔진 부터는 트랜잭션을 위해 모든 데이터 변경 작업을 멈출 필요가 없어졌고 조금 더 가벼운 글로벌 락이 필요해졌다. 그게 백업 락이다.

 

백업 락은 일반적 테이블의 데이터 변경은 허용한다. 주로 백업 서버에서 실행되며 정상적으로 복제는 실행되며 백업의 실패를 막기 위해 복제를 일시 중지한다.

 

5.2.2 테이블 락

개별 테이블 단위로 생성되는 잠금이다. 변경 시 테이블에 잠금을 걸고 변경 완료 후 해제하는 방식이다. 

 

5.2.3 네임드 락

 

임의의 문자열에 대해 잠금을 설정할 수 있다. 데이터베이스 객체가 아니라 사용자가 지정한 문자열이 대상이다. 

많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 용이하다. 

동일한 데이터를 변경, 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행할 수 있다.

 

5.2.4 메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금. 테이블 이름 변경 시 자동으로 획득된다. 

 

5.3 InnoDB 스토리지 엔진 잠금

스토리지 엔진 내부에서 제공하는 레코드 기반 잠금이다. 훨씬 뛰어난 동시성 처리가 가능하지만 MySQL 명령을 이용해 접근하기 어렵다.

 

최근 버전에서는 InnoDB 의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있다. 

 

잠금 정보가 상당히 작은 공간으로 관리되므로 락 에스컬레이션이 없다. (레코드 락 -> 페이지 락 -> 테이블 락 으로 커지는 것)

 

레코드 자체만을 잠그는 것이 레코드 락이다. InnoDB 의 차이점은 인덱스의 레코드를 잠근다는 것이다. 인덱스가 없더라도 클러스터 인덱스를 이용한다. 여기에는 큰 차이가 있다. (추후 설명)

 

갭 락은 레코드 자체가 아닌 바로 인접한 레코드 사이의 간격만 잠그는 것이다. 이건 새로운 레코드가 삽입되는 것을 제어한다. 

 

넥스트 키 락은 레코드와 갭 락을 합쳐놓은 것이다. 

 

MySLQ 에서 `AUTO_INCREMENT` 사용 시 내부적으로 테이블 수준의 잠금을 사용한다. 

 

5.3.2 인덱스와 잠금

예제로

 

first_name = aa 인 사원이 253명, last_name == bb 인 사원이 1명이 있다.

first_name 만 인덱스가 만들어져있다면 

 

first_name = aa, last_name == bb 인 사원을 업데이트하려먼 레코드에 락이 얼마나 걸릴까?

 

last_name 에 인덱스가 없기에 레코드 락은 253건 전부 걸린다. 

 

last_name 에 인덱스가 없으므로 last_name = 'bb' 조건을 만족하는 특정 레코드만을 선택적으로 락 걸 수 없습니다. MySQL은 WHERE 절 조건을 만족하는 모든 후보 레코드를 잠글 수밖에 없으며, 이 때문에 최종적으로 선택된 1건이 아닌 first_name = 'aa' 조건에 해당하는 253건 모두에 락이 걸리게 됩니다.

 

만약 인덱스가 없다면 풀 스캔하며 모든 레코드가 잠기게 될 것이다. 

 

5.4 MySQL 의 격리 수준

트랜잭션의 격리 수준은 다 잘 알고 있을거라 생각한다. 

 

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITED	발생	발생	발생
READ COMMITED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB 는 없음)
SERIALIZABLE	없음	없음	없음

 

5.4.1 READ UNCOMMITED

 

 

말 그대로 커밋되지 않은 트랜잭션도 읽을 수 있는 수준이다. 위처럼 롤백되더라도 읽을 수 있기에 문제가 발생한다.

커밋되지 않은 내용을 다른 트랜잭션이 볼 수 있는 것을 DIRTY READ 라 한다. 

 

이는 읽을때마다 데이터가 변경되고, 데이터가 나타났다 사라졌다하는 현상을 초래한다.

 

5.4.2 READ COMMITED

 

오라클에서 기본적으로 사용되는 수준이다. 커밋된 내용만 읽을 수 있는 수준이다.

하지만 사용자 B 가 처음과 끝에서 동일한 데이터를 조회한다면 결과가 달라질 수 있다. 이게 NON-REPEATABLE READ 이다.

또한 사용자 A 가 해당 데이터를 삭제하거나 삽입한다면 데이터가 사라지는 결과도 나온다.

 

5.4.3 REPEATABLE READ 

InnoDB 스토리지 엔진에서 기본으로 사용되는 수준이다. 

 

 

동일하게 언두 로그를 유지하지만 여기에는 id 를 통해서 언제 생성된 로그인지 확인한다. 이를 통해 사용자 B 는 언제 읽더라도 동일한 데이터를 읽을 수 있다. 

5.4.4 SERIALIZABLE

가장 단순하면서 엄격한 격리 수준이다. 동시 처리 능력을 포기하고 모든 트랜잭션이 순차적으로 동작하게 하는 것이다. 

 

6장 데이터 압축

MySQL 에서 디스크 저장된 데이터 파일의 크기는 쿼리의 성능에 영향을 미친다. 이를 위해 DBMS 는 데이터를 압축하는 기능을 제공한다.

 

6.1 페이지 압축

디스크에 저장하는 시점에 데이터 페이지가 압축되어 저장하고, 읽을 때 해제된다. 

문제는 동일한 페이지를 압축한 결과가 다른 용량이 될 수 있다는 것이다.

 

그래서 페이지 압축은 펀치 홀 기능을 사용한다. 

 

1. 16KB 페이지 압축

2. 7KB 라면 9KB 에 빈 데이터 기록

3. 디스크에 기록 후 9KB 에 대해 펀치 홀 생성

4. 운영체제에 9KB 를 반납

 

하지만 펀치 홀 기능 자체가 범용성 있지 않아 페이지 압축은 많이 사용되지는 않는다.

6.2 테이블 압축

운영체제나 하드웨어 제약이 없어 일반적으로 많이 사용된다. 

 

하지만 단점도 있다.

• 버퍼 풀 공간 활용률이 낮음
• 쿼리 처리 성능이 낮음
• 빈번한 데이터 변경 시 압축률이 떨어짐

테이블 압축을 위해 별도의 테이블 스페이스를 사용해야한다. 

 

`KEY_BLOCK_SIZE` 옵션을 통해 저장될 페이지의 크기를 지정할 수 있다. 만약 8KB 라면 아래와 같은 과정을 통한다.

 

1. 16KB 데이터 페이지 압축

2. 압축 결과가 8KB 이하면 그대로. 아니면 2개에 8KB 페이지에 따로 저장

 

그래서 가장 중요한 부분은 압축된 결과를 예측해 옵션값을 지정하는 것이다. 

 

InnoDB 스토리지 엔진은 압축된 테이블의 데이터 페이지를 버퍼 풀에 적재하면 압축된 상태와 해제된 상태 2개 버전을 관리한다. 이렇게 이중으로 사용하기에 메모리가 낭비된다.