[MySQL] 트랜잭션, 잠금(Lock)

Fortice·2021년 10월 14일
mysql
2

MySQL

목록 보기
4/5

트랜잭션

  • 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위
  • 작업의 완정성을 보장해주는 것, 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우 원 상태로 복구해서 Partial Update를 막아주는 기능
    • 원자성을 지켜준다
  • 데이터의 정합성을 보장하기 위한 기능
    • 정합성: 데이터가 서로 모순이 없이 일관되게 일치해야 한다는 의미
    • 무결성: 데이터가 정확한 상태
  • InnoDB 엔진만 트랜잭션 기능 제공

1. 예시

### 이미 3 데이터가 들어간 테이블 fortice에 1, 2, 3을 추가
INSERT INTO fortice (id) VALUES (1),(2),(3);
  • MyISAM: INSERT가 진행되다가 3 INSERT 시 중복 키로 종료. 3 이전의 입력인 1, 2가 추가되어 1, 2, 3 만 남아있음
    • 트랜잭션을 지원하지 않기 때문에 쿼리 시 예외처리를 하나씩 해줘야함.
  • InnoDB: INSERT가 Rollback되어 기존에 있던 3 데이터만 남아있음
    • 예외처리를 따로 안해주고 트랜잭션을 걸어주면 됨
# InnoDB 트랜잭션 코드 예시
CHECK PERMISSION;
try {
    START TRANSACTION;
    INSERT QUERY;
    SELECT QUERY;
    UPDATE QUERY;
    COMMIT;
} catch(exception) {
    ROLLBACK;
}

트랜잭션 시 AUTO_INCREMENT값
트랜잭션이 롤백 되어도 AUTO_INCREMENT값은 증가된 상태로 남는다. 10개 이상의 INSERT에 NOT_NULL인 str 칼럼에 NULL을 입력해 의도적으로 롤백시켰다. 이후 정상적인 값을 INSERT 해보았다. AUTO_INCREMENT가 설정된 id값의 중간이 빈 것을 확인할 수 있다.

2. 주의사항

트랜잭션을 너무 큰 범위로 잡거나 외부 통신이 필요한 작업도 함께 있는 경우 문제가 발생할 수 있다. 프로그램이 커넥션을 가지고 있는 범위와 트랜잭션의 범위를 가능한 한 최소화하는 것이 좋다.

  • 커넥션
    • 트랜잭션을 시작하기 전에 먼저 DB 커넥션을 생성하는데 이 또한 필요한 시점에 시작해 주는 것이 좋다.
    • DB의 커넥션 개수가 제한되어있어서 불필요한 작업이 DB 커넥션을 소유하고 있지 않도록 필요한 시점에 생성해야한다.
  • 트랜잭션
    • 알맞는 논리단위로 작업 셋을 정해, 그 부분만 트랜잭션 범위를 지정하는 것이 좋다.
    • 트랜잭션 중에 문제 발생 시 Rollback을 하게 되는데, 트랜잭션의 범위가 커지면, DB와 관련 없는 문제가 발생해도 Rollback이 될 수도 있고, 같은 작업 셋이 아닌데 같이 Rollback될 수 있다.
    • 외부 서버를 이용한 메일 전송이 트랜잭션에 포함될 경우, 메일 서버가 불안정하면, DBMS도 같이 문제가 생길 수 있다. 이 경우는 반드시 트랜잭션을 분리해야한다.

락, 잠금

MySQL에서 사용하는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 미치지 않는다.

잠금을 획득(설정)한다는 것이, 창고에 들어가지 못하게 외부에서 잠그는 것이 아니라, 내가 혼자 화장실을 사용하기 위해 문을 잠그는 것처럼 내가 점유를 하기위해 내부에서 잠그는 것입니다.

  • MySQL 엔진 레벨
    • 글로벌 락
    • 테이블 락
    • 메타데이터 락
    • 네임드 락 (유저 레벨 락)
  • 스토리지 엔진 레벨(InnoDB)
    • 레코드 락
    • 갭 락
    • 넥스트 키 락
    • 자동 증가 락

0. 공유 잠금(Shared Lock) & 베타 잠금(Exclusive Lock)

