데이터를 영속적으로 저장하고 있는 시스템인 DataBase는 같은 자원에 대해서 동시에 접근하는 경우가 생길 수 밖에 없다. 이럴 경우 데이터가 오염 되지 않도록 데이터의 일관성과 무결성을 유지해야할 필요가 있다. DBMS(DataBase Management System)가 동시성을 제어하기 위해 사용하는 공통적인 방법이 Lock이다.
1. Lock
1-1. Lock 이란
Lock이란 트랜잭션 처리의 순차성을 보장하기 위한 방법이다.
DBMS마다 Lock을 구현하는 방식과 세부적인 방법이 다르다. 따라서 DBMS를 효과적으로 이용하기 위해서는 해당 DB의 Lock에 대한 이해가 요구된다.
대표적인 DBMS인 MySQL에서 사용되는 Lock에는 크게 MySQL 엔진 레벨의 잠금과 스토리지 엔진 레벨의 잠금으로 나눌 수 있다.
MySQL에서 사용되는 락에 대해 알아보자.
1-2. MySQL 엔진의 잠금
MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미친다.
MySQL 엔진에서의 잠금은 크게 글로벌 락, 테이블 락, 네임드 락, 메타 데이터 락으로 나눌 수 있다.
1. 글로벌 락
- MySQL에서 제공하는 잠금 가운데 가장 범위가 크다(MySQL 서버 전체).
FLUSH TABLES WITH READ LOCK;명령으로 획득할 수 있다.- 한 세션에서 글로벌 락을 획득하면 다른 세선에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.
- MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야
할 때 글로벌 락을 사용해야 한다. - MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 좀 더 가벼운 글로벌 락인 백업 락이 도입되었다.(
LOCK INSTANCE FOR BACKUP;)
백업 락
- 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제 불가
- REPAIR TABLE과 OPTIMIZE TABLE 명령 불가
- 사용자 관리 및 비밀번호 변경 불가
- 일반적인 테이블의 데이터 변경 허용
2. 테이블 락
- 개별 테이블 단위로 설정되는 잠금
- 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
- 명시적
- 명시적으로 획득한 락은
UNLOCK TABLES명령으로 잠금 해제할 수 있다. - 명시적으로 테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문에 애플리케이션에서 사용할 필요가 거의 없다.
- 명시적으로 획득한 락은
- 묵시적
- MyISAM이나 MEMORY 테이블에 데이터를 변경하는 퀴리를 실행하면 발생
- MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제
- InnoDB 테이블의 경우 스토리티 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 DML 쿼리에서는 무시되고 DDL의 경우에만 영향을 미친다.
- 명시적
READ
- 테이블에 대한 읽기 락 획득- 다른 세션이 동시에 같은 테이블에 대한 읽기 락 획득 가능
- 테이블에 대한 내용은 읽을 수 있지만, 수정은 불가능
- 동시에 다른 세션이 해당 테이블에 대해서 쓰기 락 획득 불가능
WRITE
- 테이블에 대한 쓰기 락 획득
- 다른 세션이 동시에 같은 테이블에 대한 읽기 락, 쓰기 락 획득 불가능
- 쓰기 락을 획득한 세션은 테이블의 내용을 읽고 수정할 수 있음
3. 네임드 락
- 네임드 락은
GET_LCOK()함수를 통해 임의의 문자열에 대해 잠금을 설정할 수 있다. - 사용자가 지정한 문자열에 대해 락을 획득하고 반납하는 형식이다.
- 여러 클라이언트가 상호 동기화가 필요할 때 사용하게 된다.
- 같은 테이블이나 레코드에 대해서도 서로 다른 작업에 대해서 각자의 락을 걸 수 있기 때문에 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용될 수 있다.
4. 메타데이터 락
- 테이블이나 뷰 등의 DB 객체의 이름이나 구조를 변경하는 경우에 사용하는 잠금이다.
- 명시적으로 획득하거나 해제할 수가 없고 테이블의 이름을 변경하는 등과 같은 경우 자동으로 획득한다.
1-3. InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하여 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있다.
InnoDB 스토리지 엔진의 락은 공유/배타 락, 레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락이 존재한다.
1. 공유 락(S Lock)
- Row-level Lock
- read 에 대한 Lock
- 데이터를 읽을 때 사용되는 Lock
- S Lock 을 사용하는 쿼리 끼리는 같은 row 에 접근 가능하다
- S Lock이 설정된 Row에 Exclusive Lock을 사용할 수는 없다.
SELECT ... FOR SHARE등 일부SELECT쿼리는 read 작업을 수행할 때 InnoDB가 각 row에 S Lock을 건다.
2. 배타 락(X Lock)
- Row-level Lock
- write 에 대한 Lock
- 데이터를 변경하고자 할 때 사용되는 Lock
- 트랜잭션이 완료 될 때까지 유지되며, Lock이 해제될 때까지 다른 트랜잭션은 S Lock을 걸거나 X Lock을 걸 수 없다.
SELECT ... FOR UPDATE나 UPDATE, DELETE등의 수정 쿼리를 날릴 때 각 row에 걸리는 Lock이다.
3. 레코드 락(Record Lock)
- 레코드에 대해서 잠금을 거는 Lock
- InnoDB의 경우 레코드 자체를 잠그는 것보다는, 인덱스의 레코드를 잠근다.
- 개별 인덱스 레코드에 S-Lock 혹은 X-Lock 을 설정한다.
# id = 1인 레코드에 대해 S-Lock
SELECT id FROM tb_1 WHERE id = 1 LOCK IN SHARE MODE;
# id = 1인 레코드에 대해 X-Lock
SELECT id FROM tb_1 WHERE id = 1 FOR UPDATE;인덱스의 레코드에 대한 락을 거는 이유
- InnoDB는 B+ Tree 인덱스를 통해 데이터를 저장하거나 검색하는데, 이때 인덱스의 레코드를 탐색하여 특정 레코드를 찾아내기 때문에 인덱스의 레코드에 대한 접근이 필요하다.
- 인덱스에 대해서만 락을 걸기 때문에 다른 트랜잭션들이 접근하는 경우에도 충돌을 최소화하여 성능 자체에도 도움을 줄 수 있다.
4. 갭 락(Gap Lock)
- 갭 락의 경우 레코드가 아닌 레코드와 인접한 다른 레코드 사이의 간격을 잠그는 것을 의미한다.
- 조회 쿼리를 두 번 실행했을 때 다른 트랜잭션에서 수정이 발생하더라도 같은 결과가 리턴되도록 보장하여 Phantom Read를 방지하는 효과를 가진다.
SELECT * FROM member WHERE last_name LIKE "J%" FOR UPDATE; // X-Lock
SELECT * FROM member WHERE last_name LIKE "J%" LOCK IN SHARE MODE; // S-Lock
5. 넥스트 키 락(Next Key Lock)
- 넥스트 키 락은 레코드 락과 갭 락이 합쳐진 형태로 갭 락은 보통 넥스트 키 락의 일부로 사용된다.
- 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다.
- 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하여 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
binlog.format 종류 및 차이
- Statement Format : 가장 오래된 Format으로 데이터 변경에서 사용되는 모든 쿼리를 쿼리대로 저장하는 방식을 말함(5.7까지 기본 Format)
- Row Format : 변경 작업으로 변경된 모든 Row의 정보를 기록하는 방식
- Mixed Format : Statement 방식과 ROW 방식을 혼합한 방식으로 기본은 Statement 방식이고, 몇몇의 경우에는 ROW방식으로 동작하는 방식
6. 자동 증가 락(Auto Increment Lock)
- MySQL은 자동 증가하는 숫자값을 채번하기 위해
AUTO_INCREMENT라는 컬럼 속성을 제공한다. AUTO_INCREMENT컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 테이블 수준의 잠금인 자동 증가 락(Auto Increment Lock)을 사용한다.INSERT나REPLACE문장에서AUTO_INCREMENT값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.- 테이블에 1개만 존재하기 때문에, 한 쿼리에서 락을 획득하여 채번중이라면 다음 쿼리는 락을 대기해야 한다.
참고
Real MySQL 8.0
개발일지