Last Updated on 3월 31, 2023 by Jade(정현호)
안녕하세요
이번 포스팅은 트랜잭션과 잠금의 내용 중에 격리 수준을 의미하는 Isolation Level 에 대해서 정리해보려고 합니다.
전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용 으로 아래 포스팅에서 이어지는 글 입니다.
MySQL의 격리 수준
트랜잭션의 격리 수준(isolation level) 이란 여러 트랜잭션이 동시에 처리 될때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼수 있게 허용할지 말지를 결정하는 것입니다.
격리 수준은 크게 "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE" 의 4가지로 나누게 됩니다.
"DIRTY READ" 라고 하는 READ UNCOMMITTED 는 일반적인 데이터베이스에서는 거의 사용하지 않으며, SERIALIZABLE 역시도 동시성이 중요한 데이터베이스에서는 거의 사용 되지 않습니다.
데이터베이스의 격리 수준을 이야기하면 항상 같이 언급되는 것에서 세 가지 부정합의 문제에 대해서 기술하게 됩니다.
이 세가지 부정합의 문제는 격리 수준의 레벨에 따라서 발생 여부가 달라 지게 됩니다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | 발생 | 발생 | 발생 |
| READ COMMITTED | 없음 | 발생 | 발생 |
| REPEATABLE READ | 없음 | 없음 | 발생 (InnoDB 는 없음) |
| SERIALIZABLE | 없음 | 없음 | 없음 |
SQL-92 또는 SQL-99 표준에 따르면 REPEATABLE READ 격리 수준에서는 PHANTOM READ 가 발생할 수 있지만, InnoDB에서는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ 가 발생하지 않습니다.
일반적인 서비스 용도의 데이터베이스에서는 READ COMMITTED 나 REPEATABLE READ 를 사용하게 되며 Oracle Database 에서는 READ COMMITTED 를 사용하며 MySQL 의 기본 격리 수준은 REPEATABLE READ 로 되어 있습니다.
READ UNCOMMITTED
READ UNCOMMITTED 격리 수준은 각 트랜잭션에서의 변경 내용이 COMMIT 이나 ROLLBACK 여부와 상관 없이 다른 트랜잭션에서 보이게 되는 트랜잭션 격리 수준 입니다.
어떤 세션에서 진행 중인 트랜잭션이 처리가 완료 되지 않은 상태라도 다른 세션에서 해당 변경 된 정보(값)를 볼 수 있는 현상을 더티 리드(Dirty read) 라고 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED 입니다.
더티 리드 현상은 데이터가 보였다가 사라졌다 하는 현상을 초래하게 됨으로 애플리케이션 개발자와 사용자 모두를 혼란 스럽게 만들게 됩니다.
더티 리드를 유발하는 READ UNCOMMITTED 격리 수준은 DBMS 표준에서 트랜잭션의 격리 수준으로 인정하지 않을 정도로 데이터 정합성에 상당히 큰 문제를 야기시킬수 있는 격리 수준 입니다.
그래서 MySQL 사용시 최소 READ COMMITTED 이상의 격리 수준 사용이 권장됩니다.
READ COMMITTED
READ COMMITTED 는 Oracle Database 에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준 입니다.
이 레벨에서는 이전에 언급한 더티 리드(Dirty read) 같은 현상은 발생하지 않습니다. 어떤 세션의 트랜잭션에서 데이터를 변경하였을 때 COMMIT 으로 트랜잭션이 완료되어야지만 다른 세션에서 변경된 데이터를 조회할 수 있습니다.
세션 A 에서 데이터를 변경 하고 COMMIT 을 하지 않았을 경우, 세션 B에서는 이전 데이터 값을 계속 조회하게 됩니다.
이 결과는 조회를 요청한 테이블이 아닌 언두 테이블스페이스(언두 레코드, 언두 세그먼트) 를 통해서 Before Image 정보를 가져온 것 입니다.
이와 같이 하나의 데이터에 대해서 여러가지 버전이 존재하고 조회 하는 기능을 MVCC 라고 하며 Oracle 과 MySQL 에서는 Undo 로그를 이용하여 구현하고 있습니다.
세션 A 에서 COMMIT 으로 트랜잭션을 종료하게 되면 그 때 부터 다른 세션에서는 변경 된 데이터 값을 조회 할 수 있게 됩니다.
READ COMMITTED 격리 수준에서는 NON-REPEATABLE READ(REPEATABLE READ 가 불가능 하다) 라는 부정합의 문제가 있습니다.
Session A가 BEGIN 명령으로 트랜잭션을 시작하였고 first_name 이 'Gary' 라는 사용자 를 검색 했을 때는 일치 하는 결과가 없었습니다. 하지만 Session B 가 사원 번호 5000 인 사원의 이름을 'Gary' 로 변경하고 COMMIT 을 실행 한 후, Session A 에서 동일한 쿼리를 다시 조회할 경우 이번에는 'Gary' 라는 사용자가 1건이 조회가 되게 됩니다.
이는 별다른 문제가 없을 것으로 생각할 수 도 있지만, 사실 Sesssion A가 하나의 트랜잭션 내에서(BEGIN 을 하여 트랜잭션을 시작한 이후) 같은 쿼리를 수행하였을 때 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에는 어긋나게 되는 것 입니다.
이러한 부정합 현상은 하나의 트랜잭션에서 동일 데이터를 여러번 읽고 변경하는 작업이 금전적인 처리와 연결되어있다면 문제가 될수도 있는 사안 입니다.
입금과 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해 봅니다.
그러나 REPEATABLE READ 가 보장하지 않기 때문에 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져올 게 되는 것 입니다.
트랜잭션 없이 실행한 SELECT 문장과 트랜잭션 없이 실행한 SELECT 문장
가끔 사용자 중에서 트랜잭션 내에서 실행되는 SELECT문장과 트랜잭션 없이 실행되는 SELELCT 문장의 차이를 혼돈 하는 경우가 있습니다. READ COMMITTED 격리 수준에서는 실행되는 SELECT 문장의 차이가 없습니다.
하지만 REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동 합니다.
즉, START TRANSACTION(또는 BEGIN) 명령으로 트랜잭션을 시작한 상태에서 온 종일 동일한 쿼리를 반복해서 실행하면, 계속 동일한 결과값만 보이게 됩니다.
이는 다른 세션에서 해당 데이터 값을 변경하고 COMMIT 하는 것과 무관하게 트랜잭션을 시작하고 조회한 시점의 데이터를 계속 동일하게 반복적으로 보여주게 되고 이런 의미로(반복적으로 같은 값을 읽는다) REPEATABLE READ 라고 하는 것 입니다.
REPEATABLE READ
REPEATABLE READ 는 MySQL 의 InnoDB 스토리지 엔진에서 기본적으로 사용 되는 격리 수준 입니다.
이 격리 수준에서는 READ COMMITTED 에서 발생하는 "NON-REPEATABLE READ" 부정합이 발생하지 않습니다.
InnoDB 스토리지 엔진은 트랜잭션이 Rollback 될 경우를 대비하여 변경 전 데이터를 언두(UNDO) 에 백업해두고 실제 레코드 값을 변경 하게 됩니다. 그리고 트랜잭션이 COMMIT 을 하기전에 다른 세션에서 해당 데이터를 조회시 언두를 참조하여 이전 값을 보여주는 것을 MVCC 라고 합니다
REPEATABLE READ 는 MVCC 를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서 동일한 결과를 계속 보여 줄수 있게 보장을 합니다.
READ COMMITTED 도 MVCC 를 이용해 COMMIT 되기전 데이터를 보여주는 것은 동일 하지만, REPEATABLE READ 에서는 언두 영역에 백업된 레코드의 여러 버전 중에서 몇 번째 이전 버전 까지 찾아 들어갈 수 있느냐 의 차이가 있는 것 입니다.
언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단되는 시점에 주기적으로 삭제하게 됩니다.
REPEATABLE READ 격리 수준에서는 MVCC 를 보장하기 위해서 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호 보다 앞선 언두 영역의 데이터는 삭제 할 수 없습니다.
정확히는 특정 트랜잭션 번호 구간내에서 백업된 언두 데이터가 보존되어야 합니다.
그래서 REPEATABLE READ 격리 수준을 사용할 경우에는 장기간 활성화 되어 있는 SELECT 트랜잭션도 관리가 필요 합니다.
한 사용자가 BEGIN 으로 트랜잭션을 시작하고 조회하고 장시간 트랜잭션을 종료 하지 않았다면 언두 영역이 백업 된 데이터로 인하여 무한정 커질수도 있게 됩니다. 이렇게 언두에 백업 된 레코드가 많게 되면 MySQL 서버의 처리 성능이 떨어지게 됩니다.
SERIALIZABLE
SERIALIZABLE 격리 수준은 가장 단순한 격리 수준이면서 가장 엄격한 격리 수준 입니다.
그만큼 동시 처리 선능도 다른 트랜잭션 격리 수준보다 떨어지게 됩니다. InnoDB 테이블에서 기본적으로 순수한 SELECT(Insert select 나 CTAS 가 아닌) 는 아무런 레코드를 잠금 설정하지 않고 실행하게 됩니다.
InnoDB 메뉴얼에서 자주 나타나는 "Non-locking consistent read(잠금이 필요 없는 일관된 읽기)" 라는 말이 이를 의미 하게 됩니다.
하지만 트랜잭션 격리 수준을 SERIALIZABLE 로 설정하면 읽기 작업도 공유 잠금(읽기 잠금)을 획득 해야만 하며, 동시에 다른 트랜잭션에서는 절대 접근 할 수 없는 상황이 되게 됩니다.
SERIALIZABLE 격리 수준에서는 일반적인 DBMS 에서 일어나는 "PHANTOM READ" 라는 부정합이 발생하지 않습니다.
InnoDB 스토리지 엔진에서 REPEATABLE READ 을 사용할 경우 갭 락과 넥스트 키 락 덕분에 "PHANTOM READ" 가 발생되지 않기 때문에 굳이 SERIALIZABLE 을 사용할 필요성은 없어 보입니다.
갭 락과 넥스트 키 락에 관련 된 내용은 이전 포스팅을 참조하시면 됩니다.
지금 까지 해서 트랜잭션과 잠금에 대해서 살펴보았으며, 여기에서 포스팅을 마무리 하도록 하겠습니다.
Reference
Reference Book
• Real MySQL 8.0
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