멀티 스레드 환경에서 공유자원에 접근할 때 순서를 보장해주기 위해 잠금을 건다. DBMS 환경에서는 위의 두 방식으로도 표현하며, 일반적으로 Read Lock, Write Lock으로 부른다.

  • 공유 잠금
    • 데이터를 읽을 때 사용하는 Read Lock과 같은 개념
    • 공유 잠금 끼리는 충돌이 없음
      • 읽는 작업들이 동시에 자원에 접근해도 문제가 발생하지 않기 때문
    • 베타 잠금이 걸린 상태, 즉 쓰기 중이라면 읽지 못함(공유 잠금을 걸지 못함)
  • 베타 잠금
    • 데이터를 쓸 때 사용하는 Write Lock과 같은 개념
    • 공유 잠금과 베타 잠금 모두에게 충돌이 발생
      • 읽는 중에 쓰기 불가, 쓰는 중에 쓰기 불가
    • 따라서 베타 잠금이 해제될 때 까지 SELECT를 포함해서 자원에 접근이 불가능함
  • 블로킹 (Blocking)
    • 베타 잠금으로 인한 경합이 발생해 트랜잭션 작업을 진행하지 못하는 상태
    • 먼저 진행된 트랜잭션이 COMMIT 되거나 ROLLBACK되어야 진행이 가능
    • 앞선 트랜잭션의 주의사항이 이런 블로킹 현상을 막기 위함도 있음

1. 글로벌 락

FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있고, 가장 범위가 큰 잠금이다. 서버 전체에 영향을 미친다.

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태로 남는다.

백업 락
위 SQL문으로 실행된 글로벌 락은 READ LOCK을 모두 걸어버려서 모든 변경 작업을 멈추는데, InnoDB 스토리지 엔진의 트랜잭션 기능이 있기 때문에 굳이 변경을 막을 필요가 없다.이는 뒤에서 설명이 되는 Transaction ID를 이용한 읽기로 다른 수정사항에 영향을 미치지 않는 것을 의미하는 것 같다.

위와 같은 상황에서 안정적인 백업을 위해 가벼운 글로벌 락인 백업 락을 도입했다.

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

백업(Backup) vs 복제(Replica)
백업과 복제 비슷한 개념이지만 다르다. 백업은 복제를 하는 행위, 데이터를 복구하기 위한 목적에 의미가 있으며, 복제는 소스 서버의 데이터를 또 다른 서버인 레플리카 서버에 복사하여 이전시키는 의미이다.
백업은 백업을 참조해 복구하는 용도, 복제는 그 자체를 쓰는 것.

(백업 락 도입 전)
레플리카 서버는 소스 서버에서 데이터를 복제한다. 소스 서버의 안정성을 위해 레플리카 서버에서 백업을 진행하는데, 백업이 LOCK을 걸어버려 복제(일종의 Write)를 못하게 되므로 백업 완료까지 지연된다. 백업 툴들은 글로벌 락 상황에도 쓰기가 가능해 앞의 상황 없이 복제 진행 중에도 일관된 백업이 가능하다. 그런데 이 툴들은 백업 도중 스키마 변경 시 실패한다. 따라서 이런 상황을 막기 위해 백업 락을 만들었다.

2. 테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 방법, 묵시적 방법이 있다.

  • 명시적 방법
    • LOCK TABLES table_name [ READ | WRITE ]명령으로 table_name테이블을 잠글 수 있다.
    • UNLOCK TABLES 명령으로 잠금을 하제할 수 있다.
  • 묵시적 방법
    • 쿼리 실행 시 자동으로 잠금, 해제
    • MyISAM, MEMORY
      • 테이블의 데이터 변경
    • InnoDB
      • InnoDB 테이블은 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로는 테이블 락이 설정되지 않는다.
      • 스키마를 변경하는 DDL 쿼리의 경우에만 테이블 락이 설정된다.

3. 네임드 락(유저 레벨 락)

GET_LOCK()함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 자주 사용되지 않는 방식이며, DB 서버 1대에 5대의 웹 서버가 접속해서 서비스 하는 상황처럼 동기화 과정이 중요한 경우 네임드 락을 이용해 쉽게 처리 가능하다.

문자열로 잠금을 건다?
처음에는 문자열로 잠금을 건다는 것이 이해가 안됐다. 해당 문자열을 잠가서 막아버리는 것일까 생각했는데, 단순하게 문자열 자체에 의미를 두어 같은 상황을 만들지 않는 용도로 생각하게 됐다.

