[Real MySQL 8.0] InnoDB의 인덱스와 락, 트랜잭션 격리 수준

🌱 들어가기 전

이번 포스팅에서는 InnoDB의 인덱스와 락의 상관관계와 트랜잭션 격리 수준에 대해서 알아보자.
 

---

 

🌱 인덱스와 락

지난 포스팅에서 레코드 락에 대해 정리할 때 다음과 같은 멘트를 남겼다.

innoDB의 경우 레코드 자체를 잠그는 것보다는, 인덱스의 레코드를 잠근다.

인덱스를 잠그기 때문에 레코드 검색 시 발견된 모든 레코드의 락을 걸게 된다.
 
예를 들어, crew 테이블에 age = 23인 사람이 100명이라고 생각해보자.
이때, age에는 인덱스가, name에는 인덱스가 걸려있지 않은 상태다.

SELECT COUNT(*) FROM crew WHERE age = 23;
# result: 100

 
하지만, age = 23이면서 name = 'journey'인 결과는 1개만 나온다고 가정해보자.

SELECT COUNT(*) FROM crew WHERE age = 23 and name = 'journey';
# result: 1

 
이때, age = 23이고 name = 'journey'인 레코드에 대해서 last_modified_at 레코드를 NOW()로 업데이트한다고 생각해보자.

UPDATE crew SET last_modified_at = NOW() WHERE age = 23 and name = 'journey';

 
위의 쿼리문을 실행하기 위해서 몇 개의 레코드에 락을 걸게 될까?

바로, age = 23의 결과로 나온 100개의 레코드에 대해서 모두 락이 걸리게 된다.
인덱스가 만약 하나도 없다면 테이블에 대해 전체 스캔 (full-scan)을 하면서 update를 진행하기 때문에 모든 레코드에 대해서 락을 걸게 될 것이고, 성능에 치명적인 문제가 발생할 것이다. 그렇기 때문에 인덱스를 잘 거는 것이 매우 중요하다!
 
MySQL 8.0부터는 락에 대한 정보를 performance_schema DB를 통해서 확인할 수 있다.

SELECT
    t1.trx_id waiting_trx_id,
    t1.trx_mysql_thread_id waiting_thread,
    t1.trx_query waiting_query,
    t2.trx_id blocking_trx_id,
    t2.trx_mysql_thread_id blocking_thread,
    t2.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.INNODB_TRX t1 ON t1.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
INNER JOIN information_schema.INNODB_TRX t2 ON t2.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID

waiting_trx_id의 경우 대기하고 있는 트랜잭션의 아이디를, waiting_thread는 대기하고 있는 스레드의 번호를, waiting_query는 대기하고 있는 쿼리 정보를, blocking_trx_id는 블락킹 상태에 있는 트랜잭션의 아이디를, blocking_thread는 블락킹 상태에 있는 스레드를, blocking_query는 블락킹 상태에 있는 쿼리 정보를 의미한다.
 
여기서 대기 상태는 트랜잭션이 다른 트랜잭션의 작업이 완료되기를 기다리는 상태를, 블락킹 상태는 다른 트랜잭션이 이미 사용 중인 데이터나 락을 요청한 상태를 의미한다.
= 즉, waiting_trx_id가 blocking_trx_id를 기다리고 있는 상태를 한눈에 확인할 수 있게 된다.
락을 풀기 위해서는 blocking_trx_id가 가지고 있는 락을 해제하고, 그 뒤로 waiting_trx_id의 작업이 이루어져야 한다.
 
만약 더 상세한 정보를 확인하고 싶다면 data_locks의 테이블을 모두 확인해보자.

SELECT * FROM performance_schema.data_locks;

여기서 engine_transaction_id와 thread_id, 그리고 lock_type과 lock_mode을 확인해보면 어떤 트랜잭션이 어떤 스레드가 사용하고 있고, 어떤 락을 가지고 있는지까지 확인이 가능하다. 만약 결과에서 REC_NOT_GAP 이라는 lock_mode가 있다면 이는 레코드 락에서 갭이 포함되지 않은 순수한 락을 가지고 있음을 의미한다. (lock_mode의 결과로는 S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC 및 UNKNOWN가 나올 수 있다.)
 
만약, 특정 스레드가 락을 너무 오래 보유하고 있다면 아래와 같이 KILL을 해주자.

KILL thread_id

 

---

 

🌱 트랜잭션 격리 수준

