MySQL 스토리지 엔진(InnoDB)의 락 종류
락(Lock)에는 적용 요소와 적용 상황에 따라 분류가 나뉜다.
•
적용 요소에 따른 분류(Row-level Lock)
◦
Shared Lock
◦
Exclusive Lock
◦
Intention Lock
•
적용 상황에 따른 분류
◦
Record Lock
◦
Gap Lock
◦
Next-Key Lock
◦
Insert Intention Lock
◦
Auto Increment Lock
Shared Lock(S Lock)
데이터를 읽을 때 사용하는 row-level 락으로, S Lock을 획득했다면 같은 S Lock이 잠금되어 있는 데이터에 접근해 읽는 것이 가능하다.
SELECT ... FOR SHARE
SQL
복사
위와 같은 일부 SELECT 쿼리를 수행할 때, InnoDB가 각 row에 S Lock을 잠그게 된다.
Exclusive Lock(X Lock)
데이터를 쓸 때 사용하는 row-level 락으로, X Lock이 잠금되어 있는 데이터에는 다른 모든 트랜잭션에서 접근할 수 없다.
SELECT ... FOR UPDATE
SELECT ... FOR DELETE
SQL
복사
위와 같은 수정 혹은 삭제를 위한 SELECT 쿼리를 수행하면, 해당 작업이 끝날 때까지 InnoDB에서 각 row에 X Lock을 잠그게 된다.
S Lock과 X Lock 경쟁 관계
S-Lock | X-Lock | |
S-Lock | X | O |
X-Lock | O | O |
1.
여러 트랜잭션이 하나의 row에 S Lock을 걸 수 있다.(S Lock끼리는 경쟁하지 않는다)
2.
S Lock이 걸려있는 row에 다른 트랜잭션이 X Lock을 걸 수 없다.
3.
X Lock이 걸려있는 row에 다른 트랜잭션이 S Lock, X Lock을 걸 수 없다.
Intention Lock
Table-level Lock으로, 특정 row에 대해서 나중에 어떤 row-level 락을 걸 것인지 알려주기 위해 미리 table-level에 걸어두는 락이다.
SELECT ... LOCK IN SHARE MODE
SQL
복사
위 SQL 문이 실행되면, table-level에 Intention Shared Lock(IS)이 걸리고 row-level에 S Lock이 걸리게 된다.
SELECT ... FOR UPDATE
SELECT ... FOR INSERT
SELECT ... FOR DELETE
SQL
복사
위 SQL 문들이 실행되면, table-level에 Intension Exclusive Lock(IX)이 걸리고 row-level에 X Lock이 걸리게 된다.
LOCK TABLES
ALTER TABLE
DROP TABLE
SQL
복사
위의 SQL 문을 수행할 때는, IS와 IX 모두 block하는 table-level 락이 걸리고 IS, IX 락을 획득하려는 트랜잭션은 대기상태에 빠지게 된다.
이와 같이 row-level과 table-level에 두 번 락을 하는 이유는 다른 트랜잭션에서 해당 테이블의 특정 row에 락을 거는 것을 원천적으로 방지할 수 있기 때문이다.(반대의 경우에도 마찬가지)
IS, IX 락은 여러 트랜잭션에서 동시에 접근 가능하지만, row-level의 실제 락인 S, X 락에서 접근 제어를 하게 된다.
S | IS | X | IX | |
S | X | X | O | O |
IS | X | X | O | X |
X | O | O | O | O |
IX | O | X | O | X |
레코드 락(Record Lock)
MySQL의 레코드락은 테이블의 레코드가 아닌 인덱스의 레코드에 걸리는 Lock이다.
위 테이블은 last_name이 J로 시작하는 모든 레코드를 검색한 결과로, 인덱스를 통해 수행된다. 이와 같은 상황에서 인덱스를 통해 검색되는 모든 레코드에 레코드 락이 걸린다(Jo, Joe). 인덱스를 통해 검색된 모든 레코드 락이 걸리고, 만약 적당한 인덱스가 없다면 테이블의 모든 레코드에 락을 걸고 풀스캔을 시행하기 때문에 동시성이 떨어져 성능에 큰 영향을 미친다.
레코드 락이 페이지 락이나 테이블 락으로 에스컬레이션되는 경우는 없다. 레코드 락에도 row-level Lock과 마찬가지로 S Lock과 X Lock이 있다.
갭 락(Gap Lock)
갭 락은 레코드가 아닌 레코드와 레코드 사이의 간격(Gap)을 잠금으로써 레코드의 생성 및 수정, 삭제를 제어하는 방식이다. 여기서의 갭이란 인덱스 중 DB에 실제 레코드가 없는 부분을 의미한다.
위의 테이블은 레코드 락의 예시와 동일한 상황에서의 갭 락이 걸리는 것을 표현한 것이다. 이처럼 갭 락은 인덱스의 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다.
이해를 돕기위해 또 다른 예시를 보자. 1 이상 5 이하의 조건으로 데이터를 검색할 때 데이터가 2와 3만 존재한다면, 존재하지 않는 1과 4, 5가 추가될 수 있는 공간에 갭 락이 걸리게 된다.
이처럼 갭 락은 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 잠금을 거는 것이고, 때문에 데이터의 유일성이 보장되는 PK 또는 유니크 인덱스에 의한 작업에서는 갭락이 사용되지 않는다.
넥스트 키 락(Next Key Lock)
넥스트 키 락은 레코드 락과 갭 락을 합친 잠금으로, 갭 락은 단독으로 사용되기보다 주로 넥스트 키 락의 일부로 함께 사용된다.
MySQL에서는 REPEATABLE READ에서 phanthom read를 막기 위해 넥스트 키 락을 사용하기 때문에, phanthom read가 거의 발생하지 않는다.
갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 리플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어 내는 것이 주 목적이다. 하지만 넥스트 키 락과 갭 락으로 인해 교착상태(Deadlock)가 발생하거나 다른 트랜잭션을 기다리는 일이 많아, 바이너리 로그 포맷을 row 형태로 바꾸어 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
자동 증가 락(Auto Increment Lock)
MySQL은 자동 증가하는 숫자값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공하고, AUTO_INCREMENT 컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 table-level 잠금인 자동 증가 락(Auto-INC Lock)을 사용한다.
자동 증가 락은 INSERT와 REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 사용된다. 자동 증가 락은 테이블에 1개만 존재하기 때문에, 한 쿼리에서 락을 획득하여 채번 중이라면 다른 쿼리에서는 락을 기다려야 한다. 대부분의 경우 짧은 순간만 걸린 후 즉시 해제되므로 문제가 되지 않는다.
자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않는다. 한 트랜잭션에서 자동 증가 채번에 성공했지만 이후 실패하여 롤백 되더라도, 자동 증가값은 복구되지 않고 그대로 남는다. 자동 증가 값을 복구하려면 아래의 쿼리를 사용해야 한다.
ALTER TABLE tablename AUTO_INCREMENT = 1
SQL
복사
