[Real MySQL] 12장. 쿼리 종류별 잠금 (1) InnoDB의 기본 잠금 방식

먼저 트랜잭션이란 논리적인 작업셋의 완전성을 보장하기 위한 기능이고, 잠금이란 동시성을 제어하기 위한 기능이라고 할 수 있습니다.

1. InnoDB의 기본 잠금 방식

---

MySQL에서 일반적으로 사용 가능한 스토리지 엔진 가운데 InnoDB를 제외한 모든 스토리지 엔진은 대부분 테이블 잠금을 지원하고 있기 때문에 각 쿼리가 사용하는 잠금이 복잡하지도 않고 이해하기 어렵지도 않다.
하지만 InnoDB에서는 각 쿼리의 패턴별로 사용하는 잠금이 달라 복잡한 부분이 있기 때문에 주의해야 한다.

SELECT

REPEATABLE-READ 이하의 격리 수준에서는...

• InnoDB 테이블에 대한 SELECT 쿼리는 기본적으로 아무런 잠금을 사용하지 않는다.
• 또한 이미 잠금 레코드를 읽는 것도 아무런 제약이 없다.
• 만약 읽어야 할 레코드가 다른 트랜잭션에 의해 변경되거나 삭제되는 중이라면 InnoDB에서 관리하고 있는 데이터의 변경 이력인 언두 로그를 이용해서 레코드를 읽는다.
• 이처럼 InnoDB 테이블에서 SELECT만 수행할 때는 다른 트랜잭션의 쿼리에 영향을 받지 않으며, 별도의 레코드를 읽기 위해 대기하지도 않는다.

SERIALIZABLE 격리 수준에서는...

• 모든 SELECT 쿼리에 자동적으로 LOCK IN SHARE MODE가 덧붙여져서 실행되는 효과를 내기 때문에, 이 격리 수준에서 모든 SELECT 쿼리는 읽기 잠금을 걸고 레코드를 읽는다.
• 그래서 SERIALIZABLE 격리 수준에서는 MySQL 서버의 처리 성능이 떨어지게 되어, 일반적으로 MySQL에서는 REPEATABLE-READ or READ-COMMITED 격리 수준을 사용한다. (InnoDB 스토리지 엔진에서는 REPEATABLE-READ 격리 수준에서도 트랜잭션 번호를 사용해서 Phantom read 부정합이 발생하지 않는다)

 

SELECT 잠금 모드

Lost Update, 변경 유실

InnoDB, REPEATABLE 이하의 격리수준에서 SELECT시 잠금을 걸지 않는데, 변경 유실 등 잠금이 필요한 경우가 있다. 이때는 잠금 모드 옵션을 추가하면 된다.

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

• 두 가지 방법 모두 SELECT 쿼리로 읽은 레코드를 다른 커넥션의 트랜잭션에서 변경하지 못하게 막는 역할을 한다.

읽기 잠금 모드

SELECT * FROM employees FROM emp_no=10001 LOCK IN SHARE MODE;

• 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금을 다시 획득해야 한다. (트랜잭션 내에서 쓰기 잠금을 획득하기 위해 기다려야 할 수 도 있다는 의미)
• 만약 읽기 잠금을 걸어야 하는 레코드가 다른 트랜잭션에 의해 쓰기 잠금이 걸려 있다면 그 잠금이 해제될 때까지 기다려야 한다.
• 하지만 다른 트랜잭션에 의해 읽기 잠금이 걸려있을 때는 읽기 잠금끼리는 상호 호환이 되므로 별도의 대기 없이 읽기 잠금을 획득할 수 있다.
• 읽기 잠금을 가진 상태에서 다시 쓰기 잠금을 획득하는 과정은 데드락을 유발하는 가장 일반적인 형태이다. 만약 읽은 다음 변경까지 해야 한다면 LOCK IN SHARE MODE를 사용하기보다 처음부터 FOR UPDATE를 사용해 쓰기 잠금을 거는 것이 좋다.

쓰기 잠금 모드

