05. 트랜잭션과 잠금

이번 장은 동시성에 영향을 미치는 영역에 대해서 살펴보자.

• 트랜잭션 (Transaction)

• 잠금 (Lock)

• 격리 수준 (Isolation Level)

트랜잭션 (Transaction)

트랜잭션은 논리적인 작업이 모두 완벽하게 처리하거나, 그렇지 못할 경우 원 상태로 복구해서 작업의 일부만 적용되는 Patial Update 현상이 발생하지 않게 만들어주는 것이다.

간단하게 말하자면, 작업의 완전성을 보장해 주는 것이다.

InnoDB 엔진의 경우 트랜잭션을 지원하지만, MyISAM은 그렇지 않다. 이러한 이유로 부분 업데이트(partial update) 현상으로 인해 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 야기한다.

주의사항

트랜잭션 또한 꼭 필요한 최소의 코드에만 적용하여 트랜잭션 범위를 최소화 하자.

중간에 메일 전송이나 FTP 파일 전송 등 외부 시스템과 연결되는 부분에 대해서는 하나의 작업 단위로 묶게 될 경우 웹 서버 뿐만 아니라 DBMS 서버까지 영향이 미치게 된다.

// 너무 광범위하게 트랜잭션 범위를 설정한 경우

1) 처리 시작
 => 데이터베이스 커넥션 생성
 => 트랜잭션 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
 <= 트랜잭션 종료 (COMMIT)
 <= 데이터베이스 커넥션 반납
10) 처리 완료

적절히 묶여야할 트랜잭션 단위를 나누고, 외부 시스템과는 분리하는 방식으로 개선하는 것이 중요하다.

// 트랜잭션 범위를 나눈 상황

1) 처리 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 발생 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
 => 데이터베이스 커넥션 생성 (또는 커넥션 풀에서 가져오기)
 => 트랜잭션 시작 (1)
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
 <= 트랜잭션 종료 (COMMIT)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
 => 트랜잭션 시작 (2)
9) 알림 메일 발송 이력을 DBMS에 저장
 <= 트랜잭션 종료 (COMMIT)
 <= 데이터베이스 커넥션 종료 (또는 커넥션 풀에 반납)
10) 처리 완료

위의 내용이 완벽하다고 말할 수는 없지만, 저런 방식으로 트랜잭션의 범위를 설정하는게 중요하다는 것을 보여준다.

물론, 잘못 트랜잭션 범위를 설정할 경우 원자적으로 처리되어야 할 작업들이 쪼개셔 데이터 일관성을 지킬 수 없다는 점도 고려하자.

잠금 (Lock)

MySQL에서는 크게 스토리지 엔진 레벨에서의 잠금과 MySQL 엔진 레벨에서의 잠금으로 나눌 수 있다.

• MySQL 엔진 레벨의 잠금

  • 스토리지 엔진을 제외한 나머지 부분

  • 모든 스토리지 엔진에 영향을 미침

  • 테이블 락, 메타데이터 락, 네임드 락 등

• 스토리지 엔진 레벨의 잠금

  • 스토리지 엔진 간 상호 영향을 미치지 않음

  • 레코드 락, 갭 락

MySQL 엔진 레벨의 잠금

글로벌 락 (Global Lock)

MySQL에서 제공하는 잠금 중에서 가장 범위 큰 잠금이다.

• 락 획득: FLUSH TABLES WITH READ LOCK

• 락 해제: UNLOCK TABLES

• 한 세션에서 락을 획득할 경우 다른 세션에서 SELECT 를 제외한 대부분의 DDL, DML 문장을 실행하는 경우 대기 상태가 된다.

• 락의 영향 범위: MySQL 서버 전체 (작업 대상의 테이블 및 데이터베이스가 다르더라도 영향을 미친다.)

