[Real MySQL] 12장. 쿼리 종류별 잠금 (2) SQL 문장별 잠금

배타적 락 vs 공유 잠금

---

배타적 락

• 즉 배타적 락이란 "내가 쓰기를 하는 동안 남들이 쓰지 못하게 하는 잠금"

공유 잠금

• 즉 공유 잠금이란 "내가 읽는 동안 남들이 내가 읽고 데이터를 변경하거나 삭제하지 못하게 하는 잠금"
• 참고로 SELECT만을 의미하는 것이 아닌, INSERT, UPDATE , DELETE 문장을 실행할 때도 읽기가 필요하다.

SQL 문장별로 사용하는 잠금 - SELECT

---

SELECT 쿼리

• InnoDB 테이블에서는 기본 형태의 SELECT 쿼리는 별도의 잠금을 사용하지 않는다. (REPEATABLE-READ 이하 격리 수준에서)
• 만약 읽어야 할 레코드가 다른 트랜잭션에 의해 변경되거나 삭제되는 중이라면 InnoDB에서 관리하고 있는 데이터의 변경 이력인 언두 로그를 이용해서 레코드를 읽는다.
• 이처럼 InnoDB 테이블에서 SELECT만 수행할 때는 다른 트랜잭션의 쿼리에 영향을 받지 않으며, 별도의 레코드를 읽기 위해 대기하지도 않는다.
• 단 격리 수준이 SERIALIZABLE인 경우에는 기본적으로 읽기 모드 잠금 (LOCK IN SHARE MODE)가 걸린다.

SELECT FROM LOCK IN SHARE MODE

• LOCK IN SHARE MODE 옵션이 사용된 SELECT 쿼리 문장은, WHERE 절에 일치하는 레코드뿐 아니라 검색을 위해 접근한 모든 레코드에 대해 공유 넥스트 키락을 필요로 한다.
• 만약 읽기 잠금을 걸어야 하는 레코드가 다른 트랜잭션에 의해 쓰기 잠금이 걸려 있다면 그 잠금이 해제될 때까지 기다려야 한다.
• 하지만 다른 트랜잭션에 의해 읽기 잠금이 걸려있을 때는 읽기 잠금끼리는 상호 호환이 되므로 별도의 대기 없이 읽기 잠금을 획득할 수 있다.

SELECT FROM FOR UPDATE

• FOR UPDATE 옵션이 사용된 SELECT 쿼리 문장도 WHERE 조건절에 일치하는 레코드를 검색하기 위해 접근한 모든 레코드에 대해 배타적 넥스트 키 락을 걸게 된다.
• 그래서 대상 레코드가 다른 트랜잭션에 의해 읽기 잠금이나 쓰기 잠금으로 사용되고 있다면 반드시 그 잠금이 해제될 때까지 대기해야 한다.
• FOR UPDATE가 사용되면 SELECT 쿼리는 스냅 샷을 이용한 읽기를 사용하지 못하기 때문에 일관된 읽기가 무시된다.

정리

• REPEATABLE-READ 이하의 격리 수준을 사용하는 InnoDB 스토리지 엔진에서는 기본적으로 SELECT 쿼리 시 잠금이 걸리지 않는다.
• SELECT 쿼리 시 잠금이 필요한 경우 읽기 잠금과 쓰기 잠금 두 가지 잠금 모드가 존재한다.
• 읽기 잠금 모드는 LOCK IN SHARE MODE 옵션을 통해 잠금을 획득할 수 있다.
  • 읽기 잠금 모드는 해당 레코드가 쓰기 잠금이 걸려 있다면 블로킹된다.
• 쓰기 잠금 모드는 FOR UPDATE 옵션을 통해 잠금을 획득할 수 있다.
  • 쓰기 잠금 모드는 해당 레코드가 읽기 잠금이나 쓰기 잠금이 걸려있다면 블로킹된다.
• 트랜잭션이 COMMIT or ROLLBACK을 통해 종료되면 잠금이 해제된다. (만약 잠금을 해제하지 않으면 데드락이 걸린다)

SQL 문장별로 사용하는 잠금 - INSERT, UPDATE, DELETE

---

INSERT 쿼리의 잠금

• INSERT 문장은 기본적으로 배타적 레코드 잠금을 사용한다. 만약 해당 테이블에 PK나 유니크 키가 존재한다면 중복 체크를 위해 공유 레코드 잠금을 먼저 획득해야 한다. 또한 MySQL의 INSERT 문장은 추가적으로 인서트 인텐션 락이라는 조금 색다른 잠금 방식도 사용한다.
• 인서트 인텐션 락은 InnoDB의 갭 락으로 인해 동시성 감소를 최소화하기 위한 락이다.
• 인서트 인텐션 락 덕분에 서로 충돌하는 값을 INSERT 하지 않는 이상, 동일 간격에 대해 서로 간섭을 받지 않고 동시에 INSERT가 처리될 수 있다.

UPDATE 쿼리의 잠금

• 단순 UPDATE 문장은 WHERE 조건에 일치하는 레코드를 찾기 위해 스캔한 모든 레코드에 배타적 넥스트 키 락을 건다.
• 단순히 레코드만 잠그지 않고 간격까지 잠그는 것은 팬텀 레코드의 발생을 막기 위해서이다.

DELETE 쿼리의 잠금

• 단순 DELETE 문장은 UPDATE 문장과 똑같이 WHERE 조건에 일치하는 레코드를 찾기 위해 스캔한 모든 레코드에 대해 배타적 넥스트 키 락을 건다.

정리

• InnoDB 테이블에서 INSERT 쿼리 시 인서트 인텐트 락을 사용해서, (PK나 유니크 키) 충돌하는 값을 INSERT 하지 않는 이상, 동시에 INSERT가 처리될 수 있다.
• 일반적인 UPDATE, DELETE 쿼리 시 WHERE 조건에 일치하는 레코드를 찾기 위해 스캔한 모든 레코드에 대해 배타적 넥스트 키 락을 건다.