MySQL(InnoDB)의 Lock에 대해 알아보자

MySQL과 InnoDB의 Lock에 대해 알아보자

서비스를 운영하다보면 서비스의 기능들이 갑자기 멈춰버리는 순간들이 찾아오게됩니다.

그럴 때 사무실에서는 '일단 Lock 걸린거 찾아봐' 라는 말을 먼저 듣게되는데,

실제로 서비스에 Lock이 걸려있었다면 Lock을 풀자마자 서비스가 정상적으로 동작하게되는데

처음 이러한 현상을 보았을때 세삼 놀랐던 기억이 있습니다.

백엔드 개발자들이 일하다보면 한번쯤은 꼭 보게되는 RDB의 Lock에 대해 알아보려고 합니다.

트랜잭션이랑 Lock이랑 뭐가 달라?

트랜잭션은 '테이터베이스의 상태 변경 작업 완정성을 보장해주는 것' 입니다
논리적인 작업을 모두 완벽하게 처리(COMMIT)하도록 하거나, 
처리하지 못할 경우(ROLLBACK) 원 상태로 복구되어 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능입니다.

즉 트랜젝션은 데이터의 정합성을 보장하기 위함입니다.

반면 Lock은 동시성을 제어하기 위한 기능입니다.
예를 들어 하나의 회원정보 레코드를 여러 커넥션에서 동시에 변경하려 할 때 Lock이 없으면 하나의 데이터를 여러 커넥션에서 동시에 변경이 가능해집니다.
결과적으로 레코드의 결과값을 예상 할 수 없게됩니다.

그렇기에 Lock은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청 할 경우 순서대로 한 시점에는 하나의 커넥션만 접근하여 변경 할 수 있게 해주는 역할을 합니다.

인덱스와 Lock의 관계

MySQL의 인덱스는 B-Tree 알고리즘으로 만들어져 있습니다.

MySQL의 InnoDB는 레코드 자체가 아닌 인덱스에 Lock을 설정합니다.

 

B-Tree Index

MySQL에서 데이터를 찾을 때는 Index와 클러스터링 인덱스 (pk)의 B-Tree를 탐색해서 데이터를 찾게됩니다.

아래 그림은 B-Tree Index의 구조입니다. (Index, PK 생성시 B-Tree가 만들어집니다.)

 

출처 : https://baekjungho.github.io/wiki/database/mysql-index/

 

Index와 클러스터링 인덱스(pk)는 각각 다른 B-Tree를 사용합니다.

위 그림은 Index의 B-Tree이고 클러스터링 인덱스(pk)도 동일한 구조를 가지지만

Index의 리프노드는 테이블의 pk값을, 클러스터링 인덱스(pk)의 리프노드는 레코드에 대한 테이블 데이터를 가진다는 차이가 있습니다.

 

즉 Index 키로 특정 레코드를 검색하면 인덱스의 루트 노드에서 시작해 인덱스의 리프노드로 이동하고 인덱스의 리프노드의 값은 pk값이기에 클러스터링 인덱스의 키(pk값)를 찾은 후 리프노드로 이동하여 실제 데이터를 조회하게 됩니다.

그렇기에 특정 ROW들에 Lock이 설정되면 그 ROW범위 만큼 인덱스에 Lock이 걸리게 되고 그 후 PK까지 Lock이 걸리게 됩니다. 
(참고로 SELECT로 인덱스가 걸려있는 컬럼만 조회하면 pk까지 조회하지 않고 인덱스가 가지고 있는 값만으로 반환합니다.)

만약 인덱스가 설정되이 있지 않은 테이블이라면 먼저 모든 테이블 레코드에 락을 설정하고, 풀스캔하며 작업을 하게되어
성능 및 동시성에 영향을 미치게 됩니다.

 

어떻게 Index에 Lock이 걸리는지 데이터를 통해 확인해보겠습니다.

first_name 컬럼에 인덱스가 있는 Employee 테이블이 있고 테이블에 12명의 사원이 있습니다.

 

 

