[MySQL] 트랜잭션, 잠금, 격리 수준이 뭘까 ?

트랜잭션: 데이터의 정합성을 보장한다.

잠금: 동시성을 제어한다.

격리 수준: 트랜잭션 간의 작업 내용을 어떻게 공유하고, 차단할 것인지 결정하는 레벨을 의미한다.

 

세가지 다 MySQL 동시성에 관련되어 있다. 각각 더 자세하게 살펴보자.

---

01  트랜잭션

스토리지 엔진: 서버 엔진이 필요한 물리적인 데이터를 가져오는 장치

일단 먼저 트랜잭션이 얼마나 편한 기능을 제공하는지 알아보기 위해 MyISAM과 InnoDB 스토리지 엔진을 비교해보자.

 

트랜잭션의 대표적인 기능

COMMIT: 작업이 정상적으로 되어서 트랜잭션을 종료하고 데이터가 업데이트 된다.

ROLLBACK: 실행된 트랜잭션 작업에 문제가 발생해서 작업 이전의 상태로 되돌린다.

 

트랜잭션 왜 사용할까 ?

 

MyISAM은 트랜잭션을 지원하지 않지만 InnoDB은 트랜잭션을 지원한다.

작업에서 오류가 발생했을 때, 트랜잭션의 기능은 진가를 발휘한다. 예를 들어 작업 중간에 기본 키가 중복되는 오류가 발생하면 InnoDB는 ROLLBACK 기능으로 이전 상태로 되돌린다. 반면에 트랜잭션을 지원하지 않는 MyISAM은 오류 이전의 쿼리는 저장되고 중간에 작업이 종료된다. 이를 부분 업데이트라고 표현한다. 그래서 실패한 쿼리로 인해 레코드를 삭제하는 재처리 작업이 필요할 수 있어서 골치 아프다.

 

트랜잭션 사용 시 주의사항

 

트랜잭션의 필요성이 느껴진다. 하지만 트랜잭션을 사용할 때 주의사항이 있다.

1. DBMS에 데이터를 저장하는 작업부터 트랜잭션에 포함한다.
2. 네트워크를 통해 원격 서버와 통신하는 작업은 트랜잭션에서 제거한다.
3. 단순 조회는 트랜잭션에서 제거해서 적절하게 트랜잭션을 분리한다.

1번은 실제로 DBMS에 데이터를 저장하는 작업부터 트랜잭션에 포함시키라는 말이다. 커넥션 단계부터 포함시킬 필요가 없다.

2번은 만약 네트워크가 불안정해서 연결이 제대로 되지 않는 경우 대기하는 DBMS 서바가 높은 부하 상태에 빠질 수 있어서 위험하다.

3번은 데이터를 조회하는 것은 변경이 일어나지 않기 때문에 작업에 포함시키지 않아도 된다.

 

---

02 잠금

MySQL 엔진 레벨 잠금: 스토리지 엔진을 제외한 나머지 부분이고 모든 스토리지 엔진에 영향을 미친다.

스토리지 엔진 레벨 잠금: 스토리지 엔진 간 상호 영향을 미치지 않는다. 즉, 독립적이다.

 

잠금의 종류를 살펴보자.

MySQL 엔진의 잠금

글로벌 락

MySQL에서 제공하는 범위가 서버 전체로 가장 크다. FLUSH TABLES WITH READ LOCK 명령으로 획득한다. 한 세션에서 해당 락을 획득하면 다른 세션의 문장은 잠금이 해제될 때까지 대기 상태가 된다. 서버 전체에 영향을 미치고 작업 대상 테이블과 데이터베이스가 다르더라도 동일하게 영향을 미친다.

 

장시간 실행되는 쿼리와 글로벌 락이 동시에 적용되면 최악의 경우, 데이터베이스가 오래 기다려야한다. 그래서 웹서비스에 활용하는 건 적합하지 않다.

 

최근에는 기존의 글로벌 락보다 가벼운 백업 락이 제공된다. 기존 글로벌 락을 백업할 때 사용했는데 중간에 오류가 발생하면 백업에 실패한다. 하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용된다. 백업 도중 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

 

