책너두 (Real MySQL 8.0 1권) 16일차 (~184p)

현재 책너두 1.5기 모집 링크 입니다 : https://breakbook.notion.site

요약

• 트랜잭션의 격리 수준 4가지에 대한 특징을 이해하게 됨.
• 각 격리 수준에 대해, MySQL 에서 어떠한 룰을 가지고 동작하는지 이해하게 됨.
• REPEATABLE READ 격리 수준에서 SELECT … FOR UPDATE 에 대해 레코드의 쓰기잠금 & 언두 영역 잠금 설정되지 않는 부분을 명확히 이해하지 못함..

발췌

• 중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지를 정확히 예측할 수 있어야 한다는 것이다. 그리고 당연히 이를 위해서는 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알아야 한다.(~180p)

메모

MySQL의 격리 수준

• 트랜잭션의 격리 수준(isolation level)은 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 결정하는 것임.
• 격리 수준은 크게 4가지로 나뉨
  • READ UNCOMMITTED ← DIRTY READ 라고도 함.
    • 일반적인 데이터베이스에서 거의 사용하지 않음.
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
    • 동시성이 중요한 데이터베이스에서는 거의 사용되지 않음.
• 이 격리 수준은 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아짐.
  • 동시 처리 성능도 떨어지는 것이라 생각할 수 있는데, 사실 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 중 하나를 사용한다.
  • 오라클 DBMS 는 READ COMMITTED 수준을 많이 사용함.
  • MySQL 에서는 REPEATABLE READ 를 주로 사용함.
    • 아래 설명할 SQL 예제는 모두 AUTOCOMMIT 이 OFF 상태에서만 테스트 가능하다.

READ UNCOMMITTED

• READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT, 혹은 ROLLBACK 여부와 상관없이 다른 트랜잭션에서 보임.
• 위 예시에서 사용자 A 가 500000 인 Lara 를 insert 하고, 트랜잭션이 커밋되지도 않았는데, 사용자 B 가 Lara 를 select 하고 있음.
  • 사용자 A 작업이 만약 문제가 생겨 ROLLBACK 되도, 사용자 B는 Lara 를 정상 데이터로 보고 처리하게됨.
    • 이렇게 어떤 트랜잭션에 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read) 라고 함.
    • 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED 임.
    • 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자 & 사용자를 혼란스럽게 만들게 됨.
    • 또, 더티 리드를 유발하는 READ UNCOMMITTED 는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준임.
    • MySQL 을 사용하면, 최소 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장함.

READ COMMITTED

• READ COMMITTED 는 오라클 DBMS 에서 기본으로 사용되는 격리 수준임.
• 온라인 서비스에서 가장 많이 선택되는 격리 수준임.
  • 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT 이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문

• 위 예시에서 사용자 A 는 500000인 사원 Lara 를 Toto 로 변경했다. 이때, 새로운 값이 Toto 는 emplyoyess 테이블에 즉시 기록되고, 이전 값인 Lara 는 언두 영역으로 백업됨.
• 사용자 A 가 커밋을 수행하기 전에 B가 500000인 사원을 SELECT 하면 언두 영역에 백업된 레코드인 Lara 를 조회하게 된다.
  • 사용자 A가 변경된 내용을 커밋하면 그때, 다른 트랜잭션에서도 백업된 언두 레코드(Lara) 가 아닌 새롭게 변경된 Toto 값을 조회하게 된다.
• 즉, READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그 변경 내역을 조회할 수 없다.
• READ COMMITTED 격리 수준에도 NON-REPEATABLE READ (= REPEATABLE READ 가 불가능하다) 라는 부정합 문제가 있음.

• 위 예시에서는 사용자 B 가 Toto 를 조회해도 없는 상황임.
  • 근데 사용자 A 가 Lara 를 Toto 로 변경하고 COMMIT 한 상황에서 다시 사용자 B 가 Toto 를 조회하면 결과를 반환받게 된다.
• 위 상황이 별다른 문제는 없어보이지만, REPEATABLE READ 관점에서 정합성이 어긋남.
  • REPEATABLE READ 는 한 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다.
• READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없음.
  • REPETABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동함.
  • 즉, START TRANSACTION(= BEGIN) 명령으로 트랜잭션을 시작한 상태에서는 온종일 동일한 쿼리를 반복해서 실행해도 동일한 결과만 보게됨.
    • 아무리 다른 트랜잭션에서 그 데이터를 변경하고 COMMIT 했어도 동일한 결과를 봐야함.

REPEATABLE READ

• REPEATABLE READ 는 MySQL 의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준임.
• 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 함.
• 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합이 발생하지 않음.
• InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경함.
  • 이 방식을 MVCC 라고 함. (4.2.3 절에서 설명했음.)
• REPEATABLE READ 는 이 MVCC 를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장함.
  • 사실 READ COMMITTED 도 MVCC 를 이용해 COMMIT 되기 전 데이터를 보여줌.
  • REPEATABLE READ 와 READ COMMITTED 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있음. (.. 무슨말 ?)
• 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가짐.
• 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있음.
• 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제함.
  • REPEATABLE READ 격리 수준에서는 MVCC 를 보장하기 위해 실행 중인 트랜잭션 중, 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없음.
  • 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아님.
    • 즉, 정확히는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 함.

• 위 예시에서 초기 employees 테이블 값은 트랜잭션 ID 6 에 의해 INSERT 된 값이라고 가정한다.
• 사용자 A 가 사원 Lara 를 Toto 로 변경하고 커밋을 함. (이때 사용자 A 의 트랜잭션 ID 는 12 임)
• 사용자 B는 사용자 A 가 트랜잭션 변경 전후에 각 한번씩 SELECT 를 했는데, 결과는 항상 Lara 값을 가져옴.
  • 사용자 B 가 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10 보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.
• 위 예시에서는 언두 영역에 백업된 데이터가 하나만 있는 것으로 표현함.
  • 근데 사실 하나의 레코드에 대해 백업이 하나 이상 얼마든 존재할 수 있음.
  • 한 사용자가 BEGIN 으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있음.
    • 언두에 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있음.

• REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있음.
• 위 에시에서 사용자 A 가 INSERT 를 실행하는 도중, 사용자 B가 SELECT … FOR UPDATE 쿼리로 employees 테이블을 조회하는 상황임.
• 사용자 A 가 INSERT 하기 전 후에 대한 사용자 B 의 SELECT 결과가 다름.
  • 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ (또는 PHANTOM ROW) 라고 한다.
  • SELECT … FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 함.
    • 근데, 언두 레코드에는 잠금을 걸 수 없음.
    • 그래서 SELECT … FOR UPDATE 나 SELECT … LOCK IN SHARE MODE 로 조회하는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 됨.

SERIALIZABLE

• 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준임.
  • 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어짐.
  • InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행됨.
  • InnoDB 매뉴얼에 자주 나타나는 Non-locking consistent read (잠금이 필요 없는 일관된 읽기) 라는 말이 이를 의미함.
• 트랜잭션 격리 수준이 SERIALIZABLE 로 설정되면 읽기 작업도 공유 잠금(읽기 잠금) 을 획득해야 하며, 동시에 다른 트랜잭션은 그 레코드 변경하지 못함.
  • 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.
• SERIALIZABLE 격리 수준에서는 일반적인 DBMS 에서 일어나는 PHANTOM READ 문제가 발생하지 않음.
  • 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ 가 발생하지 않기 때문에 굳이 SERIALIZABLE 을 사용할 필요는 없음.