DB ) 잠금(MySQL 기준)

 

“잠금” MySQL 엔진

글로벌 락	서버 전체
테이블 락	테이블 데이터 동기화
메타데이터 락	테이블 구조 잠금
네임드 락	사용자 필요에 맞게 사용

글로벌 락

→ 잠금 가운데 가장 범위가 크다!

범위	MySQL 서버 전체
용도	MyISAM, MEMORY 테이블에서 일관된 백업이 필요할 때.
특징	서버의 모든 변경 작업을 멈춘다.

백업 락

→ 백업 툴들의 안정적인 실행을 위한 조금 더 가벼운 글로벌 락

백업 락을 획득하면 못하는 행동

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • 단, 테이블 데이터 변경은 허용
• REPAIR TABLE 과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

 

📌 정상적으로 복제를 실행하고 백업의 실패를 막기 위해
DDL 명령이 실행되면 복제를 일시 중지하는 백업 락이 도입됐다.

테이블 락

→ 개별 테이블 단위로 설정되는 잠금, 명시적 또는 묵시적으로 획득 가능

명시적 락

• 특별한 상황이 아니면 사용할 필요가 거의 없다. 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미친다.

묵시적 락

• MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생.
• 쿼리가 실행되는 동안 자동으로 획득하고 실행 후 자동 해제
• 스키마를 변경하는 DDL 쿼리에만 영향을 미친다.

네임드 락

함수를 이용해 문자열을 잠금하고 반납(해제)하는 잠금, 자주 사용되진 않는다. → 중첩 사용 가능, 한번에 해제 가능.

상호 동기화

• 여러 클라이언트가 상호 동기화를 처리해야 할 때 이용.
  • DB 서버 1대에 5대의 웹 서버가 접속해서 서비스하는데, 웹 서버들이 어떤 정보 동기화가 필요.

많은 레코드를 변경하는 트랜잭션

• 많은 레코드를 복잡한 조건으로 변경하는 트랜잭션에 유용.
  1. 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류
  2. 네임드 락을 걸고 쿼리 실행. (한꺼번에 간단 해결!)

메타데이터 락

데이터베이스 객체(테이블이나 뷰 등) 이름이나 구조를 변경하는 경우에 자동 획득.

실시간으로 테이블을 바꿔야하는 경우

1. 배치 프로그램에서 임시 테이블(rank_new)에 서비스용 랭킹 데이터 작성
2. 배치가 완료되면 현재 서비스용 랭킹 테이블(rank)을 rank_backup 으로 백업 후
3. 새 랭킹 테이블(rank_new)을 서비스용으로 대체 필요

→ 2개로 나눠 실행

// 2개로 나눠서 실행하면, rank 테이블이 존재하지 않는 순간이 생김.
// " Table not found 'rank' " 오류 발생, 적용 불가 !
mysql> RENAME TABLE rank TO rank_bakup ;
mysql> RENAME TABLE rank_new TO rank ;

→ 한꺼번에 실행

// 두 개의 RENAME 작업을 한꺼번에 실행하므로
// “ Table not found ‘rank’ ” 오류 발생 없음. 적용 가능!
mysql> RENAME TABLE rank TO rank_backup , rank_new TO rank ;

테이블 구조 변경하는 경우

1. 새로운 구조의 신규 테이블 생성.
2. 최근(1시간 직전 또는 하루 전) 데이터까지는 → PK 인 id 값을 범위 별로 나눔.
3. 여러 개의 스레드로 신규 테이블로 빠르게 복사.
   1. id ≥ 0 AND id < 1000 ;
   2. id ≥ 1000 AND id < 2000 ;
   3. id ≥ 2000 AND id < 3000 ; ….
4. 나머지 데이터는 트랜잭션, 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램 중단 없이 실행
   1. 트랜잭션 시작
   2. 작업 대상 테이블(신규, 기존)에 테이블 쓰기 락 획득
   3. (기존→신규)남은 데이터 복사
   4. 복사가 완료되면, RENAME 명령으로 새로운 테이블을 서비스로 투입
   5. 불필요한 테이블 삭제

“잠금” InnoDB 스토리지 엔진

스토리지 엔진 내부에서 레코드 기반 잠금 방식 이용, 뛰어난 동시성 처리!

→ 잠금 정보가 상당히 작은 공간으로 관리되어 *락에스컬레이션이 없다.

*레코드 락→페이지 락, 페이지 락→ 테이블 락으로 범위가 레벨업 되는 현상

레코드 락

• 레코드 자체만 잠금. DBMS 의 레코드 락과 동일.
• 단, InnoDB 스토리지 엔진은 “인덱스의 레코드를 잠근다”
• PK, 유니크 인덱스에 의한 변경은 갭 락이 아니라 레코드 락.

갭 락 (GAP)

• 레코드 사이 간격만 잠금.
• 레코드 사이 간격에 새로운 레코드가 생성되는 것을 제어한다.
• MySQL 에만 있는 기능.

넥스트 키 락

• 레코드 락 + 키 락
• REPEATABLE READ 격리 수준 사용 필요

 

✅ 갭 락이나 넥스트 키 락은
바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때
소스 서버와 동일한 경과를 만들어내도록 보장한다.

 

 

 

자동 증가 락 AUTO_INCREMENT

자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT 컬럼을 제공한다.

• 동시에 여러 레코드가 INSERT 되는 경우
  • 각 레코드가 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가진다!
• 가져오는 순간만 락이 걸렸다가 즉시 해제
• 아주 짧은 시간동안 걸렸다가 해제되어 대부분의 경우 문제가 되지 않는다.
• INSERT 쿼리가 실패해도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않는다.
  • 잠금 최소화를 위해

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라, 인덱스를 잠그는 방식.

→ 변경해야할 레코드를 찾기 위해 검색한 인덱스 레코드에 모두 락을 건다!!!

• 변경 레코드가 1건이어도 변경하기 위해 조회해야할 인덱스가 200건이라면 ?
  • 200개 레코드가 전부 잠긴다.
• 테이블에 인덱스가 하나도 없다면?
  • 테이블 전체가 잠긴다…
• 적절한 인덱스가 없다면 동시성이 상당히 떨어진다.

레코드 수준의 잠금

레코드 수준의 잠금은 테이블 레코드에 각각 잠금이 걸리므로 해당 레코드가 자주 사용되지 않는다면 오랫동안 잠겨있어도 잘 발견되지 않는다.

→ 이제는 잠금과 잠금 대기 조회가 가능하므로, 쿼리를 실행해서 잘 찾아보자!

mysql> SHOW PROCESSLIST ;

스레드	상태
17번	트랜잭션 시작하고 업데이트 실행 완료, 커밋 전이라 잠금을 그대로 가지고 있다.
18번	17번이 잠금이 끝나고 잠금 획득 대기
19번	17, 18번이 잠금 끝나고 잠금 획득 대기

순서

1. 트랜잭션 시작
2. 17번 업데이트 실행 후, 잠금 해제
3. 18번이 잠금 획득하고 업데이트 완료 후 잠금 풀기
4. 19번이 잠금 획득하고 업데이트 실행
5. 트랜잭션 종료