[MySQL] 트랜잭션의 격리수준 (Lock)

잠금(Lock)과 트랜잭션(Transactional)

잠금과 트랜잭션은 서로 비슷한 개념으로 인식되는 경우가 있지만, 사실은 아주 밀접한 관계를 가지고 있는 서로 다른 의미로 해석되어야 한다.

 

잠금(Lock)은 동시성 문제가 발생하지 않도록 하나의 레코드 또는 테이블단위에 Lock을 걸어 여러 커넥션에서 동시에 데이터를 변경할 수 없도록 하는 것을 의미하고,

 

트랜잭션(Transactional)은 하나의 작업으로 데이터의 정합성을 보장하기 위한 기능이다. 쉽게 말해서 어떠한 작업을 처리할때 하나의 트랜잭션으로 묶여있는 작업은 모두 완료 되거나, 하나라도 문제가 생기면 없던 일로 하거나.. 라는 뜻으로 이해할 수 있다.

 

이번 포스팅에서는 트랜잭션이 가지는 다양한 격리 수준(isolation level)에 대해서 자세히 알아보려고 한다.

🔒 MySQL의 Lock

트랜잭션의 격리 수준을 자세히 알아보기 전에 선수되어야 하는 지식을 간단하게 짚고 넘어가보자.

 

MySQL의 엔진은 크게 스토리지 엔진과 MySQL 엔진 레벨로 나눌 수 있다. 먼저 MySQL 엔진의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다. 이게 무슨말인지 잘 이해가 안갈수 있는데 쉽게 말해서 MySQL엔진에서 건 Lock은 MySQL을 사용하면서 처리하는 DB에 모두 적용된다는 뜻이고, 스토리지 엔진에 거는 Lock은 해당하는 스토리지 엔진에서만 Lock이 적용된다는 의미이다.

1. 글로벌 락

MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 흭득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

글로벌 락은 MySQL 서버 전체에 영향을 미치며, 작업 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 영향을 미친다.

2. 테이블 락

개별 테이블 단위로 설정되는 잠금이며, 명시적으로 테이블에 락을 거는 경우는 거의 사용되지 않는다. 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문이다.

 

묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. (서버가 데이터 변경이 일어나는 테이블에 잠금 설정 -> 데이터 변경 -> 바로 잠금 해제) 식으로 발생한다.

 

단, InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. DML쿼리에는 락이 무시되고, DDL의 경우에만 영향을 미친다.

 

참고로 여기서 말하는 MyISAM , MEMORY, InnoDB는 스토리지 엔진이며 해당 개념이 부족한 경우 간단하게 공부 후 다음 내용을 읽는것을 권장한다.

3. 유저 락

유저 락은 테이블이나 레코드를 잠그는 것이 아니라, 사용자가 지정한 문자열에 대해 흭득하고 반납하는 잠금이다. 많은 레코드를 한번에 변경하는 트랜잭션의 경우 유용하게 사용한다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다.

4. 네임 락

DB의 이름을 변경하는 경우 흭득하는 잠금이다. 네임 락은 명시적으로 흭득하거나 해제할 수 있는 것이 아니고, RENAME TABLE tab_a TO tab_B와 같이 테이블의 이름을 변경하는 경우 자동으로 흭득하는 잠금이다.

🔒 InnoDB 스토리지 엔진의 Lock

MySQL 최신버전에서는 스토리지 엔진의 default 로 InnoDB를 사용하고 있다. 기본 스토리지 엔진으로 InnoDB를 채택한 이유는 여러가지가 있지만 가장 큰 이유중 하나로 InnoDB가 지원하는 레코드 기반의 잠금 방식 때문이다. 레코드 기반의 잠금 방식 덕분에 MyISAM과 같은 스토리지 엔진보다 훨씬 뛰어난 동시성 처리를 제공한다.

1. 레코드 락

말 그대로 레코드 자체만 잠그기 때문에 위에서 설명한 것 처럼 테이블 락이나 글로벌 락등 다른 Lock보다 동시성 처리에 이점이 있는 방식이다. 한 가지 중요한 점은 InnoDB 스토리지 엔진의 레코드 락은 다른 DBMS가 가지고 있는 레코드 락과 조금 다른점이 있다. 바로 레코드 자체를 잠그는 것이 아니라, 인덱스의 레코드를 잠근다는 점이다.

 

??? 저는 Index를 생성한 적이 없는데요. 그럼 레코드 락을 사용할 수 없는건가요?

 

MySQL에서는 Index를 명시적으로 설정하지 않아도 PK로 설정된 컬럼에 자동으로 clustered index를 설정한다. 즉, 아무런 index를 걸지 않아도 clustered index를 통해 Lock을 건다.

2. 갭 락(Gap Lock)

바로 위에서 설명한 레코드 락과 세트로 알아두어야 하는 Lock이다. InnoDB의 레코드 락이 다른 DBMS의 레코드 락과 다른 점 또 하나가 바로 갭 락(Gap Lock) 이다. 그림으로 먼저 설명하자면 아래와 같이 특정 레코드에 Lock을 거는것이 아닌, 레코드와 레코드 사이의 간격에 새로운 데이터가 생성(INSERT) 되는 것을 제어하는 Lock이다.

 

