[MySQL] InnoDB의 Lock 알아보기

transaction rollback 시 auto increment 이슈에 대해 포스팅을 쓰고 Transaction Isolation level 자체에 대한 지식이 부족하다고 생각되어 포스팅을 쓰게 되었다.

*아주 정리가 잘 되어있는 글을 보고 공부를 하였다.

Intro

ACID 원칙을 철저히 지킬수록 동시성은 낮아진다. ACID 원칙을 희생하여 동시성을 얻을 수 있는데, DB엔진이 제공해주는 isolation level에 따라 다양한 locking 전략을 적용할 수 있다. 이를 적용하면, isolation 원칙에 어긋날수록 문제가 발생할 가능성은 커지지만 동시성을 높일 수 있다.

Isolation level

ANSI/ISO SQL standard에서 정의한 isolation level은 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 이다.

InnoDB의 Lock

InnoDB는 ACID 원칙과 동시성을 최대한 보장하고자 다양한 종류의 Lock을 제공한다. 빈번하게 사용되는 몇가지 종류를 알아보자.

Row-level lock
Record lock
Gap lock

Row-level Lock

가장 기본적인 lock의 종류. 테이블의 row마다 걸리는 row-level lock이다. row-level lock에는 두가지 종류가 있다.

shared lock
exclusive lock

shared lock

READ에 대한 lock이다. 다만, 일반적인 select 쿼리에 사용하는 것이 아닌 SELECT ... FOR SHARE 등 일부 쿼리는 READ 작업 수행시 lock을 건다.

exclusive lock

write에 대한 lock 이다. SELECT ... FOR UPDATE나 UPDATE, DELETE 등의 수정, 삭제 쿼리를 날릴 때 각 ROW에 걸리는 lock이다.

• 여러 transaction이 동시에 한 row에 shared lock을 걸 수 있다.
• 여러 transaction이 동시에 한 row를 읽을 수 있다.
• shared lock이 걸려있을 경우 exclusive lock을 걸 수 없다. (읽는 중 수정, 삭제가 불가능하다)
• exclusive lock이 걸려있을 경우 shared lock, exclusive lock 둘다 걸 수 없다. (쓰는 중 읽기, 쓰기, 수정, 삭제 불가능)

Record lock

Record lock은 DB의 index record에 걸리는 lock이다. 여기도 동일하게 shard lock, exclusive lock 두종류가 존재한다. (index 개념에 대한 포스팅은 여기를 참고해주세요)

shared lock
exclusive lock

row-level lock과 동일한 맥락이다. 다만, lock의 범위가 index인 것이다.

Gap lock

Gap lock은 DB index 레코드의 gap에 걸리는 lock이다. gap은 특정 범위의 레코드들을 말한다. 만약 id가 1,2,7,8인 칼럼이 존재하고 id 칼럼에 index가 걸려있다면 2 < id < 7 에 gap lock을 걸어 새로운 row가 추가되지 못하도록 막는다.

즉, record lock은 이미 존재하는 row가 변경되지 않도록 lock을 거는 반면 gap lock은 새로운 row가 추가되는 것을 방지하기 위함이다.

Lock 해제

위에 언급한 모든 lock은 transaction이 commit되거나 rollback될 때 함께 unlock된다.