Real Mysql 8.0 - 5. 트랜잭션과 잠금

트랜잭션

• 트랜잭션은 작업의 묶음으로 하나의 작업으로 처리하여 데이터 정합성을 위해 사용된다.

• MyISAM, MEMORY 엔진은 트랜잭션을 지원하지 않고, InnoDB는 트랜잭션을 지원한다.

• 트랜잭션을 사용하는 것은 데이터베이스 커넥션, 리두 로그, 언두 로그 등 많은 자원을 필요로 하기 때문에 필요한 곳에만 사용하고, 작은 단위로 사용하는 것이 좋다.

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨이 따로 존재하고, MySQL 엔진 레벨의 락이 상위 락으로서 동작한다.

글로벌 락

• 가장 큰 범위의 락으로, 모든 DDL, DML 이 글로벌 락이 해제되기까지 기다려야 한다.

• FLUSH TABLES WITH READ LOCK 명령어로 수행할 수 있으며, 실행중인 쿼리가 있다면 모든 테이블을 플러시해야하기 때문에 쿼리를 다 마칠때까지 기다린다.

• 백업을 위해 사용되었었는데, 실시간 서비스용 데이터베이스에서는 이 락을 사용할 경우 성능에 큰 영향을 미치기 때문에 MySQL 8.0 버전부터는 백업 락이 도입되었다.

백업 락

• 글로벌 락보다는 조금 가벼운 락으로, DDL 은 허용되지 않지만 DML은 허용한다.

• 백업은 주로 레플리카 DB에서 일어나며, XtraBackup 이나 Enterprise Backup 과 같은 툴들을 사용하는데 DDL이 발생할 경우 6~7 시간 백업을 하고 있었어도 바로 실패해버린다. 이러한 문제를 막기 위해 백업 락을 사용하며, DDL이 들어올 경우 레플리카에서 백업이 완료될때까지 복제를 멈추는 방식으로 동작한다.

테이블 락

• 테이블에 대한 접근을 제한하는 락으로, 묵시적 또는 명시적으로 사용될 수 있다.

• 명시적으로 테이블 락을 획득해야 하는 경우는 거의 없다.

• MyISAM, MEMORY 엔진은 데이터 변경 시 묵시적으로 테이블 락을 획득하고 변경 후 해제하는 방식으로 동작하며, InnoDB는 레코드 단위 락을 지원하기 때문에 스키마 변경과 같은 DDL이 아닌 이상 테이블 락을 사용하지 않는다.

네임드 락

• 테이블, 레코드 등에 거는 락이 아니라, 특정한 문자열에 대한 락이다.

• 많은 트랜잭션을 수행하는 배치 프로그램들을 동시에 실행시키면 데드락이 자주 발생하는데, 네임드 락을 통해 순서를 제어해서 데드락을 피하는 방법을 사용할 수 있다. 많이 사용되는 기능은 아니다.

메타데이터 락

• 테이블, 뷰와 같은 데이터베이스 객체의 이름이나 구조를 변경할 때 사용되는 락

• 서비스 중인 데이터베이스에서 새로운 데이터를 가진 temp_table 을 현재 사용중인 using_table 대신 사용하고 싶다고 하자. using_table 은 backup_table 로 변경하고 temp_table 을 using_table 로 변경하고 싶을 때, 메타데이터 락이 없다면 using_table 이 일시적으로 존재하지 않아 해당 테이블에 대한 쿼리 요청에서 에러가 발생할 것이다. 테이블 이름을 변경할 때, 쿼리 요청에 대한 일시적인 에러를 방지하기 위해 메타데이터 락이 사용된다.

InnoDB 스토리지 엔진 잠금

InnoDB 는 위에서 설명했듯이 레코드 기반의 락을 갖고 있다. 자세한 동작에 대해 알아보자.

레코드 락

• 레코드 락은 실제 레코드 자체를 잠그는 것이 아니라, 인덱스의 레코드를 잠근다.

• 인덱스를 설정하지 않은 테이블이더라도, 자동으로 생성되는 클러스터 인덱스를 통해 인덱스를 잠그는 방식을 사용한다.

• 보조 인덱스를 통한 변경 작업은 뒤에서 살펴볼 갭 락, 넥스트 키 락을 사용하지만, 프라이머리 키나 유니크 인덱스에 대한 변경 작업은 레코드 락을 사용한다.

갭 락

• 레코드를 잠그는 것이 아니라, 레코드와 인접한 레코드 사이의 간격만을 잠근다.

• 레코드와 레코드 사이에 새로운 레코드가 생성되는 것을 제어한다.

넥스트 키 락

• 레코드 락과 갭 락을 합쳐놓은 형태로, 레코드에 대한 락과 인접 레코드 사이 간격에 대한 락을 합쳐서 하나의 락으로 잠근다.

• innodb_locks_unsafe_for_binlog 설정을 비활성화 할 경우에 사용되며, 갭 락 대신 데이터 변경 시 넥스트 키 락을 사용한다. 바이너리 로그의 안정성을 보장하기 위해 더 큰 범위의 락을 사용한다.

참고)

자동 증가 락

• AUTO_INCREMENT 를 사용할 경우 여러 레코드를 동시에 INSERT 요청할 때 해당 컬럼의 일관성을 보장하기 위해 사용된다.

• 테이블에 단 하나만 존재하며, INSERT나 REPLACE 요청이 들어올 때 잠깐 AUTO_INCREMENT 컬럼의 값을 가져오기 위해 사용되고 가져온 뒤에 즉시 반납하는 방식으로 동작한다.