세션1번에서 first_name이 John인 레코드에 대한 UPDATE쿼리가 실행됩니다. (John은 테이블에 3명 있습니다.)

 

-- Lock 조회 명령어
SELECT * FROM performance_schema.data_locks;

 

위의 명령어를 통해 다른 세션에서 Lock을 확인해보면

 

세션1에서 실행시킨 UPDATE문은 idx_first_name 인덱스를 통해 first_name이 'John' 데이터를 찾고 Lock을 걸게됩니다.

만약 first_name 조건 외 다른 조건이 있어 변경되는 레코드가 1건이라 하더라도 idx_first_name를 사용한다면 Lock은 first_name이 'John'인 모든 데이터에 걸리게 됩니다.

 

Update는 X-Lock을 거는 DML이기에 범위 내 인덱스(John)에 X-Lock이 걸리게되고, Intension Lock이 동작해 테이블에는 IX-Lock도 걸립니다.

실제 데이터가 존재하는 클러스터링 인덱스(pk)의 데이터(John)에도 동일하게 X-Lock이 걸리는 모습을 확인 할 수 있으며,

해당 레코드만 잠그는 X,REC_NOT_GAP 과 Next Key Lock을 의미하는 X도 확인 할 수 있습니다.

세션1의 트랜잭션 중 인덱스의 새로운 값 추가를 방지하기 위한 GAP Lock도 걸려있습니다.

Lock의 설정 범위와 종류

앞서 데이터를 통해 설명하면서 IX-Lock , GAP-Lock, Next Key Lock이 언급되었는데, 이는 락의 종류와 범위에 관한 내용입니다.

Lock의 범위와 종류를 알아보겠습니다

Lock의 범위

- ROW (행)

1개의 행에 대해 Lock을 설정합니다. 일반 적인 DML(Update, Insert, Delete) 사용 시 적용되는 범위 입니다.

- Column (열)

1개의 열을 기준으로 Lock을 설정합니다.
컬럼에 대한 접근을 막게되며 설정 및 해제 시 많은 리소스가 사용됩니다.

 

- 페이지, 블럭 (자주 사용 X)

데이터페이스의 데이터들은 모두 스토리지 엔진에서 파일 형태로 저장되며 페이지 단위로 관리됩니다.
MySQL 페이지의 경우 보통 16KB로 설정되어 있습니다.
페이지,블록에 대해서도 Lock을 설정 할 수 있으며 이 또한 많은 리소스가 사용됩니다.

 

- 테이블

한 테이블에 대한 락을 설정합니다.
LOCK TABLES table_name [ READ | WRITE ] 명령으로 명시적으로 락을 얻을 수 있습니다.
MEMORY, MyISAM 테이블에서는 묵시적으로 사용될 때도 있습니다. 
InnoDB의 경우 레코드 기반의 잠금을 제공하기에 단순 데이터 변경 쿼리(DML)로 테이블락이 설정되지는 않고
스키마를 변경하는 DDL 구문(create, alter, drop)에는 테이블락이 설정됩니다.

- 글로벌 락 (자주 사용 X)

전체 데이터베이스에 대해 Lock을 설정합니다.
명시적으로 FLUSH TABLES WITH READ LOCK 명령어를 사용합니다.
Lock이 걸리게 되면 단 하나의 트랜잭션 즉 하나의 세션만이 DB접근이 가능하게 됩니다.

DB전체 업데이트와 같은 상황, 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY테이블에 대해 mysqldump로 일괄된 백업을 받고자 할때 사용 할 수 있습니다.
하지만 MySQL 8.0이상부터는 백업Lock이 도입되어서 백업시에는 백업Lock을 사용합니다.

락의 유형

- 공유 락 (Shared Lock, S-Lock)

