MySQL InnoDB 트랜잭션과 락, 격리 수준

잠금

스토리지 엔진 레벨과 MYSQL 엔진 레벨 둘로 나눌 수 있다.

MYSQL 엔진 레벨은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨은 상호 스토리지 간 영향을 미치지 않는다.

 

WHY?

락은 왜 존재하는가? 동시성 제어를 위해 존재한다.

테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블 구조를 잠그는 메타데이터 락, 필요에 맞게 사용하는 네임드 락 기능을 제공한다.

 

 

글로벌 락

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

그러나, 모든 변경 작업을 멈추기 때문에 더 가벼운 락의 필요성이 생겼다.

 

DML vs DDL 비교

항목                       DML                                                                         DDL

주요 작업	데이터를 조회, 삽입, 수정, 삭제	테이블 및 데이터베이스 객체 생성, 수정, 삭제
예시 명령어	SELECT, INSERT, UPDATE, DELETE	CREATE, ALTER, DROP, TRUNCATE
트랜잭션 지원	지원 가능 (COMMIT, ROLLBACK 사용 가능)	지원되지 않음
적용 대상	데이터(행/레코드)	데이터베이스 구조(스키마, 테이블, 열 등)
실행 결과	데이터가 변경되거나 조회 결과가 반환됨	데이터베이스 객체의 구조가 변경됨

 

테이블 락

 

개별 테이블 단위로 설정되는 잠금이다.

명시적, 묵시적으로 특정 테이블의 락을 획득할 수 있다. InnoDB 테이블의 경우 DML 쿼리에서는 테이블락이 설정되어도 무시되고 DDL의 경우에만 영향을 미친다.

 

네임드 락

잠금의 대상이 테이블이나 레코드가 아니라 임의의 문자열에 대해 잠금을 설정한다.

네임드 락이 유용한 경우가 있다.

1. 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서, 5대의 웹 서버가 어떤 정보를 동기화해야하는 요건

2. 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랙잭션 상황(EX. 배치 프로그램)

 

메타데이터 락

DB객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

 

---

InnoDB 스토리지 엔진 잠금

레코드 락

InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

변경해야할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야한다.

 

이 경우 first_name='Georgi'인 레코드 253건 레코드가 잠긴다. 만약 인덱스가 없다면 풀 테이블 스캔을 하며 30여 만개의 모든 레코드를 잠그게 된다. 

 

MYSQL 5.1 버전부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하다.

 

격리 수준

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

 

 

READ UNCOMMITTED은 거의 사용하지 않고 SERIALIZABLE 또한 동시성이 중요한 DB에서는 사용하지 않는다.

4개의 격리 수준에서 순서가 뒤로 갈수록 각 트랜잭션 간의 격리 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적이다.

 

Oracle 같은 상용 DB는 주로 READ COMMITTED 수준을 많이 사용하며 MYSQL에서는 REPEATABLE READ를 주로 사용한다. 

 

READ UNCOMMITTED

이 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.

 

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드라고 한다.

데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 정합성에 문제가 많은 격리 수준이다.

 

READ COMMITTED

오라클같은 상용 DBMS에서 기본으로 사용되는 격리 수준이고, 온라인 서비스에서 가장 많이 선택되는 단계이다. 

 

 

사용자 A의 작업이 COMMIT 되지 않았기 때문에 사용자 B의 작업에 대한 반환 내용이 수정 후 내용인 TOTO가 아니라 LARA가 나오는 것을 볼 수 있다.

그러나, 이 READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ라는 부정합의 문제가 있다. 

 

 

사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것이다. 

 

이 현상은 일반 프로그램에서는 크게 문제되지 않을 수 있지만, 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수도 있다.

 

REPEATABLE READ

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

 

작동방식을 이해하기 위한 특징

1. 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적 증가 값)을 가진다.

2. 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다.

3. 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.

 

 

이전 트랜잭션(10번)이 수행 중일 때 정보 수정하는 12번 트랜잭션의 변경 전후 각각 한 번씩 SELECT 했는데 항상 LARA라는 값을 가져온다. 즉, 10번의 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다. 

 

 

이 격리 수준에서도 부정합은 발생할 수 있다. 사용자 B가 시행하는 두 번의 SELECT 쿼리 결과는 똑같아야한다. 하지만 사용자 B가 실행하는 SELECT ~ FOR UPDATE 쿼리 결과는 서로 다르다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 한다. 

 

팬텀 리드 현상 발생 이유

SELECT ~ FOR UPDATE 쿼리는 SELECT하는 레코드에 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그래서 SELECT ~ FOR UPDATE나 SECLECT ~ LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.

 

 

SERIALIZABLE

가장 단순하며 가장 엄격한 격리 수준이다. 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다. 하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그 레코드를 변경하지 못 하게 된다. 

 

이 격리 수준에선 팬텀 리드 문제가 발생하지 않는다. InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 수준에서도 이미 PHANTOM READ가 발생하지 않아서 사용할 필요성은 없어 보인다.