RealMySQL 1 : 5장 트랜잭션과 잠금

• MyISAM
  • 트랜잭션 미지원
  • MySQL 엔진 레벨 잠금 존재, 스토리지 엔진 레벨 잠금 없음
• InnoDB
  • 트랜잭션 지원
  • MySQL 엔진 레벨 잠금, 스토리지 엔진 레벨 잠금 존재

트랜잭션

• 논리적인 작업이 100% 적용되거나 아무것도 적용되지 않아야함을 보장
• 필요한 부분만 트랜잭션으로 분리하고 불필요한 부분은 제거
  • 제거 예시 : 알림 메일 전송, 써드 파티 등

글로벌 락

• 가장 범위가 큰 락
• SELECT만 가능
• 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.
• 예시 : MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야할 때
• MySQL 8.0 부터는 백업 툴로 백업 락 도입

테이블 락

• 명시적 락
  • LOCK TABLES table_name [READ | WRITE]
• 묵시적 락
  • MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생
  • MySQL 서버가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후 즉시 잠금 해제
    • DML은 안걸리고 DDL만 걸린다.

네임드 락

• GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금 설정 가능
  • 사용자가 지정한 문자열에 대해 획득 및 반납
    • 테이블, 레코드가 아니다.
• 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용

메타데이터 락

• 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금

InnoDB 스토리지 엔진 잠금

• 스토리지 엔진 내부에서 레코드 기반의 잠금 방식 탑재

레코드 락

• 레코드 자체를 잠근다.
• 다른 DBMS와 다르게 MySQL은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
• 인덱스가 없더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금 설정
• PK, 유니크 인덱스에 의한 변경 작업에서 갭 락 이용하지 않고 레코드 락 설정
  • 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 or 갭 락 사용

갭 락

• 레코드와 인접한 레코드 사이의 간격만을 잠근다.
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어

넥스트 키 락

• 레코드 락 + 갭 락
• 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생
  • 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

자동 증가 락

• AUTO_INCREMENT 이 걸려있는 테이블에 동시에 여러 레코드가 INSERT 되는 경우 락이 걸린다.
• INSERT에서만 걸리고 UPDATE, DELETE에서는 걸리지 않는다.
• 테이블에 단 하나만 존재
• MySQL 5.0 이하에서 사용되던 방식
• MySQL 5.1 이상부터는 innodb_autoinc_lock_mode 시스템 변수를 이용해 작동 방식 변경 가능
  • innodb_autoinc_lock_mode = 0
    • 자동 증가 락 사용
    • INSERT SELECT는 자동 증가 락 사용
      • 여러 개의 자동 증가 값을 할당받아서 사용한다.
      • 그러나 자동 증가 값이 남아서 사용하지 못하면 폐기
      • 따라서 자동 증가 값이 연속되지 않고 누락된 값이 발생할 수 있다.
  • innodb_autoinc_lock_mode = 1
    • 더 가볍고 빠른 래치(뮤텍스)를 이용해 처리
  • innodb_autoinc_lock_mode = 2
    • 경량화된 래치(뮤텍스) 사용
    • MySQL 8.0부터 디폴트 값
    • 유니크한 값이 생성된다는 것만 보장
      • master, slave의 자동 증가 값이 달라질 수 있음

인덱스와 잠금 예시

• InnoDB의 레코드 락은 인덱스를 잠근다.
• 예시

  • employees 테이블 존재

  • firtst_name, last_name이 있는데 first_name만 인덱스가 있다.

  • first_name = ‘Georgi’ → 253명

  • first_name = ‘Georgi’ and last_name = ‘Klassen’ → 1명

  • UPDATE employees SET hire_date = NOW() WHERE first_name = ‘Georgi’ and last_name = ‘Klassen’ 을 실행한다면?

  • last_name에 인덱스가 없기 때문에 UPDATE 시 first_name = ‘Georgi’ 인 레코드가 모두 잠긴다.

    → MySQL에서 인덱스 설계가 중요한 이유

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

• 테이블 락은 쉽게 문제의 원인이 발견되고 해결할 수 있다.
• 레코드 락은 레코드 각각에 잠금이 걸리므로 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.

MySQL 격리 수준

• 기본 격리 수준
  • 오라클 : Read Commited
  • MySQL : Repeatable Read
• MVCC : 트랜잭션이 롤백될 가능성에 대비해 변경되기 이전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경