[DB] MySQL 및 InnoDB의 DB Lock 알아보기

 

MySQL에서 사용되는 락은 다음과 같은 2가지 종류로 나뉩니다.

• MySQL 엔진의 락
• 스토리지 엔진(InnoDB)의 락

MySQL 엔진의 락, 스토리지 엔진 중 현재 MySQL의 기본 스토리지 엔진인 InnoDB의 락을 알아보도록 하겠습니다.

---

0. 테스트 환경 구성

• 도커에서 MySQL 사용
• 다음과 같은 coupon 테이블, member 테이블에서 진행

---

1. MySQL 엔진의 락

MySQL 엔진은 MySQL 아키텍쳐에서 스토리지 엔진 위의 계층에 속하기 때문에

MySQL 엔진의 락은 모든 스토리지 엔진에 영향을 미치게 됩니다.

 

MySQL 엔진의 락은 다음과 같은 3종류를 제공합니다.

• 글로벌 락 (Global Lock)
• 메타데이터 락 (Metadata Lock)
• 네임드 락 (Named Lock)

 

1-1. 글로벌 락 (Global Lock)

• 모든 DDL과 데이터를 조회하는 SELECT를 제외한 DML의 실행을 막습니다.
• FLUSH TABLES WITH READ LOCK 명령을 통해 획득할 수 있습니다.
• 글로벌 락이 걸리면, 다른 트랜잭션이나 쿼리는 글로벌 락이 해제될 때까지 대기해야합니다.
• 글로벌 락의 적용 범위는 거의 모든 트랜잭션과 쿼리여서 서비스에 큰 영향을 미치기 때문에 웹 애플리케이션에 사용되는 MySQL에서는 거의 사용하지 않습니다.
• 해제는 UNLOCK TABLES 명령을 통해 해제할 수 있습니다.

 

아래는 글로벌 락을 테스트해본 예시입니다.

글로벌 락 예시

SELECT는 잘 되지만, INSERT를 실행했을 때 두 테이블 모두 Lock에 의한 오류가 발생하는 것을 알 수 있습니다.

 

 

1-2. 테이블 락 (Table Lock)

• 개별 테이블 단위로 설정되는 잠금
• LOCK TABLES table_name [ READ | WRITE ] 명령으로 테이블 락을 획득할 수 있습니다.
• 글로벌 락과 마찬가지로 테이블 하나를 잠그는 작업도 서비스에 큰 영향을 미치기 때문에 거의 사용하지 않습니다.
• 해제는 UNLOCK TABLES 명령을 통해 해제할 수 있습니다.

 

• MySQL에서는 스토리지 엔진(MyISAM, InnoDB) 별로 테이블 락이 자동으로 걸리거나, 걸리지 않을 수도 있습니다.
• MyISAM
• 테이블에 데이터를 변경하는 쿼리를 실행하면 자동으로 테이블 락이 설정됩니다.
• 데이터 변경 쿼리 실행 시 자동으로 테이블 락 설정 후 쿼리가 완료되면 자동으로 테이블 락이 해제됩니다.
• InnoDB
• 스토리지 엔진이 레코드 기반 락을 제공하므로 테이블 락이 설정되지만, 대부분의 데이터 변경 쿼리(DML)에서는 무시되고 스키마 변경 쿼리(DDL)에서만 적용이 됩니다.

테이블 락 예시

 

위는 테이블 락 상황을 나타낸 것입니다.

왼쪽 세션에서 coupon 테이블에 WRITE LOCK을 걸었을 때, 해당 세션에서는 접근이 가능하지만

오른쪽 세션인 다른 세션에서는 coupon 테이블에 접근할 수 없습니다.

왼쪽 세션에서 UNLOCK TABLES로 락을 해제함과 동시에 오른쪽 세션에서 작업이 수행되는 것을 볼 수 있습니다.

 

 

1-3. 네임드 락 (Named Lock)

