백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
1. 트랜잭션
- 하나의 논리적인 작업 단위로 실행되는 일련의 연산
- DML 문을 하나의 트랜잭션으로 묶어 실행할 수 있음
특징
Atomic
- All or Nothing
- 트랜잭션 내의 모든 작업이 완전히 수행되거나, 아예 수행되지 않음
- 중간에 오류가 발생하면 전체 트랜잭션이 롤백됨
- 데이터 정합성 보장
Consistency
- 트랜잭션 전후로 데이터베이스가 항상 일관된 상태로 유지됨
- 즉, 데이터베이스의 규칙이 유지됨 (무결성 제약 조건 유지, 외래 키 관계 등)
Isolation
- 다른 트랜잭션으로부터 독립적으로 실행됨
- 동시에 실행되는 트랜잭션들은 서로 영향을 주지 않음
Durability
- 성공적으로 트랜잭션이 커밋되면, 모든 변경사항은 영구적으로 데이터베이스에 반영됨
주의사항
트랜잭션 범위 최소화하기
- 데이터 락
- 트랜잭션이 길어지면 데이터 락이 오래 유지되므로 성능 저하
- 커넥션
- 한정적인 자원이므로, 동시 접속 가능한 커넥션 수가 제한됨
- 빠르게 커밋하거나 롤백하여 커넥션을 해제해야 함
트랜잭션 내에서 외부 프로그램과의 통신 포함하지 말기
- 외부 프로그램과의 통신에서 문제 발생 가능성 있음 (타임아웃, 네트워크 장애 등)
- DBMS에 문제가 전파됨 (성능 저하, 리소스 고갈 등)
MySQL
Engine
- InnoDB 엔진 지원 O
- MyISAM, MEMORY 엔진 지원 X
2. Lock
- 동시성을 제어하기 위한 기능
- 동일한 데이터에 대해 여러 트랜잭션이 동시에 접근하는 것을 방지
MySQL 엔진 레벨
글로벌 락
- 데이터베이스 전체 잠금
- 가장 범위가 넓은 락
- 한 세션에서 글로벌 락을 획득하면, 다른 세션에서는 락 해제 전까지 대기 (SELECT 제외)
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
백업 락
- 데이터베이스 백업 작업이 진행되는 동안, 특정 작업이 제한됨
- 테이블 스키마
- 사용자 인증 정보
- 객체 생성 및 변경, 삭제 (REPAIR TABLE, OPTIMIZE TABLE)
- 레플리카 서버에서 백업 작업 수행 시 주의사항
- 소스 서버와 데이터 싱크가 일치해야 함
- 즉, 레플리카 서버가 최신 데이터를 유지하고 있어야 함
- 만약 데이터 동기화가 되지 않았다면, 백업 도중 오류가 발생하여 작업이 중단될 수 있음
테이블 락
- 개별 테이블 단위로 설정되는 잠금
- InnoDB 엔진에서 테이블 락 사용
LOCK TABLES table_name [ READ | WRITE ];
UNLOCK TABLES table_name;
네임드 락
- 문자열을 기반으로 잠금을 설정하는 기능
- 데이터베이스 객체와 무관하게 설정됨
- 동기화가 필요한 상황에서 사용됨
SELECT GET_LOCK('mylock', 2);
SELECT RELEASE_LOCK('mylock');
SELECT IS_FREE_LOCK('mylock');
메타데이터 락
- 데이터베이스 객체의 이름이나 구조 변경 시 자동으로 획득되는 잠금
- MySQL이 자동으로 적용하는 락
- DDL 명령어 실행 시 획득됨
스토리지 엔진 레벨
- 각 스토리지 엔진이 고유하게 관리 (스토리지 엔진 간 독립적으로 동작)
- InnoDB: Row Lock
- MyISAM: Table Lock
3. InnoDB 스토리지 엔진 잠금
인덱스 레코드 기반
- 모든 락은 인덱스를 기준으로 설정됨
- 인덱스가 없다면, 테이블 전체를 잠금
종류
레코드 락
- 레코드 단위로 잠금 설정
- 기본키(클러스터 인덱스) 또는 유니크 인덱스가 있는 경우에만 적용됨
갭 락
- 레코드 사이의 간격을 잠그는 락
- 새로운 레코드 생성 방지 (Pantom read 방지)
-- ID가 10보다 크고 20보다 작은 모든 행을 읽고 잠금 (갭 포함)
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;
넥스트 키 락
- 레코드 락 + 갭 락
- 특정 범위 내에서의 레코드를 포함하여 새로운 레코드 삽입도 차단됨
- 격리 수준을 높게 유지하는데 도움을 줌
- 사용 목적
- 레플리카 환경에서 데이터 일관성을 유지하기 위함
- 바이너리 로그를 사용할 때, 트랜잭션이 실행될 때마다 동일한 결과를 보장하기 위함
자동증가 락 (AUTO_IMCREMENT)
- AUTO_INCREMENT 칼럼이 있는 테이블에서 INSERT 동시성을 관리하는 락
- INSERT가 동시에 수행되더라도, 자동 증가 컬럼에 고유한 값이 순서대로 할당되도록 보장
- 트랜잭션과 독립적으로 동작
- 테이블 락
레코드 수준의 잠금 및 해제 확인
- performance_schema.data_locks
- performance_schema.data_lock_waits