[MySQL] 인덱스와 잠금, MySQL의 격리 수준

인덱스와 잠금

InnoDB 엔진의 레코드 락 부분을 보면, 사실 InnoDB는 레코드를 잠그는 것이 아니라, 인덱스를 잠그는 방식으로 처리하는걸 볼 수 있다.

즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠궈야한다.

 

이는 인덱스 설계가 중요한 이유와 연결되는데, 예시를 통해 알아보자.

SELECT COUNT(*) FROM employees WHERE first_name = "jin"; //253

SELECT COUNT(*) FROM employees WHERE first_name = "jin" AND last_name = "hyogyeom; //1

 

만약 employees 테이블이 first_name 컬럼만을 멤버로 가지는 인덱스를 가지고 있다고 가정해보자.

first_name = jin 인 사원은 253명, last_name = hyogyeom이기까지 한 사원은 1명뿐이다.

 

만약 이 상황에서 first_name = jin이고 last_name = hyogyeom인 사원을 업데이트하려고 하면

과연 몇 건의 레코드가 잠길까 ?

last_name 컬럼은 인덱스에 없기 때문에 한 건의 업데이트를 위해 253건의 레코드가 잠기게 된다.

 

상기했듯이 변경해야할 레코드 후보를 모두 잠그기 때문에 이런 현상이 일어나게 되는 것이다.

별거 아니여보이지만 더 극단적인 예시로, 만약 30만건의 레코드가 있는 테이블에 인덱스가 없다고 가정해보면 끔찍하다.

인덱스가 없기 때문에 조회 시 30만개의 레코드를 풀 스캔하는것도 문제지만

한 건의 조회를 위해 30만개의 레코드가 잠기는 것도 큰 문제일 것이다.

 

MySQL 격리 수준

트랜잭션의 격리 수준이란, 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경/조회중인 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

 

격리 수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 4 가지로 나뉜다.

뒤로 갈수록 격리 정도가 높아지고 동시 처리 성능이 떨어진다. (사실 SERIALIZABLE을 제외하곤 성능 저하가 크게 발생하지 않는다)

 

데이터베이스의 격리 수준을 다룰 때 항상 등장하는 세 가지 부정합 문제가 있는데 표를 통해 알아보자.

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB의 경우 없음)
SERIALIZABLE	없음	없음	없음

 

일반적인 온라인 서비스에서는 READ COMMITTED, REPEATABLE READ 중 하나를 사용한다.

(MySQL의 경우 REPEATABLE READ를 주로 사용)

 

READ UNCOMMITTED

말 그대로 커밋되지 않은 데이터도 읽는다. 만약 사용자 A가 id = 100인 새로운 데이터를 INSERT한다고 가정해보자.

격리수준이 READ UNCOMMITTED인 경우, A가 이 변경사항을 커밋했는지 여부와 상관없이 다른 사용자 B가 조회할 수 있다.

 

위와 같이 어떤 트랜잭션에서 완료되지 않은 작업을 다른 트랜잭션에서 조회할 수 있는 것을 더티 리드(Dirty read)라고 한다.

당연하게도, A가 알 수 없는 문제로 INSERT내용을 롤백하더라도 B는 A가 잠깐 INSERT했던 데이터를 정상적이라 생각할 것이다.

따라서 이 더티 리드 현상은 데이터가 나타났다 사라지는 현상을 초래해 개발자들을 혼란스럽게 만든다.

 

이 격리 수준은 정합성에 문제가 많으므로, MySQL에서는 최소한 READ UNCOMMITTED 이상의 격리 수준을 권장한다.

 

READ COMMITTED

오라클 DBMS에서 기본적으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.

 

이 수준에서는 더티 리드 현상이 발생하지 않는다.

어떤 트랜잭션에서 데이터를 변경하더라도, 다른 트랜잭션은 "커밋이 완료된" 데이터만 조회할 수 있기 때문이다.

 

만약 사용자 A가 id = 100인 사원의 first_name인 "jin"을 "kim"으로 변경했다고 해보자.

사용자 A가 이 변경사항을 커밋하기 전에 사용자 B가 id = 100인 사원을 조회하면 어떻게 될까?

MVCC 부분에서도 한번 다뤘지만, 커밋되기 전이므로 변경된 "kim"을 읽는 것이 아니라 언두 로그의 "jin"을 읽게 된다.

(당연히 사용자 A가 트랜잭션을 종료하면 "kim"으로 조회될 것이다)

 

이 격리 수준에서도 NON-REPEATABLE 부정합 문제 (REPEATABLE READ가 불가능) 발생한다.

 

간단하게 말하면 REPEATABLE READ 정합성에 어긋난다는 건데, 예시를 들어보자.

만약 사용자 B가 트랜잭션을 시작하고 first_name = "jin"인 사원을 조회했는데 결과가 없었다.

