TIL

5.3 InnoDB 스토리지 엔진 잠금

• InnoDB 스토리지 엔진은 MySQL 엔진의 잠금과 별개로 스토리지 내부의 레코드 기반의 잠금 방식을 탑재하고 있다.
  • 뛰어난 동시성 처리 제공
• 어떤 트랜잭션이 어떤 잠금을 대기하고 있고, 잠금을 어떤 트랜잭션이 가지고 있는지 확인할 수도 있다.
  • MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 조회해서 확인할 수 있다.
  • 8.0버전부터는 perfornamce_schema 데이터베이스의 data_locks와 data_lock_waits 테이블로 대체되고 있다.

5.3.1 InnoDB 스토리지 엔진의 잠금

• InnoDB 엔진은 레코드 기반의 잠금을 제공 (레코드 락)
• 레코드 락뿐 아니라 레코드와 레코드 사이를 잠그는 갭 락도 존재

레코드 락

• 레코드 자체만을 잠그는 것으로 InnoDB에서는 레코드 자체가 아닌 인덱스의 레코드를 잠그는 것이 특징이다.
  • 인덱스가 하나도 없어도 자동 생성된 클러스터 인덱스에 잠금을 건다.
  • 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용
  • 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에선 갭에 대해서는 잠그지 않고 레코드에만 락을 건다.

갭 락

• 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미
• 레코드와 레코드 사이에 새로운 레코드가 생성되는 것을 제어하는 것

넥스트 키 락

• 레코드 락과 갭 락을 합친 형태의 잠금을 의미한다.

자동 증가 락

• AUTO_INCREMENT 칼럼 속성을 만족하기 위한 자동 증가 숫자 값을 채번하기 위해 InnoDB에서 제공하는 테이블 수준의 잠금을 의미한다.
  • 이 덕분에 동시에 레코드가 INSERT 되어도 중복되지 않는 순서대로의 pk를 얻을 수 있다.
  • INSERT, REPLACE에서만 걸리는 잠금
• 다른 잠금과 달리 트랜잭션과 관계 없이 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

자동 증가 락 작동 방식

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

• =0
  • MySQL 5.0과 마찬가지로 모든 INSERT 문에 자동증가 락 사용
• =1
  • 한 건이나 레코드 건 수를 정확히 예측 가능한 여러 건의 INSERT 시에는 자동 증가 락 대신 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.
  • INSERT … SELECT와 같이 건수 예측이 불가능할 땐 자동 증가 락을 사용한다.
    • 이런 경우의 대량 INSERT 시 InnoDB는 여러 개의 자동 증가 값을 한 번에 할당 받아 사용하고 남는 자동 증가 값이 있으면 폐기한다.
    • 때문에 이 경우 다음 pk 값이 연속되지 않을 수도 있다.
    • 대량 INSERT 쿼리가 오래 걸리면 다른 세션의 INSERT는 대기한다.
  • 한 번의 INSERT 문장으로 삽입되는 레코드는 연속된 증가 값을 가지게 되어 연속 모드라고도 한다.
• =2
  • 절대 자동 증가 락을 걸지 않고 래치(뮤택스)를 사용한다.
  • 한 번의 INSERT 문장으로 삽입 되더라도 연속된 증가 값을 보장하지 않아 인터리빙 모드라고도 한다.
  • 건수 예측이 불가능한 대량 삽입 쿼리에도 다른 INSERT 쿼리가 대기하지 않아도 된다.
    • 동시 처리 향상
  • 이 설정에선 자동 증가 기능은 유니크 값이 생성되는 것만 보장하고 연속성은 보장하지 않는다.
  • STATEMENT 포맷 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있다.

하지만 MySQL 8.0부터는 바이너리 로그 포맷의 기본값이 ROW 포맷이 되었고 innodb_autoinc_lock_mode의 기본값은 2가 되었다.

5.3.2 인덱스와 잠금

다시 언급하지만 InnoDB의 잠금은 레코드 자체가 아닌 인덱스에 잠금을 건다. 즉 변경할 레코드를 찾기 위해 검색한 인덱스 모두 락을 걸게 된다.

인덱스 잠금 예제

• emplyees 테이블의 first_name 컬럼에만 인덱스가 걸려 있고 last_name 컬럼에는 인덱스가 없다.

UPDATE employees SET hire_date=NOW() 
WHERE first_name='Georgi' AND last_name='Klassen';

• first_name=’Georgi’인 사람이 100명이고 last_name=’Klassen’까지 만족하는 사람은 1명이다.
• 그럼 위 sql은 1개 레코드를 수정하기 위해 100개의 인덱스에 레코드 락을 걸어야 한다.

테이블에 인덱스가 하나도 없다면 테이블 풀 스캔을 하면서 레코드를 찾기 때문에 모든 레코드를 잠그게 된다. 이런 현상이 발생하기 때문에 InnoDB에선 인덱스 설계가 매우 중요하다.

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

• employees 테이블에서 emp_no=10001인 레코드에 3개의 UPDATE 쿼리가 들어오고 아직 커밋되지 않은 상황을 살펴보자.

SHOW PROCESSLIST;

id	Time	State	Info
17	607		NULL
18	22	updating	UPDATE employees … WHERE emp_no=10001
19	21	updating	NULL

• 제일 처음 들어온 UPDATE 쿼리(17)는 커밋 되지 않았지만 명령을 실행 했고 나머지 두 쿼리는 명령을 실행하지 못하고 대기하는 것을 확인할 수 있다.
• performance_shcema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 잠금 대기 순서를 확인할 수 있다.

SELECT
	r.trx_id waiting_trx_id,
	r. trx_mysql_thread_id waiting_thread, r.trx_query walting_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
INNER JOIN information_schema.innodb_trx b
	ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
	ON r.trx_id = w.requesting_engine_transaction_id;

waiting_trx_id	waiting_thread	waiting_query	blocking_trx_id	blocking_thread	blocking_query
11990	19	UPDATE..	11989	18	UPDATE..
11990	19	UPDATE..	11984	17	NULL
11989	18	UPDATE..	11984	17	NULL

• 결과를 보면 대기 중인 스레드는 18, 19번인 것을 알 수 있다.
• 18번 스레드는 17번 스레드를 기다리고 있고, 19번은 17, 18번 스레드를 기다리고 있다.
• 17번이 커밋되면 18번이 실행되어 커밋되고 그제서야 19번이 실행될 것이다.
• 스레드가 어떤 잠금을 가지고 있는지 상세히 확인하려면 performance_schema의 data_locks 테이블의 모든 컬럼을 보면 된다.
• 이 상황에서 17번 스레드를 강제로 종료 시키려면 KILL 17; 명령어를 실행 시키면 된다.