Database Lock (2) - MySQL InnoDB Lock

글을 쓰게 된 이유

 

앞서 Lock (1)에서 Database lock을 사용하는 이유와 RDB에서 통용되는 lock까지 공부해 보았습니다.

이번에는 MySQL에서 사용하고 있는 Lock에 대해서 공부해 보겠습니다.

 

MySQL에서 사용하는 lock

MySQL에서 제공하는 lock은 어느 곳에서 동작하느냐를 기준으로 mysql engine lock, mysql storage engine lock으로 나눌 수 있습니다.

 

mysql engine lock은 global lock, table lock, named lock, metadata lock이 존재하고 storage engine lock은 innodb라 생각하고 S, X lock, intention lock, record lock, gap lock, next-key lock 등이 있습니다.

 

이번 글에서 다룰 lock은 mysql engine lock은 무엇이고 어떤 것들이 있으며 각 특징을 말하고, innodb lock에서는 record lock, gap lock, next-key lock에 대해서 알아보겠습니다.

 

MySQL engine lock

 

mysql engine lock은 모든 storage engine에 영향을 미칩니다.

 

global lock

global lock은 MySQL 전체 서버에 영향을 주는 lock입니다. session1에서 global lock을 획득한다면 session2에서는 select를 제외한 ddl, dml 문장이 모두 wait 합니다. MySQL 전체 서버이기 때문에 다른 테이블, 데이터베이스에 모두 영향을 미칩니다.

 

table lock

말 그대로 table lock은 table 단위로 설정되는 잠금입니다. MyISAM, MEMORY 테이블에는 데이터를 변경하는 쿼리를 실행하면 묵시적인 테이블 락을 획득하게 되고, 쿼리가 완료되면 자동으로 해제됩니다.

 

named lock

named lock은 DB의 객체(테이블, 뷰 등)에 lock을 거는 것이 아닌 명시한 문자열에 대해 lock을 획득하는 것입니다. named lock은 분산락을 구현하기 위해 사용된다고 합니다.

자세한 설명은 이 글을 참고해보시면 좋을 듯합니다.

 

metadata lock

metadata lock은 DB 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 명시적으로 얻은 lock이 아닌 metadata를 변경할 경우에 자동으로 획득, 해제하는 lock입니다.

 

MySQL InnoDB lock

 

MySQL 공식문서에서 MySQL InnoDB lock에 소개된 lock들입니다.

주황색 상자에 있는 lock들은 앞서 common lock에서 다뤘던 lock들이고, 이번 글에서는 파란색 상자에 있는 lock들을 공부해보도록 하겠습니다.

 

InnoDB lock은 MySQL engine lock에는 영향을 받지만 storage engine 끼리는 영향을 받지 않는다는 특징을 가지고 있습니다.

 

record lock

record lock은 index record에 대한 lock입니다.

select c from t where c = 10 for update;

위 sql에서는 t의 c 값이 10인 row에 다른 트랜잭션이 수정 연산을 할 수 없습니다.

 

 

gap lock

gap lock은 index record 사이의 gap에 대한 lock을 의미합니다.

select c from t where c between 10 and 20 for update;

위 sql에서는 t의 c 값에 10~20 모두 lock이 되기 때문에 예를 들어서 13 값을 삽입할 수 없습니다.

 

next-key lock

next-key lock은 record lock과 gap lock을 합쳐 놓은 형태입니다.

select c from t where c > 100;

//index c 는 102, 104, 106 존재

이 상황에서 102, 104, 106은 각각 record lock

-무한대 < gap lock ≤ 100

102 < gap lock ≤ 104

104 < gap lock ≤ 106

106 < gap lock < +무한대

와 같은 next-key lock이 가능합니다.

 

MySQL innoDB engine은 왜 repeatable read에서는 phantom read가 발생하지 않을까?

앞서 트랜잭션 격리 수준에서 innodb에서는 repeatable read에서는 phantom read가 발생하지 않는 이유가 gap lock과 next-key lock 때문이라고 했습니다.

앞서 예제를 다시 가져와보겠습니다.

 

이때, insert를 하게 되면 wait로 인해서 insert가 되지 않습니다.

그 이유는 id가 PK가 아니기 때문입니다. MySQL 공식 문서에서 보면 고유 인덱스를 사용하지 않은 경우에는 gap, next-key lock으로 인해 처음 레코드 이전, 마지막 레코드 이후 간격 lock을 걸기 때문입니다.

왜냐하면 pk나 유니크한 인덱스가 아니라면 중복이 발생할 수 있기 때문입니다.

 

다시 id를 PK로 설정하고 쿼리를 실행해 보겠습니다.

그러면 PK로 설정하여 고유 인덱스이기 때문에 인덱스 레코드만 잠그고 그 앞의 간격은 잠그지 않기 때문에 insert 쿼리가 실행되는 것을 알 수 있습니다.

 

그러면 왜 팬텀 리드가 발생하지 않을까요?

앞서 보시면 검색 조건이 있는 고유 인덱스인 경우에는 인덱스 레코드만 잠그고 그 앞의 간격은 잠그지 않게 됩니다. 즉, 그 조건 범위에 해당하는 인덱스를 모두 잠그기 때문에 팬텀 리드가 발생하지 않게 됩니다.

 

id >= 2 인 범위를 설정하게 되면 id >= 2인 레코드에 모두 lock을 걸게 됩니다.

그러면 이 앞의 간격 즉, id가 1인 경우에는 lock을 걸지 않기 때문에 insert가 되는 것을 알 수 있습니다.

 

정리하면 select ... for update와 같이 읽기 잠금에서는 고유 인덱스 존재/검색 조건 존재에 따라 lock을 하는 게 달라집니다.

고유한 검색 조건 + 고유 인덱스인 경우에는 인덱스 레코드만 잠그고 그 앞의 gap은 lock 하지 않습니다.

고유하지 않는 인덱스의 경우에는 이전 간격을 모두 lock 하여 insert가 되지 않습니다.

 

REFERENCES

Real MySQL

innodb 읽기 잠금(MySQL 공식 문서)

Innodb lock(MySQL 공식 문서)