이 때 사용자 B의 트랜잭션이 끝나기 전에 사용자 A가 first_name = "jin"인 사원을 INSERT하고 커밋했다.

이 후 사용자 B가 자신의 트랜잭션 종료 전에 동일한 조건의 조회를 하면 이번에는 A가 INSERT한 결과가 나올 것이다.

 

이 경우가 바로 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야한다는

"REPEATABLE READ"의 정합성에 어긋나는 경우이다.

이러한 부정합은 일반적인 웹 프로그램에서는 크게 문제가 안되더라도,

하나의 트랜잭션에서 여러 번 동일 데이터를 조회하는 작업이 금전적인 작업과 연결되면 큰 문제가 될 수도 있다.

 

REPEATABLE READ

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

바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 이상의 격리 수준을 사용해야한다.

이름에서 알 수 있듯이 위에 나온 NON-REPEATABLE 부정합 문제가 발생하지 않는다.

 

READ COMMITTED와 같이 COMMIT되기 전의 데이터를 언두 영역에 백업된 데이터를 이용해 보여주는 MVCC를 이용하지만, 

차이점은 바로 언두 영역에 백업된 레코드의 여러 버전들 중 몇 번째 이전 버전까지 찾아 들어가야하느냐에 있다.

 

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호가 있고, 언두 영역에 백업된 레코드는 변경을 발생시킨 트랜잭션의 번호가 포함되어있다.

또한 InnoDB엔진이 불필요하다고 생각하는 백업된 데이터는 언두 영역에서 주기적으로 삭제된다.

 

REPEATABLE 격리 수준에서는 MVCC를 보장하기 위해

실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.

그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 언두 데이터가 필요한 것은 아니다.

(정확하게는 특정 트랜잭션 번호의 구간 내에 백업된 언두 데이터가 보존되어야한다)

 

REPEATABLE READ 격리 수준의 작동방식을 READ COMMITTED 예시에서 조금 확장한 예를 들어서 알아보자.

READ COMMITTED에서는 사용자 A의 트랜잭션 종료 후 사용자 B가 조회하면 동일 트랜 잭션 내에서 다른 결과가 조회되었다.

그 이유는 사용자 A의 커밋이 반영 이전에는 언두 로그를 읽었지만, 커밋 이후에는 실제 변경된 데이터를 읽었기 때문이다.

 

REPEATABLE READ에서는 A가 변경사항을 커밋하더라도 B가 실제 변경된 데이터 (첫 조회와 다른 데이터)를 읽지 않는다.

과연 이게 어떻게 가능한것인가 !!

 

중요한 사실 두가지. 트랜잭션의 번호는 순차적으로 부여된다. 그리고 언두 영역의 백업 데이터는 여러 버전을 가진다.

따라서 만약 B의 트랜잭션 번호가 10이였다고 하면, B의 10번 트랜잭션 안에서 실행되는 모든 SELECT는 

B의 트랜잭션 번호보다 작은 번호에서 변경한 것, 즉 한마디로 이전의 트랜잭션에서 변경된 내용들만 보게 된다.

따라서 B는 A가 커밋했더라도 이전의 데이터를 볼 수 있는 것이다.

 

조심해야할 점은 B의 트랜잭션이 장시간 종료되지 않는다면 언두 영역의 백업데이터가 무한정 커질 수도 있고,

이는 MySQL 서버의 처리 성능을 저하시킬 수도 있다.

 

이런 REPEATABLE READ에서도 결국 부정합이 발생한다. (꼬꼬무)

바로 B가 두 번째 조회 시 SELECT가 아닌 SELECT...FOR UPDATE 쿼리를 사용했을 때이다.

이 경우 두 번의 SELECT 쿼리의 결과가 똑같아야하지만 서로 다르게 된다.

 

그 이유는 언두 레코드에는 잠금을 걸 수 가없다.. 따라서 SELECT..FOR UPDATE, SELECT...LOCK IN 등 잠금을 거는 SELECT 쿼리로 조회되는 레코드의 경우 언두 영역의 백업 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.

이렇게 A의 변경사항이 보였다 안보였다 하는 현상을 PHANTOM READ 라고 한다.

 

SERIALIZABLE

가장 단순하면서도 엄격한 격리 수준이다. 미리 언급했듯이 그만큼 동시 처리 성능은 다른 격리 수준보다 떨어진다.

기본적으로 InnoDB 테이블에서의 순수한 SELECT는 아무런 레코드 잠금을 설정하지 않고 실행된다.

InnoDB에 등장하는 Non-locking consistent read (잠금이 필요 없는 일관된 읽기) 라는 말이 이를 의미하는 것이다.

 

하지만 트랜잭션 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 S-LOCK을 획득해야만 하며, 동시에 다른 트랜잭션은 그 레코드를 변경할 수 없다. 즉 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.

 

이 격리수준에서는 PHANTOM READ 문제가 발생하지 않는다. 

하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에 굳이 사용할 필요는 없어 보인다.