Real MySQL 5장 트랜잭션과 잠금 정리

트랜잭션

개념은 위키백과 참조

MySQL에서의 트랜잭션

논리적인 작업 셋 자체가 100% 반영되거나 반영되지 않게 하는 데에 의미가 있다.

InnoDB는 트랜잭션을 지원하지만 MyISAM이나 MEMORY에선 지원하지 않는다. MyISAM이나 MEMORY에서 다음과 같은 sql을 시작할 때 어떻게 될까?

CREATE TABLE tab_myisam(
    fdpk INT NOT NULL,
    PRIMARY KEY (fdpk)
) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUES (3);
INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);

결과는 1, 2, 3 이 저장된다. 트랜잭션이 지원되지 않기 때문에 1, 2 인서트가 성공하고 3을 넣을 때 예외가 발생해서 3만 실패한다. 이런 현상을 부분 업데이트(Partial Update)라고 부른다. 트랜잭션이 지원하는 InnoDB환경이라면 두번 째 insert문이 전체 다 실패해서 3만 들어간다.

만약 두번 째 SQL이 실패했을 때 전체 다 롤백해야 한다면, MyISAM에서는 분기문과 예외 처리문 떡칠해서 수동으로 이미 반영된 결과를 취소하는 로직을 짜내야 한다. InnoDB에서는 롤백하면 그만.

---

MySQL 엔진의 잠금

MySQL에서 잠금은 스토리지 엔진 레벨의 잠금과 MySQL 엔진 레벨의 잠금이 있다. 스토리지 엔진의 잠금은 스토리지 엔진끼리 상호 영향을 미치지 않지만, MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 준다. MySQL 엔진의 잠금은 다음과 같은 락 종류를 제공한다.

글로벌 락

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

글로벌 락은 MySQL 서버에 존재하는 모든 테이블에 대한 잠금을 걸기 때문에, 락을 거는 명령어를 실행하기 전에 처리되던 모든 명령을 마칠 때까지 대기하고 모든 명령이 마쳐지면 그때 락이 걸린다.

백업 락

8.0 버전부터 트랜잭션을 지원하는 InnoDB가 기본 스토리지 엔진으로 채택되면서 데이터 일관성을 위해 MySQL 엔진 레벨의 락을 걸 필요가 없어졌고, 백업용도의 좀 더 가벼운 락이 필요해졌다. 그래서 백업 락이 도입됐다. LOCK INSTANCE FOR BACKUP 명령어로 락을 획득할 수 있고, UNLOCK INSTANCE 명령어로 락을 해제할 수 있다.

특정 세션에서 백업 락을 획득하면 일반적인 데이터 변경은 허용되지만, 다음과 같은 변경은 허용되지 않는다.

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

테이블 락

테이블 단위로 설정되는 잠금. 명시적으로는 LOCK TABLES table_name [ READ | WRITE ] 명령으로 획득할 수 있고, UNLOCK TABLES명령으로 잠금을 해제할 수 있다.

묵시적으로는 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

네임드 락

GET_LOCK() 함수를 이용해서 임의의 문자열에 대해 잠금을 건다. 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다. 네임드 락의 경우 많은 레코드에 대해서 복잡한 요건으로 변경하는 트랜잭션에 유용하게 사용할 수 있다. 사용법은 다음과 같다.

-- // "mylock"이라는 문자열에 대해 잠금을 획득한다.
-- // 이미 잠금을 사용 중이면 2초 동안만 대기한다. (2초 이후 자동 잠금 해제)
mysql> SELECT GET_LOCK('mylock',2);
-- // "mylock"이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
mysql> SELECT IS_FREE_LOCK('mylock');
-- // "mylock"이라는 문자열에 대해 획득했던 잠금을 해제한다.
mysql> SELECT RELEASE_LOCK('mylock');
-- // 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우엔 1 아니면 NULL이나 0을 반환

메타데이터 락

데이터베이스의 객체나 구조를 변경할 경우에 획득한다. 명시적으로 획득하거나 해제하는 잠금이 아니고 RENAME TABLE ~~ 같은 명령문이 실행될 때 자동으로 획득 해제 된다.

---

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL 엔진에서 제공하는 락과 별개로 레코드 단위의 락을 별도로 제공한다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 다음과 같은 잠금을 제공한다.

레코드 락

레코드 자체만 잠그는 것을 레코드 락이라고 한다. InnoDB 스토리지 엔진은 다른 사용 DBMS와는 조금 다르게 레코드 자체가 아니라 인덱스의 레코드를 잠근다. 인덱스가 잡히지 않은 테이블이라도 PK로 형성된 클러스터링 인덱스에 잡는다.

갭 락

레코드 자체가 아니라 레코드와 인접한 레코드 사이의 간격을 잠근다. 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 목적으로 사용된다. 갭락의 범위는 해당 자료 참조.

넥스트 키 락

레코드 락과 갭 락을 합쳐놓은 형태의 잠금이다. InnoDB의 갭락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 목적이다. 바이너리 로그의 포맷이 STATEMENT 라면 REPEATABLE READ 격리 수준을 사용해야 하고 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화 되면 변경을 위해 검색하는 레코드에 넥스트 키 락 방식으로 잠금이 걸린다. 넥스트 키 락이나 갭 락으로 인해 데드락 문제가 자주 발생할 수 있는데 ROW 포맷의 바이너리 로그를 쓰면 문제가 많이 줄어든다. 8.0 버전으로 오면서 ROW 포맷이 기본 설정이 되었다.

