[MySQL] 락(Lock)의 종류

 

---

목차

1. 락(Lock) 의 적용 요소에 따른 분류
2. 락(Lock) 이 적용되는 상황에 따른 분류

---

MySQL InnoDB 락(Lock)의 종류

 

실제 Inno DB내부에서는 경우 여러 트랜잭션들이 경합하고 있는 상황에서 최대한의 성능을 위해서 여러 방식의 다양한 락(Lock)을 조합해서 사용하고 있다. 이 과정에서 각 트랜잭션이 획득한 락들은 해당 트랜잭션이 commit/rollback 되어 종료될 때까지 유효하기 때문에, 이러한 lock을 최소화 하면서도 동시성을 유지하기 위해 어떤 상황에 어떤 락들이 실제로 적용되는지를 좀더 자세히 살펴보도록 하자.

 

 

 

1. 락의 적용 요소에 따른 분류

 

Shared lock (S)

• Row-level lock
• SELECT 위한 read lock
• shared lock이 걸려있는 동안 다른 트랜잭션이 해당 row에 대해 X lock 획득(exclusive write)은 불가능하지만 S lock 획득(shared read)은 가능
• 한 row에 대해 여러 트랜잭션이 동시에 S lock을 획득 가능

Exclusive lock (X)

• Row-level lock
• UPDATE, DELETE 위한 write lock
• exclusive lock이 걸려있으면 다른 트랜잭션이 해당 row에 대해 X, S lock을 모두 획득하지 못하고 대기해야 한다.

Intention lock

• Table-level lock
• 테이블 안의 “row에 대해서 나중에 어떤 row-level락을 걸 것”이라는 의도를 알려주기 위해 미리 table-level에 걸어두는 lock
• SELECT ... LOCK IN SHARE MODE 이 실행되면,
  • 먼저 intention shared lock (IS) 이 테이블에 걸림
  • 그 후 row-level에 S lock이 걸림
• SELECT ... FOR UPDATE, INSERT, DELETE, UPDATE 이 실행되면,
  • 먼저 intention exclusive lock (IX) 이 테이블에 걸림
  • 그 후 row-level에 X lock이 걸림
• IS, IX 락은 여러 트랜잭션에서 동시에 접근이 가능하다 (서로 block하지 않는다). 하지만 동일한 row에 row-level의 실제 lock (S 또는 X)을 획득하는 과정에서 동시 접근을 막거나 허용하는 제어를 하게된다.
• LOCK TABLES, ALTER TABLE, DROP TABLE 이 실행될 때는 IS, IX 를 모두 block하는 table-level 락이 걸린다. 즉 IS, IX lock 을 획득 하려는 트랜잭션은 대기상태로 빠짐
• 반대로 IS, IX 락이 이미 걸려있는 테이블에 위의 쿼리를 실행하면 를 실행하면 해당 트랜잭션이 대기상태로 빠짐.
• Table-level에서 한번, row-level에서 한번, 2단계(2-phase)로 lock을 적용하는 이유?
  • A 트랜잭션에서 이미 테이블에 대해 락이 걸려있는데, B 트랜잭션에서 해당 테이블의 특정 row에 lock을 거는것을 원천적으로 방지 할 수 있다. (반대의 경우도 마찬가지)
    • 예) row-level의 write이 일어나고 있을때 테이블 스키마가 변경되서는 안된다. write query의 경우 이미 IX 락을 획득한 상태이기 때문에 해당 테이블의 스키마가 변경되는것을 막을 수 있다.
      

 

 

2. 락이 적용되는 상황에 따른 분류

 

 

• Record Lock
  • primary key 또는 unique index (multi-column unique index 포함)로 조회해서 하나의 인덱스 레코드(=row)에만 lock을 거는 것을 의미
  • 예) SELECT id FROM t WHERE id = 10 LOCK IN SHARE MODE
    • 위 쿼리를 실행하면 id=10 인 레코드에 대해 S lock이 걸린다.
  • 예) SELECT id FROM t WHERE id = 10 FOR UPDATE
    • 위 쿼리를 실행하면 id=10 인 레코드에 대해 X lock이 걸린다.
  • row-level lock이란 결국 인덱스 레코드에 대해 락을 거는것을 의미한다.