읽기 Lock(Read Lock) 이라고도 합니다.
공유 Lock이 걸린 데이터에 대해서는 SELECT만 가능하며 다른 작업은 불가능합니다.
공유 Lock이 걸린 데이터에 대해서 다른 트랜잭션에서도 똑같이 공유 Lock을 획득 할 수 있으나, 배타 Lock은 얻을 수 없습니다.
즉 공유 Lock이 걸려도 읽기 작업은 가능합니다.

공유 Lock을 사용하여 조회한 데이터가 트랜잭션 안에서 변경되지 않음을 보장 할 수 있습니다.
MySQL에서 일반적인 SELECT은 공유 Lock을 걸지 않으나, 서브 쿼리와 SELECT FOR UPDATE구문에는 공유 Lcok을 겁니다.
명시적으로 공유 Lock을 걸기 위해서는 FOR SHARE 문법을 사용 할 수 있습니다.

select (열) from (테이블) for share

 

- 배타 락 (Exclusive Lock, X-Lock)

쓰기 Lock (Write Lock)이라고도 합니다.
데이터에 대해 배타 Lock을 획득한 트랜잭션은 읽기, 쓰기 모두 실행 할 수 있습니다.
다른 트랜잭션은 배타 Lock이 걸린 데이터에 대해 읽기쓰기작업 모두 수행 할 수 없습니다.
즉 베타 Lock이 걸려있다면 다른 트랜잭션은 공유 Lock, 배타 Lock 어느것도 획득 할 수 없습니다.

명시적으로 베타 Lock을 걸기 위해서는 FOR UPDATE구문을 사용할 수 있습니다.

select (열) from (테이블) for update

 

- Intention Lock (Intention Lock)

InnoDB에서는 multiple granularity lock(MGL)을 지원합니다. (다중잠금)
즉 Row Lock과 Table Lock의 공존이 가능합니다.

Intention Lock은 2종류가 있으며 테이블에 Lock을 거는 '테이블 Lock'입니다.
- Intention Shared Lock(IS-Lock)
- Intention Exclusive Lock(IX-Lock)

SELECT FOR SHARE 쿼리가 실행되면 테이블에 IS-Lock을 먼저 설정 후 Row에 S-Lock을 설정합니다.
SELECT FOR UPDATE, INSERT, DELETE 쿼리가 실행되면 테이블에 IX-Lock먼저 설정 후 Row에 X-Lock이 설정됩니다.
즉 Intention Lock은 Row에 Lock을 건다는 '의도'를 알려주는 테이블Lock입니다.

Intetsion Lock(IS-Lock, IX-Lock)은 여러 트랜잭션에서 발생하는 새로운 IS,IX Lock의 동시 접근이 허용됩니다.
하지만 IS,IX-Lock 이후 발생하는 X-Lock, S-Lock을 걸게되는 시점 즉 Row Lock시점에 다른 트랜잭션의 동시접근이 막히게 됩니다.

왜 Intension Lock이 필요할까??

예를들어 트랙잭션A가 특정Row들에 Update쿼리를 시작하고 Row들에 X-Lock을 걸려있는 상황일때
트랜잭션B가 테이블스키마 변경과 같이 테이블 자체에 X-Lock을 설정하는 쿼리를 실행시킨다면
Row 변경 중 테이블 스키마가 변경되면 안되기에 트랜잭션A가 끝나기 전까지 트랜잭션 B는 대기해야합니다.

트랜잭션B는 테이블의 전체 Row데이터를 확인하며 Row-Lock이 걸려있는지 확인해야하지만
트랜잭션A가 테이블에 미리 설정해둔 IS-Lock이 있기에 트랜잭션B에서 테이블에 S-Lock이 존재한다는걸 Row-Level가 아닌 Table-Level 에서 확인 할 수 있기에
트랜잭션B가 굳이 Row를 확인하지 않을 수 있어 성능상의 이점이 존재합니다.

추가로 Lock TABLES, ALTER TABLE, DROP TABLE 같은 쿼리가실행되면 IS,IX Lock 모두 Block하는 별도의 테이블 Lock(Sch-M 락 등)이 설정되며 IS, IX Lock을 얻으려는 트랜잭션은 대기상태가 됩니다.

 

