Real My SQL 8.0 읽고 공부하기 - ⑤-③ InnoDB 스토리지 엔진 잠금

1) InnoDB 스토리지 엔진의 잠금

 

InnoDB스토리지 엔진은 레코드 기반의 잠금을 제공한다.

잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드락이 페이지 락 혹은 테이블락으로 레벨업(락 에스컬레이션)

는 존재하지 않는다.

 

InnoDB엔진에서는 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.

 

1. 레코드락

 

레코드 자체만을 잠그는것을 레코드 락이라고 하며, InnoDB엔진은 레코드 자체가 아니라 인덱스 레코드를 잠근다.

만약 인덱스가 존재하지 않더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

 

보조 인덱스를 이용한 변경 작업은 넥스트 키 락 혹은 갭 락을 사용하지만 프라이머리 키 혹은 유니크 인덱스에 의한

변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에만 락을 건다.

 

 

2. 갭 락

 

레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이 간격에

새로운 레코드가 생성되는 것을 제어 한다. 주로 넥스트 키락의 일부로 사용된다.

 

 

3. 넥스트 키 락

 

레코드 락과 갭 락을 합쳐놓은 형태의 잠금을 넥스트 키 락이라 한다.

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 하는데

innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화 되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로

잠금이 걸린다. 이는 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될때 소스 서버와 결과가 동일하게

보장하는것이 목적이다.

 

넥스트 키락 과 갭락으로 인해 데드락이 자주 발생할 수 있으므로 ROW형태의 로그 포맷을 바꿔주는 것이 좋다.

(8.0부터는 기본 포맷이 ROW이다.)

 

 

4. 자동 증가 락

 

MySQL에서는 자동 증가하는 숫자 값을 추출 하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다.

 

동시에 여러 레코드가 INSERT되는 경우 자동 증가된 일련번호 값을 보장하기 위해서 AUTO_INCREMENT 락이라고하는

테이블 수준의 잠금을 사용하게 된다.

 

AUTO_INCREMENT 락은 INSERT 혹은 REPLACE 쿼리 문장과 같이 새로운 레코를 저장하는 쿼리에서 필요하다.

AUTO_INCREMENT값만 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

 

해당 락을 명시적으로 획득하고 해제하는 방법은 없으며, 아주 짧은 시간 락이 걸리기 때문에 문제가 되지 않는다.

 

5.1부터는 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

 

값

0: 모든 INSERT문 자동 증가락 사용

1: 레코드 건수가 정확히 예측할 수 있을 때 래치(뮤텍스) 이용해 처리 , 단 INSERT... SELECT와 같이 서버가 건수를

예측하기 어려운 경우 자동 증가락 사용

2: 경량화된 래치(뮤텍스) 사용. 연속된 자동 증가값을 보장하지 않고 유니크한값만 보장 .STATEMENT 포맷의 바이너리 로그를

사용하는 복제에서는 소스서버와 레플리카 서버의 자동증가 값이 달라질 수 있기 때문에 주의해야한다.

(8.0부터는 2가 기본값으로 변경되었다. 만약 STATEMENT 로그 포맷을 사용한다면 1로 변경해서 사용하는 것을 권장한다.)

 

 

 

 

---

 

2) 인덱스와 잠금

 

InnoDB에서는 변경해야할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

다음과 같은 쿼리가 있다고 치면

SELECT COUNT(*) 
  FROM member
 WHERE weight = 20 -- 인덱스 O
   AND name = 'KTH' -- 인덱스 X

weight가 20인 사람들 모두 락이 걸리게 된다. 

name에는 인덱스가 없기 때문에  AND조건으로 락이 걸리지 않는 것이다.

일반적인 생각으로는 weight가 20이면서 name이 KTH인 사람이 걸릴것 같지만 MySQL의 락은 다르게 적용된다.

 

이에 따라서 MySQL에서는 UPDATE를 진행할때 조건절을 주의해서 사용해야 다른 세션에게 락에의한 방해를 하지 않는다.

 

만약, 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE작업을 하는데, 이 과정중 테이블에 있는 (예시)30여만건의

모든 레코드를 잠그게 된다.

 

 

 

---

 

3) 레코드 수준의 잠금 확인 및 해제

 

레코드 수준의 잠금은 오랜시간동안 잠겨진 상태로 남아있어도 발견하기 어렵다.

만약 다음과 같은 잠금 시나리오가 있다고 가정하면

 

-- 커넥션 1:
BEGIN;

UPDATE member
       SET name = 'TH'
 WHERE seq_member = 1001;

-- 커넥션 2:

BEGIN;

UPDATE member
       SET name = 'KT'
 WHERE seq_member = 1001;

COMMIT;



-- 커넥션 3:
BEGIN; 

UPDATE member
       SET name = 'KM'
 WHERE seq_member = 1001;

COMMIT;

커넥션 1이 COMMIT이 이루어지지 않았기 때문에 커넥션 2가 잠금을 대기하고 커넥션 3가 1,2의 잠금을 대기하게 된다.

 

SHOW PROCESSLIST; 명령어를 통해 현재 진행중인 프로세스 목록을 볼 수 있다.

SQL 프로세스 목록

현재 24번 프로세스가 커넥션 1의 상황으로 UPDATE문을 실행 후 COMMIT을 진행하지 않고 있는 상황이다.

이에 따라  25,26 프로세스가 명령을 실행중인 상태로 남겨져 있다.

 

잠금 대기 순서를 확인하려면 performance_schema의 data_locks테이블, data_locks_waits 테이블을 조인해서 순서를 확인할 수 있다.

SELECT r.trx_id AS 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 performance_schema.data_lock_waits w
  JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id
  JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id;

대기중인 스레드&쿼리, 블락킹된 요인의 스레드와 쿼리

현재 대기중인 스레드는 25,26이고 25번스레드(2번 커넥션)는 24번(1번 커넥션)에 의해 대기되고 

26번 스레드(3번 커넥션)은 24,25번의 스레드에 의해 대기된다.

 

만약 어떤 잠금을 가지고 있는지 조금 더 확인하고 싶으면

SELECT * FROM performance_schema.data_locks\G;

를 통해 확인하면 된다.

 

 

스키마의 데이터 락 정보

 

위의 정보는 TABLE락의 락 모드가 IX 잠금을 사용하고 있으며,

레코드에 대해서는 1번 레코드에 대해서 (쓰기)잠금을 가지고 있다는 것을 알 수 있으며 REC_NOT_GAP표시가 있으므로

갭이 포함되지 않은 순수 레코드에 대해서만 잠금을 가지고 있다.

 

만약 이 상황에서 해당 잠금을 가지고 있는 해당 프로세스를 죽이고 싶다면

KILL 24; 명령어를 통해 잠금 경합을 끝낼 수 있다.