05. 트랜잭션과 잠금 (Transaction and Lock)

5장: 트랜잭션과 잠금 (Transaction and Lock) 요약

트랜잭션과 잠금은 MySQL의 동시성(Concurrency)과 데이터 정합성(Consistency)을 유지하는 핵심적인 기능입니다.

잠금은 동시성 제어하기 위한 기능이고, 트랜잭션은 데이터 정합성을 보장하기 위한 기능이다.

5.1 트랜잭션 (Transaction)

원리:

• 트랜잭션은 작업의 완결성을 보장하는 기능입니다. 여러 개의 쿼리를 포함하는 하나의 논리적 작업 묶음이 전부 성공하거나(COMMIT), 실패할 경우 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않도록 원자성(Atomicity)을 보장합니다.
• MySQL 서버에서는 InnoDB 스토리지 엔진이 트랜잭션을 지원하지만, MyISAM이나 MEMORY 같은 구형 엔진은 트랜잭션을 지원하지 않습니다.
• 실무적 중요성: 트랜잭션이 지원되지 않는 MyISAM 테이블에 INSERT 작업 실패 시, 부분 업데이트 현상이 발생하여 데이터 정합성이 깨지거나, 실패한 레코드를 삭제하기 위한 추가 복구 작업이 필요할 수 있습니다. InnoDB를 사용하면 트랜잭션 내에서 COMMIT 또는 ROLLBACK을 통해 이러한 문제를 방지하고 코드를 간결하게 구현할 수 있습니다.

실무 관점 주의사항:

• 애플리케이션 코드를 작성할 때 트랜잭션의 범위를 최소화하는 것이 좋습니다. 트랜잭션의 범위가 길어지면 해당 자원을 사용하는 다른 쿼리가 대기하는 시간이 길어져 동시성 및 성능 저하를 유발할 수 있습니다.
• 특히, 네트워크 작업이나 파일 처리 등 DBMS 외부 작업이 트랜잭션 범위에 포함되지 않도록 주의해야 합니다. 이러한 작업들은 트랜잭션을 불필요하게 장시간 활성화된 상태로 유지하게 만들어 위험을 높입니다.

5.2 MySQL 엔진의 잠금 (MySQL Engine Locks)

MySQL의 잠금은 크게 MySQL 엔진 레벨 잠금과 스토리지 엔진 레벨 잠금으로 구분됩니다. 엔진 레벨 잠금은 스토리지 엔진의 잠금과는 상호 영향을 미치지 않습니다.

MySQL 엔진 레벨에서 사용하는 주요 잠금은 다음과 같습니다:

1. 글로벌 락 (Global Lock) (5.2.1):
   • FLUSH TABLES WITH READ LOCK 명령으로 획득하며, MySQL 서버 전체에 영향을 미칩니다.
   • 백업을 수행할 때 주로 사용되었으며, DML 문장(INSERT, UPDATE, DELETE)이나 DDL 문장(CREATE, DROP 등)의 실행을 막습니다.
   • 실무적 관점: MySQL 8.0부터는 Xtrabackup이나 Enterprise Backup 같은 백업 도구들이 안정적인 백업 실행을 위해 글로벌 락을 획득하지 않고도 백업할 수 있도록 개선되었습니다.
2. 테이블 락 (Table Lock) (5.2.2):
   • LOCK TABLES table_name READ | WRITE 명령어를 사용하여 명시적으로 획득하거나, DDL 쿼리를 실행할 때 묵시적으로 획득될 수 있습니다.
   • InnoDB 테이블의 경우 레코드 수준 잠금을 사용하므로 DML 쿼리에는 테이블 락이 무시되지만, 스키마를 변경하는 DDL 쿼리에는 영향을 미칩니다.
3. 네임드 락 (Named Lock) (5.2.3):
   • GET_LOCK() 함수를 이용해 임의의 문자열에 잠금을 설정하는 기능입니다.
   • 데이터베이스 객체(레코드, 테이블) 외의 애플리케이션 로직에서 필요한 동기화 처리에 유용하게 사용됩니다.
4. 메타데이터 락 (Metadata Lock) (5.2.4):
   • 테이블이나 뷰와 같은 데이터베이스 객체의 이름이나 구조를 변경하는 경우(DDL)에 획득됩니다.
   • 실무적 관점: Online DDL을 지원하지 않는 경우, DDL이 실행되는 동안 해당 객체에 대한 메타데이터 락이 유지되면서 INSERT, UPDATE, DELETE 같은 DML 작업이 실행되지 못하고 장시간 대기하는 문제가 발생할 수 있습니다.

5.3 InnoDB 스토리지 엔진 잠금 (InnoDB Storage Engine Locks)

InnoDB는 레코드 기반의 잠금 방식을 채택하여 MyISAM보다 훨씬 뛰어난 동시성 처리 능력을 제공합니다.

