RealMySQL 트랜잭션과 잠금

MySQL 엔진의 잠금

• 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나뉜다.
• Global Lock
  • 영햠 범위 : MySQL 서버 전체
• Table Lock
  • 개별 테이블 단위로 설정되는 잠금
  • 명시적 테이블 락
    • 특별한 상황이 아니면 어플리케이션에서 사용할 필요가 없다.
  • 묵시적 테이블 락
    • MyISAM이나 MEMORY 테이블의 데이터를 변경하는 쿼리를 실행하면 발생한다.
    • InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 묵시적 테이블 락이 설정되지 않는다.
      • 대부분 DML 쿼리에서는 무시되고 DDL의 경우에만 영향을 미친다.
• Named Lock
  • 임의의 문자열에 대해 설정되는 잠금
• Metadata Lock
  • 데이터베이스 객체(테이블, 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
  • 명시적으로 획득하거나 해제할 수 없고, 메타데이터가 변경될 경우 자동으로 획득하는 잠금

InnoDB 스토리지 엔진 잠금

• Record Lock
  • 다른 DBMS는 레코드 자체를 잠금지만 인덱스의 레코드를 잠근다.
    • 인덱스가 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금 설정
• Gap Lock
  • 레코드가 아닌 레코드와 인접한 레코드 사이의 간격만을 잠금
• Next Key Lock
  • Record Lock 과 Gap Lock 을 합쳐 놓은 형태의 잠금
  • question 그래서 Next Key Lock 이 뭐지?
• Auto Increment Lock
  • INSERT와 REPLACE 와 같이 새로운 레코드를 저장하는 쿼리에서만 필요
    • UPDATE, DELETE는 걸리지 않는다.
  • 트랜잭션과 관계없이AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
  • 명시적으로 획득하거나 해제하는 방법은 없으며, 아주 짧은 시간 걸렸다가 해제되기 때문에 대부분은 문제가 되지 않는다.

Info

테이블 수준의 잠금의 경우 쉽게 문제의 원인이 발견되고 해결될 수 있다. 그러나 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다. performance_schema의 data_locks와 data_lock_waits 테이블로 쉽게 메타 정보를 조회할 수 있다.

MySQL의 격리 수준

데이터베이스 격리 수준에 따른 세 가지 부정합 문제

	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

READ UNCOMMITED

• 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 읽을 수 있는 현상을 Dirty Read라고 한다.
• RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다. READ COMMITED
• 트랜잭션에서 데이터를 변경했더라도 COMMIT 완료된 데이터만 다른 트랜잭션에서 조회가 가능하다.
• 오라클의 기본 격리 수준
• 하나의 트랜잭션 안에서 다른 트랜잭션이 COMMIT 할 경우 처음 조회한 결과값과 두번째 조회한 결과값이 다를 수 있는 문제가 발생할 수 있다.
  • Non-Repeatable Read REPEATABLE READ
• MySQL의 기본 격리 수준
• MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
  • MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.
• SELECT FOR UPDATE는 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없기 때문에 도중에 추가된 데이터에 대해서는 결과가 변할 수 있다.
  • Phantom Read
• MySQL에서는 Next Key Lock 덕분에 Phantom Read가 발생하지 않는다. SERIALIZABLE
• 가장 엄격한 격리 수준으로 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 접근할 수 없다.
• 대부분 RDBMS는 MVCC를 지원하기 때문에 다른 Select 쿼리는 조회가 가능하다.