SELECT * FROM employees FROM emp_no=10001 FOR UPDATE;

• 그래서 대상 레코드가 다른 트랜잭션에 의해 읽기 잠금이나 쓰기 잠금으로 사용되고 있다면 반드시 그 잠금이 해제될 때까지 대기해야 한다.
• FOR UPDATE는 SELECT 쿼리 문장을 읽은 레코드를 쓰기 모드로 잠그는데, 이는 다른 트랜잭션에 대한 영향도가 크기 때문에 반드시 읽은 레코드를 변경해야 할 때만 사용하는 것이 좋다.
• LOCK IN SHARE MODE나 FOR UPDATE와 같은 잠금 읽기 기능은 COMMIT이나 ROLLBACK이 실행되면 잠금이 해제되므로 반드시 하나의 트랜잭션에서만 유효하다는 점을 명심해야 한다.
• LOCK IN SHARE MODE나 FOR UPDATE를 사용하는 잠금 읽기는 TRY~FINALLY 구문으로 작성하고 FINALLY 구문에서는 COMMIT 이나 ROLLBACK을 사용해 반드시 트랜잭션 종료가 보장되도록 프로그램을 작성하는 것이 좋다. (물론 잠금 읽기 뿐만 아니라 트랜잭션을 사용할 때 지켜야 할 기본 규칙이다)
  • 만약 해제되지 않은 잠금의 누수가 발생하면 MySQL 서버에서는 다른 모든 커넥션이 잠금 대기 상태로 빠지고, 그로 인해 MySQL의 커넥션 수가 계속 증가하다가 결국 MySQL 서버는 아무런 처리도 할 수 없는 상태에 빠질 것이다.

INSERT, UPDATE, DELETE

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

• 즉, 커넥션의 AutoCommit이 활성화된 상태라 하더라도 SQL을 처리하기 위해 잠금을 걸고 해제하는 작업은 생략하지 않는다.
• 단 AutoCommit 모드에서는 각 SQL 문장별로 자동으로 트랜잭션이 시작되고 종료되는 것이다. AutoCommit이 비활성화된 상태에서는 SQL 문장의 실행과 함께 자동으로 트랜잭션이 시작되지만 트랜잭션의 종료는 반드시 COMMIT이나 ROLLBACK 명령을 사용해 수동으로 종료시켜야 한다.

UPDATE, DELETE

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

• 이러한 이유에서 InnoDB에서 UPDATE나 DELETE 문장이 인덱스가 엄청 중요하다는 것을 알 수 있다.
• 인덱스를 전혀 사용하지 못하는 UPDATE, DELETE 문장은 테이블의 모든 레코드에 잠금을 걸게 되므로 테이블 수준의 잠금을 사용하는 MyISAM보다 동시성이 더 떨어질 수도 있다.
• 쿼리를 튜닝할 때 SELECT 쿼리뿐만 아니라 UPDATE, DELETE 쿼리 문장도 꼭 함께 검토해야 한다.

추가 사항

---

이처럼 InnoDB 스토리지 엔진에서 인덱스는 빠른 검색이나 정렬 등의 목적으로도 사용되지만 InnoDB 내부적으로는 레코드 잠금의 기준으로도 사용된다.
복제를 사용하지 않는 MySQL 서버는 바이너리 로그가 필요하지 않을 수도 있다. 이처럼 바이너리 로그를 기록하지 않는다면 InnoDB의 트랜잭션 격리 수준을 REPEATABLE-READ가 아니라 READ-COMMITED로 사용할 수 도 있다.
트랜잭션 격리 수준이 READ-COMMITED에서는 인덱스와 관계없이 실제 변경되는 레코드만 잠금을 걸게 된다.

• 복제를 사용하지 않는다면 불필요한 바이너리 로그를 사용하지 않도록 설정하고, 트랜잭션 격리 수준을 READ-COMMITED로 사용하는 것을 고려해보자.

 

쿼리 종류별 잠금(2)에 대한 내용은 다음 링크에서 이어집니다.

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