내맘대로 정리하는 Real MySQL #12장) 쿼리 종류별 잠금

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

이번 장에서는 대표적인 SQL 문장별로 어떤 잠금이 사용되고 이러한 잠금을 어떻게 우회할 수 있는지도 함께 살펴보겠다. 대부분의 잠금 관련 내용은 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 내용이다.

InnoDB의 기본 잠금 방식

InnoDB에서는 각 쿼리의 패턴별로 사용하는 잠금이 다르다. InnoDB 테이블에서 기본적인 SQL 문장이 어떤 잠금을 필요로 하는지 살펴보자.

SELECT

SERIALIZABLE 격리 수준에서는 모든 SELECT 쿼리에 자동적으로 LOCK IN SHARE MODE가 덧붙여져서 실행되는 효과를 내기 때문에 이 격리 수준에서 모든 SELECT 쿼리는 읽기 잠금을 걸고 레코드를 읽는다.

하지만 일반적으로 MySQL에서는 REPEATABLE-READ나 READ-COMMITTED 격리 수준을 사용하므로 SERIALIZABLE 격리 수준의 성능 저하는 크게 걱정하지 않아도 된다.

LOCK IN SHARE MODE나 FOR UPDATE 키워드를 사용하면 각각 읽기 잠금과 쓰기 잠금이 걸린다.

SELECT * FROM employees WHERE emp_no = 10001; # 잠금이 없디
SELECT * FROM employees WHERE emp_no = 10001 LOCK IN SHARE MODE; # 읽기 모드 잠금
SELECT * FROM employees WHERE emp_no = 10001 FOR UPDATE; # 쓰기 모드 잠금

애플리케이션에서 서로 다른 API에 의해 쓰기 잠금이 걸리고 해제되는 것은 꼭 막아야 한다. 두 번재 요청이 오지 않으면 잠금이 계속 걸려 있어 Connection Pool의 모든 Connection이 잠금 대기 상태로 빠져 결국 MySQL이 아무런 처리도 할 수 없게 되기 때문이다.

INSERT, UPDAT, DELETE

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

AutoCommit 모드에서는 각 SQL 문장별로 자동으로 트랜잭션이 시작되고 종료된다. AutoCommit이 비활성화된 상태에서는 SQL 문장의 실행과 함께 자동으로 트랜잭션이 시작되지만 트랜잭션의 종료는 반드시 COMMIT이나 ROLLBACK 명령을 실행해 명시적으로 트랜잭션을 시작할 수도 있는데, 이때는 AutoCommit이 비활성화된 상태에서와 같이 반드시 COMMIT이나 ROLLBACK 명령을 이용해 수동으로 트랜잭션을 종료해야 한다.

InnoDB 스토리지 엔진은 WHERE절에 포함된 모든 조건이 아니라 인덱스를 사용할 수 있는 조건만 MySQL 엔진으로부터 전달받기 때문에 인덱스를 사용할 수 있는 조건만 일치하면 모두 잠그게 되는 것이다. 이때문에 서비스를 대상으로 쿼리를 튜닝하거나 검토할 때 UPDATE DELETE 쿼리 문장도 꼭 함께 검토해야 한다.

인덱스를 전혀 사용하지 못하는 UPDATE나 DELETE 문장은 테이블의 모든 레코드에 잠금을 걸게 되므로 테이블 수준의 잠금을 사용하는 MyISAM보다 동시성이 더 떨어질 수도 있다. 트랜잭션 격리 수준이 READ-COMMITED에서는 인덱스와 관계없이 실제 변경되는 레코드만 잠금을 걸게 된다.

SQL 문장별로 사용하는 잠금

SELECT ... FROM

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

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

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

SELECT ... FROM ... FOR UPDATE

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

INSERT ...

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

인서트 인텐션 락은 INSERT를 실행할 의도를 가진 쿼리가 획득해야 하는 잠금으로, 모든 INSERT 쿼리는 인서트 인텐션 락을 획득한 후 INSERT를 실행한다. 이는 갭락의 일종이며, 여러 트랜잭션이 동시에 인서트 인텐션 락을 획득할 수 있다. 하지만 다른 트랜잭션이 레코드나 갭 락을 걸고 있다면 인서트 인텐션 락을 걸기 위해 기다려야 한다.

만약 인서트 인텐션 락이 없다면 어떻게 되는지 살펴보자

# tx 1 - 5, 3, 4는 PK이다
BEGIN;
INSERT INTO test VALUES (5);

# tx 2
BEGIN;
INSERT INTO test VALUES (3);

# tx 3
BEGIN;
INSERT INTO test VALUES (4);

인서트 인텐션 락이 없다면 다음과 같은 순서로 실행된다.

1. test 테이블에 PK가 1 ~ 6인 레코드까지의 간격을 베타적 갭 락을 잠근다. (왜 하필 1 ~ 6인지는 모르겠다 ㅠㅠ)
2. 새로운 PK를 가진 레코드를 INSERT 한다
3. 새로 INSERT된 PK 값에 대해 베타적 레코드 잠금을 한다

핵심은, 앞의 트랜잭션 작업이 끝나기를 기다려야 하므로 위의 INSERT 작업들은 동시에 실행되지 못하고 순차적으로 실행된다.

만약 인텐션 락을 사용한다면, 첫 번재 단계에서 PK가 1 ~ 6인 레코드에 대해 인서트 인텐션 락을 여러 트랜잭션에서 동시에 획득하여 같은 값을 INSERT하지 않는 이상 서로의 간섭없이 동시에 실행될 수 있다.