• 잠금의 대상이 레코드, 테이블 같은 데이터베이스 객체가 아니라 사용자가 설정한 문자열
• GET_LOCK(’잠금 설정할 문자열’, time out 시간)으로 네임드 락을 설정할 수 있다.
• RELEASE_LOCK으로 잠금을 해제할 수 있다.

네임드 락 예시

 

위의 사진은 ‘seongha’라는 문자열로 네임드 락을 설정한 모습입니다.

네임드 락 설정 결과 값은 다음과 같습니다.

• 1 : 잠금 설정 성공
• 0 : 인자로 설정한 timeout 보다 시간이 지나서 잠금 설정에 실패한 경우
• null : 잠금 설정 중 에러가 발생했을 경우 (ex : Out Of Memeory, 스레드 종료 등)

위의 사진은 왼쪽 세션에서 ‘seongha’라는 네임드 락이 설정되었고,

오른쪽 세션에서 ‘seongha’라는 네임드 락을 설정하려고 했지만 이미 왼쪽 세션에서 같은 문자 네임드 락이 있기 때문에

인자로 설정한 10초가 지나서 잠금 설정에 실패한 경우 예시입니다.

 

네임드 락 활용 사례는 아래 우아한형제들 기술 블로그에 자세히 나와 있습니다.

https://techblog.woowahan.com/2631/

MySQL을 이용한 분산락으로 여러 서버에 걸친 동시성 관리 | 우아한형제들 기술블로그

 

 

1-4. 메타데이터 락

• 테이블이나 뷰의 이름이나 구조를 변경하는 경우에 설정하는 잠금
• 명시적으로 명령어로 설정할 수 없고, 테이블이나 뷰의 이름이나 구조를 변경하는 경우에 자동으로 설정된다.

---

2. 스토리지 엔진의 락 (InnoDB)

스토리지 엔진의 락은 MySQL 8.0의 기본 스토리지 엔진인 InnoDB를 기준으로 설명하도록 하겠습니다.

 

InnoDB는 이전 기본 스토리지 엔진이었던 MyISAM과 달리 레코드 기반의 잠금 방식을 사용하기 때문에

동시성 처리가 뛰어나다는 장점이 있습니다.

 

InnoDB의 락은 다음과 같은 4가지 종류가 존재합니다.

• 레코드 락 (Record Lock)
• 갭 락 (Gap Lock)
• 넥스트 키 락 (Next Key Lock)
• 자동 증가 락 (Auto Increment Lock)

 

여러 락을 살펴보기 전에,

InnoDB의 Lock은 레코드 자체가 아니라 인덱스의 레코드를 잠그게 됩니다.

인덱스의 레코드를 잠그는 것이 어떤 것인지, 어떤 의미인지 살펴보도록 하겠습니다.

 

※ 인덱스의 레코드를 잠근다?

‘인덱스의 레코드를 잠근다.’라는 것은 인덱스 설계가 동시성 측면에서도 영향을 줄 수 있다는 것을 의미합니다!

인덱스를 생각하면 조회 시에 더 빠르게 값을 찾아올 수 있는 색인 기능이라고 생각해서

인덱스를 설계할 때 조회 성능에만 집중하여 설계할 수 있습니다.

 

하지만 MySQL InnoDB에서는 동시성 제어에 사용하는 Lock이

레코드 자체가 아닌 인덱스 레코드를 잠그기 때문에,

결국 인덱스 설계 고려 요소에 동시성이라는 요소도 고려해야한다는 것을 의미합니다!

 

아래의 member 테이블로 간단히 인덱스 설계 시 왜 동시성도 고려해야 하는지 예를 들어봅시다.

예시 테이블

 

현재 member 테이블에는 PK를 제외하고 nickname, address 컬럼이 존재합니다.

이때, address 컬럼에는 인덱스를 설정해놓은 상태입니다.

 

여기서 다음 상황을 가정해봅시다.

• member의 address = ‘서울’인 멤버(레코드)는 100명
• address가 서울인 멤버 중에서 ninkname = ‘성하’인 멤버(레코드)는 1명

 

이때 서울에 사는 성하라는 닉네임을 가진 멤버가 생일이 지나서 나이를 증가시키는 쿼리를 수행한다고 해봅시다.

