TIL - 0618

Posted on | In | 0 comments

Lock(2)

지난 글에서 Share Lock/ Exclusive Lock에 대해 이야기해보았다. 오늘은 회사에서 사용하고 있는 Mysql Lock 종류에 대해 알아보려한다.

Mysql 엔진

Mysql은 크게 서버엔진과 스토리지 엔진 구조로 이루어져있다.

  • 서버엔진: 클라이언트가 쿼리를 날리면, 쿼리 parshing 과 스토리지 엔진에 데이터를 요청하는 작업을 수행
  • 스토리지 엔진: 물리적 저장장치에서 데이터를 읽어오는 역할을 담당

Mysql 엔진 잠금

스토리지 엔진 레벨과 Mysql 엔진 레벨로 나눠지는데, Mysql 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 준다. 반대로 스토리지 엔진 레벨의 잠금은 스토리지 엔진간에는 영향을 주지 않는다.

  • Table Lock
  • Global Lock
  • Name Lock: 테이블 이름 변경, 삭제 할때 암묵적으로 걸림
  • User Lock: 사용자 레벨에서 lock 이름과 타임아웃 정의하여 사용하는 방법
  • Row Level Lock

InnoDB

  • 스토리지 엔진을 명시하지 않은 경우 default로 설정된 스토리지 엔진
  • transaction-safe 하며 커밋,롤백 그외 데이터 복구 기능을 제공
  • row-level locking을 제공 -> Insert,Update,Delete 에 대한 속도가 빠름
  • 데이터를 clustered index에 저장 PK기반 쿼리의 I/O 비용을 줄임/ FK 제약 제공하여 데이터 무결성 보장
    • 클러스터형 인덱스란: 테이블당 한개만 생성되며, 지정한 열에 대해 자동정렬(영어사전에 비유)
    • 비클러스터형 인덱스: 테이블에 여러개 생성 가능하며, 클러스터형 인덱스처럼 자동 정렬이 안됨

InnoDB Lock 종류

  1. Shared Lock
    • row-level lock
    • SELECT 위한 read lock
    • shared lock이 걸려있는 동안 다른 트랜잭션이 해당 row에 대해 exclusive lock을 얻는것은 불가능하나 shared lock 을 얻는 것은 가능
  2. Exclusive lock
    • row-level lock
    • update, delete 위한 write lock
    • 배타적 lock이 걸려있으면 다른 트랜잭션이 대기해야함
  3. Intention lock
    • Table level lock
    • 테이블안에 특정 row에 어떤 row-level lock을 걸었인지에 대해 미리 알려주기 위해 table 단위로 락을 걸음
    • intention shared lock (table) -> shared lock (row)
    • intention exclusive lock (table) -> exclusive lock (row)
    • lock tables, alter table, drop table이 실행되는 경우 모두 block 하는 table lock이 걸림 -> IS, IX lock 획득이 필요한 트랜잭션은 대기상태
  4. Record lock
    • InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스 레코드를 잠근다.
  5. Gap lock
    • 레코드 자체와 인접한 레코드 사이의 간격만을 잠금
    • 역할 레코드와 레코드 사이의 간격에 새로운 레코드 생성(Insert) 되는 것을 방지
  6. Next key lock
    • Record lock + Gap lock

Mysql 격리 수준

격리 수준이란 특정 트랜잭션이 다른 트랜잭션에서 변경, 조회하는 데이터를 볼 수 있도록 허용할지 말지 결정하는 수준을 의미한다.
순서가 뒤로 갈수록 각 트랜잭션 간의 데이터 격리정도가 높아지는 동시에 동시성은 떨어진다.

격리 현상

  1. Dirty Read: 커밋되지 않은 수정중인 데이터를 다른 트랜잭션에서 읽을 수 있음
  2. Non-Repeatable Read(Inconsistent Analysis): 한 트랜잭션 내에서 같은 쿼리를 두번 수행했을 때, 그 사이 다른 트랜잭션이 값을 수정/삭제하여 일관성이 어긋남
  3. Phantom Read: 한 트랜잭션 안에서 일정 범위 레코드를 두번 이상 읽을 때, 첫번재 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 현상

격리 수준

  1. Read uncommitted
    • Dirty read, Non-repeatable read, Phantom read 현상 발생
    • 트랜잭션에서의 변경 내용이 작업 여부와 (commit/rollback 여부)와 상관없이 다른 트랜잭션에 보여짐 -> Dirty read 발생,
  2. Read committed
    • Non-repeatable read, Phantom read 현상 발생
    • Shared Lock 사용
    • 가장 기본적으로 사용하는 격리수준
    • 트랜잭션이 commit 완료된 데이터만 다른 트랜잭션에서 조회 가능 -> Dirty read 발생하지 않음
    • 완료되기 전에 다른 트랜잭션이 조회하면 백업된 레코드 가져옴
    • 그러나 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야한다는 “REPETABLE READ” 정합성에 어긋남 (예시는 참고 블로그에 더 자세히 나옴)
  3. Repetable read
    • Phantom read 현상 발생
    • InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준
    • NON-REPETABLE READ 가 발생안함
    • 언두 영역에서 특정 트랜잭션 번호의 구간내에서 백업된 데이터를 줌, 그러나 트랜잭션을 종료하지 않으면 무한정 언두 영역이 커질 수 있어서 Mysql 성능 떨어짐 (트랜잭션 번호가 작은 트랜잭션 번호에서 변경한 것만 보이게됨)
    • Phantom Read(Phantom row) : select for update 쿼리의 경우 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 할 수 있음 -> 언두 영역은 lock 할 수 없기 때문에 변경전 데이터가 아닌 현재 변경된 레코드를 표현
    • Undo 영역: UPDATE, DELETE와 같은 문장으로 데이터를 변경했을 때 변경되기 전 데이터를 보관하는 곳
  4. Serializable 읽기 작업도 공유잠금을 획득해야하며 동시에 다른 트랜잭션은 레코드를 변경하지 못하게함 -> 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근 할 수 없음

    참고