MySQL 트랜잭션, 잠금

Photo by Andrew Teoh on Unsplash

MySQL 트랜잭션, 잠금

Real MySQL 8.0 스터디-7

💡
이 글은 Real MySQL 8.0공식 문서를 읽고 개인적으로 정리한 내용입니다.

트랜잭션과 잠금(Lock)은 서로 비슷한 개념 같지만 사실 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이고, 잠금은 동시성을 제어하기 위한 기능입니다.

1. 트랜잭션

(이미지 출처: https://fauna.com/blog/database-transaction)

트랜잭션의 수명 주기는 다음과 같습니다.

  1. Active: 트랜잭션을 실행하는 동안 첫 번째 상태입니다. 트랜잭션은 명령(읽기 또는 쓰기 작업)이 수행되는 한 활성 상태입니다.

  2. Partially Committed: 이 상태에서는 변경 사항이 실행되었지만 데이터베이스가 디스크에서 변경 사항을 아직 커밋하지 않았습니다. 이 상태에서는 메모리 버퍼에 데이터가 저장되고 버퍼가 아직 디스크에 기록되지 않았습니다.

  3. Committed: 이 상태에서는 모든 트랜잭션 업데이트가 데이터베이스에 영구적으로 저장되므로 이 시점 이후에는 트랜잭션을 롤백할 수 없습니다.

  4. Failed: 트랜잭션이 실패하거나 활성 상태 또는 부분적으로 커밋된 상태에서 중단된 경우 실패 상태가 됩니다.

  5. Terminated: 커밋 또는 중단 상태 이후의 마지막이자 최종 트랜잭션 상태이며, 데이터베이스 트랜잭션 수명 주기의 종료를 나타냅니다.

DBMS의 커넥션과 마찬가지로 꼭 필요한 최소한의 코드에만 적용하는 것이 좋습니다. 프로그램의 코드에서 라인 수는 한두 줄이라고 하더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 합니다. 이런 실수로 인해 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 발생합니다.

2. MySQL 엔진 레벨 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨MySQL 엔진 레벨로 나눌 수 있습니다.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치고, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않습니다.

2.1. 글로벌 락(Global Read Lock)

공식 문서에서는 정확히 Global Read Lock으로 설명하고 있습니다. (책에서 Global Lock이라고 설명하고 있는데, 이렇게 가끔 책에서 독자적으로 쓰는 용어들 때문에 저같은 초보들이 문서와 같이 보게될 때 혼란을 일으킵니다. 😵‍💫)

한 세션에서 글로벌 락을 획득하면 해제될 때까지 조회를 제외한 대부분의 명령이 대기 상태가 됩니다. 글로벌 락은 MySQL 서버 전체에 영향을 미칩니다.

여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 사용하면 됩니다.

FLUSH TABLES WITH READ LOCK으로 획득 후, UNLOCK TABLES로 해제합니다.

2.2. 테이블 락(Table Lock)

테이블 락은 개별 단위로 설정되는 잠금입니다. 명시적 또는 묵시적으로 획득할 수 있습니다.

명시적으로 LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블의 락을 획득하고, UNLOCK TABLES로 해제합니다. 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문에 특별한 상황이 아니면 사용할 필요가 거의 없습니다.

묵시적으로 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생합니다. InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않습니다. 정확히는 InnoDB 테이블에도 테이블 락이 설정되지만, 대부분의 DML 쿼리에서는 무시되고 DDL의 경우에만 영향을 미칩니다.

2.3. 네임드 락(Named Lock)

네임드 락은 임의의 문자열에 대해 잠금을 설정할 수 있습니다.

많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있습니다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되는데, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 데드락 문제를 해결할 수 있습니다.

2.4. 메타데이터 락(Metadata Lock)

메타데이터 락은 데이터베이스 객체(대표적으로 테이블이나 뷰) 등의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 명시적으로 획득하거나 해제할 수 없고 자동으로 획득하는 잠금입니다.

3. InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 내부에서 레코드 기반의 잠금 방식을 탑재하고 있습니다.

MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있습니다.

3.1. 레코드 락(Record Lock)

다른 DBMS의 레코드 락(레코드 자체를 잠금)과 다르게 InnoDB에서는 인덱스의 레코드를 잠급니다. 인덱스가 하나도 없더라도 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정합니다.

3.2. 갭 락(Gap Lock)

갭 락 또한 다른 DBMS와 다르게 레코드 자체가 아니라, 레코드와 바로 인접한 레코드 사이의 간격만을 잠급니다. 갭 락의 역할은 레코드와 레코드 사이의 간력에 새로운 레코드가 생성(INSERT) 되는 것을 제어하는 것입니다.

3.3. 넥스트 키 락(Next Key Lock)

넥스트 키 락은 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금입니다.

InnoDB는 테이블 인덱스를 검색하거나 스캔할 때, 발견되는 인덱스 레코드에 공유 또는 배타적 잠금을 설정하는 방식으로 레코드 락을 수행합니다.

바이너리 로그가 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적입니다.

3.4. 자동 증가 락(AUTO-INC Lock)

MySQL에서는 자동 증가하는 숫자를 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공합니다. 이 속성이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 내부적으로 자동 증가 락이라고 하는 테이블 수준의 잠금을 사용합니다.

명시적으로 획득하고 해제하는 방법은 없습니다. 아주 짧은 시간동안 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않습니다.

자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유는 자동 증가 락을 최소화하기 위함입니다.

근데... 자동 증가 값을 감소시키면 왜 안 될까요? 🤔

만약 자동 증가 값이 감소할 수 있다면 삽입 작업이 실패하고 롤백될 때 자동 증가 값을 이전 상태로 다시 조정해야 함을 의미합니다.

이전 상태로 되돌리는 조정은 auto-increment counter에 대한 잠금을 다시 획득해야 하므로 높은 동시성 환경에서 잠재적으로 잠금 경합 및 성능 문제로 이어질 수 있습니다.

설계상 MySQL은 자동 증가 값이 일단 증가하면 절대 감소하지 않음으로써 이러한 잠금 경합을 피합니다. 따라서 삽입 실패 또는 행 삭제가 발생한 경우 자동 증가 값이 롤백되지 않습니다. 대신 후속 삽입 작업에 대해 계속 증가하여 각 삽입에 대해 auto-increment counter에 대한 잠금을 다시 획득할 필요 없이 고유한 값을 보장합니다.

이 접근 방식은 잠금 오버헤드를 최소화하여 성능과 동시성을 우선시하는 최적화입니다. 그러나 테이블에서 행을 삭제하면 삭제된 행의 자동 증가 값이 재사용되지 않아 자동 증가 값 시퀀스에 간격이 생길 수 있다는 점에 유의해야 합니다. 연속 시퀀스를 유지하는 것이 응용 프로그램에 필수적인 경우 코드에서 명시적으로 처리하거나 다른 메커니즘을 사용하여 자동 증가 값을 관리해야 합니다.

하지만 아래와 같은 의문을 품는 분들(저 같은)이 있을 것 같습니다. 🤔

INSERT 실패 시에 락을 걸고 UNDO 영역에 있던 값으로 롤백 하면 되지 않을까? 잠금으로 인한 성능 저하가 있다는 것은 충분히 이해했지만 그런 Trade-off 방식을 원할 수도 있는데 왜 시스템 변수 설정으로라도 변경 못하게 해놓았을까?

이 제약 때문에 채번을 자동으로 다시 하고 싶으면 테이블 자체를 날려버려야 한다. 왜 시스템 변수로 선택지를 주지 않는지 궁금하다.

가장 중요한 점은 자동 증가는 카운트 하기 위해 존재하는 기능이 아닙니다.

현재 auto-increment counter value가 10이라고 해서 10개의 행이 있을 것을 보장할 필요가 없다는 것이죠. 말 그대로 그냥 "증가하는 값"일 뿐입니다. 카운트를 하고 싶다면 애플리케이션 코드 내에서 증가 값을 직접 관리하거나, 아예 시퀀스 테이블을 따로 두어서 관리합시다.


🎯정리

  • 트랜잭션은 데이터의 정합성을 보장하기 위한 기능입니다.

  • MySQL 서버의 성능을 위해 트랜잭션은 꼭 필요한 코드에만 적용하는 것이 좋습니다.

  • 잠금은 동시성을 제어하기 위한 기능입니다.

  • MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치고, 스토리지 엔진 레벨의 잠금은 스토리지끼리 서로 영향을 미치지 않습니다.


🔖참고