트랜잭션 격리 수준은 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 할지 말지 결정하는 것이다. 크게 4가지의 격리 수준이 존재하며, READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE로 나뉜다. Read Uncommitted와 Serializable의 경우 보통 잘 사용되지 않는다.
 

	DIRTY-READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O (innoDB에서는 발생 X)
SERIALIZABLE	X	X	X

오라클에서는 Read Committed를 사용하며, MySQL에서는 Repeatable Read를 주로 사용한다.
 

---

 

💬 READ UNCOMMITTED

기본적으로 커밋이나 롤백 여부에 상관없이 다른 트랜잭션에서 확인할 수 있는 격리 수준이다.
 

위 그림을 보면, 아직 'journey'라는 레코드가 커밋되지 않았는데도 사용자 B가 해당 내용을 read 할 수 있는 것을 볼 수 있다.
 
작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 'Dirty Read'라고 하며, 이런 경우엔 데이터가 나타났다가 사라졌다가 하는 현상을 초래하기 때문에 데이터의 정합성에 문제가 발생한다. 웬만하면 read uncommitted의 경우 사용하지 않는 것이 좋다.
 

---

 

💬 READ COMMITTED

오라클에서 기본적으로 사용하는 격리 수준이며, Dirty Read가 해결된 격리 수준이다.
다른 트랜잭션에 의해 데이터가 변경되더라도 Commit이 완료된 데이터만 조회가 가능하다.
 

위 그림을 보면, 기존의 1번 레코드는 name = hello였고, 이에 대해서 'hi'라고 업데이트를 한 것을 볼 수 있다.
이때 사용자 B가 1번 레코드에 대해서 조회할 때, 테이블이 아닌 업데이트 시 언두 로그에 저장했던 값을 읽어서 반환하기 때문에 업데이트 되기 전 데이터가 반환되며, 추후 커밋이 되어야 완전하게 반영이 완료된다.
 

커밋된 데이터만 읽기 때문에 '작업이 완료되지 않은 데이터를 읽는다'는 Dirty Read는 해결되었지만, 하나의 트랜잭션에서 SELECT 시 항상 같은 결과를 반환하는 것이 아닌 UPDATE 이전의 데이터와 이후의 데이터가 다른 데이터가 반환되기 때문에 'Non-Repeatable Read'가 발생한다.
 
단순한 웹 서비스에서 이는 크게 문제가 되지 않지만, 하나의 트랜잭션에서 동일한 데이터를 여러 번 읽고 쓰는 작업이 금전 행위와 관련이 되면 문제가 발생할 수 있다. 특히, 입-출금 로직을 생각해보자. 처음에 10000원이 조회되어서 1000원을 추가하고 11000원이 나오길 기대했는데, 다시 조회하니 6000원으로 나온다면 매우 당혹스러울 것이다. 
 
참고로, 트랜잭션 내부에서 실행되는 SELECT 문과 외부에서 실행되는 SELECT문의 차이는 없다.
하지만, Repeatable-Read에서는 SELECT까지 트랜잭션 범위 내에서만 작동하여 계속 동일한 결과만 보이도록 만들게 된다. 이에 대해서는 아래에서 확인해보자.
 

---

 

💬 REPEATABLE READ

InnoDB에서 기본으로 사용하는 격리 수준으로, 바이너리 로그를 가진 MySQL 서버에서는 최소 Repeatable read 이상의 격리 수준을 가져야 한다. innoDB에서는 MVCC를 활용하여 변경 전 레코드를 언두 공간에 백업해두고, 실제 레코드 값을 변경하는데 이 MVCC를 활용하여 동일한 트랜잭션에서는 동일한 결과를 보여주도록 보장한다.
근데 앞서 봤던 Read Committed 역시 언두 로그에 있는 값을 보여줬었는데, repeatable read와 뭐가 다른 것일까?
이는, 언두 영역에 백업된 값 중 몇 번째 이전까지 찾아 들어가는지에 따라 달라진다.
 
언두 영역에는 기본적으로 변경을 발생시킨 트랜잭션의 번호도 함께 저장해둔다. innoDB는 불필요하다고 판단하는 시점에 과거의 언두 로그를 제거하는데, 이때 현재 실행 중인 트랜잭션 중 가장 오래된 트랜잭션 번호보다 앞선 언두 영역의 데이터는 제거가 불가능하다. 정확하게는, 특정 트랜잭션 구간 내에서 백업된 언두 데이터가 보존되어야 한다.

그림이 너무 아래로 길어져서 양옆으로 쪼갰다.
먼저, 사용자 B가 트랜잭션을 시작하기 전에 id = 1, name = hello인 레코드와 id = 2, name = journey인 레코드가 존재했다.
이때 select를 통해 조회했을 때 name = hello를 조회하였다.
 