INSERT, UPDATE, DELETE 쿼리가 아주 오랜 시간동안 대기 상태가 걸릴 수 있어 웹 서비스용으로 사용되는 경우에 가급적 사용하지 않는 것이 좋다. 또한, 백업 프로그램이 내부적으로 해당 명령어를 사용하여 백업 작업을 진행할 수 있다.

테이블 락 (Table Lock)

개별 테이블 단위로 설정되는 잠금으로 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

• 락 획득: LOCK TABLE table_name [ READ | WRITE ]

• 락 해제: UNLOCK TABLES

명시적인 테이블 락

명시적으로 테이블 락을 거는 행위 또한, 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미친다. 찾아보니 동시성 저하, 대기 현상, 서비스 가용성 및 트랜잭션 격리와 충돌 등 심각한 영향을 미칠 수 있다고 한다.

따라서, 불가피한 상황이 아니라면 사용을 지양하자.

묵시적인 테이블 락

• MyISAM, MEMORY 테이블에 데이터를 변경하는 경우에 사용된다.

• InnoDB의 경우 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경에 묵시적인 테이블 락이 걸리지는 않는다.

  • 정확하게 말하자면, 데이터 변경 쿼리(DML)에서는 무시되며, 스키마 변경 쿼리(DDL)의 경우에만 영향을 미친다.

네임드 락 (Named Lock)

MySQL 함수를 이용해 임의의 문자열에 대해 잠금을 설정해서 사용한다.

• 락 획득: SELECT GET_LOCK('mylock', 2)

• 락 해제: SELECT RELEASE_LOCK('mylock')

네임드 락의 특이한 점은 잠금 대상이 테이블, 레코드, 데이터베이스 객체가 아니라 단순 사용자가 지정한 문자열(String)이라는 점이다.

동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다. 물론, 락이 자동으로 해제되지는 않아 꼭 락의 획득 및 반납에 대한 로직을 철저하게 구현해줘야 한다. 세션 관리에 주의해야할 점이 많다.

MySQL을 이용한 분산락으로 여러 서버에 걸친 동시성 관리 | 우아한형제들 기술블로그우아한형제들 기술블로그 |

메타데이터 락

테이블이나 뷰 등 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금을 말한다.

• 명시적으로 획득하거나 해제할 수 없음

• RENAME TABLE tab_a TO tab_b 같이 변경시 자동으로 획득하는 잠금

  • tab_a 와 tab_b 두 개 모두 한꺼번에 잠금을 설정한다.

-- 배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터 생성

-- 랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업
-- 새로 만들어진 랭킹 테이블(rank_new)을 서비스용으로 대체하고자 하는 경우

mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;

하나의 RENAME TABLE 명령문에 두 개의 작업을 한꺼번에 실행하면 "Table not found 'rank'" 와 같은 상황이 발생하지 않고 적용할 수 있다. 아마 원자적으로 처리하기 때문인 것으로 보인다.

하지만, 2개의 명령문으로 나눠서 실행한다면 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생겨 오류가 발생할 수도 있다.

mysql> RENAME TABLE rank TO rank_backup;
mysql> RENAME TABLE rank_new TO rank;

2번 케이스

로그 테이블에서의 메타데이터 잠금

InnoDB의 트랜잭션과 메타데이터 잠금을 동시에 사용해야하는 케이스도 존재한다.

-- 접근(액세스) 로그용 테이블
CREATE TABLE access_log
(
    id BIGINT NOT NULL AUTO_INCREMENT,
    client_ip INT UNSIGNED,
    access_dttm TIMESTAMP,
    ...
    PRIMARY KEY(id)
);

위와 같은 테이블에 INSERT 작업만 실행되는 테이블이 있다고 가정해보자. 요구사항이 변경되어 테이블의 구조를 변경해야한다면 어떻게 대응해야할까?

단순 지금 생각

• ALTER TABLE 을 통해서 테이블 구조를 변경할 경우에도 락이 걸릴까?