즉, 홍준섭 이라는 레코드 자체에 Lock을 거는 것이 아닌, 홍준섭과 이호성이 레코드 사이 간격에 Lock을 거는 것이다. InnoDB 스토리지 엔진에서는 대부분 보조 인덱스를 이용한 변경 작업(즉 명시적으로 Index를 설정한 경우)은 바로 아래에서 설명할 넥스트 키 락또는 갭 락을 사용하지만, Index를 명시적으로 설정하지 않고 clustered index만 존재하는 경우에는 아래 그림처럼 레코드 자체에만 Lock을 건다.

3. 넥스트 키 락(Next Key Lock)

넥스트 키 락은 위에서 설명한 레코드 락과 갭 락을 합쳐놓은 형태라고 볼 수 있다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다.

이 넥스트 락 덕분에 아래에서 설명할 Phantom Read라는 것이 발생하지 않게된다.

🌞 트랜잭션의 격리 수준

들어가기전에 먼저 Undo 영역에 대해서 간략하게 알고 넘어가자.

Undo 영역은 UPDATE 문장이나 DELETE 와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다. 예를 들어 다음과 같은 업데이트 문장을 실행했다고 가정해보자.

UPDATE member SET name='김문섭' WHERE member_id = '1';

위 문장이 실행되면 트랜잭션 커밋을 하지 않아도 실제 데이터 파일 내용은 "김문섭"으로 변경된다. 그리고 변경되기 전의 값이 "김영겁" 이었다면, 언두 영역에는 "김영겁" 이라는 값이 백업 되는 것이다.

 

이 상태에서 만약 커밋하게 되면 현재 상태(김문섭)이 그대로 유지되는 것이고, 롤백하게 되면 언두 영역의 백업된 데이터를 다시 데이터 파일(데이터/인덱스 버퍼)로 복구한다.

 

언두 데이터는 트랜잭션의 롤백 대비용과 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는데 사용된다. 즉, 트랜잭션을 수행하기 이전의 데이터를 보관해놓는 공간이다.

1. READ UNCOMMITTED

결론부터 말하자면 해당 격리수준은 정합성 문제가 많기 때문에 사용을 권장하지 않는다. 예제를 통해 쉽게 이해해보자.

S라는 대출 업체가 존재한다.

• A라는 트랜잭션에서 S 라는 대출업체의 현재 이율이 20%인데, 너무 높은 것 같아서 이율을 15%로 내렸다. (Update) 하지만 아직 A 트랜잭션은 commit하지 않은 상태다.
• B라는 트랜잭션에서 S라는 대출업체를 이용하려고 이율을 조회한다. (Select) 조회된 이율은 15%
• S 대출업체는 아무리 생각해봐도 15%로는 회사 운영이 힘들 것 같아서 다시 20%로 올렸다. A 트랜잭션 Rollback
• B라는 트랜잭션에서 S 대출업체를 이용하려는 고객은 이율을 15%로 알고 이용하게 된다.

바로 이런식으로 데이터의 정합성 문제가 발생하게 되는것을 Dirty READ 라고 부른다. 즉, 다른 트랜잭션에서 발생한 변경 내용이 commit여부와 관계 없이 다른 트랜잭션에서 조회할 수 있는것이다.

 

2. READ COMMITTED

해당 격리수준은 오라클 DBMS에서 default로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.

예를들어 A 트랜잭션에서 데이터를 변경했더라도, Commit이 완료된 데이터에 대해서만 B 트랜잭션에서 조회할 수 있다. 따라서 바로 위에서 언급한 Dirty Read와 같은 정합성 문제가 일어나지 않는다. 바로 아래 그림과 같은 흐름으로 진행되는 것이다.

하지만 READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ 라는 문제점이 발생하는데 NON-REPEATABLE READ의 의미를 쉽게 정의하면 "하나의 트랜잭션에서 Select 쿼리를 실행할때는 항상 같은 값을 읽어야 한다" 라는 정의를 어긴것이다. 위 그림에서는 USER B라는 트랜잭션에서 2번 Select와 4번 Select 결과가 서로 다르기 때문에 NON-REPETABLE READ가 발생한 것이다.

 

이러한 부정합 현상은 대부분의 서비스에서는 크게 문제가 되지 않지만, 아래 예시처럼 금전적인 부분을 다루는 서비스에서는 상황에 따라 문제가 될 수도 있다.

• A라는 트랜잭션에서 S라는 대출업체의 이율을 20%에서 15%로 내림. 아직 Commit하지 않은 상태
• B라는 트랜잭션에서 S 대출업체의 이율을 조회함 -> commit되지 않았기 때문에 Undo에서 조회. 결과 : 20%
• A라는 트랜잭션에서 commit을 진행함
• B라는 트랜잭션에서 다시 S 대출업체의 이율을 조회함 -> 결과 : 15%
• 사용자는 이율이 20%인지, 15%인지 정확히 알 수 없게됨

