[Real MySQL] 12. 쿼리 종류별 잠금

InnoDB의 기본 잠금 방식

InnoDB에서는 각 쿼리의 패턴별로 사용하는 잠금이 다르다.
먼저 기본적으로 각 쿼리가 어떤 잠금 방식을 사용하는지 알아보자.

SELECT

REPEATABLE-READ 이하의 트랜잭션 격리 수준에서 InnoDB 테이블에 대한 SELECT 쿼리는 기본적으로 아무런 잠금도 사용하지 않는다.
또한 이미 잠긴 레코드를 읽는 것도 아무런 제약이 없다.

SELECT 쿼리로 읽은 레코드를 잠그는 방법은 읽기 모드와 쓰기 모드 잠금으로 두 가지가 있다.

SELECT * FROM sample_tbl WHERE col1 = 10001 LOCK IN SHARE MODE;
SELECT * FROM sample_tbl WHERE col1 = 10001 FOR UPDATE;

LOCK IN SHARE MODE는 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금을 다시 획득해야 한다.
그래서 변경이 같이 필요하다면 처음부터 FOR UPDATE를 거는 것이 좋다.

위 잠금 읽기는 COMMIT이나 ROLLBACK이 실행되면 잠금이 해제되므로 반드시 하나의 트랜잭션에서만 유효하다.
만약 하나의 트랜잭션 안에서 두 가지 요청이 일어나는 경우, 첫 번째 요청에서 FOR UPDATE 등의 잠금이 걸린 채 과정이 종료되면 해당 커넥션은 대기 상태에 빠지는 위험이 있을 수 있다.
따라서 잠금 읽기는 항상 FINALLY 구문으로 트랜잭션의 종료를 보장해야 한다.

INSERT, UPDATE, DELETE

모두 기본적으로 쓰기 잠금을 사용하며, 필요 시에는 읽기 잠금을 사용할 수도 있다.

InnoDB에서는 UPDATE, DELETE 문장으로 실행할 때 SQL 문장이 조건에 일치하는 레코드를 찾기 위해 참조하는 인덱스의 모든 레코드에 잠금을 건다.
이는 해당 쿼리의 WHERE 조건에 일치하지 않는 레코드라도 잠금의 대상이 될 수 있음을 의미한다.

레코드에 잠금을 거는 주체는 InnoDB 스토리지 엔진이고, 업데이트할 레코드를 최종 결정하는 것은 MySQL 엔진인데, MySQL 엔진은 WHERE 절의 모든 조건이 아니라 인덱스를 사용할 수 있는 조건만 InnoDB 스토리지 엔진으로 전달하기 때문이다.
따라서 InnoDB 스토리지 엔진은 해당 인덱스의 모든 레코드에 잠금을 걸 수밖에 없다.

그러므로 테이블 크기와 상관 없이 UPDATE, DELETE 실행 시에도 인덱스의 생성을 고려해야 한다.
해당 UPDATE, DELETE 쿼리가 이용할 인덱스가 없다면 InnoDB 스토리지 엔진은 모든 레코드를 대상으로 잠금을 걸 것이기 때문이다.

"테이블의 레코드 건수가 적어서 인덱스를 생성하지 않아도 된다"라는 생각 보다는 "테이블의 레코드가 적으므로 인덱스를 더 생성해도 무리가 가지 않을 것이다"라고 생각을 바꿔야 한다.

복제를 사용하지 않는 MySQL 서버에서는 바이너리 로그를 사용하지 않는 것을 고려해 볼 수 있다.
바이너리 로그를 사용하지 않는다면 트랜잭션 격리 레벨을 REPEATABLE-READ 대신 READ-COMMITTED로 사용할 수도 있다.
이 때 READ-COMMITTED에서는 인덱스와 관계 없이 실제 변경되는 레코드만 잠금을 걸게 된다.

하지만 이는 인덱스 조건으로 먼저 검색해 잠금을 건 후, 대상이 아닌 레코드는 다시 잠금을 해제하는 방식이기 때문에 결론적으로는
READ-COMMITTED 격리 레벨이더라도 인덱스를 사용할 수 있게 해주는 것이 좋다.

---

SQL 문장별 잠금

SELECT