• 락이 걸린다고 한다면, 아마 테이블 락이 걸릴 것으로 예상된다. (CRUD 작업으로 인해 변경이 생길 수 있으므로)

• InnoDB에서 Online DDL 기능을 통해 테이블 변경 시에도 DML이 실행될 수 있다. (p.140)

MySQL Online-DDL (당근 테크 블로그)

시간이 너무 많이 걸리는 작업이 될 경우 고려해야할 점이 많다.

• 언두 로그의 증가

• Online DDL 버퍼 크기

• DDL 작업은 단일 스레드로 동작

이러한 상황을 대처하기 위해서는 id 값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사하는 방법이 있다.

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

이후 나머지 데이터는 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행할 수 있다.

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

-- auto commit false 
mysql> SET autocommit=0; 

-- 작업 대상 테이블 2개에 대해 테이블 쓰기 락 획득
-- access_log 에 대해서 새로운 값이 들어오는 것을 방지
-- (?) access_log_new에도 락이 필요한가? 
mysql> LOCK TABLES access_log WRITE, access_log_new WRITE;

-- 남은 데이터를 복사
mysql> SELECT MAX(id) as @MAX_ID FROM access_log_new;
mysql> INSERT INTO access_log_new SELECT * FROM access_log WHERE pk > @MAX_ID;
mysql> COMMIT;

-- 새로운 테이블로 데이터 복사 완료시 RENAME 명령으로 새로운 테이블을 서비스로 투입
mysql> RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;
mysql> UNLOCK TABLES;

-- 불필요한 기존 테이블 삭제
mysql> DROP TABLE access_log_old;

InnoDB 스토리지 엔진 잠금

MySQL 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금을 탑재하고 있다.

• 이러한 레코드 기반의 잠금은 뛰어난 동시성 처리를 제공할 수 있다.

• 반면, 이원화된 잠금 처리로 인해 InnoDB 스토리지에서 사용되는 잠금에 대한 정보를 MySQL 명령을 통해 접근하기 어렵다.

InnoDB 트랜잭션 및 잠금 정보 확인

MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS , INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 관련 정보들을 확인할 수 있다.

또한, InnoDB의 중요도가 높아지면서 Performance Schema를 이용해 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.

MySQL :: MySQL 8.4 Reference Manual :: 29 MySQL Performance Schema

레코드 락 (Record Lock)

레코드(행) 자체만을 잠그는 것을 의미하지만, InnoDB에서는 특이하게 레코드 자체가 아닌 인덱스의 레코드를 잠근다.

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

InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용하지만, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에 대해서는 갭 락을 사용하지 않고 레코드 자체에 대해서만 락을 건다.

갭 락 (Gab Lock)

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

레코드 사이의 간격에 새로운 레코드가 생성(INSERT) 되는 것을 제어하는 것이 목적

넥스트 키 락 (Next key Lock)

레코드 락 + 갭 락 형태의 잠금을 의미한다.

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

• innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화(0)되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식 적용

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

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

자동 증가 락 (Auto Increment Lock)

Auto Increment를 사용하는 상황에서 값을 채번하는 과정에서 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.

이렇게 가능하게 해주는 것은 자동 증가 락을 통해서 가능하게 해준다.

• INSERT, REPLACE 쿼리같이 새로운 레코드를 저장하는 쿼리에서만 필요하다.

• 트랜잭션 여부와 상관 없이 AUTO INCREMENT 값만 가져올 때 잠간 락이 걸렸다 즉시 해제된다.

명시적으로 락을 제어할 수 없지만, innodb_autoinc_lock_mode 시스템 변수를 통해 작동 방식을 변경할 수 있다.

innodb_autoinc_lock_mode = 0

• 모든 INSERT 문장은 자동 증가 락을 사용

innodb_autoinc_lock_mode = 1 (consecutive mode, ~ 5.7)