락 종류간 호환 여부 표 (갈등 = 충돌)

InnoDB의 Lock 

- 레코드 Lock

레코드를 잠그는 Lock입니다.

performance_schema.data_locks 테이블에서는 REC_NOT_GAP 으로 표시됩니다.

만약 인덱스가 하나도 없는 테이블이라면 내부적으로 자동 생성된 클러스터링 인덱스(PK)를 통해 잠금을 설정하며
모든 테이블의 레코드에 락을 걸고, 풀스캔하며 적절한 레코드를 찾아 Lock을 걸어주어야 하기에 조회 뿐만 아니라 동시성 성능에 큰 영향을 미치게 됩니다.

 

- 갭 Lock (사진)

인덱스 레코드가 아닌 '레코드와 인접한 레코드 사이의 간격'만을 잠그는 Lock입니다.

performance_schema.data_locks 테이블에서는 GAP 으로 표시됩니다.
갭락을 사용함으로서 레코드와 레코드 사이에 새로운 레코드가 생성(insert)되는것을 제어하며 펜텀리드를 방지합니다.

주로 넥스트 키 Lock의 일부로서 사용됩니다. 

 

데이터로 갭락을 확인해보겠습니다.

직원 테이블에 급여에 관한 인덱스 idx_second_name을 만들고

세션1에서 FOR UPDATE구문을 사용해 2개의 레코드(조건 : 급여가 52000 ~ 55000)에 X-Lock을 걸은 후

 

이후 세션2에서 세션1에서 SELECT 범위 내에 값을 추가하는 INSERT문(급여 : 55000)을 실행하고 Lock을 확인해보면 아래와 같이 INSERT가 실패하는걸 확인 할 수 있습니다.

그 이유가 바로 GAP-Lock때문입니다.

세션1에서 Lock을 건 범위가 아닌 부분에 INSERT를 하게되면 정상적으로 수행됩니다.

 

 

- 넥스트 키 Lock

레코드 락과 갭락을 합친 형태입니다.

performance_schema.data_locks 테이블에서 X락 뒤에 GAP,REC_NOT_GAP 과 같은 키워드가 없다면 넥스트 키 Lock이라고 생각하시면 됩니다. 

SELECT * FROM table WHERE pk > 99;

출처 : https://stackoverflow.com/questions/74965677/next-key-lock-explication-primary-key-for-range

위와 같은 쿼리가 실행되었을때 RDB는 클러스터링 인덱스(pk)를 스캔하면서 

pk가 99보다 큰 최초의 레코드 101을 찾아냅니다.

101의 앞뒤 레코드 97, 101 사이에 갭 Lock을 설정하고

이후 pk > 99인 모든 레코드에 갭에 Lock 설정 후, 각 레코드 데이터에 대해서도 레코드 Lock을 설정합니다.

InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버와 동일한 결과를 만들도록 보장해줍니다.
그런데 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리는 일이 자주 발생 할 수 있어 바이너리 로그 포맷을 ROW형태로 바꾸어서 넥스트 키 락과 갭락을 줄이는것이 좋다고 합니다.

 

- 자동 증가 Lock (Auto Increment Lock)

MySQL은 자동 증가하는 숫자값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공하며, 이는 주로 FK에 사용됩니다. 
AUTO_INCREMENT 컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 
내부적으로 테이블 수준의 잠금인 자동 증가 락(Auto Increment Lock)을 사용합니다. (짧은 순간만 걸렸다가 즉시 해제)

INSERT와 REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 사용되며,
트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간에 락이 걸립니다.
자동 증가 락은 테이블에 1개만 존재하기 때문에, 한 쿼리에서 락을 획득하여 채번중이라면 다음 쿼리는 대기합니다.
자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않으며 트랜잭션과도 무관합니다.

자동 증가값 채번에 성공 후, 이후 쿼리에서 실패하여 트랜잭션이 롤백되어도 자동 증가값은 복구되지 않고 그대로 남습니다.