트랜잭션과 잠금

트랜잭션과 잠금

 

트랜잭션

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을 때)함을 보장해 주는 것이다.

MyISAM이나 MEMORY 스토리지 엔진처럼 부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있다. 따라서 InnoDB 스토리지 엔진이 지원하는 트랜잭션 기능은 애플리케이션 개발에서 고민해야 할 문제를 줄여주는 아주 필수적인 DBMS의 기능이라는 점을 명시해야 한다.

 

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락(Metadata Lock) 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named Lock)이라는 잠금 기능도 제공한다.

 

글로벌 락

글로벌 락(GLOBAL LOCK)은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 즉, 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.

 

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

MySQL 8.0 버전부터 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다. 특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사요요자의 인증 관련 정보를 변경할 수 없지만 일반적인 테이블의 데이터 변경은 허용된다.

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

 

테이블 락

테이블 락(Table Lock)은 개별 테이블 단위로 설정되는 잠금이다. 명시적으로 LOCK TABLES table_name [READ | WRITE] 명령으로 특정 테이블의 락을 획득할 수 있다. 그리고 명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납(해제)할 수 있다. 묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않는다.

 

네임드 락

네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 네임드 락의 경우 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다. 각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수는 있지만, 이는 간단한 방법이 아니며 완전한 해결책이 될 수도 없다. 이러한 경우에 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

 

메타데이터 락

메타데이터 락(Metadata Lock)은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니로 RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

 

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하여 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있다. 최근 버전에서 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다. MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 또한 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수 있다.

 

레코드 락

레코드 자체만을 잠그는 것을 레코드 락(Record Lock, Record only lock)이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다. 한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 이어서 설명할 넥스트 키 락(Next key lock) 또는 갭 락(Gap lock)을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭(Gap, 간격)에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

 

갭 락

다른 DBMS와의 또 다른 차이가 바로 갭 락(Gap lock)이다. 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어한다. 갭 락은 넥스트 키 락의 일부로 자주 사용된다.

 

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락(Next key lock)이라고 한다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

 

자동 증가 락

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

AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다. AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다.

 

인덱스와 잠금

위에서 언급했지만 InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다. 

 

아래 쿼리 이전에 employees 테이블에는 first_name 컬럼만 멤버로 담긴 ix_firstname이라는 인덱스가 준비되어 있다고 가정하자.

그리고 employees 테이블에 first_name='Georgi'인 사원은 전체 253명이 있고, first_name='Georgi'이고 last_name='Klassen'인 사원은 딱 1명만 있다.

-- // employees 테이블에서 first_name='Georgi'이고 last_name='Klassen'인 사원의
-- // 입사 일자를 오늘로 변경하는 쿼리를 실행해보자
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

위 UPDATE 문장이 실행되면 1건의 레코드가 업데이트 될 것이다. 하지만 이 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까? UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이며, last_name 컬럼은 인덱스에 없기 때문에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠긴다. 지금은 몇 건 안 되는 레코드만 잠그지만 UPDATE 문장을 위해 적절히 인덱스가 준비되어 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 수 있다.

만약 employees 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하기 때문에 테이블에 있는 30여만 건의 모든 레코드를 잠그게 된다. 이것이 MySQL의 방식이며, MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이것이다.

 

MySQL의 격리 수준

트랜잭션의 격리 수준(Isolation Level)은 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준은 크게 READ UNCOMMITED, READ COMMITED, REPEATABLE READ, SERIALIZABLE 4가지로 나뉜다.

 

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITED	발생	발생	발생
READ COMMITED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

 

READ UNCOMMITED

READ UNCOMMITED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다. 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 Dirty Read라 하고, Dirty Read가 허용되는 격리 수준이 READ UNCOMMITED다. 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만들고 정합성에 문제가 많은 격리 수준이다. 따라서 MySQL을 사용한다면 최소한 READ COMMITED 이상의 격리 수준을 사용할 것을 권장한다.

 

READ COMMITED

READ COMMITED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 이 레벨에서는 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문에 더티 리드 현상은 발생하지 않는다. 다른 트랜잭션에서 COMMIT된 데이터만 조회할 수 있는 것은 변경 전 데이터를 언두 로그로 복사해서 언두 영역에 백업된 레코드에서 조회하기 때문이다. 최종적으로 COMMIT이 되면 다른 트랜잭션에서도 백업된 언두 레코드가 새로 변경된 값을 참조할 수 있게 된다.

 

READ COMMITED 격리 수준에서도 NON-REPEATABLE라는 부정합의 문제가 있다. A 트랜잭션에서 두 번의 조회를 하는 쿼리를 한 후 COMMIT한다고 가정했을때, 중간에 B 트랜잭션에서 데이터를 변경한 후 COMMIT을 한다면 A 트랜잭션에서 두 번째 조회를 하는 쿼리에서 변경된 데이터를 읽는 결과가 발생한다. 하나의 트랜잭션에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것이다. 이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 일고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.

 

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. 이 격리 수준에서는 READ COMMITED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합이 발생하지 않는다. REPEATABLE READ는 InnoDB가 지원하는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서 동일한 결과를 보여줄 수 있게 보장한다. READ COMMITED와 REPEATABLE READ의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다. 

 

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)을 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다. 하나의 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 자신보다 작은 트랜잭션 번호에서 변경한 것만 보기 때문이다.

 

REPEATABLE READ 격리 수준에서 하나의 트랜잭션에서 두 번의 SELECT ... FOR UPDATE 쿼리가 다른 결과를 내는 경우가 있다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 하는 현상을 PHANTOM READ(또는 PHANTOM ROW)라고 한다. SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그래서 SELECT ... FOR UPDATE나 SELECT .. LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.

 

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없기 때문에 Non-locking consistent read)잠금이 필요없는 일관된 읽기)가 불가능하다.

SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 PHANTOM READ라는 문제가 발생하지 않는다. 그리고 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에(위에서 언급했듯이 SELECT ... FOR UPDATE, SELECT ... FOR SHARE'에서는 발생 가능) SERIALIZABLE을 사용할 필요성이 없어졌다.

 

출처

Real MySQL 8.0 1권 : 개발자와 DBA를 위한 MySQL 실전 가이드