[MySQL]MySQL 벼락치기(5) - 갭락(Gap Lock)과 넥스트 키 락(Next-Key Lock)

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

넥스트 키 락(갭 락)에 대해서 조금더 알아보자.

이전 포스팅(http://idea-sketch.tistory.com/45)을 준비하면서 잘 이해가 안갔던 부분이 넥스트 키 락(특히 갭 락)인데 다른 포스팅들을 봐도 다들 똑같은 글만 있을뿐 자세한 설명에 대해서 찾기가 어려웠다... 그래서 직접 테스트를 해보면서 어떻게 넥스트 키 락(갭 락)이 동작하는지에 대해 조금더 확인해 보았다.
여기서부터 소개하는 내용은 정확하지 않을수 있다. 

Step1. 우선은 테스트를 위해서 격리수준을 확인하고 AutoCommit을 끈다.

SHOW VARIABLES LIKE 'tx_isolation';

SHOW VARIABLES LIKE 'autocommit';
SET AUTOCOMMIT=false;

Step2. 테스트를 위한 테이블을 하나 생성한다. 

CREATE TABLE zipcode(
	id int auto_increment,
	code nvarchar(255), -- 우편번호
	code_serial_num int, -- 우편일련번호
	city_kr nvarchar(255), -- 시도
	city_en nvarchar(255), -- 시도영문
	district_kr nvarchar(255), -- 시군구
	district_en nvarchar(255), -- 시군구영문
	town_kr nvarchar(255), -- 읍면
	town_en nvarchar(255), -- 읍면영문
	road_code nvarchar(255), -- 도로명코드
	road_kr nvarchar(255), -- 도로명
	road_en nvarchar(255), -- 도로명영문
	is_cellar tinyint(1), -- 지하여부
	primary key(id)
);

Step3. 생성된 테이블은 우편번호에 대한 정보를 저장하는 테이블로 테스트를 위해서는 생성된 테이블에 데이터를 채운다!
우편번호에 대한 정보는 여기서 다운받는다.(http://www.epost.go.kr/search/zipcode/newAddressDown.jsp)
다운받은 데이터에서 불필요한 정보들은 제거한뒤 csv 파일로 포맷을 변경하고 다음과 같은 명령어로 데이터를 테이블에 INSERT 한다. 

LOAD DATA LOCAL INFILE 'C:\\Users\\XXX\\Documents\\dumps\\Dump20170413\\20150710_seoul_utf8.csv' INTO TABLE zipcode FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (`code`,code_serial_num,city_kr,city_en,district_kr,district_en,town_kr,town_en,road_code,road_kr,road_en,is_cellar);
LOAD DATA LOCAL INFILE 'C:\\Users\\XXX\\Documents\\dumps\\Dump20170413\\20150710_gyeonggi_utf8.csv' INTO TABLE zipcode FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (`code`,code_serial_num,city_kr,city_en,district_kr,district_en,town_kr,town_en,road_code,road_kr,road_en,is_cellar);

서울과 경기도 정보를 넣으니까 대략 150만건 정도의 데이터가 들어갔다.

Step4. 넥스트 키 락은 보조인덱스를 사용해서 변경하는 작업에서 발생하니까 보조인덱스를 만들자.

ALTER TABLE zipcode ADD INDEX ix_code_serial_num_code(code_serial_num,code);

이제 테스트를 위한 기본적인 환경 구성은 다 되었다. 
이제부터는 넥스트 키락을 발생시키고 이를 통해서 넥스트 키락(갭락)이 어느범위까지 어떻게 락을 거는지 확인해보자. 
우선은 다음과 같은 명령어로 트랜잭션을 실행후 넥스트 키 락을 발생 시키는 쿼리를 실행시키자.

위와 같은 명령어가 실행될 경우 ix_code_serial_num_code 라는 인덱스에서 code_serial_num 이 103 인 인덱스들은 넥스트 키 락이 걸리게 된다.  이를 확인하기 위해서 다른 Session 에서 다음과 같이 code_serial_num 이 103인 데이터를 INSERT 시도해보자. 

다음과 같이 INSERT 를 시도하다가 넥스트 키 락이 점유하고 있는 쓰기 잠금을 기다리다가 타임아웃이 발생한다. 이를 확인하기 위해서 쓰기잠금을 기다리는 동안에 아래의 명렁어를 사용해보자. 

SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

명령어를 실행하면 다음과 같이 MySQL 에서 LOCK을 대기하고 있는 상황을 알 수 있다. 

명령어의 결과를 보면 38번 스레드의 8903번 트랜잭션이 41번 스레드의 8902 트랜잭션에 의해서 대기하고 있다는 사실을 알 수 있다.
그리고 이와 같은 상황일때 다음 명령어를 실행해보면 해당 대기상태가 넥스트 키 락에 의한것이라는걸 알 수 있다. 

mysql > SHOW ENGINE INNODB STATUS;

ix_code_serial_num_code 인덱스의 page no 16960에서 X locks gap 이 실행된것을 통해 넥스트 키 락이 실행됐음을 예상해 볼 수 있다.
지금의 상황을 그림으로 그려보면 아래와 같다. 

이 상태에서 두개의 Row 사이에 데이터를 추가하려고 시도하면서 락을 대기하게 된다.
그렇다면 두개의 데이터사이가 아닌 위나 아래에 데이터를 추가하려고 하면 어떻게 될까? 

예상과는 다르게 두개의 데이터 사이가 아닌 위나 아래에(102,111) 데이터를 넣으려고 해도 동일하게 넥스트 키락에 의한 대기가 일어난다.
갭락은 사실 관련 데이터들 뿐만 아니라 바로 직전, 직후의 Row 사이에도 락을 걸게된다. 그래야지만 Phantom Rows 현상을 제대로 막을 수 있다. 사실 갭락에 의한 잠김의 범위는 아래와 같다. 

이때문에 잠금이랑 아무 상관없을것으로 예상했던 code_serial_num 이 104~110 인 데이터를 INSERT 시도할경우 넥스트 키락에 의한 잠금대기 상태가 발생하게 된다! 

물론 잠김범위 외에 데이터를 넣는 작업은 아무 문제 없이 동작한다. 
예를들어 code_serial_num는 102 이면서  AND code는 445775 이하의 값을 넣으려고 하면 정상적으로 동작한다.

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~