InnoDB Locking 기법 정리

InnoDB Locking

confict -> 호환 불가, compatible -> 호환 가능

• MySQL 의 InnoDB 에서 사용하는 Locking 기법
• Shared Lock, S-Lock
  • 특정 레코드를 읽을 때 사용되는 Lock
  • Shared Lock 끼리는 동시 접근이 가능하다.
  • 하나의 레코드를 여러 트랜잭션이 동시에 읽을 수 있다.
  • 어떤 자원에 Shared Lock 이 하나라도 걸려있으면 Exclusive Lock을 걸 수 없다. 
    • 동시에 읽기는 가능하지만, 변경은 불가능하게 한다.
  • e.g. SELECT ... FOR SHARE
• Exclusive Lock, X-Lock
  • 변경(write) 작업을 할 때 사용되는 lock 이다.
  • Exclusive Lock 이 걸린 레코드에는 다른 트랜잭션에서 변경이나 삭제가 불가능하다.
  • 특정 레코드에 Exclusive Lock 이 걸려있으면 Shared Lock 이나 Exclusive Lock 둘 다 걸 수 없다.
    • 작업이 끝날 때까지 대기한다.
  • e.g. SELECT ... FOR UPDATE
• Intention Lock
  • 테이블 수준의 잠금
  • 테이블 내 행에 걸리게 될 잠금 수준(row-level lock) 을 나타내는 잠금
  • 다른 락이 걸리는 것을 사전에 예방한다.
    • Intention Lock 을 기준으로 어떤 row 에 락을 걸 수 있는지에 대한 여부를 확인할 수 있다.
  • IX Lock
    • 어떤 row 에 X-Lock 을 걸 것을 나타낸다.
    • X, S 락과 충돌을 일으킨다.
    • 레코드를 변경하기 위해 테이블에 IX 락이 걸려있다면 다른 세션에서 해당 레코드에 X, S 락을 설정할 수 없다.
  • IS Lock
    • 어떤 row 에 S-Lock 을 걸 것을 나타낸다.
    • X 락과 충돌을 일으킨다.
    • 행을 읽기 위해 테이블에 IS 락이 잡혀있다면, 다른 세션에서는 레코드에 X 락을 잡을 수 없다.
• 참고
  • Lock Escalation
    • 하위 객체에 락을 걸면 상위 객체에 Intention Lock 이 걸리게 된다.
    • 하위 객체에 락을 걸면 걸 수록 상위 객체에 락을 건 것과 다름이 없음에도 불구하고 수많은 intention lock 이 생기게 된다.
    • lock 이 많아질수록 관리에 따른 코스트가 발생한다.
    • 차라리 상위 객체에 락을 하나 걸어놓게 되면 더 효율적
    • 따라서 최적화를 위해 상위 수준의 락으로 변환하는 Lock Escalation 이라는 프로세스를 갖고 있다.

 

Record Lock

• 인덱스 레코드에 설정되는 Lock
• 테이블에 인덱스가 정의되지 않아도 InnoDB 에서는 숨겨진 Clustered Index 를 생성하기 때문에 이를 활용하여 Record Lock 을 적용
• SELECT name FROM test WHERE id = 1 FOR UPDATE
  • id 가 1인 레코드에 Record Lock 이 설정하기 때문에 다른 트랜잭션이 id 가 1인 레코드를 변경하는 작업을 수행할 수 없다.

 

Gap Lock

https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

• MySQL 서버에서는 위의 상태의 테이블에서 id = 1 인 레코드와 3인 레코드 사이의 간격과 3 ~ 6 사이의 간격을 잠글 수 있다.
• 실제 존재하지 않는 레코드 공간(간격)을 잠그는 것을 Gap Lock 이라고 한다.
  • primary key 뿐만 아니라 secondary index 에도 동일하게 사용된다.

https://idea-sketch.tistory.com/46

• 마찬가지로 103 사이에는 gap lock 으로 데이터를 insert 할 수 없는데,
• 위의 케이스라면, 아래와 같이 lock 이 설정된다.

https://idea-sketch.tistory.com/46

• 따라서 관련 데이터 뿐만 아니라 직전, 직후의 레코드 사이에도 락을 걸게된다.

 

Next-Key Lock

https://www.letmecompile.com/mysql-innodb-lock-deadlock/

• Record Lock + Gap Lock
  • 찾아진 데이터와 해당하는 인덱스 레코드 사이사이에 복합적으로 lock 이 걸린다.
• next-key lock 은 다음 레코드를 락 걸기 때문에 107 이후의 레코드를 삽입하는 것도 불가능하다.
• SELECT * WHERE pk > 4 and pk < 90 FOR UPDATE; 상황을 가정하면
  • 3 < pk < 97 범위에 gap lock 이 적용되었을 것이다.

•