[MySQL] InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 이전에 봤던 MySQL 엔진 레벨의 잠금과 별개로, 

스토리지 엔진 내부에서 레코드 기반의 잠금방식을 지원한다.

 

이전에는 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보가 얻기 어려웠는데

최근 버전에는 MySQL 서버에 존재하는 information_schema 데이터베이스에 존재하는

INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WATES 라는 테이블을 조인해서 조회 시 확인할 수 있다.

 

InnoDB는 기본적으로 MVCC를 이용한 낙관적인 동시성 제어를 지원하지만

특정 상황에 S-Lock, X-Lock을 이용해 비관적인 동시성 제어를 사용한다.

X-Lock

배타적 락이라고도 불리는 이 잠금은, read/write에 대한 잠금이다.

SELECT .. FOR UPDATE 문이나, UPDATE, DELETE 등의 쿼리에 X-Lock을 걸 수 있다.

이는 어떤 트랜잭션에 데이터를 변경하고자 할 때 해당 트랜잭션이 완료될 때까지 해당 테이블 혹은 row를 다른 트랜잭션에 읽거나 쓰지 못하도록 한다.

 

흔히들 어떤 레코드에 이 X-Lock이 걸려있으면, S-Lock을 걸 수 없다고 하는데,

X-Lock이 걸린 레코드에 대해 단순 SELECT를 할 수 없다고 오해할 수 있다.

하지만 단순 SELECT가 S-Lock을 차지한다는 것은 레코드 레벨의 락이 아닌 테이블의 메타데이터 레벨의 잠금을 얻는걸 뜻한다.

따라서 레코드에 X-Lock이 걸려도 단순 조회가 가능하다. (물론 격리 수준에 따라 어떤 데이터를 읽을지는 달라진다)

 

S-Lock

공유 락이라고도 불리는 이 잠금은, read에 대한 잠금이다.

S-Lock은 여러 트랜잭션이 동시에 레코드를 읽을 수 있도록 허용한다.

S-Lock끼리는 호환이 되지만, S락과 X락은 충돌한다. 즉 읽는 동안 다른 트랜잭션이 해당 레코드에 X락을 획득할 수 없다.

"SELECT ... LOCK IN SHARE MODE" 명령으로 S-Lock을 획득할 수 있다.

 

X-Lock에서도 말했듯이, 어떤 레코드에 X-Lock이 걸리면 해당 레코드에 대해 S-Lock을 얻을 수 없다.

하지만 단순 SELECT = 레코드 레벨의 S-Lock 은 잘못된 사실이다. 따라서 조회는 가능하다는 것을 알아야한다.

 

Intention Lock (의도 락)

의도 락은 테이블 락과 레코드 락이 공존할 수 있도록 만든다.

기본적으로 테이블의 레코드에 대해서 나중에 요청되는 잠금이 어떤 형태의 잠금인지 표현하는데,

레코드 단위 잠금을 획득하기 전 이 인텐션 락을 먼저 획득한다.

InnoDB에서는 2가지 타입의 인텐션 락을 수행한다

• Intention Shared Lock (IS) : 트랜잭션이 테이블의 개별 레코드에 공유 락 설정
• Intention Exclusive Lock(IX) : 트랜잭션이 테이블의 개별 레코드에 배타 락 설정

만약 SELECT ... LOCK IN SHARE MODE를 실행해 레코드 단위의 S-Lock을 얻고자 한다면,

해당 테이블에 대해서 먼저 IS 이상의 락을 걸고, 이후 S-Lock이 걸리며,

SELECT .. FOR UPDATE를 실행해 해당 레코드의 X-Lock을 얻고자 한다면

해당 테이블에 대해서 먼저 IX 이상의 락을 걸고, 그다음에 X-Lock이 걸린다.

 