• Gap Lock (= range lock)
  • 실제 존재하는 인덱스 레코드에 락을 거는것이 아니고 범위를 지정하기 위해 인덱스 레코드 사이의 범위(gap)에 락을 거는 것을 의미한다.
  • negative infinity (최초 레코드 이전), positive infinity (마지막 레코드 이후) 를 가상의 인덱스 레코드로 생각해서 lock을 적용하는것이 가능하다.
  • Gap lock을 통해서 같은 SELECT 쿼리를 두번 실행했을때 다른 트랜잭션에서 데이터가 수정되었더라도 같은 결과가 리턴되는 것을 보장할 수 있다. (Phantom read 방지)
    • 예) SELECT id FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE
    • 위 쿼리를 실행하면 에서 c1=10~20 사이에 X락이 걸리기때문에 다른 트랜잭션에서 c1=15를 가지는 데이터를 INSERT하려면 대기 상태로 빠진다.
  • “READ COMMITTED” isolation level을 사용하면 트랜잭션 내부에서 “동일 SELECT 실행, 동일 결과” 보장을 하지 않아도 되기때문에 gap lock 비활성화 해도된다. 이경우 DB의 쿼리 처리량을 늘릴 수 있지만, 그만큼 대기시간이 줄어들고 동시 write가 증가하기때문에 데드락에 걸릴 확률이 높아진다.
  • 컬럼에 대한 WHERE 절로 많은 row들을 제외시키고 하나의 레코드만 추출되었을때, Record lock과 Gap lock 어느것이 사용될까?
    • 컬럼에 unique index가 걸려있는 경우에는 gap lock이 필요없다. (record lock이 사용됨)
    • 컬럼에 index가 걸려있지 않거나, index가 걸려있어도 unique 하지 않다면 gap lock이 필요하다.
      • index가 걸려있다면 row를 찾기위해 스캔했던 index range에 대해서 gap lock 적용
      • index가 걸려있지 않다면 결국 테이블 전체를 스캔해야 되기때문에 모든 row에대해 lock이 걸림.
• Next-Key Lock
  • 범위를 지정한 쿼리를 실행하게되면 실제로는 위에서 각각 설명했던 record lock (찾아진 인덱스 레코드에 대해)과 gap lock (해당하는 인덱스 레코드 사이사이) 이 복합적으로 사용된다.
  • 다음 그림을 통해 Next-Key lock이 어떤 것인지를 한눈에 살펴보자.

 

[그림-1] FOR UPDATE 실행시 일어나는 일들

 

• Insert Intention Lock
  • INSERT 구문이 실행될 때 InnoDB 엔진 내부적으로 implicit하게 획득하는 특수한 형태의 gap lock. (gap lock은 일반적으로 explicit하게 locking read를 위한 SELECT 구문이 실행될때 발생하지만 이 lock은 INSERT 시점에 implicit하게 자동으로 발생)
  • 여러개의 트랜잭션들이 gap 안의 다른 위치에 INSERT를 동시 수행할 때 기다릴 필요가 없도록 하는것이 목적 (Insert intention lock들 간에는 충돌이 일어나지 않는다)
  • INSERT될 row에 대해서 exclusive lock을 걸기 전에 먼저 insert intention lock을 건다.
  • 예) pk=3, pk=6의 레코드가 존재하는 테이블이 존재
    • A 트랜잭션에서 pk=5에 INSERT, B 트랜잭션에서 pk=4에 INSERT시도
    • 만약 일반적인 gap lock 사용한다면:
      • A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 gap lock 걸림
      • B트랜잭션이 pk=4에 INSERT 시도시 pk=3~5에 gap lock이 걸려있기때문에 A가 트랜잭션이 완전히 종료될 때 까지 기다려야 한다.
      • 대기시간 존재!
    • Insert Intention Lock 사용시:
      • A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 insert intention lock 걸림
      • B트랜잭션이 pk=4에 INSERT 시도시 pk=3~5에 insert intention lock이 걸려있더라도 pk가 겹치지 않기때문에 바로 진행 가능
      • 대기시간 없음!
      • 실제 InnoDB의 동작 방식
• AUTO-INC Lock
  • 여러 트랜잭션이 동시에 실행될때 AUTO_INCREMENT 컬럼의 값을 일관성 있게 증가시키기 위해 필요한 lock
  • AUTO_INCREMENT 방식에대해서 InnoDB에서 몇가지 모드를 제공하는데 자세한 내용은 MySQL – InnoDB Auto Increment 성능최적화 글을 참고.