MySQL InnoDB Next-Key Locking

 

InnoDB Next-Key Locking (phantom row 방지 기법)

 

MySQL(InnoDB)의 트랜잭션 모델은 Repeatable Read Isolation 레벨을 기본으로 하고 있다.

Repeatable Read는 Read Uncommitted, Read Committed 레벨과는 달리 Phatom Row가 발생하지 않는다는 장점이 있다.

 

Phantom Row의 "Phatom"은 유령이라는 뜻으로, 아래와 같이 트랜잭션 수행중 다른 트랜잭션이 생성한 Row가 갑자기 나타나는 것을 말한다.

아래 예제에서는 Phantom Row를 만들기 위해 세션의 Isolation 레벨을 Read Committed로 변경하고, 2번째 SELECT 쿼리를 수행하기 전에 다른 트랜잭션에서 새로운 값을 INSERT 처리하였다.

이렇게 Phantom Row가 등장하는 것을 방지하기 위해 Repeatable Read 레벨에서는 Next-Key Lock이라는 개념이 사용된다.

우선 InnoDB는 Record Lock과 Gap Lock, 2가지 종류의 Lock을 사용한다.

● Record Lock : 각 인덱스 Record에 설정되는 Lock

● Gap Lock : 인덱스 Record 사이의 구간에 설정되는 Lock (Unique 인덱스에서 1건 데이터 변경시 Gap Lock 설정되지 않음)

 

 

Next-Key Lock은 Record Lock과 Gap Lock이 결합된 형태로, 특정 Row를 변경하는 경우 해당 Row에는 Record Lock이, 그리고 다음 Record 까지의 구간에는 Gap Lock을 설정하는 것을 의미한다. 예를 들어 id 인덱스 칼럼에 [10, 20, 30, 40] 값이 저장되어 있을 때, id=20인 데이터를 수정하게 되면 Record 20에게는 Record Lock이, 20~30 구간에는 Gap Lock이 설정된다.

(하지만 실제 테스트를 해 보면, 변경이 발생하는 앞 뒤 구간에 모두 Gap Lock이 설정된다.)

기본적으로 Repeatable Read 레벨에서는 트랜잭션에서 처음 SELECT가 수행된 시점의 Commit 완료된 데이터를 읽게 된다.

예를들어 트랜잭션에서 UPDATE 쿼리를 수행하고 뒤에 SELECT 쿼리를 수행하면, UPDATE 와 SELECT 수행 시점 사이에 다른 트랜잭션이 commit 한 내용을 읽게 되는데, 이 때 Gap Lock으로 데이터가 변경된 구간에 새로운 Row가 INSERT 되는 것을 막지 않는다면 아래 예제와 같이 phantom row가 발생하게 된다.

예제 ) seuis398 테이블에는 id 값이 10, 20, 30, 40인 4개의 Row가 존재한다고 가정 (Gap Lock을 사용하지 않을 경우)

 

트랜잭션#1	트랜잭션#2
begin; // id = 20 인 Row가 업데이트 됨 UPDATE seuis398 SET name='Changed' WHERE id = 20; // 동일한 WHERE 조건의 SELECT 쿼리에 Phantom Row가 등장 select * from seuis398 where id =20; +------+---------+ | id | name | +------+---------+ | 20 | Changed | | 20 | phantom | +------+---------+	// 원래는 Gap Lock에 의해 Block 되어야 하는 쿼리임 insert into seuis398 values (20, 'phantom');

 

아래 예제는 id = 20인 데이터를 변경하고, 다른 트랜잭션에서 Gap Lock이 걸린 구간에 INSERT를 해 본 것이다.

Gap Lock 구간에 대한 INSERT 쿼리는 대기 상태가 되며, 트랜잭션 상태는 "lock_mode X locks gap before rec insert intention waiting" 상태로 보여진다. 결국 계속 대기하다가 Lock Wait Timeout 설정에 의해 자동으로 Rollback 된 것을 볼 수 있다.

# 트랜잭션 1

# 트랜잭션 2

# InnoDB Status에 보이는 트랜잭션 상태

 

---TRANSACTION F7D5DA8, ACTIVE 54 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 26842478, query id 18256592640 localhost mysql update INSERT INTO seuis398 VALUES (22, 'Phantom') ------- TRX HAS BEEN WAITING 54 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 725 page no 4 n bits 80 index `id` of table `mysql`.`seuis398` trx id F7 D5DA8 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000001e; asc ;; 1: len 6; hex 0000081d483f; asc H?;;     Posted by 김득은     자료 출처 : http://blog.naver.com/PostView.nhn?blogId=seuis398&logNo=70117139617&categoryNo=11