테이블 락

테이블 단위로 설정되는 잠금이고 특정 테이블에 락을 획득할 수 있다. LOCK TABLES table_name [ READ | WRITE ] 명령으로 획득한다. 해당 락도 특별한 상황이 아니라면 온라인 작업에 상당한 영향을 미치기 때문에 애플리케이션에서 사용할 필요가 거의 없다.

 

쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동 해제된다. InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 터 변경 쿼리는 무시되고 스키마 변경 쿼리만 영향을 준다.

 

네임드 락

네임드 락은 GET_LOCK() 함수를 이용해서 임의의 문자열에 대해 잠금을 설정할 수 있다. 특징은 대상이 테이블, 레코드, 자등 증가와 같은 데이터베이스 객체가 아니라 단순히 사용자가 지정한 문자열에 대해 잠금을 획득하고 해제하는 동작을 한다. 여러 클라이언트가 상호 동기화를 처리하는 경우가 아니라면 잘 사용되지 않는다.

 

한꺼번에 많은 레코드를 변경하는 쿼리는 데드락의 주요 원인이다. 이러한 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 네임드 락을 걸고 실행하면 간단히 해결할 수 있다.

 

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금이다. 명시적으로 획득, 해제하는 잠금이 아니고 RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다. 원본 이름과 변경될 이름 두 개 모두 잠금을 획득한다.

 

테이블 이름 변경을 한 번에 처리하면 테이블 이름을 찾지 못하는 상황이 발생하지 않는다. 하지만 문장을 2개로 나눠서 실행하면 아주 짧은 시간이지만 테이블 이름을 찾지 못하는 오류가 발생할 수도 있다.

 

테이블 구조 변경이 필요한 경우 단일 스레드로 작동하면 많은 시간이 걸리기 때문에 새로운 구조의 테이블을 생성하고 기본 키를 범위 별로 나눠서 멀티 스레드로 빠르게 복사한다. 데이터를 복사하는 동안 테이블 락과 메타데이터 락을 획득하기 때문에 가능하면 미리 아주 최근 데이터까지 복사해 둬야 잠금 시간을 최소화해서 서비스에 미치는 영향을 줄일 수 있다.

InnoDB 스토리지 엔진의 잠금

MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 제공한다. 그래서 훨씬 뛰어난 동시성 처리가 가능하다. information_schema 데이터베이스의 테이블(INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS)을 조인해서 조회하면 트랜잭션과 잠금에 대한 정보를 조회할 수 있다.

 

잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업 되는 경우는 없다. DBMS와는 조금 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐만이 아니라 레코드와 레코드 사이 간격을 잠그는 갭 락이 존재한다.

 

레코드 락

레코드 자체만을 잠그는 기능이다. InnoDB 레코드 락 특징은 레코드 자체를 잠그는게 아니라 인덱스의 레코드를 잠근다는 점이다. 인덱스가 존재하지 않더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 레코드 자체를 잠그는 것과 인덱스의 레코드를 잠그는 것은 중요한 차이가 있다.

 

갭 락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격을 잠그는 것을 의미한다. 레코드 사이의 새로운 데이터가 생성되는 것을 막는 역할을 한다. 주로 넥스트 키 락의 일부로 사용된다.

 

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이 넥스크 키 락이다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리와 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 하는 것이 주목적이다. 그런데 갭 락, 넥스트 키 락으로 인해 데드락이 발생하거나 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

 

자동 증가 락

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

 

해당 락은 INSERT와 REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다. UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다. 다른 잠금과 달리 트랜잭션과 관계 없이 쿼리에서 AUTO_INCREMENT를 가져올 때만 잠금을 획득하고 즉시 해제한다. 락은 단 하나만 존재하기 때문에 잠금을 획득한 상태라면 다른 쿼리는 대기해야 한다. 자동 증가 락은 아주 짧게 사용되기 때문에 대부분의 경우 문제가 되지 않는다. 자동 증가 락은 한 번 증가하면 줄어들지 않는다. 잠금을 최소화하기 위해서다. 쿼리가 실패해도 값은 증가하고 줄어들지 않고 그대로 남는다.