자동 증가 락

AUTO_INCREMENT 쓸때 걸리는 락이다. 동시에 여러 인서트가 돼도 일관되게 값을 보장하기 위해 걸리는 락이다. 트랜잭션과 관계 없이 INSERT 나 REPLACE 문이 실행될 때 걸렸다가 해제된다.(이래서 트랜잭션 롤백 하더라도 auto_increment로 증가한 값이 초기화 되지 않는구나..) 명시적으로 걸거나 해제하는 방법은 없고 innodb_autoic_lock_mode 시스템 변수로 락의 작동 방식을 튜닝할 수 있다.

---

인덱스와 잠금

InnoDB의 락은 레코드 자체를 잠그는 게 아니라 인덱스를 잠근다. 변경할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다. 실제로 수정하는 레코드가 한 개라고 해도 그 한 개를 찾기 위해 key로 사용된 인덱스의 레코드 전체에 락이 걸린다는 뜻이다. 예를 들어 first_name이라는 컬럼에 인덱스가 걸려있고 first_name이 Alexander 인 사람이 250명 존재한다고 치자, 조건문에서 first_name이 Alexander 면서 last_name이 Volkanovski인 사람의 레코드를 수정하려 하면 인덱스가 잡힌 first_name이 Alexander인 레코드 250개 전체에 락이 잡힌다.

인덱스가 하나도 없다면? key로 풀 테이블 스캔이 잡히게 되고 테이블 전체에 락이 걸린다.😱 그래서 인덱스 설계가 중요하다.

---

MySQL의 격리 수준

여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정한다. 격리 수준에 따라 발생하는 문제들이 있는데 다음과 같이 정리할 수 있다.

격리 수준	DIRTY READ	NON REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 X)
SERIALIZABLE	없음	없음	없음

일반적인 온라인 서비스에선 READ COMMITTED나 REPEATABLE READ를 가장 많이 사용한다.

READ UNCOMMITTED

트랜잭션에서의 변경 내용이 커밋이나 롤백 여부에 상관없이 다른 트랜잭션에서 보인다. 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 더티 리드(Dirty Read) 현상이 발생한다. 정합성에 문제를 많이 일으킨다.

---

READ COMMITTED

온라인 서비스에서 가장 많이 채택되는 격리 수준이다. 커밋된 데이터만 읽어들이기 때문에 더티 리드가 발생하지 않는다. 다른 트랜잭션에서 변경한 데이터는 언두 영역에서 읽어오기 때문에 완전히 커밋된 데이터만 딱 읽어들인다.

하지만 NON-REPEATABLE READ라는 부정합의 문제가 있다. 이런 문제는 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수도 있다. 중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는 지를 정확히 예측할 수 있어야 한다는 것.

---

REPEATABLE READ

InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 이상의 격리 수준을 사용해야 한다. READ COMMITTED에서 발생하는 NON-REPEATABLE READ와 같은 부정합 문제가 발생하지 않는다.

READ COMMITTED처럼 언두 영역에서 데이터를 읽어오고 차이점은 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다. 모든 트랜잭션은 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드는 이 트랜잭션 번호를 가진다. 그리고 InnoDB 스토리지 엔진이 불필요하다고 생각하는 시점에 이 백업 데이터를 삭제하는데, REPEATABLE READ는 실행중인 트랜잭션 보다 이전 트랜잭션 번호를 가진 데이터만 보게 하고 그 데이터들은 삭제할 수 없게 한다. 그래서 트랜잭션이 길어지면 삭제되지 못하는 언두 데이터들이 쌓여서 MySQL 서버의 성능 문제를 야기할 수 있다.

REPEATABLE READ에도 PHANTOM READ라는 부정합 문제가 존재한다.(InnoDB에는 발생 X) SELECT ~ FOR UPDATE 구문처럼 잠금을 거는 경우에 발생하는데, 언두 레코드에는 잠금을 걸 수 없기 때문이다. 그래서 SELECT ~ FOR UPDATE나 SELECT ~ LOCK IN SHARE MODE 구문으로 조회하는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오기 때문에 발생한다.

---

SERIALIZABLE

가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 구리다. InnoDB에서 순수 읽기 작업은 아무런 락 없이 동작하는데 이 격리 수준에선 모든 읽기 작업에서 S-LOCK을 획득한다. S-LOCK이 걸린 데이터는 다른 트랜잭션이 수정할 수 없으니 PHANTOM READ 문제가 발생하지 않는다. 하지만 InnoDB에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ 문제가 발생하지 않는다. 레코드를 수정하거나 삽입하려는 경우 인접 범위에 락이 잡히기 때문에 그 범위에 레코드를 삽입하거나 수정하려면 대기해야 하기 때문에 그렇다.

사실 이 격리 수준은 여러 사용자의 요청을 동시 처리하는 어플리케이션의 경우 사용할 일이 없다고 한다. 동시 처리 성능이 너무 떨어지기 때문에…