1. 개요
트랜잭션은 데이터베이스 시스템에서 작업의 완전성을 보장하는 핵심적인 기능이다. 이는 한 작업 단위에 대해 모든 작업이 완벽하게 처리되거나 아니면 전혀 처리되지 않도록 보장하는 것을 의미한다. 즉, 트랜잭션은 논리적인 작업 집합을 완전히 수행하거나, 그렇지 못할 경우 롤백(작업 취소)을 통해 데이터의 부분적 적용을 방지한다. 이러한 특성으로 인해 트랜잭션은 데이터베이스의 데이터 정합성을 유지하는 데 필수적인 역할을 수행한다.
잠금(LOCK)은 트랜잭션과 연관된 개념으로, 동시성 제어를 위해 사용된다. 데이터베이스에서 여러 커넥션이 동시에 하나의 데이터를 변경하려고 할 때, 잠금 없이는 최종 데이터의 상태를 예측하기 어렵다. 이러한 상황을 방지하기 위해 잠금은 한 시점에 단 하나의 커넥션만이 특정 데이터를 변경할 수 있도록 제어한다. 잠금은 데이터의 일관성과 무결성을 유지하면서 여러 사용자가 동시에 데이터베이스에 접근할 수 있도록 관리하는 중요한 메커니즘이다.
2. 트랜잭션
트랜잭션은 단일 또는 다중 쿼리를 포함한 논리적 작업 단위로서, 모든 작업이 완전히 적용되거나 전혀 적용되지 않도록 보장하는 데 중요한 역할을 한다. 이는 데이터의 일관성과 무결성을 유지하는 데 필수적이다.
InnoDB 스토리지 엔진은 트랜잭션을 지원하여, 작업의 완전성을 보장한다. 반면, MyISAM이나 MEMORY와 같은 트랜잭션을 지원하지 않는 스토리지 엔진들은 데이터 정합성을 유지하는 데 어려움을 만들어 낼 수 있다. 이러한 차이를 MyISAM과 InnoDB의 테이블을 사용하는 예시를 통해 확인할 수 있다.
MyISAM 테이블에서는, 예를 들어 PRIMARY KEY 중복으로 인한 에러 발생 시, 에러가 발생하기 전까지의 데이터는 저장되지만 이후 데이터는 저장되지 않는다. 이를 'Partial Update'라고 하며, 이로 인해 데이터 정합성에 문제가 발생할 수 있다. MyISAM에서는 프로그램 코드 내에 추가적인 데이터 검증 및 클렌징 로직이 필요할 수 있다.
반면, InnoDB에서는 트랜잭션을 활용하여 간단한 로직으로 데이터의 일관성을 유지할 수 있다. START TRANSACTION과 COMMIT, ROLLBACK을 활용하여 트랜잭션을 관리함으로써, 작업 중 에러가 발생하더라도 모든 변경 사항을 취소할 수 있다.
하지만 트랜잭션의 범위는 최소화하는 것이 좋다. 예를 들어, 게시판에서 게시물 작성과 관련된 일련의 처리 로직에서는 실제로 데이터베이스에 변경을 가하는 부분만 트랜잭션에 포함시키는 것이 효율적이다. DB와 관련 없는 로직(예: 이메일 발송)은 트랜잭션 밖에서 처리하는 것이 바람직하다. 이렇게 함으로써 트랜잭션으로 인한 성능 저하나 잠금으로 인한 대기 시간을 최소화할 수 있다.
따라서 애플리케이션 코드 설계 시 데이터베이스 커넥션을 유지하는 범위와 트랜잭션 활성화 범위를 신중하게 고려해야 한다. 이는 프로그램의 성능과 안정성을 향상시키는 데 중요한 요소가 된다.
3. 잠금(Lock)
MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 해당 엔진에만 국한된다.
글로벌 락(Global Lock)
- 글로벌 락은
FLUSH TABLES WITH READ LOCK명령으로 획득 가능하며, MySQL 서버 전체에 영향을 미친다. 이 잠금은 SELECT를 제외한 대부분의 DDL과 DML 문장을 대기시킨다. MyISAM이나 MEMORY 테이블의 일관된 백업을 위해 주로 사용된다.
백업 락(Backup Lock)
- MySQL 8.0에서는 InnoDB 스토리지 엔진이 기본이 되면서, Xtrabackup이나 Enterprise Backup 같은 툴을 위한 새로운 백업 잠금 유형이 도입되었다. 이 잠금은
LOCK INSTANCE FOR BACKUP명령으로 설정할 수 있으며, 데이터베이스 객체의 변경을 방지하면서도 DML 작업을 허용한다.
테이블 락(Table Lock)
- 테이블 락은 특정 테이블에 설정되며,
LOCK TABLES명령을 통해 명시적으로 설정할 수 있다. MyISAM과 InnoDB 모두 사용할 수 있지만, 일반적으로 애플리케이션 수준에서는 자주 사용되지 않는다. MyISAM이나 MEMORY 테이블에서 데이터 변경 시 묵시적으로 설정되며,UNLOCK TABLES로 해제할 수 있다.
네임드 락(Named Lock)
- 네임드 락은
GET_LOCK()함수를 사용하여 설정하며, 임의의 문자열을 기반으로 잠금을 획득하고 해제한다. 주로 분산 락 구현에 사용되며, Redis나 ZooKeeper 같은 다른 솔루션 대신 MySQL 내에서 분산 락을 구현할 때 유용하다.
메타데이터 락(Metadata Lock)
- 메타데이터 락은 데이터베이스 객체의 구조를 변경할 때 획득되는 잠금으로, 명시적으로 설정할 수는 없다. 트랜잭션을 시작한 후 데이터를 조회하면 다른 세션에서의 스키마 변경을 대기하는 형태로 작동한다.
RENAME TABLE과 같은 명령어를 사용할 때 자동으로 설정된다.
이러한 잠금 메커니즘은 MySQL의 데이터 무결성과 동시성 제어에 중요한 역할을 한다. 애플리케이션 개발자나 데이터베이스 관리자는 이러한 다양한 잠금 수준을 이해하고 적절히 활용하여 시스템의 성능과 안정성을 최적화할 수 있다.
4. InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진에서 잠금 메커니즘은 인덱스 기반으로 작동한다. 즉, InnoDB는 레코드 자체를 직접 잠그는 것이 아니라, 해당 레코드를 찾는 데 사용된 인덱스를 잠그는 방식으로 처리한다. 이러한 특성 때문에, MySQL에서 인덱스 설계는 성능과 락킹(잠금) 측면에서 매우 중요하다.
예를 들어, employees 테이블에서 first_name 필드에 대한 인덱스 ix_firstname가 존재한다고 가정해보자. 여기서 first_name이 'Georgi'인 사람들의 수를 세는 쿼리는 256건의 결과를 반환한다.
이제, first_name='Georgi'이면서 last_name='Klassen'인 사람의 hire_date를 업데이트하는 쿼리를 실행할 경우, InnoDB는 first_name='Georgi' 인 모든 레코드에 대해 잠금을 걸게 된다. first_name 필드에는 인덱스가 있지만, last_name 필드에는 인덱스가 없기 때문에, InnoDB는 first_name='Georgi' 조건에 맞는 256건의 레코드 전체를 잠금 상태로 만든다.
만약 first_name 또는 last_name 필드 어느 쪽에도 인덱스가 존재하지 않는다면, InnoDB는 테이블의 모든 레코드를 스캔하게 되고, 그 결과 1개의 업데이트 쿼리를 실행하기 위해 전체 테이블의 레코드에 잠금을 걸게 된다. 이러한 상황은 성능 저하 및 잠금 경합으로 이어질 수 있으므로, InnoDB를 사용할 때는 적절한 인덱스 설계가 필수적이다.
결론적으로, InnoDB에서의 인덱스 설계는 단순히 검색 성능 향상뿐만 아니라 락킹 행태와 관련된 문제를 최소화하는 데도 중요한 역할을 한다. 이는 특히 동시성이 높은 환경에서 데이터베이스의 성능과 안정성에 큰 영향을 미칠 수 있다.
5. MySQL 격리 수준
트랜잭션의 격리 수준은 다중 트랜잭션 환경에서 데이터의 일관성을 유지하는 데 중요한 역할을 한다. MySQL에서 지원하는 네 가지 격리 수준과 이들이 초래할 수 있는 부정합 현상에 대해 자세히 살펴보겠다.
1. READ UNCOMMITTED
- 정의: 다른 트랜잭션이
아직 커밋되지 않은 변경사항을 볼 수 있는 격리 수준이다. - 부정합 현상:
'더티 리드(Dirty Read)'현상이 발생할 수 있다. 예를 들어, 트랜잭션 A가 데이터를 변경하는 중이지만 아직 커밋하지 않았을 때, 트랜잭션 B가 해당 변경 사항을 볼 수 있다. 이후 트랜잭션 A가 롤백을 하면, 트랜잭션 B는 실제로는 존재하지 않는 데이터를 읽은 것이 된다.
2. READ COMMITTED
- 정의:
커밋된 변경사항만 다른 트랜잭션에서 볼 수 있는 격리 수준이다. - 부정합 현상:
'Non-repeatable read'문제가 발생할 수 있다. 예를 들어, 트랜잭션 A가 데이터를 읽고 있는 동안, 트랜잭션 B가 해당 데이터를 변경하고 커밋한다면, 트랜잭션 A가 동일한 데이터를 다시 읽었을 때 다른 결과를 얻게 된다.
3. REPEATABLE READ
- 정의:
MySQL의 기본 격리 수준으로, 트랜잭션이시작될 때의 데이터 상태를 유지한다. - 부정합 현상: 일반적으로
'Phantom Read'문제가 발생할 수 있지만, InnoDB 스토리지 엔진에서는 발생하지 않는다. 'Phantom Read'는 트랜잭션 A가 일련의 데이터를 읽는 동안, 트랜잭션 B가 해당 범위에새로운 데이터를추가하고 커밋했을 때, 트랜잭션 A가 같은 범위를 다시 읽었을 때 추가된 데이터를 볼 수 있는 현상이다.
4. SERIALIZABLE
- 정의:
가장 엄격한 격리 수준으로, 트랜잭션이 진행되는 동안 외부에서 해당 데이터에 접근할 수 없다. - 부정합 현상: 이 격리 수준에서는 'Dirty Read', 'Non-repeatable Read', 'Phantom Read' 문제가 발생하지 않는다. 하지만
동시 처리 성능이 크게 저하될 수 있다.
