[MySQL] MySQL 락

DB에 대해 공부하던 중, 문득 이때까지 프로젝트를 진행하면서 단 한번도 DB에서 일어나는 동시성 문제에 대해 고려해본적이 없다는 생각이 들었다. 그래서 이번에는 DB 동시성 문제 해결 과정에 대해 이야기할 것이며 먼저 이번 글에서는 자주 헷갈리는 부분인 트랜잭션 격리 수준과 MySQL 락에 관해 간단하게 정리해보고자 한다.

 

이 글은 대부분 저의 기억을 상기하여 짧게 쓴 글이기 때문에 자세한 정보를 원하신다면 MySQL 도큐먼트를 추천합니다.

 

 

---

 

 

MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. 스토리지 엔진 레벨의 잠금은 테이블의 데이터를 다루기 위한 락이며, MySQL 엔진 레벨의 잠금은 테이블이나 데이터베이스 등을 위한 락에 해당한다.

 

 

☑️ InnoDB 엔진 락

 

💠 S-lock (Shared-Lock)과 X-lock (Exclusive-Lock)

S-lock은 읽기락, 공유락이라고도 하며 대상 레코드에 대해서 다른 트랜잭션에서 읽는 것은 허용하지만 DML 작업은 불가능하다. 그렇기 때문에 서로 다른 S-lock이 걸린 레코드에 대한 수정을 한다면 데드락이 발생한다.

 

X-lock은 쓰기락, 베타락이라고도 하며 다른 트랜잭션에서 X-lock, S-lock 모두 걸 수 없다. 이 때 유의해야할 점은 Select For Update, Update, Delete를 못하는 것이지 순수한 Select를 못하는게 아니라는 것이다.

 

 

💠 레코드락

트랜잭션이 DML를 진행할 때 대상 레코드에 거는 락으로 InnoDB 엔진은 데이터가 아닌 레코드의 인덱스에 락을 건다.

이 때 인덱스에 락을 거는 것에 대해 생각해볼 필요가 있다.

 

'O' 성을 가진 Member는 100명이고 이름이 'Juyeop'인 사람은 1명이며 인덱스는 last_name에해서만 걸려 있다고 할 때, 아래와 같은 쿼리를 실행한다면 하나의 레코드를 수정하기 위해 100개의 인덱스 레코드에 락을 걸어야만 한다. 그렇기 떄문에 인덱스를 설계하는 것은 동시성에 큰 영향을 준다.

UPDATE member SET age = 26 WHERE last_name LIKE 'O%' AND first_name = 'Juyeop';

 

 

💠 갭 락

MySQL과 다른 RDBMS의 가장 큰 차이점이며 레코드와 레코드 사이의 간격을 잠그는 것을 말한다. 다시 말해 인덱스 테이블의 지정된 범위를 대상으로 하는 잠금이다. 이는 대체로 단독으로 사용되지 않고 레코드락과 함께 넥스트 키 락으로 사용된다. 또한 팬텀리드가 발생하는 경우 중 하나인 조회 사이에 다른 트랜잭션에서의 DML 작업이 있을 때, 일관된 읽기가 가능하도록 해준다.

 

 

💠 넥스트키 락

레코드 락과 갭락을 합친 잠금이다.

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

 

 

💠 자동 증가 락

MySQL은 자동 증가하는 숫자값을 위해 AUTO_INCREMENT라는 컬럼을 제공하며, 이는 주로 대체키에 사용된다. 이때  AUTO_INCREMENT 컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 테이블 수준의 잠금인 자동 증가 락을 사용한다. 해당 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간에 락이 걸린다. 자동 증가 락은 테이블에 1개만 존재하기 때문에, 한 쿼리에서 락을 획득하여 채번중이라면 다음 쿼리는 락을 대기해야 한다. 하지만 아주 짧은 순간만 걸렸다가 즉시 해제되므로 대부분의 경우 문제가 되지 않는다.자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않는다. 그리고 앞서 설명하였듯 이는 트랜잭션과도 무관하다. 따라서 자동 증가값 채번에 성공하였지만, 이후의 쿼리에서 실패하여 트랜잭션이 롤백되어도 자동 증가값은 복구되지 않고 그대로 남는다.

 

 

☑️ MySQL엔진 락

💠테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적으로는 아래와 같은 명령으로 특정 테이블의 락을 획득하고 반납할 수 있다.

LOCK TABLES table_name [ READ 또는 WRITE]
UNLOCK TABLES

 

💠네임드 락

테이블이나 레코드와 같은 데이터베이스 객체에 거는 락이 아닌 사용자가 지정한 임의의 문자열 거는 락이다. 이는 여러 개의 WAS 서버에서 하나의 DB 서버에 접근할 때 상호 동기화하는데에 주로 쓰인다.

 

💠 메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경 할 때 자동으로 얻어지는 락이다. 이는 명시적으로 얻을 수 없고 테이블의 이름을 변경할 때 자동적으로 얻어지는 락이다.

 

 

 

 

☑️ 트랜잭션 격리 수준

💠 SERIALIZABLE

SELECT에도 대상 레코드에 넥스트 키 락을 공유 락으로 걸어 모든 트랜잭션을 순서대로 처리하는 격리 수준이다. 동시성에 대한 문제가 없지만 성능이 좋지 않다는 단점이 있다.

 

 

💠 REPEATABLE READ

변경된 레코드에 대해서 언두 영역에 기존 데이터를 트랜잭션 넘버와 함께 저장한다. 이를 통해 일관된 읽기(Non-Lockong Read)를 지원하며  트랜잭션 롤백 시 데이터 복구를 가능하게 한다. 또한 InnoDB에서는 갭락을 지원하기 때문에 SELECT<User A> → INSERT<User B>  → SELECT FOR UPDATE<User A>를 제외한 대부분의 경우 팬텀 리드가 발생하지 않는다.

 

 

💠 READ COMMITED

커밋된 레코드만 조회 가능하며 팬텀 리드와 더불어 Non-Repeatable Read 문제가 발생한다.

 

 

💠 READ UNCOMMITED

커밋하지 않은 레코드도 조회가 가능하며 팬텀리드, Non-Repeatable Read 그리고 Dirty-Read 문제가 발생한다. Dirty-Read는 한 트랜잭션에서 커밋되지 않은 데이터가 다른 트랜잭션에서 읽히는 것을 의미한다.