UPDATE member SET age=27 WHERE address='서울' AND nickname='성하';

 

여기서 UPDATE를 수행할 때 MySQL의 InnoDB에서는 어떻게 락이 설정될까요?

InnoDB의 락은 레코드 자체가 아니라 인덱스 레코드에 락이 설정된다고 했습니다.

여기서 address는 인덱스가 존재하지만, nickname에 인덱스가 존재하지 않기 때문에

위의 상황에서는 address가 서울인 인덱스 레코드 전부에 락이 걸릴 것입니다.

 

이렇게 된다면, address = ‘서울’ 조건이 있는 모든 쓰기 작업이 락이 해제될 때까지 대기해야 합니다.

• address가 서울인 멤버의 나이 UPDATE
• address가 서울인 멤버의 닉네임 UPDATE
• …

현재 예시에서는 컬럼이 많지 않아서 대기 상황이 많지 않지만 실제 서비스에서는 엄청난 대기가 발생할 수 있을 것입니다.

 

만약, 해당 테이블에 인덱스가 존재하지 않는다면 어떻게 될까요?

이런 경우에는 테이블을 풀 스캔하면서 UPDATE를 작업하고, 테이블에 있는 모든 레코드에 락을 설정하게 됩니다.

 

요약하면, 주어진 상황에 맞게 인덱스를 잘 설계하는 것이 조회 성능 뿐만 아니라 동시성 성능에도 영향을 미친다는 것을 알 수 있습니다.

 

 

2-1. 레코드 락 (Record Lock)

레코드 락은 인덱스 레코드를 잠그는 기본적인 MySQL InnoDB의 락을 말합니다.

인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터링 인덱스(PK 인덱스)를 통해 락을 설정합니다.

앞서 인덱스 레코드를 잠그는 기본 동작은 잘 설명했기 때문에, 레코드 락은 빠르게 넘어가도록 하겠습니다.

 

 

2-2. 갭 락 (Gap Lock)

갭 락은 인덱스 레코드가 아닌, 인덱스 레코드와 인접한 인덱스 레코드 사이의 간격을 잠그는 것을 의미합니다.

이러한 갭 락의 역할은 인덱스 레코드와 인덱스 레코드 사이에 새로운 레코드가 생성되는 것을 잠그는 역할입니다.

갭 락은 다른 DBMS에는 없는 MySQL의 InnoDB 기능으로,

이는 트랜잭션 격리 레벨과 관련 있는 Phantom Read 문제를 MySQL InnoDB에서 방지하는 방법으로 사용됩니다.

글 마지막에서 Phantom Read 문제를 어떻게 해결하는지 살펴보겠습니다.

 

 

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

넥스트 키 락은 레코드 락과 갭 락을 합친 락입니다.

보통 MySQL InnoDB에서는 앞에서 설명한 갭 락을 단독으로 사용하지 않고

넥스트 키 락을 걸어서 레코드 락과 함께 사용하게 됩니다.

이 부분도 마지막에 Phantom Read 문제를 해결하는 방법을 살펴볼 때 살펴보겠습니다.

 

 

2-4. 자동 증가 락 (Auto Increment Lock)

MySQL은 자동 증가하는 숫자 값을 추출하기 위해서 AUTO_INCREMENT라는 컬럼 속성을 제공합니다.

이때 AUTO_INCREMENT 컬럼(자동 증가하는 숫자 컬럼)을 사용하는 테이블에 동시에 여러 레코드가 INSERT 되는 경우에 해당 레코드들은 중복되지 않고 순서대로 증가하는 값을 가져야 합니다.

InnoDB에서는 이를 위해 내부적으로 Auto Increment Lock인 테이블 수준의 자동 증가 락을 사용합니다.

 

자동 증가 락은 자동 증가하는 숫자 컬럼의 동시성을 제어하기 위한 락이므로

UPDATE, DELETE에는 설정되지 않고 INSERT, REPLAC와 같이 새로운 레코드를 저장하는 쿼리에서만 자동 증가 락이 설정됩니다.

 