• INSERT 쿼리에서 레코드 건 수를 정확히 예측할 수 있는 경우

  • 자동 증가 락 사용하지 않음

  • 대신 훨씬 가볍고 빠른 래치(뮤텍스)를 이용하여 처리

• INSERT 쿼리에서 레코드 건 수를 예측할 수 없는 경우

  • 자동 증가 락 사용

innodb_autoinc_lock_mode = 2 (interleaved mode, 8.0 ~)

• 무조건 경량화된 래치(뮤텍스) 사용

• 자동 증가 값을 보장하지 않음

  • 단순 유니크 값이 생성됨만을 보장

MySQL :: MySQL 8.4 Reference Manual :: 17.6.1.6 AUTO_INCREMENT Handling in InnoDB

인덱스와 잠금

레코드 락의 내용처럼 InnoDB의 락은 레코드를 잠그는게 아닌 인덱스를 잠그는 방식으로 처리한다.

이러한 방식의 락 방식으로 인해 1개의 레코드에 대해서 변경을 시도할 경우, 연관된 레코드들이 모두 잠기는 현상이 일어나게 된다.

-- first_name 컬럼에 인덱스(ix_firstname)이 걸려있는 상황
-- employees 테이블에 first_name='Georgi'인 사원이 253명이 존재
--                 first_name='Georgi' && last_name='Klassen'인 사원이 1명만 존재

mysql> SELECT COUNT(*) 
        FROM employees 
        WHERE first_name='Georgi';
        
+---------+
|      255|
+---------+

mysql> SELECT COUNT(*) 
        FROM employees 
        WHERE first_name='Georgi' AND last_name='Klassen';
        
+---------+
|        1|
+---------+


-- 이 상황에서 'Georgi Klassen'의 입사 일을 오늘로 변경한다면? 
mysql> UPDATE employees 
        SET hire_date=NOW()
        WHERE first_name='Georgi' AND last_name='Klassen';

어떻게 보면 인덱스에 락을 거는 행위 자체가 이상하다고 보일 수 있겠지만, 인덱스를 걸지 않을 경우에는 어떤 일이 일어날까? 테이블을 풀 스캔하면서 UPDATE 작업을 하는데 이 과정에서 테이블에 있는 모든 레코드를 잠그게 된다.

레코드 수준의 잠금에도 문제가 있다.

• 테이블 잠금과 달리 잠금의 대상을 쉽게 파악할 수 없다.

• 레코드가 오랫동안 사용되지 않는다면 오랜 시간 동안 락 상태로 남아 있어도 잘 발견되지 않는다.

최근 버전에선 레코드 락과 락 대기에 대한 조회가 가능해졌다.

MySQL 8.0 이전

• inofrmation_schema 데이터베이스

  • INNODB_TRX , INNODB_LOCKS , INNODB_LOCK_WAITS 테이블

MySQL 8.0 ~

• performance_schema 데이터베이스

  • data_locks, data_lock_waits 테이블

격리 수준 (Isolation Level)

여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션의 변경 및 및 조회하는 데이터를 볼 수 있게 허용할지 말지 결정하는 것

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O (InnoDB는 발생 X)
SERIALIZABLE	X	X	X

아래의 테스트는 모두 SET AUTOCOMMIT = OFF 상황을 가정한다.

DIRTY READ

어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상

NON-REPETABLE READ

말 그대로 반복 읽기(REPETABLE READ)가 보장되지 않는 것으로 다른 트랜잭션에서 커밋 여부에 따라 읽기 결과가 달라지는 현상

PHANTOM READ

다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상

READ UNCOMMITTED

각 트랜잭션에서 변경 내용이 COMMIT / ROLLBACK 과 상관없이 다른 트랜잭션에서 보이는 격리 수준이다.

더티 리드로 인해 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만들 수 있으며 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성 문제가 많다.

따라서 MySQL 에서는 최소 READ COMMITTED 이상의 격리 수준을 권장한다.