INSERT를 실행하는 도중 PK나 Unique Key와 같이 중복이 허용되지 않는 칼럼에 대해 중복된 값이 이미 존재한다면 InnoDB는 반드시 기존의 중복된 레코드에 공유 레코드 락을 걸어야 한다. 이는 중복 키 오류를 발생시킨 트랜잭션이 COMMIT이나 ROLLBACK 명령으로 종료될 때까지는 중복된 값을 가진 레코드가 다른 트랜잭션에 의해 변경되거나 삭제되면 안되기 때문이다.

UPDATE ... WHERE

단순 UPDATE 문장은 WHERE 조건에 일치하는 레코드를 찾기 위해 참조한 모든 레코드에 베타적 넥스트 키 락을 걸게된다. 단순 레코드만 잠그지 않고 간격까지 잠그는 것은 팬텀 레코드의 발생을 마기 위한 것이다. 일반적으로 넥스트 키 락을 설정하는 이유는 이 처리가 수행되는 동안 다른 트랜잭션에 의해 처리 범위의 레코드가 영향을 받지 않게 하기 위해서다.

DELETE ... WHERE

단순 DELETE 문장은 UPDATE 문장과 똑같이 WHERE 조건에 일치하는 레코드를 찾기 위해 참조한 모든 레코드에 대해 배타적 넥스트 키 락을 건다. 단순 레코드만 잠그지 않고 간격까지 잠그는 것은 복제에서 마스터와 슬레이브의 동기화를 유지하기 위해서다. 일반적으로 넥스트 키 락을 설정하는 이유는 이 처리가 수행되는 동안 다른 트랜잭션에 의해 처리 범위의 레코드가 변경되지 않게 하는 데 있다.

InnoDB에서 주로 발생할 수 있는 deadlock 상황

상호 거래 관련

2개의 트랜잭션이 등장하고 첫 번째 트랜잭션은 A에서 100원을 빼서 B에게 100원을 더하고, 두 번재 트랜잭션은 B에서 100원을 빼서 A에게 100원을 더한다
시간 순서로 실행되는 내용을 보자

• Tx1 시작
• Tx2 시작
• Tx1 : A 에서 100원을 빼는 UPDATE 수행 (유저 A 레코드 쓰기 잠금)
• Tx2 : B에서 100원을 빼는 UPDATE 수행 (유저 B 레코드 쓰기 잠금)
• Tx1 : B에다 100원을 더하는 UPDATE 수행을 위해 B 잠금 해제 기다림
• Tx2 : A에다 100원을 더하는 UPDATE 수행을 위해 A 잠금 해제 기다림

해결방법은 애플리케이션의 업무 순서가 아니라 테이블의 PK 순으로 처리하는 것이다. 이렇게 되면 두 업데이트가 서로 크로스 될 일이 사라진다

유니크 인덱스 관련

3개의 트랜잭션이 관여하는데, 모두 같은 테이블에 같은 데이터를 넣는다고 하자

• Tx1 시작
• Tx2 시작
• Tx3 시작
• Tx1에서 pk 2로 INSERT (해당 레코드 베타적 잠금)
• Tx2에서 pk 2로 INSERT : pk2에 대한 공유잠금 획득 대기
• Tx3에서 pk2 로 INSERT : pk2에 대한 공유잠금 획득 대기
• Tx1 rollback : pk2 베타적 잠금 해제
• Tx2와 Tx3이 동시에 pk2에 대한 공유잠금 획득
• Tx2 입장에서는 pk2에 대한 베타적 잠금을 얻기 위해 Tx3이 건 공유잠금 해제 대기
• Tx3 입장에서는 pk2에 대한 베타적 잠금을 얻기 위해 Tx2가 건 공유잠금 해제 대기

만약 이런 패턴의 데드락이 자주 발생한다면, 아래와 같은 해결책을 사용해볼 수 있다

• 배치 프로그램이라면 실행 시간을 변경해본다
• 웹 서비스와 같은 OLTP 환경이라면 데드락에 대한 핸들링 코드를 추가한다

외래 키 관련

Board 테이블과 Article 테이블이 1 : N 이라고 하자

• Tx1 시작
• Tx2 시작
• Tx1에서 article_id가 1이고 board_id가 1인 Article 생성
  • pk가 1인 Board 공유잠금
  • pk가 1인 Article 베타적 잠금
• Tx2에서 article_id가 2이고 board_id가 1인 Article 생성
  • pk가 1인 Board 공유잠금 (공유잠금은 여러개 가능)
  • pk가 2인 Article 베타적 잠금
• Tx1에서 Board에 있는 필드인 article_count를 1 더하기 위해 Tx2가 Board에 건 공유잠금 해제 대기
• Tx2에서 Board에 있는 필드인 article_count를 1 더하기 위해 Tx1이 Board에 건 공유잠금 해제 대기

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

PK와 별도의 인덱스가 있다고 가정하자

두 개의 update가 동시에 시작하는데

• 첫 번째 update는 idx 기준으로 데이터를 업데이트 하는 쿼리라 '인덱스 레코드를 잠그고' PK 인덱스의 잠금 해제를 기다린다
• 두 번째 update는 PK 기준으로 데이터를 업데이트 하는 쿼리라 'PK 인덱스를 잠그고' 인덱스 레코드 잠금 해제를 기다린다

이렇게 둘이 서로를 기다리며 데드락이 걸릴 수 있다.