인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계다. 앞에서 설명했듯이 InnoDB의 잠금은 레코드 자체를 잠그지 않고 인덱스의 레코드를 잠그는 방식으로 처리한다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야한다. 하지만 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성은 상당히 떨어져서 다른 클라이언트가 기다려야하는 상황이 발생할 수 있다.

 

테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 모든 레코드를 잠그게 된다. 그렇기 때문에 InnoDB에서 인덱스 설계가 중요하다.

 

레코드 수준의 잠금은 자주 사용되지 않는다면 잠겨진 상태가 잘 발견되지 않기 때문에 테이블 수준의 잠금보다 더 복잡하다. 

 

03  MySQL의 격리 수준

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용하지 말지를 결정하는 것이다. 격리 수준은 아래 4가지로 나뉜다.

	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

 

격리 수준이 표에서 아래로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지고, 동시 처리 성능도 떨어진다. 격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것  같지만 SERIALIZABLE 격리 정도가 아니라면 크게 성능 차이는 없다.

 

표에서 보듯이 세 가지 부정합 문제가 존재한다.

READ UNCOMMITTED

해당 격리 수준은 각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에 보인다. 예를 들어 사용자 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 사용자 B는 조회할 수 있다. 문제는 작업 도중 오류가 발생해도 데이터를 조회한다는 것이다. 이처럼 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서도 볼 수 있는 현상을 DIRTY READ라고 한다.

READ COMMITTED

오라클 DBMS에서 기본으로 사용되는 격리 수준이다. DIRTY READ 같은 현상은 발생하지 않는다. 어떤 트랜잭션이 데이터를 변경하더라도 COMMIT이 완료된 데이터만 조회가 가능하기 때문이다. 트랜잭션이 실행되면 이전 데이터는 언두 영역으로 백업된다. COMMIT이 완료되기 전에 조회를 하면 언두 영역에 백업된 데이터를 가져온다. 그런데 만약 사용자 B의 트랜잭션이 실행되고 그 안에서 다른 사용자 A의 트랜잭션이 데이터를 변경한다면 사용자 B는 사용자 A 트랜잭션 전후의 조회한 데이터는 결과가 다를 것이다. 이는 별다른 문제가 없어 보이지만, 하나의 트랜잭션 내에서 똑같은 조회 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다. 이 문제를 NON-REPEATABLE READ 라고한다. 

 

금액의 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져올 것이다. SQL 문장이 어떤 결과를 가져오게 되는지를 정확히 예측할 수 있어야 하지만 여기서는 불가능하다. 이런 문제로 데이터의 정합성이 깨지고 그로 인해 서비스에 버그가 발생하면 찾아내기가 쉽지 않다.

REPEATABLE READ

InnoDB 스토리지 엔진에서 기본으로 사용하는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 해당 격리 수준 이상을 사용해야 한다. 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 언두 영역에 백업해두고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC라고 하며, 이것을 위해 언두 영역에 백업된 이전 데이터로 동일 트랜잭션 내에서는 동일한 결과를 보여준다. READ COMMITTED와의 차이는 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 들어가야 하느냐에 있다. 

 

트랜잭션은 고유한 번호를 가진다. 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 이후의 언두 영역 데이터는 삭제할 수가 없다. 더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다. 예를 들어 현재 실행 중인 트랜잭션의 번호가 10이라면 구간 내에서 다른 트랜잭션이 데이터를 변경하고 나서 10번 트랜잭션이 데이터를 조회한다면 변경된 데이터가 아닌, 10번 트랜잭션이 실행될 당시 상태의 데이터를 조회한다는 것이다.

 

문제는 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있다. 언두 영역에 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다. 또한 부정합 문제도 존재한다. SELECT ... FOR UPDATE 쿼리는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그래서 해당 쿼리로 조회한다면 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ 라고 한다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 격리 수준에 비해 떨어진다. 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 작업 중인 레코드를 변경하지 못하게 된다. 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. 덕분에 PHANTOM READ 문제는 발생하지 않지만, InnoDB에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않기 때문에 SERIALIZABLE을 사용할 필요성은 없다.