원리:

• InnoDB 잠금은 기본적으로 레코드 수준 잠금이며, 레코드뿐만 아니라 레코드와 레코드 사이의 간격인 갭(GAP)에도 잠금을 걸 수 있습니다.
• 잠금 모니터링: MySQL 8.0부터는 performance_schema의 data_locks와 data_lock_waits 테이블을 조회하여 현재 트랜잭션이 어떤 잠금을 대기하고 있고 어떤 잠금을 가지고 있는지 확인하여 잠금 대기 순서를 파악할 수 있습니다.

주요 잠금 유형 (5.3.1):

• 레코드 락 (Record Lock): 인덱스 레코드 자체에 걸리는 잠금입니다.
• 갭 락 (Gap Lock): 레코드 자체가 아닌 레코드와 레코드 사이의 간격에 걸리는 잠금입니다. 새로운 레코드 삽입(INSERT)을 제어하는 역할을 합니다.
• 넥스트 키 락 (Next Key Lock): 레코드 락과 갭 락을 합친 형태의 잠금이며, InnoDB의 기본 격리 수준인 REPEATABLE READ에서 사용되어 팬텀 리드(Phantom Read) 현상을 방지합니다.
• AUTO_INCREMENT 락 (5.3.1.4): AUTO_INCREMENT 속성을 가진 칼럼에 INSERT 시 순차적인 값을 보장하기 위해 사용되는 잠금입니다. 이 잠금의 동작 방식은 innodb_autoinc_lock_mode 시스템 변수를 통해 조정 가능합니다.

인덱스와 잠금의 관계 (5.3.2):

• InnoDB의 잠금은 레코드 자체가 아닌 인덱스 레코드를 잠그는 방식으로 처리됩니다.
• 실무적 관점: 쿼리가 인덱스를 사용하지 못하거나, 인덱스가 커버링하지 못하는 경우, InnoDB는 쿼리 조건을 만족시키기 위해 훨씬 더 많은 레코드(혹은 테이블 전체)를 잠글 수 있습니다. 따라서 적절하고 효율적인 인덱스 설계는 불필요한 잠금 경합을 최소화하는 데 매우 중요합니다.

5.4 MySQL의 격리 수준 (Isolation Levels)

원리:

• 격리 수준(Isolation Level)은 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션이 변경하는 데이터를 어느 정도까지 허용하고 볼 수 있는지를 결정하는 규칙입니다.
• 격리 수준이 낮을수록 동시성이 높아지지만, 비정합성 문제(Dirty Read, Non-Repeatable Read, Phantom Read)가 발생할 가능성이 높아집니다.

MySQL이 제공하는 4가지 격리 수준은 다음과 같습니다:

1. READ UNCOMMITTED (5.4.1):
   • 커밋되지 않은 다른 트랜잭션의 변경 내용을 읽을 수 있습니다 (Dirty Read 발생).
   • 실무적 관점: 데이터 정합성 문제가 심각해질 수 있으므로, 최소한 READ COMMITTED 이상의 격리 수준을 사용하는 것이 권장됩니다.
2. READ COMMITTED (5.4.2):
   • 커밋이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있습니다 (Dirty Read 방지).
   • 하지만 같은 트랜잭션 내에서 같은 쿼리를 두 번 실행했을 때, 다른 트랜잭션의 커밋으로 인해 결과가 달라질 수 있습니다 (Non-Repeatable Read 발생 가능).
3. REPEATABLE READ (5.4.3):
   • InnoDB 스토리지 엔진의 기본 격리 수준입니다.
   • Dirty Read와 Non-Repeatable Read가 발생하지 않습니다.
   • MVCC (Multi Version Concurrency Control) 기술을 사용하여 트랜잭션이 시작된 시점의 데이터를 언두 로그(Undo Log) 영역에서 복사하여 보존함으로써, 트랜잭션이 끝날 때까지 항상 동일한 데이터를 조회하는 것을 보장합니다.
   • SQL-92 표준에서는 Phantom Read가 발생할 수 있으나, InnoDB는 넥스트 키 락(갭 락 포함)을 사용하여 일반적인 상황에서 Phantom Read를 방지합니다. (MVCC 덕에 REPEATABLE READ에서도 Phantom Read를 대부분 방지)
4. SERIALIZABLE (5.4.4):
   • 가장 엄격한 격리 수준으로, 모든 SELECT 쿼리에 공유 잠금을 추가하여 모든 비정합성 문제가 발생하지 않도록 합니다.
   • 실무적 관점: 동시성이 크게 떨어지므로, 일반적으로 온라인 서비스에서는 잘 사용되지 않습니다. 온라인 서비스에서는 READ COMMITTED나 REPEATABLE READ 중 하나를 선택하여 사용합니다.