만약 이런 문제가 발생할 수 있는 서비스 환경에서는 격리수준을 REPEATABLE READ로 올려서 사용해야 한다.

3. REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL의 장비에서는 최소 REPEATABLE READ 격리 수준을 사용해야 한다.

 

해당 격리 수준에서는 "NON-REPEATABLE READ" 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션은 항상 ROLLBACK될 가능성이 있다는 비관적 잠금 방식을 채택하고 있기 때문에 항상 데이터가 변경되기 전 레코드를 언두(Undo) 영역에 백업해두고 실제 레코드 값을 변경한다.

이렇게 언두영역에 백업된 데이터를 이용해 동일한 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장한다.

 

 

🤔그렇다면 REPEATABLE READ 격리수준을 가지는 InnoDB는 어떤 방식으로 데이터 부정합 문제를 해결하고 있을까?

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 연두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 아래 그림을 보면서 어떤식으로 부정합 문제를 해결하는지 이해해보자.

• 트랜잭션 ID가 5인 트랜잭션에 의해 초기 테이블이 생성(INSERT) 되었다.
• 트랜잭션 ID가 12인 트랜잭션A에서 UPDATE 쿼리를 날려 NAME을 "홍준성"으로 변경한다.
• Undo영역에는 변경되기 전 데이터를 백업한다.
• 트랜잭션 ID가 10인 트랜잭션B에서 SELECT 쿼리를 날려 ID가 1인 컬럼을 조회한다. 조회결과는 실제 데이터가 아닌 Undo 영역에 백업된 데이터를 읽어온다.

즉, 트랜잭션 ID가 10인 트랜잭션 B는 자신의 트랜잭션 번호(10)보다 작은 트랜잭션에서 변경한 것만 보게된다. 따라서 트랜잭션 ID가 12인 트랜잭션 A의 변경내용은 볼 수 없게된다. 이러한 매커니즘으로 인해 "NON-REPEATABLE READ" 부정합 문제를 발생시키지 않는다.

 

🧤 Phantom READ란?

REPEATABLE READ 격리 수준에서도 Phantom READ라는 문제점이 발생한다.

Phantom READ 이름 그대로 '유령 읽기'로, 위 그림처럼 하나의 동일한 트랜잭션 안에서 동일한 Select ~ FOR UPDATE 쿼리를 실행했을때 첫 번째 조회했을때는 보이지 않던 레코드가, 두 번째 트랜잭션에서는 보이는 현상을 말한다. (유령처럼 없던게 보인다고 이해하면 된다.)

 

🤔 분명히 REPEATABLE READ 격리 수준에서는 자신의 번호보다 낮은 트랜잭션에서만 읽어온다고 했는데..

SELECT ... FOR UPDATE 쿼리는 일반 SELECT 쿼리와 다르게 특정 세션이 데이터에 대해 수정을 할 때까지 LOCK이 걸려 다른 세션이 데이터에 접근할 수 없게한다. SELECT한 쿼리에 대해서 UPDATE쿼리가 발생해야만 다른 트랜잭션에서 해당 컬럼에 대해 SELECT 쿼리를 사용할 수 있다.

SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, Undo 영역의 레코드에는 잠금을 걸 수 없다. 그래서 SELECT ... FOR UPDATE로 조회되는 레코드는 Undo 영역이 아닌 실제 데이터 값을 가져오게 되는 것이다.

 

 

💡 하지만, InnoDB 스토리지 엔진에서는 이러한 상황에서도 Phantom READ가 발생하지 않는다.

 

바로 InnoDB 스토리지 엔진이 사용하는 넥스트 키 락 덕분인데, 바로 아래 그림처럼 해당하는 Index 뿐만 아니라 바로 직전, 직후에 인접한 Row에도 Lock을 걸어버린다.

이렇게 Index 112에 넥스트 키 락이 걸려있는 상태에서 Index 111에 해당하는 레코드에 INSERT또는 UPDATE를 시도할 경우 넥스트 키 락에 의한 잠금대기가 발생하게 된다. 따라서 Phantom READ가 발생하지 않는다.

 

4. SERIALIZABLE

결론부터 말하자면 첫 번째로 소개했던 READ UNCOMMITTED와 더불어 거의 사용되지 않는 격리 수준이다.

가장 엄격한 격리 수준이기 때문에 그만큼 동시 처리 능력도 떨어지게 되며, Phantom READ현상도 발생하지 않는다.

하지만 MySQL의 스토리지 엔진인 InnoDB도 넥스트 키 락을 통해 Phantom READ가 발생하는 현상을 방지하고 있기 때문에 굳이 SERIALIZABLE 격리 수준까지 올려서 사용할 필요는 없다.

 

 

Reference

https://idea-sketch.tistory.com/46

http://www.yes24.com/Product/Goods/6960931

Real MySQL