예를 들면 특정 유저가 동일 작업을 하는 것을 방지하기 위해 트랜잭션 시 GET_LOCK("user_name")으로 LOCK을 걸어 동일한 user는 해당 작업을 잠금시켜 버린다.

  • SELECT GET_LOCK('lock', 2);
    • lock 이라는 문자열에 대해 잠금을 획득하고, 이미 잠금을 사용 중이면 2초 동안 대기한다.
    • 성공 시 1,
  • SELECT IS_FREE_LOCK('lock');
    • lock 이라는 문자열에 대해 잠금이 설정되어 있는지 확인
    • 걸려있으면 0, 걸려있지 않으면 1
  • SELECT RELEASE_LOCK('lock');
    • lock 이라는 문자열에 대해 잠금 해제
    • 성공 시 1, 실패 시 NULL
  • 유용한 경우
    • 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션 (배치 프로그램)

4. 메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 자동으로 획득, 해제하는 잠금이다. RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 두 테이블 모두 잠금을 설정한다.

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 훨씬 뛰어난 동시성 처리를 할 수 있다. 하지만 이원화된 잠금 처리 때문에 해당 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 까다롭다.

information_schema 데이터베이스의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고, 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인 가능하다.

잠금 정보가 상당히 작은 공간으로 관리되어 레코드 락이 페이지 락, 테이블 락으로 레벨업 되는 경우(락 에스컬레이션)는 없다.

1. 레코드락

레코드 자체만을 잠그는 것이다. InnoDB 스토리지 엔진은 특이하게 레코드 자체가 아니라 인덱스의 레코드를 잠근다. 인덱스가 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 설정한다.

  • 보조 인덱스에 의한 변경 작업
    • 넥스트 키 락
    • 갭 락
  • PK 또는 유니크 인덱스에 의한 변경 작업
    • 갭에 대해서 잠그지 않고 레코드 자체에 대해서만 잠금

2. 갭 락

레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것이다. 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다. 갭 락 그 자체보다는 넥스트 키 락의 일부로 자주 사용된다.

3. 넥스트 키 락

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

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

의외로 이로 인해 데드락이나 블로킹이 자주 발생한다. 가능하다면 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

바이너리 로그 포맷

  • STATEMENT
    • 가장 오래된 포맷으로 데이터 변경에 사용되는 모든 쿼리를 쿼리대로 저장하는 방식 (5.7 기본)
    • 한번에 많은 변경의 경우 효과적 (WHERE절로 수백개 데이터 UPDATE)
  • ROW
    • 변경 작업으로 변경된 모든 ROW의 정보를 기록하는 방식 (8.0 기본)
    • 작은 변경이 여러번 일어나는 경우 효과적
  • MIXED
    • 두 방식을 혼합

4. 자동 증가 락

AUTO_INCREMENT가 설정된 칼럼이 사용된 테이블에 여러 레코드가 동시에 INSERT 되는 경우 저장되는 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져온느 순간만 락이 걸렸다가 즉시 해제된다.

innodb_autoinc_lock_mode 변수를 설정해 작동 방식을 설정한다. 0 값으로 모든 INSERT문에 사용, 1 값으로 서버가 레코드 건수 예측 가능 시 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤택스)를 이용해 처리, 2값으로 무조건 래치를 이용해 처리한다.

트랜잭션에서 보았듯이 쿼리가 실패해도 AUTO_INCREMENT값은 줄어들지 않는다.

5. 인덱스와 잠금

레코드 락에서 나오는 인덱스 잠금에 대해 알아본다.

	UPDATE t SET c='col' WHERE A AND B

위 SQL문에서 A조건을 만족하는 레코드는 250개 B를 만족하는 조건은 1개라고 가정하자. 인덱스로 이용할 수 있는 칼럼이 A조건일 때, 해당 SQL문은 인덱스 리프 노드를 통해 250개의 레코드만 탐색하면서 B조건을 만족하는 칼럼을 찾아 총 250개의 레코드가 잠기게 된다. 만약 테이블에 인덱스가 하나도 없다면, UPDATE는 풀 스캔을 하면서 모든 레코드를 잠그게 된다. 이것이 인덱스 설계가 중요한 이유다.

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

레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다. MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 확인 가능하다.

profile
Fortice
서버 공부합니다.
이전 포스트

[MySQL] InnoDB 아키텍처

다음 포스트

[MySQL] 격리 수준

1개의 댓글

comment-user-thumbnail
해도앙
2023년 2월 12일

잘 읽었어염 !!

답글 달기