이후, 트랜잭션 아이디 12번이 update를 통해서 id = 1의 레코드의 name을 hi로 변경하였고, 변경 전 사항에 대해 언두 로그에 기록하였다. 이때, 언두 로그에는 변경 전 트랜잭션 아이디인 6번도 함께 기록되어 있다.
 
사용자 B가 다시 id = 1에 대해 조회를 했을 때 언두 로그에 대한 정보를 확인하는데, 이때 사용자 B의 트랜잭션 아이디인 10보다 작은 언두 로그의 값을 확인하여 조회하게 된다. 덕분에 업데이트가 되든 안 되든 항상 같은 name = hello의 값을 읽어올 수 있게 된다.
위 경우는 예제를 단순화하기 위해서 언두 영역의 데이터가 하나지만, 당연히 여러 개의 데이터가 존재할 수 있다. 트랜잭션이 길어지면 길어질수록 언두 영역의 데이터 역시 계속 늘어날 수 있기 때문에 MySQL 서버 성능에도 영향을 끼친다는 것을 주의하자.
 
하지만, 이 역시 부정합이 발생할 수 있다.

처음 SELECT에서는 journey만 받아오지만, 두 번째 SELECT에서 journey와 cat을 받아왔다.
이렇게 다른 트랜잭션에 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 'Phantom Read' 라고 한다.
 
기본적으로 select ... for update는 쓰기 락을 걸어야 하는데, 언두 로그에는 락을 걸 수 없기 때문에 select ... for update나 select ... lock in share mode로 조회되는 레코드는 변경 전 데이터가 아닌 현재 레코드의 값을 가져오게 된다.
- select for update는 특정 레코드에 대해서 X-Lock을 거는 구문이고 (업데이트를 위해서 베타 락을 거는 느낌), select lock in share mode는 S-Lock을 거는 구문이다.
 

💡 Non-Repeatable Read랑 똑같은 거 아닐까?
 여기서 '결과의 집합이 다르게 나왔다'는 점에 포커스를 맞춰야 한다. non-repeatable read의 경우 1개의 결과가 나오지만, 업데이트로 인해서 hello, hi라는 2개의 결과가 나왔고, 여기에서는 journey와 journey, cat이라는 2개의 결과가 나왔다. 
동일한 트랜잭션 내에서 동일한 쿼리를 실행하더라도 다른 결과를 얻는다는 게 non-repeatable read이고, 다른 결과 집합을 얻을 수 있다는 게 phantom-read이다. non-repeatable read의 경우 수정된 데이터에 대한 일관성 문제를, phantom read의 경우 삽입이나 삭제된 데이터에 대한 일관성 문제를 가진다.

 
 

💡 InnoDB에서는 왜 Phantom Read가 발생하지 않을까?

지난 포스팅에서 봤듯이, innoDB에서 제공하는 락 중에서 '레코드 락'과 '갭 락'을 합친 '넥스트 키 락'을 제공한다는 것을 알 수 있다. 

위와 같이 사용자 B의 트랜잭션이 커밋되기 전에 select 시 락을 잡고 있어서 계속 같은 결과를 반환하는 것을 볼 수 있다.
 

⭐️ 하지만, select 이후 select for update로 조회하게 된다면 언두 영역이 아닌 원본 테이블의 값을 받아오기 때문에 데이터의 부정합이 발생한다. 즉, innoDB에서 phantom read가 무조건 발생하지 않는 것은 아니다! select for update의 경우 발생할 수 있다.
 

---

 
💬 SERIALIZABLE

가장 단순하면서도 엄격한 격리 수준이다. 그렇기 때문에 다른 격리 수준보다 동시성 작업이 떨어진다.
기본적으로 innoDB 테이블에서 순수한 select 작업은 락 없이도 수행되지만, serializable에서는 읽기에 대해서도 S-Lock을 걸어야 하며, 다른 트랜잭션에서는 해당 레코드를 변경할 수 없게 된다. 이 덕분에 Phantom read 현상이 발생하지는 않지만, innoDB에서는 Gap Lock과 Next Key Lock 덕분에 repeatable read에서도 phantom read가 발생하지 않아서 굳이 사용할 필요가 없다.
 

---

 
코드 레벨로 위 격리 수준을 확인하고 싶다면 아래 포스팅을 확인해 주세요 😊
[MySQL] 트랜잭션 격리 수준을 쿼리를 통해 직접 테스트해보기