[Real MySQL] 5-1. 트랜잭션과 잠금

"Real MySQL" 책을 정리한 포스팅 입니다.

 

1. 트랜잭션

• 일련의 연산 (하나의 작업 단위 구성)(논리적)
• DML 묶음

 

Atomic

• All or Nothing (작업 셋 적용: 100% or 0%)
• 데이터 정합성 보장 (Partial Update 방지)

 

Consistency

• 트랜잭션 전후로 데이터베이스 상태가 항상 일관된 상태로 유지됨
• 일관성: 데이터베이스의 규칙이 유지됨 (무결성 제약 조건 유지, 외래 키 관계 등)

 

Isolation

• 트랜잭션 실행 중에, 다른 트랜잭션으로부터 독립적으로 실행됨 (트랜잭션 격리 수준 제공)
• 데이터 일관성 유지

 

Durability

• 성공적으로 트랜잭션이 커밋되면, 모든 변경사항은 영구적으로 데이터베이스에 반영됨

 

MySQL

Engine

• InnoDB
• (MyISAM, MEMORY 지원 X)

 

주의사항

트랜잭션 범위 최소화하기

• 한정적인 자원인 커넥션을 최소시간으로 사용하기 (사용가능한 커넥션 개수 최대로 유지)

 

트랜잭션 내에서 외부 프로그램과의 통신 포함 X

• 외부 프로그램과의 통신에서 문제 발생 가능성 있음 (타임아웃, 네트워크 장애) 
• DBMS에 문제가 전파됨 (전체 성능 저하, 리소스 고갈)

 

2. Lock

• 여러 세션에서 동일한 데이터에 대해 동시 접근 불가
• 동시성을 제어하기 위한 기능

 

MySQL 엔진 레벨

• 모든 스토리지 엔진에 영향을 미칩니다.
• Table Lock, Metadata Lock, Named Lock

 

스토리지 엔진 레벨

• 각 스토리지 엔진이 고유하게 관리 (스토리지 엔진 간 독립적으로 동작)
• InnoDB: Row Lock
• MyISAM: Table Lock

 

글로벌 락

• 가장 큰 범위의 락
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서는 락 해제 전까지 대기 (SELECT 제외)
• 모든 오브젝트에 적용 (다른 데이터베이스, 다른 테이블)

 

백업 락

• 일반적인 테이블의 데이터 변경 허용 O
• 데이터베이스 백업 작업이 진행되는 동안, 특정 데이터 변경 허용 X  (테이블 스키마, 사용자 인증 정보)
  • 객체 생성 및 변경, 삭제 (REPAIR TABLE, OPTIMIZE TABLE)
  • 사용자 관리 및 비밀번호 변경
• 레플리카 서버에서 백업 작업 수행
  • 데이터 싱크가 소스 서버와 일치해야 함. 일치하지 않으면 백업 작업 중단됨

 

명령어

FLUSH TABLES WITH READ LOCK

 

 

테이블 락

• 개별 테이블 단위로 설정되는 잠금

 

InnoDB 엔진

• 레코드 기반 잠금 (테이블 스키마 변경 쿼리)

LOCK TABLES table_name [ READ | WRITE ];
UNLOCK TABLES table_name;

 

네임드 락

• 문자열 단위 (데이터베이스 객체 X)

SELECT GET_LOCK('mylock', 2);
SELECT RELEASE_LOCK('mylock');
SELECT IS_FREE_LOCK('mylock');

 

메타데이터 락

• 데이터베이스 객체의 이름이나 구조 변경
• 자동 획득 (명시적 획득X)

 

3. InnoDB 스토리지 엔진 잠금

• 인덱스 레코드 기반 (인덱스 관련 레코드 모두 잠금)
  • 인덱스가 없다면, 테이블에 있는 모든 레코드를 잠금

 

종류

레코드 락

• 특정 레코드 동시성 제어
  • 클러스터 인덱스 (기본키)(내부적으로 자동 생성됨)
  • 유니크 인덱스

 

갭 락

• 레코드 사이의 간격을 잠그는 락
• 새로운 레코드 생성 방지 (Pantom read 방지)

 

넥스트 키 락

• 범위 내에서의 레코드 락(레코드 락 + 갭 락)
• 백업 동시성
  • 바이너리 로그에 기록되는 쿼리
  • 레플리카 서버에서 쿼리가 실행될 때, 소스 서버에서의 결과와 동일한 결과를 만들어 내는 것이 목적
• 격리 수준을 높게 유지하는데 도움을 줌 (성능 저하 야기할 수 있음)

 

자동증가 락 (AUTO_IMCREMENT)

• 테이블 락
• 동시에 INSERT가 되는 경우, 자동 증가 컬럼에 고유한 값이 순서대로 할당되도록 보장
• 트랜잭션 독립적

 

레코드 수준의 잠금 및 해제 확인

• performance_schema.data_locks
• performance_schema.data_lock_waits