잠금은 동시성을 제어하기 위한 기능으로 하나의 데이터를 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다.

• MySQL 엔진 레벨
  모든 스토리지에 영향을 미친다.

• 스토리지 엔진 레벨
  스토리지 엔진 간 상호 영향을 미치지는 않는다.

MySQL 엔진의 잠금

글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 락이 해제될 때까지 대기 상태로 남는다.

글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

글로벌 락을 거는 FLUSH TABLES WITH READ LOCK 명령은 실행과 동시에 서버에 존재하는 모든 테이블을 닫고 잠금을 거는데, 읽기 잠금을 걸기 전에 먼저 테이블을 플러시 해야 하기 때문에 실행 중인 모든 종류의 쿼리가 완료돼야 한다. 명령이 실행되기 전에 테이블이나 레코드에 쓰기 잠금을 거는 SQL이 실행되었다면 해당 테이블의 읽기 잠금을 걸기 위해 먼저 실행된 SQL과 그 트랜잭션이 완료될 때 까지 기다려야 한다.

장시간 실행되는 쿼리와 FLUSH TABLES WITH READ LOCK 명령이 최악의 케이스로 실행되면 서버의 모든 쿼리가 오랜 시간 실행되지 못하고 대기할 수 있다.

백업락

MySQL 8부터 InnoDB가 기본 스토리지 엔진으로 채택되었고, InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없기 때문에 조금 더 가벼운 글로벌 락의 필요성이 생겼고, 백업 락이 도입되었다.

1
2

LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;

특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 되지만, 일반적인 테이블의 데이터 변경은 허용된다.

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• REPAIR TABLE과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

테이블 락

테이블락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획들할 수 있다. MyISAM뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정 가능하다.

• 명시적: LOCK TABLES table_name [ READ | WRITE ] 명령

  • UNLOCK TABLES 명령으로 해제
  • 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다.

• 묵시적: 테이블에 데이터를 변경하는 쿼리를 실행하면 발생

  • MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고, 변경한 후 즉시 잠금을 해제한다.
  • InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않고, DDL의 경우에만 영향을 미친다.

네임드 락

네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 잠금 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금이다.

네임드 락은 자주 사용되지는 않지만, 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 쉽게 해결할 수 있다.

많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 데드락의 원인이 되곤 하는데, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

1
2
3
4
5
6
7
8

-- "mylock" 이라는 문자열에 대해 잠금을 획득
SELECT GET_LOCK('mylock', 2);

-- "mylock" 이라는 문자열에 대해 잠금 설정 확인
SELECT IS_FREE_LOCK('mylock');

-- "mylock" 이라는 문자열에 대해 잠금을 반납(해제)
SELECT RELEASE_LOCK('mylock');

MySQL 8 버전부터는 네임드 락을 중첩해서 사용할 수 있게 되었으며, 현재 세션에서 획득한 네임드 락을 한번에 모두 해제하는 기능도 추가되었다.

1
2
3
4
5
6
7

SELECT GET_LOCK('mylock_1', 10);
SELECT GET_LOCK('mylock_2', 10);

SELECT RELEASE_LOCK('mylock_1');
SELECT RELEASE_LOCK('mylock_2');

SELECT RELEASE_ALL_LOCKS();

메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 잠금이다. 명시적으로 획득하거나 해제할 수 없고, 테이블의 이름을 변경하는 경우 자동으로 획득한다.

READ TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다.

1

RENAME TABLE rank TO rank_backup , rank_new TO rank;

위와 같이 하나의 RENAME TABLE 명령문에 두 개의 RENAME 작업을 한번에 실행하면 실제 애플리케이션에서는 “Table not found 'rank'“같은 상황을 발생시키지 않고 적용하는 것이 가능하다.

하지만 이 문장을 나누어 실행하면 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생기며, 그 순간에 실행되는 쿼리는 오류를 발생시킨다.

1
2

RENAME TABLE rank TO rank_backup;
RENAME TABLE rank_new TO rank;

메타데이터 잠금과 트랜잭션 동시 활용

때로는 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용해야 하는 경우도 있다.

1
2
3
4
5

CREATE TABLE access_log (
  id BIGINT NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY(id)
);

위와 같이 INSERT만 실행되는 로그 테이블이 있을때, 이 테이블의 구조를 변경해야 한다면 Online DDL을 이용하여 변경할 수 도 있지만, 시간이 너무 오래 걸리는 경우 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많다. 더 큰 문제는 MySQL서버의 DDL은 단일 스레드로 작동하기 대문에 상당히 많은 시간이 소모된다.

이때는 새로운 구조의 테이블을 생성하고 먼저 최근 데이터까지는 프라이머리 키인 id 값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사하고, 나머지 데이터는 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행할 수 있다.

이때 남은 데이터를 복사 하는 시간 동안은 테이블의 잠금으로 인해 INSERT를 할 수 없게 되어 가능하면 미리 아주 최근 데이터까지 복사해 둬야 잠금 시간을 최소화하여 서비스에 미치는 영향을 줄일 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE access_log_new (
  id BIGINT NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY(id)
) KEY_BLOCK_SIZE=4;

INSERT INTO access_log_new SELECT * FROM access_log WHERE id >= 0 AND id < 10000;
INSERT INTO access_log_new SELECT * FROM access_log WHERE id >= 10000 AND id < 20000;
INSERT INTO access_log_new SELECT * FROM access_log WHERE id >= 20000 AND id < 30000;
INSERT INTO access_log_new SELECT * FROM access_log WHERE id >= 30000 AND id < 40000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SET autocommit = 0;

LOCK TABLES access_log WRITE, access_log_new WRITE;

SELECT MAX(id) as @MAX_ID FROM access_log_new;
INSERT INTO access_log_new SELECT * FROM access_log WHERE pk > @MAX_ID;
COMMIT;

RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;
UNLOCK TABLES;

DROP TABLE access_log_old;

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 이러한 레코드 기반 잠금 방식 덕뿐에 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다.

하지만 이원화된 잠금 처리로 인해 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기 까다롭고, 내용도 어셈블리 코드를 보는 것 같아서 이해하기 어려웠다.

• lock_monitor
  • innodb_lock_monitor라는 이름의 InnoDB 테이블을 생성하여 잠금 정보를 덤프하는 방법
• SHOW ENGINE INNODB STATUAS

최근 버전에서 InnoDB의 트랜잭션과 잠금, 잠금 대기중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되었다.

• MySQL 서버의 information_schema 데이터베이스
  • INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블 활용
• Performance Schema
  • InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락, 테이블 락으로 락 에스컬레이션 되는 경우는 없다.

레코드 락 뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는 갭 락도 존재한다.

레코드락

레코드 자체만 잠그는 것을 레코드 락(Record lock, Record only lock)이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다. 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

• 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

따라서 InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 이어서 설명할 넥스트 키 락(Next key lock) 또는 갭 락(Gap lock)을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

갭 락

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어한다.

• 갭 락은 그 자체보다는 넥스트 키 락의 일부로 자주 사용된다.

넥스트 키 락

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

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어 내도록 보장하는 것이 주목적이다.

넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하므로, 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다. AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

• AUTO_INCREMENT락은 INSERT, REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다.
• InnoDB의 다른 잠금과 달리 트랜잭션과 관계 없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
• AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 대문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 락을 걸면 나머지 쿼리는 락을 기다려야 한다.
  • AUTO_INCREMENT 컬럼에 명시적으로 값을 설정하더라도 자동 증가 락을 걸게 된다.

innodb_autoinc_lock_mode
MySQL 5.0 이하 버전에서는 AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다. 하지만 아주 짧은 시간동안 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않는다.

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

• innodb_autoinc_lock_mode=0
  모든 INSERT 문장이 자동 증가 락을 사용한다.

• innodb_autoinc_lock_mode=1
INSERT하는 쿼리 중에서 MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.

  • 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다.
  • 건수를 예측할 수 없을때는 이전 같이 자동 증가 락을 사용한다.
  • 한번에 할당 받은 자동 증가 값이 남아서 사용되지 못하면 폐기하므로 레코드 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다.
  • 하나의 INSERT 문장으로 INSERT 되는 레코드는 연속된 자동 증가 값을 가지게 된다.

• innodb_autoinc_lock_mode=2
  절대로 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다.

  • 설정에서는 하나의 INSERT 문장으로 INSERT 되는 레코드라 하더라도 연속된 자동 증가값을 보장하지는 않는다.
  • INSERT ... SELECT와 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다.
  • 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장한다. 따라서 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있어 주의해야한다.

MySQL 5.7 까지는 기본값이 1이었으나, 8버전 바이너리 로그 포맷이 STATEMENT가 아니라 ROW로 변경되었기 때문에 2로 바뀌었다. ROW 포맷이 아니라 STATEMENT 포맷의 바이너리 로그를 사용한다면 1로 변경해서 사용해야 한다.

자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유는 AUTO_INCREMENT 잠금을 최소화 하기 위해서다. INSERT 쿼리가 실패했더라도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.

래치(Latch)란?
DBMS에서 여러 스레드 혹은 프로세스가 동시에 접근할 수 있는 데이터에 대한 접근 제어 기술 중 하나이다.
래치는 뮤텍스(Mutex)와 유사한 개념이지만, 뮤텍스는 오직 한 스레드 혹은 프로세스만이 해당 자원에 접근할 수 있도록 하는 동기화 기술이고, 래치는 여러 개의 스레드 혹은 프로세스가 읽기만 가능하고 쓰기는 하나의 스레드 혹은 프로세스만 가능하도록 하는 동기화 기술이다.
래치는 읽기 래치와 쓰기 래치로 구분되며, 읽기 래치는 여러 스레드 혹은 프로세스가 동시에 해당 데이터를 읽을 수 있게 하고, 쓰기 래치는 오직 하나의 스레드 혹은 프로세스만 해당 데이터를 변경할 수 있게 한다.
래치는 주로 인덱스 구조나 버퍼풀 등에서 사용되며, 동시성을 높이기 위해 적극적으로 활용된다. 그러나 래치를 과도하게 사용하거나 사용 방법이 부적절할 경우 데드락(deadlock) 등의 문제가 발생할 수 있으므로 주의해서 사용해야 한다.

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

--// KEY ix_firstname (first_name)

SELECT COUNT(*) 
FROM employees 
WHERE first_name='Georgi'
;
/*
+---+
|253|
+---+
*/

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'
;

UPDATE 쿼리가 실행되면 1건의 레코드가 업데이트 되지만, UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name뿐이며, last_name 컬럼은 인덱스가 없기 때문에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠긴다.

UPDATE 문장을 위해 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생한다.

만약 employees 테이블에 인덱스가 하나도 없다면, 테이블을 풀스캔 하면서 UPDATE를 수행하기 때문에 테이블에 있는 모든 레코드를 잠그게 된다.

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

InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡하다.

테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있으나, 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.

예전 버전의 MySQL 서버에서는 레코드 잠금에 대한 메타 정보를 제공하지 않았기 때문에 어려웠지만, 5.1 버전부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행하면 잠금과 잠금 대기 상태를 바로 확인할 수 있다.

• MySQL 5.1 +
  information_schema라는 DB에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 통해 확인
• MySQL 8.0 +
  information_schema의 정보들은 조금씩 제거되고 있다.
  • performance_schema의 data_locks, data_lock_waits로 확인