스토리지 엔진 수준의 락

• MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. 스토리지 엔진 레벨의 잠금은 테이블의 데이터를 다루기 위한 락이며, MySQL 엔진 레벨의 잠금은 테이블이나 데이터베이스 등과 같은 부분을 위한 락에 해당한다. 그리고 이번에 살펴볼 잠금은 스토리지 엔진 레벨의 잠금이다.
  • 레코드 락(Record Lock)
  • 갭 락(Gap Lock)
  • 넥스트 키 락(Next Key Lock)
  • 자동 증가 락(Auto Increment Lock)

레코드 락 (Record Lock)

• 일반적으로 레코드 락은 테이블 레코드 자체를 잠그는 락이나 MySQL에서의 레코드 락은 인덱스의 레코드를 잠근다.
• MySQL에서 인덱스와 테이블은 별도의 자료구조

인덱스와 잠금

• 위에서 언급한 것 처럼 InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 왜 인덱스를 잠그는 방식으로 동작하는지는 아래의 쿼리를 예시로 들어 설명하겠다.

-- first_name만으로 구성된 ix_firstname이라는 인덱스가 준비돼 있다는 가정으로 진행 
SELECT COUNT(*) FROM employees WHERE first_name='Georgi';  
+-----------+
|       256 |
+-----------+
SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';  
+-----------+
|         1 |
+-----------+
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

• 위에서 1개의 UPDATE 쿼리를 실행할 때, first_name에는 인덱스가 존재하지만 last_name에는 인덱스가 없기 때문에 256 rows에 락이 걸린다.
• 만약 인덱스가 아예 존재하지 않는다면 풀 스캔이 일어나면서 1개의 update를 위해 모든 레코드가 잠기게 될 것이다.
  

갭 락 (Gap Lock)

• 갭 락(Gap Lock)은 레코드가 아닌 레코드와 레코드 사이의 간격을 잠금으로써 레코드의 생성, 수정 및 삭제를 제어한다.
  • 예를 들어 현재 성이 J로 시작하는 레코드가 Jo, Joe 2개가 있다고 하자. 그리고 언제든지 다른 데이터들 ex) Jang, Jeong, Jung 이 추가될 수 있다. 따라서 현재 트랜잭션에서 조회를 할 때, 다른 트랜잭션에서 임의의 데이터가 추가되지 않도록 잠그려면 아래와 같은 쿼리를 실행해야 한다. 락은 트랜잭션이 커밋 또는 롤백될 때 해제된다. 
  • 갭 락은 인덱스 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다. 이러한 구조를 그림으로 표현하면 다음과 같다. 인덱스는 정렬된 순서로 존재하므로, 현존하는 레코드의 앞 뒤에 갭 락이 걸린다.

• 즉, 갭 락은 아직 존재하지는 않지만 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금이다. 즉 데이터가 항상 Unique한 PK나 Unique Index에 의한 작업에서는 갭 락이 사용되지 않는다. 그리고 갭 락은 Panthom Read를 방지하는데 도움이 된다.

넥스트 키 락 (Next Key Lock)

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

자동 증가 락 (Auto Increment Lock)

• MySQL은 자동 증가하는 숫자값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공하고, AUTO_INCREMENT가 사용된 테이블에 동시에 여러 레코드가 insert되는 경우, 중복되지 않고 순차적으로 증가하는 일련번호 값을 가지도록 해주는데 이때 내부적으로 테이블 수준의 잠금인 자동 증가 락 (Auto Increment Lock)을 사용한다.
• 자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않고 트랜잭션과도 무관하다. 가령 자동 증가값 채번에 성공하였지만,이후의 쿼리에서 실패하여 트랜잭션이 롤백되어도 자동 증가값은 복구되지 않고 그대로 남는다. 만약 해당 값을 초기화하려면 아래의 쿼리를 사용해야 한다.

ALTER TABLE tablename AUTO_INCREMENT = 1