[DB] MySQL의 락 (feat. Auto Increment Lock)

 

아래글에 이어 작성되었습니다.

 

[DB] 락(Lock)과 트랜잭션

 

 

MySQL의 락에 대하여

---

 

MySQL의 락

MySQL은 내부적으로 다양한 컴포넌트와 엔진들의 조합으로 구성됩니다. 

엔진은 크게 쿼리 처리 및 데이터베이스 관리를 위한 코어 엔진과, 데이터를 실제로 저장하고 관리하는 스토리지 엔진이 있습니다.

 

때문에 락도 MySQL 엔진 락과 MySQL 스토리지 엔진 락으로 나뉩니다.

이들은 서로 다른 레벨에서 동작하며 데이터베이스의 전반적인 동작 및 각 스토리지 엔진의 내부 동작을 관리합니다.

 

 

MySQL 엔진 락 :

MySQL 엔진의 락은 모든 스토리지 엔진에 영향을 줍니다.

 

• 글로벌 락 (Global Lock): 전체 데이터베이스를 잠그는 락, 일반적으로 백업에 사용.
• 테이블 락 (Table Lock): 특정 테이블 전체를 잠그는 락.
• 네임드 락 (Named Lock): 변수처럼 임의의 문자열을 사용하여 잠금을 설정하는 락.
• 메타 데이터 락 (Metadata Lock): 테이블의 이름, 구조를 수정하기 전에 설정되는 락. 스키마 변경이나 DDL 수행 시 사용.

 

MySQL 스토리지 엔진 락 :

다른 스토리지에 영향 없이 해당 스토리지에만 락이 걸립니다.

 

• 레코드 락 (Record Lock): 특정 레코드에 대한 락.
• 갭 락 (Gap Lock): 레코드 간의 간격에 대한 락. 다른 트랜잭션이 해당 간격에 새로운 레코드를 삽입하는 것을 방지.
• 넥스트 키 락 (Next-Key Lock): 읽은 행의 다음 키에 대한 락. 범위 검색 중 행의 변경을 방지.
• 자동 증가 락 (Auto-Increment Lock): AUTO_INCREMENT 열에 대한 값을 관리하는 락. ⭐

 

이중 자동 증가 락에 대해 더 알아볼까요?

 

 

자동 증가 락 (Auto-Increment Lock) ⭐

자동 증가 락은  다중 클라이언트가 동시에 동일한 AUTO_INCREMENT 값을 가져오지 않도록 보장합니다.

 

AUTO INCREMENT 칼럼 속성을 제공하기 위한 InnoDB 스토리지 엔진의 락입니다.

AUTO INCREMENT가 설정된 칼럼에 여러 레코드가 동시에 들어올 경우 테이블 수준의 락을 걸기게 됩니다.

 

해당 칼럼은 중복되지 않으며, 순서대로 증가하는 일련의 번호 데이터를 가지게 됩니다.

 

 

 

아래는 두 개의 INSERT 쿼리가 들어왔을 때의 시나리오입니다.

 

1. 첫 번째 INSERT 쿼리가 실행되고, MySQL은 해당 테이블 락을 설정합니다.
2. 첫 번째 쿼리가 AUTO_INCREMENT 값을 요청합니다.
3. MySQL은 이전에 할당된 값에 기초하여 새로운 AUTO_INCREMENT  값을 생성합니다.
4. 이때 다른 클라이언트가 동일한 테이블에 대해 두 번째 INSERT 쿼리를 실행합니다.
5. MySQL은 두 번째 쿼리를 처리하기 위해 테이블 락을 기다립니다.
6. 첫 번째 쿼리가 완료되고 AUTO_INCREMENT 값이 할당되면, 두 번째 쿼리에 대한 락이 해제됩니다.
7. 두 번째 쿼리가 실행되고, MySQL은 AUTO_INCREMENT 열에 대해 새로운 값을 할당하고, 해당 레코드를 삽입합니다.

이런 특성 때문에 PK에서 AUTO INCREMENT가 설정된 INT 타입, 대신 uuid를 사용합니다.
여러 INSERT가 발생할 때 락으로 인한 지연이 생기는 것보다 uuid 발급받아 직접 서버에 넣는 게 낫다는 거죠.

 

 

 

MySQL 5.1 버전 이후에서의 자동 증가 락의 변화

 

MySQL 5.1 이상부터는 `innodb_autoinc_lock_mode` 라는 파라미터를 이용해

자동 증가 락의 작동 방식을 변경할 수 있도록 변경되었습니다.

 innodb_autoinc_lock_mode = 0 :

MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용한다.

 

innodb_autoinc_lock_mode = 1 :

MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때에는 자동 증가 락대신 뮤텍스(래치)를 사용한다.
단, 쿼리를 실행하기 전 예측할 수 없을 때에는 MySQL 5.0에서와 같이 자동 증가 락을 사용한다.

 

innodb_autoinc_lock_mode = 2 :

Innodb 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 항상 경량화된 래치(뮤텍스)를 사용한다.

 

5.1 버전부터는 기본값이 1로 설정되어 었있는데요.

더 최신버전의 8.0부터는 기본값이 2로 설정되어 무조건 뮤텍스만을 사용하게 되었습니다.
어떤 방식으로 데이터를 삽입하든 락을 거는 것보다 뮤텍스가 더 효과적이었나 봐요.🤔

 

 

만약 동시에 AUTO INCREMENT에 접근한다면?

미세한 속도 차이에 의해서 동시성 이슈가 발생할 수 있습니다.
OS에서도 상호배제를 구현하기 위해 뮤텍스를 썼지만 완벽한 방법은 아니니까요.

DB에서도 이 이슈는 똑같기 때문에, 이 경우에는 충돌이 발생하고 결국 해당 트랜잭션은 롤백하게 됩니다.
하지만 데이터베이스의 무결성을 지키기 위해서 AUTO INCREMENT는 롤백되지 않습니다.

 

모종의 이유로 삽입에 실패하면 해당 값을 건너뛰고 다음 숫자를 발급해 주죠.

 

•  AUTO INCREMENT로 설정된 아이디 칼럼이 존재
• 데이터를 추가하여 아이디가 1이 존재
• 데이터를 추가하여 아이디가 2인 게 자동생성되어야 하나, 실패
• 다시 한번 데이터를 추가하여 이번에는 성공, 2는 건너뛰고 3이라는 아이디를 발급받게 된다

 

만약 AUTO INCREMENT에도 롤백이 적용된다면, 추후 다른 INSERT가 그 아이디를 발급받을 수 있으니까요.
이러면 데이터베이스의 일관성이 깨지고 무결성을 보장할 수 없는 상태가 될 겁니다.
따라서 자동 증가 락은 실패 시에도 감소하지 않게끔 되어 있습니다. ( 인덱스를 직접 수정하지 않는 한 )

 

 

---

 

 

 

감사합니다.

 

공부한 내용을 복습/기록하기 위해 작성한 글이므로 내용에 오류가 있을 수 있습니다.