[MySQL]MySQL 벼락치기(6) - 트랜잭션과잠금(2)

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

InnoDB 의 기본 잠금 방식

SELECT

REPEATABLE-READ 이하의 트랜잭션 격리 수준에서 InnoDB 테이블에 대한 SELECT 쿼리는 기본적으로 아무런 잠금을 사용하지 않는다.(http://idea-sketch.tistory.com/45) REPEATABLE-READ보다 더 높은 격리 수준인 SERIALIZABLE 격리 수준에서는 모든 SELECT 쿼리에 자동적으로 LOCK IN SHARE MODE 가 덧붙여져서 실행되는 효과를 내기 때문에 이 격리 수준에서는 모든 SELECT 쿼리가 읽기잠금을 걸고 레코드를 읽는다. 
SELECT 쿼리로 읽은 레코드를 잠그는 방법은 읽기 모드와 쓰기모드 잠금으로 두가지가 있다.

• 읽기 모드
  

  SELECT * FROM employees WHERE emp_no = 100001 LOCK IN SHARE MODE;
  

• 쓰기 모드
  

  SELECT * FROM employees WHERE emp_no = 100001 FOR UPDATE;
  

두가지 방법 모두 SELECT 쿼리로 읽은 레코드를 다른 커넥션의 트랜잭션에서 변경하지 못하게 막는 역할을 한다. 하지만 LOCK IN SHARE MODE는 읽기잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금을 다시 획득해야 한다. 읽기 잠금을 가진 상태에서 다시 쓰기 잠금을 획득하는 과정은 데드락을 유발하는 가장 일반적인 형태이다. 

INSERT, UPDATE, DELETE

INSERT, UPDATE, DELETE 쿼리는 기본적으로 쓰기 잠금을 사용하며 필요시에는 읽기잠금을 사용할 수도 있다. AutoCommit이 활성화된 상태에서도 BEGIN이나 START TRANSACTION 명령을 실행해 명시적으로 트랜잭션을 시작할 수도 있는데 이때는 AutoCommit이 비활성된 상태에서와 같이 반드시 COMMIT 이나 ROLLBACK 명령을 이용해 수동으로 트랜잭션을 종료해야 한다.

InnoDB 에서 UPDATE, DELETE 문장을 실행할 때 SQL 문장이 조건에 일치하는 레코드를 찾기 위해 참조(스캔)하는 인덱스의 모든 레코드에 잠금을 건다.  여기서 참조(스캔)한 레코드에 점금을 걸었다는 사실은 실제로 해당 쿼리 문장의 WHERE 조건에 일치하지 않는 레코드도 잠금 대상이 될 수 있음을 의미한다. InnoDB는 쿼리의 WHERE 절에 명시된 모든 조건에 일치하는 레코드만 선별적으로 잠그는 것이 불가능하다. 

InnoDB가 레코드를 잠그는 방식을 예제로 한번 보자.

UPDATE employees
SET last_name = '....'
WHERE first_name = 'Georgi' AND gender=  'F';

위의 UPDATE 문장을 사용할때 employees 테이블에는 first_name 컬럼만 가진 인덱스가 있다고 가정해보자. 
이럴경우 InnoDB 스토리지 엔진에서는 first_name이 'Georgi' 인 모든 레코드를 잠그지만 최종적으로는 first_name이 'Georgi' 이면서 성별이 'F'인 사원의 last_name만 변경하게 된다.

즉 결과적으로 InnoDB의 UPDATE, DELETE 문장이 실행될 때는 항상 잠금대상 레코드가 변경 대상 레코드보다 범위가 크거나 같다. 

SQL 문장별로 사용하는 잠금

SELECT 쿼리 잠금

SELECT ... FROM

InnoDB 테이블에서 기본 형태의 SELECT 쿼리는 별도의 잠금을 사용하지 않는다. 만약 읽어야 할 레코드가 다른 트랜잭션에 의해 변경되거나 삭제되는 중이라면 InnoDB 에서 관리하고 있는 데이터의 변경 이력(언두 로그)을 이용해 레코드를 읽는다. 

SELECT ... FROM ... LOCK IN SHARE MODE

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

SELECT ... FROM ... FOR UPDATE

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

INSERT 쿼리 잠금

INSERT ...

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

인서트 인텐션 락은 INSERT를 실행할 의도를 지닌 쿼리가 획득해야 하는 잠금으로 모든 INSERT 쿼리는 인서트 인텐션 락을 획득한 후 INSERT를 실행한다. 그리고 INSERT 된 레코드에 대해서는 배타적 레코드 잠금을 자동으로 획득하게 된다. 인서트 인텐션 락은 갭락의 일종으로, 인서트 인텐션 락끼리는 서로 호환된다. 즉 여러 트랜잭션이 동시에 인서트 인텐션 락을 획득할 수 있다는 것을 의미한다. 하지만 이미 다른 트랜잭션이 레코드나 갭 락을 걸고 있다면 인서트 인텐션 락을 걸기 위해 기다려야 한다. 

UPDATE 쿼리 잠금

UPDATE ... WHERE ...

단순 UPDATE 문장은 WHERE 조건에 일치하는 레코드를 찾기 위해 참조(스캔)한 모든 레코드에 배타적 넥스트 키 락을 걸게된다. 

DELETE 쿼리 잠금

DELETE FROM ... WHERE ...

DELETE 문장은 WHERE 조건에 일치하는 레코드를 찾기 위해 참조(스캔)한 모든 레코드에 대해 배타적 넥스트 키 락을 건다. 

InnoDB 에서 데드락 만들기

InnoDB 에서 대부분의 데드락은 공유 잠금을 가진 상태에서 다시 배타적 잠금을 얻으려고 하는 잠금 업그레이드 상황에서 자주 발생한다. 이번에는 InnoDB에서 데드락을 발생시킬수 있는 패턴들을 알아보자. 

상호 거래 패턴

가장 많이 알려진 데드락 패턴이다. A사용자가 B사용자에게 10포인트를 전달하고, 그와 동시에 B사용자도 A사용자에게 10포인트를 전달하는 시나리오를 생각해보자. 

많이 알려져 있기도 하고 자주 겪는 데드락이기도 하지만, 사실 이런 패턴의 데드락은 아주 간단하게 피해갈 수 있다. 이런 패턴의 데드락을 피하는 방법은 데이터 처리를 어플리케이션에서 업무(포인트의 차감과 증가) 순서가 아니라 테이블의 PK인 user_id값을 기준으로 처리해주면 된다. 

유니크 인덱스 패턴

위의 시나리오에서는 트랜잭션 1번이 ROLBACK을 실행하기 바로 직전까지는 프라이머리 키가 9인 레코드에 대한 배타적 잠금은 트랜잭션 1번이 가지고 있고, 트랜잭션 2번과 3번은 공유 레코드 잠금을 획득하기 위해 대기하고 있는 상태이다. 트랜잭션 1번이 ROLLBACK을 실행하면 트랜잭션 2,3 번은 동시에 공유잠금을 획득하고 프라이머리 키가 9인 레코드를 INSERT 하기위해 배타적 잠금을 걸려고 할 것이다. 
이때트랜잭션 2,3번 중에서 어느 트랜잭션이 먼저 배타적 잠금을 요청하느냐에 상관없이 둘중 아무도 배타적 잠금을 걸지 못하게 된다. 이미 트랜잭션 2,3번이 각자 공유잠금을 가지고 있기 때문에 서로의 공유잠금으로 인해 배타적 잠금을 걸지 못하고 서로 대기하게 된다. 이러한 상황이 데드락 상황인것이다. 

서로 다른 인덱스를 통한 잠금

이번에는 단 하나의 UPDATE 문장만 포함된 트랜잭션에서 데드락이 발생하는 예제를 한번 보자. 이는 타이밍이 중요하기 때문에 실제 테스트를 해보기는 쉽지 않다. 테스트를 위해 다음과 같이 예제 테이블을 생성하자. 

CREATE TABLE tb_user (
  user_id INT NOT NULL,
  user_name VARCHAR(20) NOT NULL,
  user_status TINYINT NOT NULL,
  PRIMARY KEY (user_id),
  INDEX ix_status(user_status)
);
INSERT INTO tb_user VALUES (1, 'Ronald',0), (2, 'John', 1), (3, 'Jane', 1), (4, 'Lara', 1), (5, 'Rula', 0)

이 상태에서 다음과 같이 두 트랜잭션에서 쿼리를 실행한다고 가정해보자. 

이 시나리오에서 각 트랜잭션이 변경하고 있는 조건은 다르지만, 사실 두 업데이트 문장은 공통적으로 user_id=2 인 회원 정보를 변경하고 있다. 그런데 트랜잭션 1번의 업데이트 문장은 정상적으로 실행됐고, 트랜잭션 2번의 문장은 데드락으로 인해 종료됐다. 이 시나리오에서 왜 데드락이 발생했는지 살펴보자.

이런 패턴의 데드락은 발생 빈도가 낮지만 각 트랜잭션에서 UPDATE 쿼리 하나씩만 실행하는 과정 중에도 데드락이 발생 할 수 있음을 확인 할 수 있다. 

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~