• INSERT ... SELECT 와 같이 대량의 데이터를 insert 할 경우 매번 락을 걸면 성능이 좋지 않으므로 한 번에 여러 개의 auto_increment 값을 가져와 사용한다. 마지막쯤의 insert 가 수행될 때는 가져온 값들이 모두 사용되지 못하고 일부 폐기 될 수 있다. 이런 방식으로 인해 대량의 insert 이후에 insert 를 수행할 경우 auto_increment 값이 연속적이지 않을 수 있다.

• innodb_autoinc_lock_mode 변수를 0으로 설정할 경우 위의 자동 증가 락이 사용된다. (MySQL 5.0 버전 이전 기본값)

• innodb_autoinc_lock_mode 변수를 1으로 설정할 경우 INSERT 문으로 들어오는 데이터의 수를 정확히 알 수 있을 경우 락을 사용하지 않고 경량화된 뮤텍스를 사용해 더 빠르게 처리한다. 데이터의 수를 예측할 수 없는 경우 여전히 자동 증가 락을 사용한다. 뮤텍스를 사용할 경우 auto_increment 값은 항상 연속적임이 보장된다. (MySQL 5.7 버전 이전 기본값)

• innodb_autoinc_lock_mode 변수를 2로 설정할 경우 자동 증가 락을 절대 사용하지 않고 뮤텍스만 사용한다. STATEMENT 모드의 바이너리 로그를 작성할 때는 정합성 문제가 발생할 수 있다. (MySQL 8.0 버전 이후 기본값)

인덱스와 잠금

• 위에서 레코드 락을 레코드 자체에 거는 것이 아니라 인덱스에 건다고 설명했다. 데이터 변경 시에 필요한 모든 인덱스에 대해 락을 거는 방식으로 동작한다.

• 만약 인덱스 조건 없이 update 문을 수행한다면, 테이블을 풀 스캔하면서 모든 테이블의 데이터에 락이 걸리게 된다. InnoDB에서 인덱스 설계가 중요한 이유이다.

MySQL의 격리 수준

READ UNCOMMITTED

• 가장 낮은 격리 수준으로 dirty read 문제가 발생한다.

• dirty read 문제란 다른 트랜잭션에서 데이터를 변경하고 커밋이 되지 않아 실제로 커밋해 저장이 될지, 롤백해 저장이 되지 않을지 모르는 dirty data 를 읽어와 사용하는 문제이다.

• 격리 수준 중 가장 좋은 동시 처리량을 제공하지만 정합성에 심각한 문제가 있어 잘 사용하지 않는다.

READ COMMITTED

• 커밋한 데이터만 읽을 수 있는 격리 수준이다. 따라서 dirty read 문제는 발생하지 않는다.

• 언두 로그를 통해 다른 트랜잭션에서 변경중인 데이터일지라도 변경되기 이전 버전의 데이터를 사용할 수 있다.

• 트랜잭션 내에서 읽어온 A 데이터를 다른 트랜잭션이 변경 후 커밋하면, 다시 읽어왔을 때 변경된 데이터를 가져오게 된다. 이러한 non-repeatable read 문제가 발생한다.

• non-repeatable read 문제는 웹 서비스 등에서는 문제가 되지 않아 온라인 서비스에서 가장 많이 사용되는 격리 수준이다. 하지만, 금전적인 데이터 등 정합성이 중요한 경우 non-repeatable read 문제는 문제가 될 수 있다.

REPEATABLE READ

• 위에서 설명한 non-repeatable read 문제가 일어나지 않는 격리 수준이다.

• STATEMENT 방식의 바이너리 로그를 사용할 경우 격리 수준이 최소 REPEATABLE READ 여야 한다.

• READ COMMITTED 격리 수준에서 언두 로그를 사용해 다른 트랜잭션이 변경한 데이터의 다른 버전을 보여주듯이 한 번 읽은 데이터는 변경이 일어나도 언두 로그에서 읽어올 수 있도록 하면 쉽게 구현될 수 있다. 어떤 버전을 보여줘야하는지 정도의 차이만 있을 뿐이다.

• 내부적으로는 트랜잭션이 순차적으로 증가하는 고유한 트랜잭션 번호를 갖고 있어, 자신의 트랜잭션 번호보다 이후의 트랜잭션 번호를 가진 트랜잭션이 변경한 데이터는 사용하지 않고 언두 로그에서 가져오는 식으로 동작한다.

• 구간의 데이터를 가져와 사용하는 경우, 해당 구간에 데이터가 삽입되거나 없어졌을 때, 다시 읽을 경우 기존의 데이터와 달라지는 phantom read 문제가 대부분의 repeatable read 격리 수준의 DB에서 나타난다. 하지만 InnoDB는 트랜잭션 번호를 통해 자신보다 이후에 시작된 트랜잭션의 데이터 변경은 무시하기 때문에 phantom read 문제가 발생하지 않는다. 그럼에도 select ... for update 문으로 데이터를 가져오는 경우에는 언두 레코드에 잠금을 걸 수 없어 현재 테이블 데이터를 가져와 사용하기 때문에 예외적으로 phantom read 문제가 발생한다.

SERIALIZABLE

• 가장 엄격한 격리 수준으로, 모든 레코드에 대한 읽기 작업에도 잠금을 걸어 phantom read 문제가 발생하지 않는다.

• 그러나 InnoDB의 경우 REPEATABLE READ 수준으로도 phantom read 문제가 발생하지 않기 때문에 사용할 필요가 없다.