• SELECT ... FROM ...
  • InnoDB 테이블에서 기본 형태의 SELECT 쿼리는 별도의 잠금을 사용하지 않는다.
  • 만약 읽어야 할 레코드가 다른 트랜잭션에 의해 변경되는 중이라면 언두 로그를 사용해 레코드를 읽는다.
• SELECT ... FROM ... LOCK IN SHARE MODE
  • WHERE 절에 일치하는 레코드뿐 아니라 검색을 위해 접근한 모든 레코드에 대해 공유 넥스트 키 락(Shared next-key lock)을 필요로한다.
  • 읽기 잠금을 걸어야 하는 레코드가 쓰기 잠금이 걸려 있다면 해당 잠금이 풀릴 때까지 대기해야 하며, 읽기 잠금이 걸려있을 때는 상호 호환이 되므로 별도의 대기 없이 읽기 잠금을 획득할 수 있다.
• SELECT ... FROM ... FOR UPDATE
  • WHERE 조건절에 일치하는 레코드를 검색하기 위해 접근한 모든 레코드에 대해 베타적 넥스트 키 락(Exclusive next-key lock)을 걸게 된다.
  • 언두 로그를 통한 읽기가 불가능하기 때문에 일관된 읽기를 할 수 없다.

INSERT

INSERT 문장은 기본적으로 배타적 레코드 잠금(쓰기 잠금)을 사용한다.
만약 해당 테이블에 프라이머리 키나 유니크 키가 존재한다면 중복 체크를 위해 공유 레코드 잠금(읽기 잠금)을 먼저 획득해야 한다.
또한 MySQL의 INSERT는 추가적으로 인서트 인텐션 락이라는 색다른 잠금 방식도 사용한다.

인서트 인텐션 락은 INSERT를 실행할 의도를 지닌 쿼리가 획득해야 하는 잠금이다.
이는 일종의 갭 락이며, 인서트 인텐션 락끼리는 서로 호환되기 때문에 여러 트랜잭션이 동시에 해당 잠금을 획득할 수 있다.

InnoDB에서 인서트 인텐션 락을 사용하는 이유는 InnoDB의 갭 락으로 인한 동시성 감소를 최소하하기 위해서이다.

배타적 잠금은 내가 쓰기를 하는 동안 남들이 쓰지 못하게 하는 것이다.
공유 잠금은 내가 읽는 동안 남들이 내가 읽고 있는 데이터를 변경하거나 삭제하지 못하게 하는 것이다.

• INSERT INTO ... ON DUPLICATE KEY UPDATE ...
  • 중복된 키 값이 이미 있는지 판단하기 위해 공유 잠금을 걸어야 한다.
  • 레코드가 존재한다면 배타적 잠금을 걸고 업데이트를 수행하며, 없다면 인서트 인텐션 락을 걸고 INSERT를 수행한다.
• REPLACE
  • 중복된 키 값이 이미 있는지 판단하기 위해 공유 잠금을 걸고, 존재한다면 배타적 잠금을 걸고 레코드를 삭제한다. 나머지 과정은 INSERT와 동일하게 처리된다.
• INSERT INTO tb_new ... SELECT ... FROM tb_old ...
  • tb_new 테이블에는 새로 INSERT되는 레코드에 배타적 레코드 락을 획득하고, tb_old 테이블의 대상 레코드에는 공유 넥스트 키 락을 설정한다.
  • 넥스트 키 락으로 간격까지 잠그는 이유는 대상 레코드의 범위를 동결해 복제의 무결성을 보장해 주기 위함이다.
  • 이 방법은 다른 쿼리들에 영향을 주는 공유 잠금이 걸리기 때문에 두 개의 쿼리로 나눠서 실행하는 것이 좋다.

UPDATE / DELETE

단순한 WHERE 절의 UPDATE, DELETE는 참조한 모든 레코드에 배타적 넥스트 키 락이 걸린다.
넥스트 키 락을 거는 이유는 WHERE절의 처리 범위를 고정하기 위해서이다.

JOIN UPDATE, JOIN DELETE는 변경 대상 테이블에는 배타적 잠금이 걸리고, 단순 참조만 하는 조인 테이블에서는 공유 잠금이 걸린다.

---

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.