RealMySQL - 잠금, 격리수준

MySQL 엔진의 잠금

글로벌 락

• MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.

• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

• 영향을 미치는 범위는 MySQL 서버 전체(작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.)

• 8.0 버전부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 백업 툴들의 안정적인 실행을 위해 백업 락이 도입 됨

• 백업 도중에 스키마 변경이나 DDL 명령이 실행되면 백업은 실패 한다.
  이러한 백업 실패를 막기위해 백업락은 DDL명령이 실행되면 복제를 일시 중지하는 역할을 한다.

테이블 락

• 개별 테이블 단위로 설정되는 잠금
• InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

네임드 락

• 네임드 락은 사용자가 지정한 문자열에 대해 획득하고 반납(해제)하는 잠금이다.

메타데이터 락

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

InnoDB 스토리지 엔진 잠금

• MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재

• 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.

레코드 락

• 레코드 자체만을 잠그는 것을 레코드 락이라고 한다.

• InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

• 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

갭 락

• 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다.

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태
• InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

자동 증가 락

• InnoDB의 다른 잠금(레코드 락이나 넥스트 키 락)과는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

• AUTO 락은 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO 락을 걸면 나머지 쿼리는 AUTO 락을 기다려야 한다.

• 5.1 이상 부터는 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

• 8.0에서는 innodb_autoinc_lock_mode의 기본 값이 2로 바뀌었다.
  (동시 처리 성능, 유니크한 값 생성 보장)

인덱스와 잠금

• InnoDB의 잠금은 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

• update 문장을 실행시 인덱스에 해당하는 모든 레코드가 잠기게 됨

• 만약 인덱스가 적절히 준비돼 있지 않다면 수만건의 레코드가 잠기게 될수도 있다. 동시성이 떨어짐

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

• 5.1 부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 잠금과 잠금대기를 바로 확인할 수 있다.

• 8.0 부터는 performance_schema의 data_locks와 data_lock_waits 테이블을 통해 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금을 어떤 트랜잭션이 가지고 있는지를 쉽게 메타 정보를 통해 조회할 수 있다.

MySQL 격리 수준

READ UNCOMMITTED

• 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다.

READ COMMITTED

• 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

• NON-REPEATABLE READ 라는 부정합의 문제(SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋남

REPEATABLE READ

• 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서 동일한 결과를 보여줄 수 있게 보장한다.

• InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다.

• 언두 영역에 백업된 데이터는 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제 한다.

• 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ 라고 한다.

• SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그래서 SELECT ... FOR UPDATE나 SELECT.. LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.

SERIALZABLE

• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것