쿼리별 잡히는 lock에 대해 알아 보기전에 InnoDB의 lock에 대해 먼저 알아 보는 시간을 가져 보려 했지만 워낙 잘 정리가 된 블로그가 있어서 공유한다. 아래의 내용들을 보기 전에 링크 되어 있는 블로그의 글을 먼저 읽어 본다면 많은 도움이 되리라 생각한다.
MySQL InnoDB lock & deadlock 이해하기 - www.letmecompile.com/mysql-innodb-lock-deadlock/
SELECT … FROM
- consistent read 의 경우 transaction isolation level이 SERIALIZABLE이 아니면 lock을 잡지 않는다.
LOCKING READ(SELECT … FOR UPDATE, SELECT … LOCK IN SHARE MODE), UPDATE, DELETE
- unique index에 unique search condition을 사용하는지, range-type search를 사용하는지에 따라 사용되는 lock이 달라짐
- unique index, unique search condition인 경우 해당 record에만 lock을 검. gap lock 사용 안함
- unique search condition이 아니거나, unique index가 아닌 경우 조건에 맞는 모든 index range가 다른 세션으로 부터 추가 insert를 막기 위해 gap lock 또는 next-key lock을 이용하여 블록 시킴
- select … for update 를 사용하면 인덱스가 다르다고 하더라도 select … lock in share mode 혹은 다른 읽기 오퍼레이션을 막음. 하지만 consistent read는 모든 lock을 무시하기 때문에 동작 가능함.
UPDATE … WHERE ...
- 조건에 맞는 모든 record 들에 대해 exclusive next-key lock을 검
- unique index에 unique search condition 인 경우, index record lock만 사용
- update 수정이 여러 index record에 걸쳐 일어 날때, 묵시적인 lock이 조건에 맞는 secondary record에 걸린다. 이 작업은 신규 secondary-record 추가(insert) 전 중복 검사 할 때 와 실제 추가시 shared lock 또한 건다.
DELETE FROM … WHERE ...
- 조건에 맞는 모든 record에 대해 exclusive next-key lock 설정
- update와 마찬가지로 unique index에 unique search condition 인 경우, index record lock만 사용
INSERT
- insert 되는 row에 exclusive lock 검. index-record lock임. next-key lock이 아님. 심지어 gap lock은 아예 없음.
- record index가 중복이 아니라면 다른 세션에서 insert하는것을 막지 않음.
row를 insert 하기 전에, insert intention gap lock 이라고 불리는 gap lock의 일종인 lock이 셋팅 됨(intention lock이니까 table level lock이다).
이 lock은 동일 인덱스 '갭'에 insert 하는 여러 트랜잭션들이 동일 인덱스에 insert 하는 것만 아니라면 서로를 기다리지 않아도 됨을 의미.
예를 들어 인덱스 4, 7이 있다고 가정하고, 각가 5와 6을 insert 하려는 트랜잭션들이 있다면, 이 트랜잭션들은 insert를 위한 exclusive lock을 획득하기 전에 insert intention lock를 먼저 걸지만 4, 5, 6, 7 그 어떤 것도 서로 겹치는 index는 없기 때문에 서로를 블록하지 않음. - 만일 중복키 오류가 발생하게 되면..(여기서 부터 골때림). 중복 키에 shared lock이 걸림. 요건 나중에 데드락을 유발할 수 있는 아주(?까진 아니지만 암튼) 위험한 놈임.
예를 들어 동일 key에 여러 세션에서 동시에 insert를 하려고 하면 dead lock 발생함. 아래는 그 발생 시나리오임.
간단하게 테스트용 테이블 하나 만듦.CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
세션 1에서 아래 쿼리 날림START TRANSACTION; INSERT INTO t1 VALUES(1); -- X lock
세션 2에서 아래 쿼리 날림START TRANSACTION; INSERT INTO t1 VALUES(1); -- S lock
세션 3에서 아래 쿼리 날림START TRANSACTION; INSERT INTO t1 VALUES(1); -- S lock
이렇게 되면 세션1이 exclusive lock을 잡고 나머 세션에서 insert 하려는 것들은 중복키 이기 때문에 shared lock이 걸린다.
세션 1이 정상적으로 commit 까지 수행하면 나머지 두 세션은 duplicate key 오류를 받지만 rollback을 하게 되면 다른 두 세션들은 insert를 하기위해 exclusive lock을 서로 획득하려고 한다. 하지만 각각 이미 shared lock을 가지고 있으므로 서로 exclusive lock을 획득하지 못하고 둘중에 하나는 deadlock 오류를 발생 시킨다.
하지만 걱정 안해도 된다. 그냥 쿼리가 실패하는거지 mysql db가 dead lock에 걸려 아무것도 못하는 사태는 발생하지 않는다. - deadlock error code 1213
1213 에러가 발생한다면 해당 transaction을 다시 수행 할수 있도록 해야 한다.
