리두로그와 언두로그를 공부하려했더니 Buffer Pool 이라는 개념이 등장했다. 테이블을 메모리에 캐싱해두는 개념인데, 단순 개념만 알고있기에는 중요한 내용이기에 정리하였다.
2. InnoDB Buffer Pool이 뭐에요?
말 그대로 InnoDB 엔진에서 제공하는 버퍼 풀인데, 이녀석의 역할은 바로 캐싱이다.
테이블이나 인덱스를 캐싱한다고 한다. 그럼 정확히 어떤 형태로 캐싱을 하고, 실제 환경에서 어떻게 사용되는지 알아보자.
3. Buffer Pool 은 '페이지'들이 캐싱되어 있어요
버퍼 풀에는 '페이지'들이 저장되어 있다. InnoDB 엔진에서의 페이지란 데이터베이스에서 데이터를 저장 단위이다. I/O를 통해 디스크로부터 페이지 단위로 데이터를 조회하면, Buffer Pool에 이를 그대로 저장한다. 페이지 하나의 크기는 16KB이다. (수정 가능하다)
페이지들은 버퍼 풀에 차곡차곡 쌓이는게 아니다. 버퍼 풀은 데이터를 효율적으로 캐싱하기 위해 LRU 리스트, Flush 리스트, Free 리스트 라는 3개의 리스트로 구성되어 있으며, 이 중 LRU 리스트에 어떤 규칙에 따라 저장되게 된다.
3.1. Free 리스트
비어있는 페이지들의 목록이다. 사용자의 쿼리가 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.
3.2. Flush 리스트
디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(== 더티 페이지)들의 목록이다. 즉, 디스크에서 데이터 페이지를 읽은 후 변경이 가해진 데이터 페이지들의 목록이다. 데이터가 변경되면 해당 페이지는 Flush 리스트에서 관리됨과 동시에 변경 내용을 리두 로그에 기록하고, 버퍼 풀에도 변경 내용을 반영한다. 그 후 특정 시점에 리두 로그가 디스크로 기록된다.
3.3. LRU (+MRU) 리스트
MySQL InnoDB 에서는 변형된 LRU 방식을 채택하고 있다. 바로 LRU와 MRU(Most Recently Used)가 결합된 하이브리드 방식이다. 참고로 LRU와 MRU는 캐시 알고리즘이다.
* LRU (Least Recently Used) 가장 최근에 사용되지 않은 페이지를 교체하는 알고리즘. 가장 오래된 캐시를 제거한다.
* MRU (Most Recently Used) 가장 최근에 사용된 페이지를 교체하는 알고리즘. 가장 최근에 사용된 캐시를 제거한다.
4. 가장 오래된 페이지와 가장 최근의 페이지를 제거한다고??
위 두 캐시 알고리즘이 버퍼 풀에 적용되어 있는 구조이다. 이해가 잘 되지 않는다면 어떻게 동작하는지에 대한 메커니즘을 그려나가보면 이해할 수 있을것이다.
아래는 변형된 LRU, 즉 LRU와 MRU가 결합된 형태의 리스트이다.
LRU+MRU 리스트
InnoDB에서의 페이지 캐시 메커니즘에 의해 New 서브리스트 자주 사용되는 페이지가 저장되는 영역,Old 서브리스트는 신규 혹은 자주 사용되지 않는 페이지가 저장되는 영역으로 사용될것이다. 편의상 New 서브 리스트가 존재하는 영역을 MRU 영역, Old 서브 리스트가 존재하는 영역을 LRU 영역이라 표현하겠다.
긴말 필요없이 바로 동작 메커니즘을 살펴보자
1) 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사한다. [InnoDB 어댑티브 해시 인덱스 > 테이블 인덱스] 를 통해 페이지를 검색한다.
* 어댑티브 해시 인덱스 B-Tree 의 성능 개선을 위해 InnoDB 에서 자동으로 추가하는 인덱스이다. 자주 읽히는 데이터 페이지의 키값을 이용해 해시 인덱스를 만들고, 필요할 때마다 해당 데이터 페이지로 즉시 찾아갈 수 있게 한다. - LRU/MRU를 설명하는 글이므로 자세히 다루지 않겠다.
2) 버퍼 풀에 데이터 페이지가 있다면 MRU 영역 방향으로 승급한다. (MRU 리스트로 들어가는게 아닌 방향으로 승급이다.)
3) 버퍼 풀에 데이터가 없다면 디스크로부터 필요한 데이터 페이지를 버퍼 풀에 적재와 동시에 LRU 영역 Head 부분에 추가한다.
4) LRU 영역에 추가한 데이터 페이지가 조회되면 MRU 영역 방향으로 승급하고, 버퍼풀에 없다면 LRU Head 부분에 추가하는 과정을 반복한다.
5) 이때, 승급하는 데이터 페이지가 LRU 영역의 Head에 위치했을 경우, 바로 위인 MRU 영역의 Tail이 아닌 MRU 영역의 Head로 이동한다. (이부분이 핵심이다!)
6) 1,2,3,4,5 과정이 반복되면서 자주 사용되지 않는 데이터 페이지들은 자연스럽게 LRU 영역의 Tail 쪽으로 밀려난다. 끝까지 밀려난 데이터 페이지들은 버퍼 풀에서 제거된다.
7) 자주 사용되는 데이터 페이지의 경우 성능 향상을 위해 페이지의 인덱스 키를 '어댑티브 해시 인덱스'에 추가한다.
5. LRU, 오래된 데이터가 제거되는 것은 이해가는데.. MRU는?
LRU 알고리즘이 적용됐다는 것은 이해가 갈것이다. 그럼 최근에 사용된 데이터가 먼저 삭제된다는 MRU는 대체 어디에 적용된 것일까? 데이터 레코드를 버퍼 풀로 읽어오는 과정을 직접 그려보며 '최근 사용된 데이터가 먼저 삭제'되는 MRU 부분을 이해해보자.
1) 1시 30분, 디스크로부터 A 데이터 페이지를 읽어 버퍼 풀에 적재한다. 최초 버퍼 풀 적재시에는 LRU 영역 Head 부분에 저장된다.
A 데이터 레코드 조회
2) 1시 32분, 디스크로부터 B 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 기존 A 데이터 페이지는 Tail 쪽으로 밀려난다.
B 데이터 레코드 조회
3) 2시 0분, 디스크로부터 C 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 A, B 데이터 페이지는 Tail 쪽으로 밀려난다.
4) 2시 10분, 드디어 C 데이터 페이지를 찾는 쿼리가 실행됐고, C 데이터 페이지를 버퍼 풀에서 로드하게 된다. 이때 C 데이터 페이지는LRU 영역의 Head에 위치했기 때문에 MRU 영역의 Head로 이동하게 된다.
5) 2시 20분, 디스크로부터 D 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 A, B 데이터 페이지는 Tail 쪽으로 밀려난다.
6) 계속해서 신규 쿼리가 들어옴에 따라 디스크로부터 데이터 페이지를 읽어오게 되었다. E, F, G... X, Y, Z 까지, 다양한 데이터 페이지를 디스크로부터 로드하다보니 5시 30분이 되었다.
6. 최신의 데이터가 먼저 제거된다!
2시 10분에 저장한 C 데이터 페이지보다 최근에 사용했었던 E, F, G, H... 들의 데이터 페이지가 버퍼 풀에서 먼저 제거되었다. MRU 영역에서도 자주 사용되지 않은 데이터 페이지들은 Tail 쪽으로 밀려난다. 단, MRU 영역에 있는 C 데이터 페이지가 삭제되지 않은 이유는, 다른 데이터 페이지들이 여러번 재사용되지 않았고, 이로 인해 MRU 영역에서의 '경쟁'이 발생하지 않았기 때문이다.
'최근 사용된 데이터인 E,F,G,H 등의 데이터 페이지들이 먼저 삭제'된다는 점에서 MRU 알고리즘이 적용되었음을 알 수 있다.
Z 레코드까지 LRU 리스트에 저장됨
7. 정리하면... InnoDB의 LRU + MRU 버퍼 풀
InnoDB 버퍼 풀은 하이브리드 방식을 사용함으로써 단순 오래된 데이터 페이지뿐 아니라, 자주 사용되지 않은최신의 데이터 페이지들도 제거하고 있다. 만약 MRU 알고리즘이 적용되지 않았다면 어떨까? 최근 액세스한 데이터 페이지 들 중 자주 사용되지 않아 사실상 필요없는 데이터 페이지가 메모리에서 공간만 차지할것이고, 버퍼 풀이 가득 차서야 제거될것이다. 캐시 메모리를 최대한 활용하기 위해서는 자주 사용하는 데이터를 많이 저장하고, 최신의 데이터라할지라도 자주 사용하지 않은 데이터는 제거하는 것이 좋지 않겠는가?!
오늘은 InnoDB Buffer Pool에 대한 기본 구조와 InnoDB의 LRU + MRU 리스트에 대해 알아보았다. 다음은 리두 로그와 언두 로그에 대해 공부해보도록 하겠다!
각 트랜잭션이 같은 레코드를 변경할 가능성이 낮다고 낙관적으로 가정하는 상황에서의 잠금이다. 리소스를 잠그지 않고 데이터를 업데이트 한 후, 마지막 커밋 시 충돌을 확인하는 방식이다.
충돌이 감지되면(트랜잭션이 시작되고 끝나는 사이, 다른 트랜잭션이 데이터를 수정) 트랜잭션이 롤백되고 오류를 반환한다.
충돌을 어떻게 확인할까?
테이블에 수정 시점을 추적할 수 있는 버전 필드 또는 타임스탬프를 추가하는 것이다.
버전 필드 (Version Field) 낙관적 잠금에서 각 데이터 레코드에 추가되는 필드로, 해당 레코드가 마지막으로 수정된 시점을 추적하는 용도로 사용된다. 레코드가 업데이트 될 때마다 이 버전 번호가 증가하는 형태이다.
트랜잭션이 데이터를 읽을 때 버전 필드를 함께 읽고, 트랜잭션이 커밋할 때 버전 번호를 확인하여 일치하는지를 확인한다. 만약 버전 필드 값이 달라졌다면, 다른 트랜잭션에서 데이터를 수정한 것이라 판단하고 트랜잭션을 롤백 또는 재시작한다.
타임스탬프를 사용할 경우 해당 레코드의 수정 시간을 기재한다. 마찬가지로 트랜잭션이 시작, 종료될 때 레코드의 수정 시간이 달라졌다면 롤백을, 같다면 커밋을 하는 방식이다.
-- 1. 데이터 읽을 때 버전 정보도 함께 읽음
SELECT product_id, product_name, price, version
FROM products
WHERE product_id = 1;
-- 2. 수정 시 버전 확인
UPDATE products
SET price = 200.00, version = version + 1
WHERE product_id = 1 AND version = 2; -- '2'는 읽은 버전 번호
낙관적 잠금 장단점
자원에 대한 잠금이 없기때문에, 대기 시간이 짧고, 처리 속도가 향상될 수 있다. 특히 데이터 충돌이 드문 경우에는 트랜잭션이 빠르게 처리된다. 또한 여러 트랜잭션이 동시에 데이터를 읽고 수정할 수 있으므로, 데이터 충돌이 발생하는 상황이 적다면 그만큼 높은 동시성을 유지하게된다.
하지만 데이터 충돌이 발생할 경우 트랜잭션을 다시 시도해야 한다. 충돌이 자주 발생하거나처리 비용이 높은 트랜잭션이라면, 재시도 비용도 증가하게 된다. 또한 충돌에 대한 예외 로직이 필요하다. 변경을 다시 시도하거나, 사용자에게 알림을 주는 등의 별도 처리가 필요하다. 이러한 예외 코드를 잘못 구현하기라도 한다면 데이터 무결성이 손상될수 있으므로 주의해야한다.
비관적 잠금
각 트랜잭션이 같은 레코드를 변경할 가능성이 높다고 비관적으로 가정하는 상황에서의 잠금이다.즉, 하나의 트랜잭션이 데이터를 조회하는 시점에 락을 걸고, 트랜잭션이 끝날때까지 유지한다.
조회 시점에 잠금을 획득하는 대표적인 방법은 SELECT FOR UPDATE 이다.
비관적 잠금의 장단점
트랜잭션의 동시 접근을 확실하게 방지할 수 있기에 데이터의 무결성이 보장된다.
하지만 동시성이 떨어져 처리 속도가 느리다. 트랜잭션이 각각의 자원을 점유하는 상태에서 서로의 자원을 요청한다면, 데드락이 발생하게 된다.
비관적 잠금 데드락 재현
users 테이블
1) 1번 트랜잭션이 users 테이블의 id = 1인 레코드에 대해 비관적 잠금을 설정한다. 트랜잭션은 끝나지 않고, 다른 작업들을 처리하고 있다.
select * from users where id = 1 FOR UPDATE;
2) 도중 2번 트랜잭션이 시작되었고, users 테이블의 id = 10인 레코드에 대해 비관적 잠금을 설정한다. 마찬가지로 트랜잭션은 끝나지 않고, 다른 작업들을 처리하고 있다.
select * from users where id = 10 FOR UPDATE;
3) 1번 트랜잭션의 작업 중 유저 정보 일괄 수정 위해 업데이트 쿼리가 나갔지만, 2번 트랜잭션에서 id = 10인 레코드에 대한 잠금으로 인해 '대기상태'에 들어간다.
update users set name = '이름';
4) 2번 트랜잭션에서도 마찬가지로 유저 정보 일괄 수정 위해 업데이트 쿼리가 나갔지만, 1번 트랜잭션에서 id = 1인 레코드에 대한 잠금으로 인해 '대기상태'에 들어간다.
5) 트랜잭션 1, 2번은 데드락 상태에 빠지게 된다.
하지만 실제 테스트를 해보니 무기한 데드락이 걸리지 않고 한쪽 트랜잭션을 롤백시키는 것을 볼 수 있는데, 이는 InnoDB에서 제공하는 자동 데드락 탐지 기능에 의해 한쪽 트랜잭션이 롤백되는 것이다.
데드락 상황을 인지하고, 트랜잭션 재시작
또한 락에 대한 타임아웃도 설정되어있기에, 락이 걸린 자원을 획득하지 못한다면 무한정 대기상태에 들어가지 않고, 에러 및 롤백 후 트랜잭션 재시작을 시도한다.
Lock wait timeout 초과 시 트랜잭션 재시작
잠금에 대한 대기 시간 변수는 innodb_lock_wait_timeout이며 , 기본 50초로 설정되어 있다.
우리가 설치하고, 쿼리를 작성하는 MySQL 서버는 크게 두 엔진으로 구성된다. 하나는 MySQL 엔진, 하나는 스토리지 엔진. 스토리지 엔진은 데이터를 저장하는 하드웨어와 관련된 엔진이고, MySQL 엔진은 그 외 알쏭달쏭한 모든것이라고 생각하면 된다.
MySQL 엔진과 스토리지 엔진
뭔 개소린가 싶겠지만 일단은 이렇게 이해하고 시작해보도록 하자. 이게 정신건강에는 좋은것같다.
2. MySQL 전체 구조 핥아보기
상당히 친해보이는 두 엔진이 MySQL 어디에 속하는지 알아보자. 아래는 Real MySQL8.0 에 기재된 MySQL 서버 구조이다.
MySQL 서버의 전체 구조
우리의 몸에 여러 기관들이 있듯이 하나의 엔진에도 여러 요소들이 존재한다. 각 요소들을 살펴보기 전에 그림에 나온 MySQL 서버의 전체 구조를 이해해보자.
3. MySQL 서버 전체 구조
MySQL 서버 전체 구조는 크게 프로그래밍 API, MySQL 서버, 운영체제 하드웨어로 나뉜다.
1) 프로그래밍 API
MySQL 서버로 들어오는 요청에 대한 API를 말한다. Java 진영의 백엔드 개발자 입장에서는 JPA, MyBatis 를 사용해 MySQL 서버로 연동하는 부분이라 생각하면 된다.
2) MySQL 서버
우리가 배워야할 MySQL 엔진과 스토리지 엔진으로 구성된 서버이다. 생소한 용어들이 대부분이나 InnoDB는 뭔가 익숙하다. 일단 넘어가자.
3) 운영체제 하드웨어
DB 데이터는 운영체제 내 디스크에 저장된다. 단순 DB 데이터 뿐 아니라 로그파일도 저장된다.
4. MySQL 엔진 요소
1) 커넥션 핸들러
MySQL을 설치하고 가장 먼저 MySQL 서버에 접속한다. 접속에 성공하면 MySQL 서버와 접속한 클라이언트 간 커넥션이 생성된다. 커넥션 핸들러는 이러한 커넥션을 생성하고 클라이언트의 쿼리 요청, 즉, 커넥션에 관한 처리들을 핸들링한다.
여기서 중요한건 '쿼리 요청을 핸들링'하는 것이다. 커넥션 핸들러가 쿼리를 분석하여 '처리'하진 않는다. 쿼리 요청이 오면 쿼리를 처리하는 어떤 녀석에게 "select 쿼리 처리해주세요!, insert 쿼리 처리해주세요!" 처럼 요청할 뿐이다.
2) SQL 인터페이스
DML, DDL, 프로시져, 뷰, 커서, 트리거, 함수 등의 지원을 위한 인터페이스를 말한다.
3) 쿼리 파서 (SQL 파서)
커넥션 핸들러로부터 들어온 요청을 처리하며, 요청받은 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어준다. 이러한 트리형태의 데이터를 '파서 트리'라고 하며, 이 과정에서 문법 오류를 체크한다.
4) SQL 옵티마이저
파서 트리를 효율적으로 처리하기 위한 실행 계획을 결정한다. 여기서 결정된 실행 계획에 따라 쿼리 처리 속도나 비용이 달라지게 된다. MySQL 뿐만 아니라 모든 DBMS에서도 쿼리의 실행계획을 수립하는 옵티마이저를 갖고 있다. 실행 계획에 대해 잘 이해하여 쿼리의 불합리한 부분을 찾아낸다면 옵티마이저의 최적화에 도움을 줄 수 있을것이다.
추후 실행계획에 대해서 공부한 후 포스팅하도록 하겠다.
5) 캐시
MySQL 에서는 InnoDB Buffer Pool, Table Open Cache, Thread Cache 캐시가 사용된다. 캐시에 대한 자세한 내용은 다음에 다루도록 하겠다.
* 제거된 쿼리 캐시 MySQL 8.0 부터 쿼리 캐시 기능이 제거됐다. 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했었다. SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하도록 동작했다. 하지만 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 했다. 이는 심각한 동시 처리 성능 저하, 버그를 유발해 결국 제거됐다.
6) 버퍼
Join, Order By, Group By, Select 등의 쿼리 실행 시 사용되는 임시 메모리 공간을 의미한다. 종류로 Sort Buffer, Join Buffer, Read Buffer 가 있다. 각각에 대해 알아보자.
- Sort Buffer
ORDER BY나 GROUP BY를 사용할 때 사용되는 버퍼로, 정렬에 필요한 데이터를 메모리에 저장하고, 이 안에서 정렬을 수행한다. 만약 정렬할 데이터가 Sort Buffer에 담기지 않을 경우 일부 데이터를 디스크의 임시 파일에 저장하고, 메모리와 디스크를 함께 사용해 정렬한다.
* ORDER BY 시 Select 절에는 꼭 필요한 컬럼만 기재하자 Order By 사용 시 Select 절 "*" 를 넣거나 굳이 필요없는 컬럼 추가한다면 Sort Buffer 의 사이즈를 초과할 수 있다. 이 경우 디스크에 임시 파일 형태로 데이터들이 저장될것이고 많은 I/O 비용을 들이는 메모리&디스크 정렬이 수행되기 때문이다.
- Join Buffer
인덱스가 없는 조인 작업을 수행할 때 사용하는 버퍼이다. 두 테이블을 조인할 때 보다 작은 테이블의 데이터를 Join Buffer에 저장하고, 큰 테이블의 데이터를 반복해서 비교하는 방식이다. 만약 테이블의 데이터가 Join Buffer에 담기지 않을 경우 여러번에 나누어 조인을 수행한다. 이는 성능 저하를 초래한다.
* Join Buffer에 의존하기보단 인덱스 기반 조인을 사용하자 일반적으로 Join Buffer에 의존한 조인보다 인덱스 기반의 조인이 성능면에서 우수하다. 키나 인덱스로 설정되지 않는 컬럼에 대한조인 시, 속도가 느리다면 Join Buffer를 추가하는것보다 인덱스 설정을 고려하자.
- Read Buffer
테이블의 데이터를 순차적(Full Scan)으로 읽을 때 사용되는 버퍼이다. 규모가 큰 테이블일수록 버퍼의 효율이 높아진다.
* 규모가 클수록 효율이 높은 이유 Full Scan이 발생하면 MySQL은 디스크에서 테이블 데이터를 한번에 읽어오는게 아닌 조금씩 읽어온다. 이 과정에서 I/O 작업이 발생하는데, 읽어야할 테이블의 데이터가 많을 수록 I/O 작업은 늘어나게 된다. Buffer를 사용하게 된다면 조금씩이 아닌 버퍼 사이즈만큼 읽어올 수 있으므로 버퍼의 효율이 높은것이다. read_buffer_size 옵션으로 조절 가능하며 기본 값은 128KB이다.
5. MySQL 쿼리 실행 구조
스토리지 엔진을 알아보기 전 MySQL 엔진에서 일어나는 쿼리 실행 프로세스에 대해 알아보자. 여기서 사용되는 개념은 앞선 MySQL 서버 전체 구조의 요소에 중복된 내용이 있을 수 있으니 복습한다 생각하고 참고하도록 하자.
MySQL 쿼리 실행 구조
1) 클라이언트 접속
클라이언트가 MySQL 서버에 접속하기 위해 ID/PW를 입력한다. 입력하는 시점에 MySQL 네이티브 프로토콜을 통해 MySQL 서버와 TCP/IP 기반의 커넥션이 맺어지게 된다. 이때 커넥션이 생성된다는 뜻인데, 정확히는 '임시 커넥션'이 생성되며, ID/PW 인증이 성공할 경우 완전한 커넥션이 맺어지게 된다.
2) 쿼리 요청 처리
클라이언트가 쿼리를 입력하면 커넥션 핸들러가 쿼리 요청을 처리하기 위해 쿼리를 쿼리 파서에게 전달한다.
3) 파서 트리 생성 및 문법 오류 체크
쿼리 파서는 받은 쿼리를 토큰으로 분리해 파서 트리를 생성한다. 추가로 이 과정에서 문법 오류를 체크한다. 문법에 이상이 없으면 전처리기에게 파서 트리를 전달한다.
4) 개체 검사 및 권한 체크
전처리기는 파서 트리를 분석해 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 명, 컬럼 명, 내장 함수 명 등과 같은 개체에 매핑하여 유효성을 검사하고, 접근 권한을 확인한다. 실제 존재하지 않거나, 권한 상 접근할 수 있는 개체의 토큰은 이 단계에서 체크한다. 그 후 옵티마이저에게 전달한다.
5) 실행 계획 수립
옵티마이저는 쿼리를 분석해 실행 계획을 수립한다. 하지만 계획을 할당하고, 이행하는 주체는 따로 있다. 실행 엔진과 핸들러인데, 이들의 관계를 회사로 비유하면 아래와 같다.
- 옵티마이저 : 회사의 경영진 (회사의 계획 수립)
- 실행 엔진 : 중간 관리자 (계획을 받아 업무를 할당)
- 핸들러 : 실무자 (업무를 이행)
이 단계에서는 옵티마이저가 실행 엔진에게 실행 계획을 전달한다.
6) 핸들러에게 처리 요청
실행 엔진은 (옵티마이저가 만든) 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러의 요청의 입력으로 연결하는 역할을 수행한다.
7) 핸들러의 작업 처리
핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하거나 읽어 오는 역할을 담당한다. 즉, 핸들러는 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.
올해 6월부터 코로나 위기 단계가 하향 조정됨에 따라 7일 격리였던 격리 수준이 5일로 완화되었다. :) 어떤 단계에 따라 격리 수준 변경되고 있는데 우리가 사용하는 DB, 트랜잭션에도 격리 수준이란게 존재한다. 트랜잭션 격리수준이란 무엇인지 차근차근 이해해보자.
제공 : 질병관리청
2. 트랜잭션 격리수준이란?
2.1. 정의
트랜잭션 격리수준이란 동시에 여러 트랜잭션이 처리될 때, 트랜잭션끼리 얼마나 고립(격리)되어 있는가에 대한 수준을 말한다.
2.2. 트랜잭션은 원래 격리된거 아닌가?
트랜잭션의 성질 중 격리성(Isolation)이 있다. 격리성이란 트랜잭션 수행 시 다른 트랜잭션이 끼어들이 못하도록 보장하는 것을 말한다. 이는 '트랜잭션은 서로 완전히 격리되어 있으니 다른 트랜잭션이 끼어들지 못합니다. 땅땅땅!' 이 아니다. 격리성을 강화시킨다면 다른 트랜잭션이 끼어들지 못하게 보장할 수 있고, 약화시킨다면 다른 트랜잭션도 중간에 끼어들 수 있다는 의미이다. 코로나처럼 7일간 무조건 집콕일수도 있고, 5일간 집콕 권고일수도 있다.
그럼 이렇게 격리수준을 나눈 이유는 뭘까? 먼저 트랜잭션의 성질을 살펴보자.
2.3. 트랜잭션 ACID
1) Atomicity (원자성)
한 트랜잭션의 연산은 모두 성공하거나, 모두 실패해야한다. 예를들어 돈을 이체할 때 보내는 쪽에서 돈을 빼오는 작업만 성공하고, 받는 쪽에 돈을 넣는 작업은 실패하면 안된다.
2) Consistency (일관성, 정합성)
트랜잭션 처리 후에도 데이터의 상태는 일관되어야 한다. '모든 계좌 정보에는 계좌 번호가 있어야 한다.'라는 제약 조건 즉, 상태가 걸려있을 때, 계좌번호를 삭제하거나 계좌번호가 없는 상태로 계좌정보를 추가하려 한다면 '모든 계좌 정보에는 계좌 번호가 있어야 한다' 에서 '모든 계좌 정보에는 계좌 번호가 없어도 된다' 라는 상태로 변경되게 된다. 때문에 상태를 변화시키는 트랜잭션은 실패하는것이다.
3) Isolation (격리성)
트랜잭션 수행 시 다른 트랜잭션의 연산이 끼어들이 못하도록 보장한다. 하나의 트랜잭션이 다른 트랜잭션에게 영향을 주지 않도록 격리되어 수행되어야한다.
4) Durability (지속성)
성공적으로 수행된 트랜잭션은 영원히 반영되어야 한다. 중간에 DB에 오류가 발생해도 다시 복구되어야 한다. 즉, 트랜잭션에 대한 로그가 반드시 남아야한다는 의미하기도 한다.
2.4. 트랜잭션의 Isolation
여기서 격리성을 따져보자. 트랜잭션 수행 중간에 다른 트랜잭션이 끼어들수 없다면 어떻게될까? 모든 트랜잭션은 순차적으로 처리될 것이고, 데이터의 정확성은 보장될 것이다. 속도는 어떨까? 트랜잭션이 많아질수록 처리를 기다리는 트랜잭션은 쌓여간다. 앞에 있는 트랜잭션이 기다리는 시간만큼 대기 시간은 늘어난다. 결국 트랜잭션이 처리되는 속도가 느려지게 되고, 어플리케이션 운용에 심각한 문제가 발생할 수 있다.
결국 준수한 처리 속도를 위해서는 트랜잭션의 완전한 격리가 아닌 완화된 수준의 격리가 필요하다. 이처럼 속도와 데이터 정확성에 대한 트레이드 오프를 고려하여 트랜잭션의 격리성 수준을 나눈것이 바로 트랜잭션의 격리수준이다.
3. 트랜잭션 격리수준 단계
3.1. DBMS마다 다른 격리수준
트랜잭션 격리수준은 총 4단계로 Uncommitted Read, Committed Read, Repeatable Read, Serializable 로 구성된다. DBMS 마다 격리 수준에 대한 내용이 다를 수 있으니 보다 정확하게 알기 위해서는 공식 문서를 확인해야 한다. 필자는 MySQL 에서 제공하는 격리수준을 기준으로 하였으며, 필요에 따라서는 타 DBMS에서의 격리수준도 비교 분석하였다. 참고로 MySQL의 기본 격리수준은 Repeatable Read 이다.
먼저 격리수준이 가장 낮은 Uncommitted Read부터 알아보자.
3.2. Uncommitted Read (커밋되지 않은 읽기)
다른 트랜잭션에서 커밋되지 않은 데이터에 접근할 수 있게 하는 격리 수준이다. 가장 저수준의 격리수준이며, 일반적으로 사용하지 않는 격리수준이다.
10번 트랜잭션이 '박기영'이라는 데이터를 '박경'으로 UPDATE 한 후 Commit 하지 않았을 때 13번 트랜잭션에서 접근하여 커밋되지 않은 데이터를 읽을 수 있다.
Uncommitted Read
그런데 13번 트랜잭션이 데이터를 읽은 후 10번 트랜잭션에 문제가 발생하여 롤백된다면 데이터 부정합을 발생시킬 수 있다.
데이터 부정합은 어플리케이션에 치명적인 문제를 야기할 수 있다. 그래서인지 오라클에서는 이 수준을 아예 지원하지 않는다. 이처럼 커밋되지 않는 트랜잭션에 접근하여 부정합을 유발할 수 있는 데이터를 읽는 것을 더티읽기(Dirty Read)라고 한다.
Dirty Read
3.2 Committed Read (커밋된 읽기)
다른 트랜잭션에서 커밋된 데이터로만 접근할 수 있게 하는 격리 수준이다. MySQL을 제외하고 대부분 이를 기본 격리수준으로 사용한다.
10번 트랜잭션이 '박기영'이라는 데이터를 '박경'으로 UPDATE 한 후 Commit 하지 않았을 때, 13번 트랜잭션에서 이를 조회할 경우 UPDATE 전 데이터인 '박기영' 이라는 값이 조회된다. Dirty Read 현상은 발생하지 않는다.
그럼 어떻게 Read Committed는 UPDATE 전 값을 조회한걸까? 그 키는 바로 Undo 영역에 있다.
Committed Read
※ Undo 영역
앞서 살펴본 트랜잭션의 성질 중 지속성(Durability)을 보면 다음과 같이 정의되어 있다.
성공적으로 수행된 트랜잭션은 영원히 반영되어야 한다. 중간에 DB에 오류가 발생해도, 다시 복구되어야 한다. 즉, 트랜잭션에 대한 로그가 반드시 남아야한다는 성질을 의미하기도 한다.
트랜잭션에 대한 로그가 반드시 남아있어야 한다. 즉, 복구는 로그를 기반으로 처리된다. 이 로그는 크게 두 가지가 있다. 오류에 의한 복구에 사용되는 Redo Log와 트랜잭션 롤백을 위해 사용되는 Undo Log이다.
다시실행의 뜻을 갖는 Redo는 커밋된 트랜잭션에 대한 정보를 갖고 있고(왜? 복구하려면 다시 실행해줘야하니까), 실행 취소의 뜻을 갖는 Undo는 데이터베이스의 변경이 발생할 경우 변경되기 전 값과 이에 대한 PK 값을 갖고 있다(왜? 롤백하면 다시 되돌려야 하니까)
그런데 Undo 영역이라고 말한 이유는 Undo Log가 Undo Log Buffer 형태로 메모리에 저장되고, 특정 시점에 디스크에 저장된 Undo Log File 에 I/O 작업으로 쓰여지기 때문이다. 추가로 이렇게 단계가 나눠지는 이유는 데이터에 변경사항이 생길때마다 Disk에 I/O 작업을 하는것보다 메모리 입력하고, 읽는것이 속도와 리소스 측면에서 유리하기 때문이다.
정리하면, Undo 영역이란 변경 전 데이터가 저장된 영역이고, Commit 하기 전 데이터를 읽어올 수 있는 이유는 Undo 영역에 있는 데이터를 읽어오기 때문이다.
※ Non Repeatable Read(반복 가능하지 않은 읽기) 현상 발생
Committed Read 수준에서는 Non Repeatable Read 현상이 발생한다. 이는 하나의 트랜잭션에서 동일한 SELECT 쿼리를 실행했을 때 다른 결과가 나타나는 것을 말한다.
아래 그림을 보면 13번 트랜잭션이 동일한 SELECT 쿼리를 두 번 실행했을 때 결과가 다른 것을 볼 수 있는데, 10번 트랜잭션이 데이터 UPDATE 후 COMMIT 하기 전, 후에 SELECT 쿼리를 실행했었기 때문이다.
Non Repeatable Read
3.3. Repeatable Read (반복 가능한 읽기)
Non Repeatable Read 문제를 해결하는 격리 수준으로, 커밋된 데이터만 읽을 수 있되 자신보다 낮은 트랜잭션 번호를 갖는 트랜잭션에서 커밋한 데이터만 읽을 수 있는 격리수준이다. 이게 가능한 이유는? 그렇다 Undo 로그때문이다. 또한 트랜잭션 ID를 통해 Undo 영역의 데이터를 스냅샷처럼 관리하여 동일한 데이터를 보장하는 것을 MVCC(Multi Version Concurrency Control) 라고 한다.
아래 그림에서 10번 트랜잭션은 10번 보다 작은 트랜잭션에서 커밋한 데이터만 읽을 수 있으므로 13번 트랜잭션에서 변경한 내용은 조회할 수 없다. 같은 SELECT 쿼리가 두 번 실행됐을 때 같은 결과가 조회되므로 Non-Repeatable-Read 현상이 해결됨을 확인할 수 있다.
Repeatable Read
※ Repeatable Read를 지원하지 않는 오라클?!
오라클은 Repeatable Read 수준을 지원하지 않는다. 그럼 Non Repeatable Read 문제를 해결할 수 없을까? 아니다! 해결할 수 있는 방법이 있다. 바로 Exclusive Lock을 사용하는 방법이다.
※ Exclusive Lock (배타적 잠금 / 쓰기 잠금)
Exclusive Lock이란 특정 레코드나 테이블에 대해 다른 트랜잭션에서 읽기, 쓰기 작업을 할 수 없도록 하는 Lock 이다. SELECT ~ FOR UPDATE (업데이트 하려고 조회하는거에요~ 그러니까 다른 트랜잭션에서 접근못하도록 막아주세요~) 구문을 통해 사용할 수 있다.
아래 참고 자료를 보자. SELECT ~ FOR UPDATE 를 사용하여 조회된 레코드에 대해 Exclusive Lock 을 걸면 다른 트랜잭션에서 해당 레코드에 대해 쓰기 작업 시 LOCK이 해제될때까지 대기하는 것을 볼 수 있다.
그런데 이상한 점이 하나 있다. Exclusive Lock은 읽기나 쓰기 작업을 할 수 없도록 한다고 했는데 아래 gif를 보니 다른 트랜잭션에서 SELECT를 통해 읽기 작업을 하고있다. 이건 바로 MVCC 기술을 통해Undo 영역에서 읽어오는 것이다.
Exclusive Lock
이제 이 과정을 그림으로 이해해보자. 10번 트랜잭션이 select id, name, from user for update 를 실행하여 레코드를 조회함과 동시에 Exclusive Lock이 건다. 다른 트랜잭션에서 접근 시 Lock이 풀릴때까지 대기하게 된다. 이후 10번 트랜잭션이 똑같은 쿼리를 실행해도 처음 조회했던 데이터와 같은 데이터가 조회되게 된다. Non-Repeatable Read 문제가 해결된것이다.
Oracle의 Exclusive Lock을 통한 Non Repeatable Read 문제 해결
※ Phantom Read (유령 읽기)
Repeatable Read와 Exclusive Lock 를 통해 Non Repeatable Read 문제를 해결했다. 그런데 새로운 문제가 발생한다. 바로 Phantom Read 현상이다.
Phantom Read는 하나의 트랜잭션 내에서 여러번 실행되는 동일한 SELECT 쿼리에 대해 결과 레코드 수가 달라지는 현상을 말한다. Non Repeatable Read는 레코드의 데이터가 달라지는 것을 의미한다면 Phantom Read는 기존 조회했던 레코드의 데이터는 달라지지 않지만, 새로운 레코드가 나왔다가 사라졌다가 하는 것이다. 마치 유령처럼!! :(
먼저 Exclusive Lock 을 보자. UPDATE, DELETE에 대한 Lock을 걸어 읽기, 쓰기 작업을 막을 수 있었지만, INSERT에 대한 LOCK은 걸 수 없다. 그 이유는 조회된 레코드에 대해서만 Exclusive Lock을 거는 것이지 조회되지 않은 레코드, 즉 나중에 추가할 레코드에 대해서는 Lock을 걸지 않기 때문이다.
이는 Exclusive Lock을 사용해도 다른 트랜잭션에서 INSERT 작업이 가능하다는 뜻이고, 아래와 같이 처음엔 조회되지 않았던 레코드가 조회될 수 있다는 것이다. 마치 유령처럼!! :)
Oracle Exclusive Lock의 Phantom Read 현상
※ MySQL 에서는 발생하지 않는 Phantom Read
InnoDB 엔진을 사용하는 MySQL 에서는 Repeatable Read 수준에서 Phantom Read 현상이 발생하지 않는다. 그 이유는 SELECT ~ FOR UPDATE를 통해 Lock을 걸때 Exclusive Lock이 아닌 Next Key Lock 방식을 사용하기 때문이다.
※ Next Key Lock
Next Key Lock은 조회된 레코드에 대한 Lock 뿐 아니라 실행 쿼리에 대한 범위에 설정되는 Lock이다. 즉, Next Key Lock은 Record Lock, Gap Lock 이 조합된 Lock 이다.
예를 들어 SELECT * FROM USERS WHERE ID BETWEEN 0 AND 10 FOR UPDATE쿼리를 실행시키면, 조회된 레코드에 대한 Record Lock과,0 < ID <=10 에 해당하는 범위에 해당하는 Gap Lock이 걸린다. 이뿐 아니다! 마지막으로 조회된 레코드의 Index인 ID에 대해 그 다음 존재하는 ID 까지의 범위를 Gap Lock으로 설정한다. 만약 아래와 같이 2 이후 ID가 20인 레코드가 있다면 2 ~ 20 까지 Gap Lock을 건다.
때문에 다른 트랜잭션에서 SELECT 쿼리를 통해 정해진 GAP에 해당하는 데이터를 INSERT 시도할 경우 Gap Lock으로 인해 대기상태에 들어가기 되고, 이는 기존 트랜잭션의 여러 동일 SELECT 쿼리에 대한 동일성이 보장되게 된다.
ID
NAME
1
심승갱
2
박기영
20
홍길동
Mysql Next Key Lock
참고로 SELECT * FROM USERS FOR UPDATE 쿼리를 실행한다면 조회된 모든 레코드에 대한 Lock과 모든 범위에 대한 GAP LOCK이 걸리게 된다.
3.4. Serializable
가장 고수준의 격리수준으로 트랜잭션을 무조건 순차적으로 진행시킨다. 트랜잭션이 끼어들 수 없으니 데이터의 부정합 문제는 발생하지 않으나, 동시 처리가 불가능하여 처리 속도가 느려진다.
트랜잭션이 중간에 끼어들 수 없는 이유는 SELECT 쿼리 실행 시 Shared Lock(공유 잠금)을, INSERT, UPDATE, DELETE 쿼리 실행 시 Exclusive Lock (MySQL의 경우 Nexy Key Lock)을 걸어버리기 때문이다.
※ Shared Lock
Shared Lock이란 다른 트랜잭션에서의 읽기 작업은 허용하지만, 쓰기 작업은 불가능하도록 한다. SELECT ~ FOR SHARE 문법을 통해 사용하는데, 키 포인트는 이 Lock의 경우 동시에 Exclusive Lock을 허용하지 않는다는 것이다.
SELECT 쿼리를 실행하면 Shared Lock이 걸리게 되고, 다른 트랜잭션에서 UPDATE, DELETE, INSERT와 같은 쿼리 실행 시Exclusive Lock, Next Key Lock을 얻어오려고 할텐데 Shared Lock 은 이를 허용하지 않아 대기 상태가 된다. 이러한 원리에 의해 트랜잭션들이 중간에 끼어들 수 없고 순차적으로 되는것이다.
4. 회고
처음엔 트랜잭션의 격리 수준에 따라 트랜잭션 내에서 실행한 쿼리들이 어떻게 동작하는지만 이해하고 넘어가려 했지만, 이를 이해하기 위해서는 Commit, Rollback의 내부 동작 부터 시작해 Undo Log, Undo Log Buffer와 File, Cache Miss, Hit, MVCC, Lock 등.. DB에 대한 전반적인 흐름과 개념들을 알아야 했다. 또한 DBMS 마다 격리 수준의 내용이 달라지다보니 이를 비교 분석해야 했다.
먼저 영상을 통해 기본 개념들을 학습했고, 여러 블로그 글들을 참고하여 정리해나갔다. chat gpt를 통해 이해한 내용을 검증하기도 했다. (근데 이녀석이 자꾸... 말을 바꾸네...??) 잘못된 내용을 바로잡을때마다 쓰고, 지우고를 반복했다. 그림을 갈아 엎어야할때는 마음이 너무 아팠지만, 그때 나사가 빠진(?) 부분이나 이해가 필요한 부분들을 시각적으로 찾을 수 있어서 매우 유익했다.
이 글은 필자가 이해한 내용을 정리한거라 틀린 내용이 있을 수 있다. 독자분들께서 읽고 수정이 필요한 부분이나 추가 정보가 있다면 꼭! 댓글로 부탁드린다! :)
스프링 서버에서 DB 조회 결과를 내가 커스텀한 객체(DAO)로 가져오고 싶은 경우가 있다. 그럴 때 아래 예제처럼 resultType을 풀패키지 경로로 입력해야한다. 만약 입력한 BoardDAO 객체가 다른 쿼리에서도 사용된다면, 해당 쿼리의 resultType 또한 풀 패키지경로로 입력해야할 것이다. 추가로 패키지 경로가 바뀌거나 클래스 명이 바꿔버린다면? 모든 쿼리 설정파일을 바꿔줘야할 것이다.
이러한 문제를 미연에 방지할 수 있는 방법이 바로 Mybatis의 typeAlias(별명) 설정이다.
2. typeAlias
typeAlias는 패키지에 대한 별명을 지정할 수 있다. Mybatis 설정 파일에서 설정 가능하며, 아래 예제와 같이 typeAlias 태그의 type 값에 풀 패키지명을 입력하고, alias에는 별명을 지정한다.
typeAlias 설정
3. Mybatis 설정 파일 로드
Mybatis 설정파일을 로드하는 방법은 스프링의 context 설정 중 sqlSessionFactory를 설정하는 부분에 configLocation 값으로 mybatis 설정파일 경로를 넣어주면 된다. 내 설정파일은 클래스파일 경로에 포함되어있기 때문에 다음과 같이 classpath 로 경로설정을 하였다.
4. 적용
설정이 적용되면 resultType에 Alias로 값을 넣고 테스트해보자. 정상적으로 조회됨을 확인할 수 있을것이다.
mybatis 설정 에러가 발생하는 경우가 있는데, 그럴때는 seetings라는 태그가 configuration태그 내 첫번째로 위치하는지 확인하자.
5. oracle 서비스 수동 전환(서비스가 자동 실행 되어있으면 oracle을 사용하지 않을 때에 컴퓨터가 버벅거릴 수 있음)
시작 - 서비스
1) Oracle ~~ TNSListener - 수동으로 변경
2) OracleServiceORCL - 수동으로 변경
컴퓨터를 재부팅하면 해당 서비스는 자동으로 실행이 되지 않음. 때문에 오라클 프로그램에 필요한 서비스가 로드되지 않아 에러가 발생할 수 있음. 때문에 불필요한 메모리 낭비를 하기 싫다면 위의 설정을 따르고, 메모리 낭비가 상관없을 만큼 컴퓨터 사양이 좋다면 위의 설정은 하지 않아도 된다.
서비스를 재실행시키려면 서비스 탭으로 들어가 서비스를 실행시키거나, bat파일을 만들어 서비스를 실행시키면 된다.
6. cmd 실행 후 sqlplus 입력
7. 사용자 명에 sys as sysdba를 입력하고 비밀번호는 입력하지 않고 엔터키를 누르면 설치한 Oracle Database 12c에 접속됨.
Oracle Database 12c 접속 성공
9. tablespace 생성
* tablespace란 데이터를 관리하는 논리적인 저장 구조이다. 쉽게 말하면 테이블을 관리하는 공간을 의미한다. 테이블 데이터의 기본 용량을 설정할 수 있으며, 용량 초과 시 자동적으로 10 mbyte씩 증가한다.