READ COMMITTED

더티 리드가 발생하지 않도록 COMMIT된 데이터만 다른 트랜잭션에서 조회할 수 있다. 이 격리수준부터 MySQL에서는 MVCC를 통해 언두 로그의 데이터를 보여주게 된다.

하지만 B 트랜잭션에서 같은 SELECT 문을 통해서 데이터 조회시 A 트랜잭션에서 해당 레코드에 대한 변경을 했을 경우 읽어오는 값이 변동되게 된다.

사용자 A(트랜잭션1)                      사용자 B(트랜잭션2)
───────────────────────────────      ──────────────────────────────
BEGIN;                           

SELECT * FROM employees
WHERE emp_no = 500000;
-- 결과: first_name = Lara

                                     BEGIN;
  
                                     UPDATE employees
                                     SET first_name = 'Toto'
                                     WHERE emp_no = 500000;
  
                                     COMMIT;

SELECT * FROM employees
WHERE emp_no = 500000;
-- 결과: first_name = Toto <--- ?

COMMIT;

같은 트랜잭션 내에서 항상 같은 결과를 반환해야하는 REPEATABLE READ 정합성에 어긋나는 현상이다.

REPEATABLE READ

MySQL InnoDB 엔진의 기본 격리 수준으로 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

MySQL에서 바이너리 로그를 기반으로 마스터/슬레이브 복제 및 백업에서 사용된다.

이 때 8.0을 기준으로 Mixed(Statement + row) 방식을 사용하지만, 기본적으로 실행된 쿼리문(SQL statement)을 로그에 기록한다.

단순 쿼리문만을 기록하기 때문에 이상 현상에 제대로 대처할 수가 없다는 문제점으로 인해 REPEATABLE READ 이상의 격리 수준을 사용해 안전하게 복제/복구가 가능하다.

InnoDB 환경에서 MVCC를 통해 언두(Undo) 공간에 백업한 데이터를 기반으로 REPEATABLE READ를 가능하게 한다.

모든 InnoDB 트랜잭션에는 고유 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 로그 영역에 백업된 모든 레코드는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다.

일반 SELECT의 경우 MVCC와 넥스트 키 락 덕분에 팬텀 리드가 발생하지 않지만, 아래의 특수한 경우(SELECT ... FOR UPDATE와 같이 락이 필요한 상황)에는 언두 레코드를 잠글 수 없기에 실제 레코드에 대해 락을 얻기 위해 팬텀 리드가 발생할 수 있다.

팬텀 리드가 발생하는 상황

• 사용자 A가 employees 테이블에 INSERT 실행 도중

• 사용자 B가 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회한 경우

초기 상태: employees 테이블
┌─────┬────────┬────────┐
│ id  │ name   │ salary │
├─────┼────────┼────────┤
│ 1   │ Alice  │ 5000   │
│ 2   │ Bob    │ 5500   │
└─────┴────────┴────────┘



사용자 A(트랜잭션1)                      사용자 B(트랜잭션2)
───────────────────────────────      ──────────────────────────────
BEGIN;

SELECT * 
FROM employees
WHERE salary > 5000;
-- 결과: 2명 (Alice, Bob)

                                     BEGIN;
                                     INSERT INTO employees (id, first_name, salary)
                                     VALUES (3, 'Charlie', 6000);
                                     COMMIT;

SELECT * 
FROM employees
WHERE salary > 5000;
-- 결과: 3명 (Alice, Bob, Charlie)   <--- 팬텀 리드 발생

COMMIT;

SELECT ... FOR UPDATE 의 경우 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없기에 발생하는 문제이다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준으로 동시 처리 성능도 매우 떨어진다.

읽기 작업도 공유 잠금(Shared-Lock, 읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못한다.

InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ에서도 팬텀 리드가 발생하지 않기에 굳이 해당 격리 수준을 사용해야할까에 대해서는 생각해보자.