자동 증가 락은 테이블 당 1개만 존재하기 때문에 여러 INSERT 쿼리가 동시에 실행되는 경우에하나의 쿼리에서 설정한 자동 증가 락이 해제될 때까지 나머지 쿼리들이 기다려야 합니다.

 

 

 

※ 팬텀 리드 (Phantom Read) 문제 - Gap Lock으로 해결

다른 트랜잭션에서 수행한 변경 작업(커밋된 변경 작업)에 의해

이전 조회 레코드에서 존재하지 않았던 레코드가 조회 시 새롭게 추가되어 나타나는 현상을 PHANTOM READ라고 합니다.

일반적인 Phantom Read 예시

 

일반적으로 잠금이 없는 조회 (단순 SELECT)에서는

MVCC를 통해 언두로그에서 데이터를 조회하기 때문에 Phantom Read가 발생하지 않습니다.

왜냐하면, 자신의 트랜잭션보다 나중에 실행된 트랜잭션의 쓰기 작업은 무시하면 되기 때문입니다.

 

그러나, 잠금을 사용하는 조회 (SELECT FOR SHARE, SELECT FOR UPDATE)에서는

데이터 조회가 언두로그에서 일어나지 않고 테이블에서 수행됩니다.

따라서, 일반적으로는 Phantom Read 현상이 잠금을 사용하는 조회에서 발생할 수 있습니다.

 

MySQL InnoDB는 이러한 Phantom Read 문제를 갭 락(Gap Lock)을 사용하여 방지합니다.

MySQL InnoDB를 사용할 때 레코드 1개를 조회한다면 해당 레코드에 레코드 락을 설정하고,

해당 레코드 전후 공간에는 갭 락을 설정해서 넥스트 키 락을 걸게 됩니다.

Next Key Lock 예시

 

위의 그림은 product_id가 4인 레코드를 잠금을 설정하는 조회(SELECT FOR UPDATE)로 조회했을 때 락이 설정된 모습입니다.

조회한 4번 레코드에는 Record Lock을 설정하고,

전후의 빈 공간인 2~3번과 5~6번에는 Gap Lock을 설정하여 Next Key Lock을 설정한 모습임을 알 수 있습니다.

 

해당 상황에서 어떻게 Phantom Read가 발생하지 않는지 살펴봅시다.

Phantom Read 방지 예시

 

먼저 사용자 B가 트랜잭션 B에서 잠금 조회(SELECT FOR UPDATE)로 product_id가 4 이상인 상품을 조회합니다.

1. 사용자 B가 트랜잭션 B에서 잠금 조회(SELECT FOR UPDATE)로 product_id가 4 이상인 상품을 조회
2. product_id 2~7 레코드에 Next Key Lock 설정
3. 사용자 A가 트랜잭션 A에서 product_id 6번으로 상품을 INSERT 시도
4. product_id 2~7 레코드에는 이미 Lock이 걸려있기 때문에 Blocking 상태 진입 (트랜잭션 B 커밋 전까지 대기)

원래 기존 Phamtom Read는 3번에서 INSERT를 한 후에,

트랜잭션 B에서 다시 조회를 했을 때 이전 조회에서 없었던 레코드가 등장하는 문제였습니다.

하지만, MySQL InnoDB는 위 과정처럼 Next Key Lock(정확히는 Gap Lock)을 사용해서

트랜잭션 B에서 다시 조회를 했을 때 이전과 같은 데이터가 조회되어 Phamtom Read를 방지합니다.

이러한 원리로 MySQL InnoDB의 기본 격리 수준인 REPEABLE READ에서 Phantom Read가 발생하지 않는 것이었습니다.

---

Reference

 

Real MySQL8.0 책(저자 백은빈, 이성욱)

 

https://mangkyu.tistory.com/298

[MySQL] 스토리지 엔진 수준의 락의 종류(레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락)

 

https://mangkyu.tistory.com/299

[MySQL] 트랜잭션의 격리 수준(Isolation Level)에 대해 쉽고 완벽하게 이해하기