이렇게 2번에 걸쳐 락을 거는 이유는, 당연히 이미 한 트랜잭션이 테이블에 대한 락을 걸어두면 다른 트랜잭션은 해당 레코드에 접근하여 락을 걸지 못하기 때문이다. 만약 쓰기 작업의 경우라면, 이미 IX락이 걸려있기 때문에 데이터를 마음대로 변경할 수 없다.

 

레코드 수준의 S, L 잠금과 헷갈리니 특정 잠금을 했을 때 다른 트랜잭션의 어떤 동작이 호환되는지 표로 정리해보자.

 

	S	IS	X	IX
S	호환 가능	호환 가능	충돌	충돌
IS	호환 가능	호환 가능	충돌	호환 가능
X	충돌	충돌	충돌	충돌
IX	충돌	호환 가능	충돌	호환 가능

 

 

레코드 락

레코드 락은 위 X-Lock, S-Lock에도 잠깐 나온 내용인데, 레코드에 대해서 잠금을 거는 것이다.

다른 상용 DBMS의 레코드 락과 동일한 역할을 하지만, 한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체를 잠그는 것보다는, 인덱스의 레코드를 잠근다.

인덱스가 없더라도 클러스터링 키를 내부적으로 생성하는것처럼, 여기서도 자동으로 생성된 클러스터 인덱스를 레코드 잠금에 사용한다.

 

상기했듯이, 레코드 레벨의 S-Lock과 X-Lock을 얻는 방법은 다음과 같다.

# id = 1인 레코드에 대해 S-Lock
SELECT id FROM tb_1 WHERE id = 1 LOCK IN SHARE MODE;

# id = 1인 레코드에 대해 X-Lock
SELECT id FROM tb_1 WHERE id = 1 FOR UPDATE;

 

레코드 자체를 잠그는게 아닌 인덱스를 잠그는 이유에 대해서는 나중에 다시 다루겠다.

 

갭 락

다른 DBMS와의 또 다른 차이점이 바로 갭 락이다.

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

이 잠금의 역할은 레코드와 레코드 사이에 새로운 레코드가 INSERT 되는 것을 제어하는데, 넥스트 키 락의 일부로 자주 사용된다.

 

간단한 예시로

select * from tb_1 where id between 1 and 10;

위와같은 쿼리는 1~10 사이에 X락이 걸리는 것과 동일하기 때문에 1~10사이에 새로운 데이터를 INSERT하면 대기하게 된다.

격리 레벨이 READ COMMITED인 경우에는 갭 락을 사용하지 않는다.

 

넥스트 키 락

레코드 락과 갭 락을 합친 형태를 넥스트 키 락이라고 한다. REPEATABLE READ 격리 수준을 사용해야한다.

innoDB가 테이블 인덱스를 검색하거나 스캔하면 실제 인덱스 레코드에  대해 레코드 단위 락을 걸게 된다.

 

자동 증가 락

MySQL에서는 auto_increment라는 컬럼을 통해서 자동으로 증가하는 값을 관리할 수 있다.
auto_increment가 사용된 테이블에 여러 레코드가 삽입되는 경우 자동 증가 락을 통해 auto_increment도 일관성 있게 증가해야 한다.
 
자동 증가 락의 경우 기본적으로 insert, replace 같이 새로운 레코드를 삽입하는 쿼리에서만 필요하며, auto_increment 값을 가져오는 순간에만 락이 걸렸다가 즉시 해제된다.
테이블당 단 1개만 존재하기 때문에, 2개의 insert문이 동시에 실행되는 경우 나머지 하나의 insert는 락이 해제되기를 기다려야한다.

 

명시적으로 자동 증가 락을 획득/해제하는 방법은 없다.

MySQL5.1 부터는 어떤 식으로 자동 증가 락을 설정할지 innodb_autoinc_lock_mode 시스템 변수로 관리 가능하다.

(0 : 모든 INSERT에 대해 자동 증가 락 사용, 1 : Bulk Insert 시에만 사용, 2 : 절대 사용 x)