트랜잭션과 잠금

1. MySQL에서의 트랜잭션

---

트랜잭션은 하나의 논리적인 작업 셋 자체가 100% 적용되거나(Commit 실행) 아무것도 적용되지 않아야(Rollback 실행) 함으로 보장해준다. 이러한 트랜잭션 기능은 InnoDB 테이블에서만 제공되는 기능이며, 이를 통해 쿼리 실행 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙을 따르게 된다.

 

잠금(Lock)과 트랜잭션 기능을 헷갈릴 수 있지만, 기능의 사용 목적을 간단히 정리하면 다음과 같다.

• 잠금(Lock) : 동시성 제어하기 위한 기능이다. 여러 커넥션에서 동시에 동일한 자원을 변경할 수 없도록 한다.
• 트랜잭션 : 데이터의 정합성을 보장하기 위한 기능이다.

 

 

2. MySQL 엔진의 잠금

---

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨 잠금은 모든 스토리지 엔진에 영향을 미치는 더 큰 범위의 잠금이라고 생각하면 된다. 종류는 글로벌 락, 백업 락, 테이블 락, 네임드 락, 메타데이터 락이 있다.

 

1) 글로벌 락

 

다음 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.

FLUSH TABLES WITH READ LOCK;

 

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 실행을 수행하지 못하고 대기 상태로 남게 된다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 주로 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump 로 일관된 백업을 받아야 할 때 사용한다.

 

이처럼 글로벌 락은 MySQL 서버의 모든 변경 작업을 멈추는 매우 무거운 락이다. MySQL에서는 트랜잭션을 지원하는 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택되며 좀 더 가벼운 글로벌 락의 필요성이 생겼는데, 여기에서 백업 락이 도입되었다.

 

 

2) 백업 락

 

MySQL 8.0 버전 부터는 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입되었다.

mysql> LOCK INSTANCE FOR BACKUP;
-- // 백업 실행
mysql> UNLOCK INSTANCE;

 

특정 세션에서 백업 락을 획득하게 되면 모든 세션에서는 아래와 같이 테이블의 스키마, 사용자 인증 관련 정보를 변경할 수 없게 된다.

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

 

하지만 일반적인 테이블의 데이터 변경은 가능하다. 

 

기존 글로벌 락을 사용했다고 가정하면 백업이 Replica Server에서 수행하더라도 Xtrabackup 이나 Enterprise Backup 툴이 실행되는 도중에 스키마 변경이 발생되면 결국 최종 백업된 파일은 사용할 수 없는 상태가 되어버린다. 만약 장시간 수행되었던 백업이었다면 다시 스키마 변경 후 백업을 수행하기 위해 많은 시간을 사용하게 될 것이다.

그래서 8.0 버전에서 이런 목적으로 백업락이 도입이 되었으며, 정상적으로 복제는 이루어지지만 백업의 실패를 막기 위해서 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 하게 된다.

 

 

3) 테이블 락

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

// 명시적
mysql> LOCK TABLES table_name [ READ | WRITE ];

묵시적 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. 즉, 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동으로 해제된다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 준다.

 

 

 

4) 네임드 락

GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있으며, 단순히 사용자가 지정한 문자열(String)을 획득하고 반납(해제)하는 잠금이다. 주로 분산 서비스에서 여러 대의 서버가 하나의 DB에 있는 자원에 대해 동기화된 처리를 하기 위해 활용할 수 있으며, MySQL 에서의 네임드락을 사용 이외 대표적으로 Redis 나 ZooKeeper 와 같은 솔루션을 통한 분산락 구현을 할 수 있기 때문에 많이 사용되지는 않는다.

[Redis를 활용한 분산락 구현 참고]

레디스와 분산 락(1/2) - 레디스를 활용한 분산 락과 안전하고 빠른 락의 구현

 

5) 메타데이터 락(Metadata Lock)

데이터베이스 객체(테이블이나 뷰)의 이름 변경(RENAME) 구조를 변경(Column 추가)하는 경우에 획득하는 잠금이다. 명시적으로 획득하거나 해제할 수 있는 것이 아니고 자동으로 획득된다.

 

아래와 같이 이름 변경(RENAME)일 경우, 원본 이름과 변경될 이름 모두 한꺼번에 잠금을 설정한다.

mysql> RENAME TABLE tab_a TO tab_b;

 

다음같이 실시간으로 테이블을 바꿔야 하는 상황이 있다고 가정하자. rank_new라는 임시 테이블을 rank라는 현재 서비스하는 테이블로 이름을 변경하고, 기존의 rank는 rank_backup이라는 이름으로 변경하는 상황이다.

자칫하면 rank 테이블이 존재하지 않는 순간이 생기며(rank가 rank_backup으로 먼저 변경되는 경우) 이 때 rank 테이블을 대상으로 쿼리가 실행되면 "Table not found rank" 오류가 발생한다.

 

아래와 같이 두 개의 RENAME 작업을 한꺼번에 실행하면 문제 없이 변경을 수행할 수 있다.

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

 

 

3. InnoDB 스토리지 엔진 잠금

---

InnoDB 스토리지 엔진은 내부에서 레코드 기반의 잠금 방식을 탑재하고 있으며 이를 기반으로 동시성 처리를 제공할 수 있게 된다.

MySQL 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되어, 편리하게 잠금 정보를 확인 / 조작할 수 있게 되었다. 잠금의 종류로는 레코드 락, 갭 락, 넥스트 키 락이 있다.

 

1) 레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 하며, 다른 DBMS와 중요한 차이는 InnoDB 스토리지 엔진은 인덱스의 레코드를 잠근다는 점이다. InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락, 갭 락을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에 대해서만 락을 건다.

 

2) 갭 락

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

 

3) 넥스트 키 락

레코드 락과 갭 락을 합쳐놓은 형태의 잠금을 의미한다. 다음의 경우에서 넥스트 키 락이 사용된다.

• STATEMENT 포맷 로그
  REPEATABLE READ 격리 수준을 사용하므로 넥스트 키 락이 걸린다.
  로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만든 결과와 동일한 결과를 만들어 내야하기 때문이다.
  
  세션 1번 -> 세션 2번의 트랜잭션 순서대로 진행되었다고 가정하자.
  만약 세션 2번이 먼저 COMMIT되고 그 이후 세션 1번이 COMMIT된다면, 복제 Source DB와 Replica DB에서의 트랜잭션 실행 순서가 거꾸로 되기 때문에, 데이터는 서로 달라지는 결과를 만들게 된다. 이렇게 바이너리 로그 포맷이 STATEMENT일 때 간격의 잠금이 필요한 이유는, 이런 복제 Source와 Replica간의 데이터 부정합을 막기 위함이다.
• innodb_locks_unsafe_for_binlog
  해당 변수가 비활성화(0)일 때, 변경을 위해 검색되는 레코드에 넥스트 키 락이 걸린다.

 

참고

---

MySQL - 트랜잭션 과 잠금 (1) - Real MySQL 8.0

MySQL Gap Lock 다시보기

Select 쿼리는 S락이 아니다. (X락과 S락의 차이)

MySQL InnoDB lock & deadlock 이해하기