[MySQL] Lock Type과 데드락(DeadLock) 예방하기

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

배경

데이터베이스를 다루는데 가장 중요하고 핵심 개념인 Lock과 DeadLock에 대해서 알아본다. Lock은 데이터 정합성을 보장하고 동시성 이슈를 해결할 수 있는 방식 중 하나이며 서버 개발에서 가장 중요한 부분이다. 또한 Lock을 다루면서 DeadLock이 발생하는 케이스와 이를 예방하는 방법을 알아본다. 

MySQL Lock

일반적으로 하나의 DB 서버에는 여러대의 애플리케이션이 붙어서 동작하게 된다. 이러한 상황에서는 Race Condition(경합상태)와 같은 동시성 이슈가 발생할 수 있는데, MySQL을 비롯한 관계형 데이터베이스는 이러한 상황에서 애플리케이션의 고가용성(HA(high availability) : 서버, 네트워크, 프로그램 등의 정보 시스템이 상당히 오랜 기간 동안 지속적으로 정상 운영이 가능한 성질)을 보장하기 위해 다양한 형태의 Lock을 제공한다.

Race Condition(경합 상태)

여러 트랜잭션이 공유 자원에 대해 동시에 접근하며 정합성이 깨지는 상태. 이러한 경합 상태가 발생하는 것을 방지하 데이터 일관성과 정합성을 보장하기 위해 MySQL은 다음과 같은 유형의 Lock을 제공한다.

Shared Lock (S)

하나의 Row 레벨에 적용되는 Lock 유형을 말한다. S라고도 한다. 흔히 읽기(Read) 작업이 이루어지는 동안 해당 Row에 대해 변경이 일어나지 않도록 구성하는 Read Lock으로 활용된다. 만약 해당 Row에 이 Shared Lock이 걸려있다면 Row를 Write하거나 Update하는 Lock은 사용할 수 없다. 쉽게 말해 어떤 Row를 읽는 동안은 항상 동일한 결과를 보장하는 것이다. SELECT ... LOCK IN SHARE MODE로 설정할 수 있다. 

 

예를 들어 동일한 Row에 접근하는 두 트랜잭션 A와 B가 동시에 발생했다고 가정하자. 트랜잭션 A는 특정 Row에 대해 SELECT를 한다. 반면 B는 동일한 Row에 UPDATE를 한다.

이 상황에서 A가 Read Lock을 획득하고 Row를 조회하는 동안 B가 UPDATE를 호출하면? A가 먼저 Read Lock을 획득하고 있으므로 B의 요청은 대기한다. 이후 A가 정상적으로 데이터를 조회하고나서 해당 Row에 대해 Lock을 반납하면 B의 UPDATE 구문이 호출된다.

 

SELECT 작업과 UPDATE 작업을 수행하는 트랜잭션이 발생하는 것과 달리 SELECT 작업을 하는 여러 개의 트랜잭션은 동시에 Shared Lock을 획득(공유)할 수 있다. 예를 들어 이번에는 동일한 Row를 조회하는 두 트랜잭션 A와 B가 동시에 발생했다고 가정하자. A는 특정 Row에 대해 SELECT를 한다. B도 동일한 Row에 SELECT를 한다. 이 경우 단순 데이터 조회이므로 굳이 B가 대기할 필요가 없다. B도 Shared Lock을 획득하고 해당 Row를 조회한다. (Shared Lock의 목적이 항상 동일한 읽기 결과를 보장하기 위함이라는 것을 기억하자.)

 

결국 제한 없이 데이터를 읽을 수 있지만, 다른 트랜잭션이 해당 데이터를 수정할 수 없게된다.

Exclusive Lock (X)

Shared Lock과 같이 Row 레벨(= 하나의 Row)에 적용되는 Lock 유형이지만 이 LocK은 Write/Update Lock으로 활용된다. X라고도 한다.

 

예를 들어 동일한 Row에 접근하는 두 트랜잭션 A와 B가 동시에 발생했다고 가정하자. A는 특정 Row에 대해 SELECT를 한다. (Shared Lock을 획득한다.) 반면 B는 동일한 Row에 UPDATE를 한다.

 

이 상황에서 A가 Read Lock을 획득하고 Row를 조회하는 동안 B가 UPDATE를 호출하면? A가 Shared Lock을 획득하고 있으므로 B는 UPDATE를 호출하기 전에 Exclusivce Lock을 대기한다.  A가 정상적으로 데이터를 조회하고나서 해당 Row에 대해 Shared Lock을 반납한다. 이후 B는 Exclusvice Lock을 획득하고 UPDATE 구문이 호출된다.

 

즉, 동일한 Row에 대해 작업하는 여러 개의 트랜잭션은 서로 동시에 Shared Lock과 Exclusive Lock을 획득할 수 없다.

쉽게 말해 동일한 Row에 대해 A가 Shared Lock을 획득하면서 B가 Exclusive Lock을 획득할 수 없다.

즉, Shared Lock을 여러 트랜잭션이 동시에 가질 수 있지만 Exclusive Lock은 오직 한 트랜잭션만이 가질 수 있다.

 

SELECT ... FOR UPDATE는 Row에 대해 이 Exclusive Lock을 거는데 다른 Lock을 사용하는 트랜잭션에서는 항상 대기하게 된다. (또다른(SELECT ... FOR UPDATE도 대기해야한다.)

 

Intention Lock (IS, IX)

Table 레벨에 적용되는 Lock 유형, Shared Lock인 경우 IS, Exclusive Lock인 경우 IX라고 한다. 잠금 호환성을 확인하기 위한 용도로 사용되며, 실질적으로 Table 수준에서 Lock이 존재하는지 확인하는 역할을 한다. 쉽게 말해서 보통 쿼리를 대상 날리는 Table에게 특정 Row에 어떤 유형의 Lock을 걸지 알려주는데 사용된다.

 

예를 들어 SELECT를 하는 경우 대상 Table에 Intention Shared Lock(IS)을 걸고 Row에 Shared Lock(S)을 거는 방식으로 동작한다. 이와 유사하게 INSERT, UPDATE를 하는 경우 대상 Table에 Intention Exclusive Lock(IX)을 걸고 Row에 Exclusive Lock(X)을 거는 방식으로 동작한다. 결과적으로 트랜잭션이 실행 중인 동안 다른 트랜잭션이 동일한 Table에 대해 불필요한 Lock을 설정하지 않도록 보호하는 역할을 한다. 이러한 특성으로 Intension Lock은 Lock 충돌을 방지하여 잠재적인 DeadLock 상황을 줄이는데 쓰인다.

 

예를 들어 트랜잭션 A가 특정 Table의 Row에 대해 Exclusive Lock을 걸고 Write 작업을 하는 동안, 트랜잭션 B가 동일한 Table에 대해 스키마 변경 작업을 시도한다고 가정해보자. 이때 트랜잭션 A가 해당 Table에 Intention Exclusive Lock(IX)을 이미 설정하고 있으므로, 트랜잭션 B는 Table 수준에서 충돌을 감지하고 대기하게 된다. 이로 인해 불필요한 Lock 충돌이 발생하지 않으며, 잠재적인 Deadlock 상황을 미리 피할 수 있다.

 

Intention Lock은 Row에 직접적인 영향을 주지 않으므로 여러 개의 트랜잭션이 동시에 획득할 수 있다. 하지만 테이블에 대해서 수정을 가하는(e.g. ALTER TABLE, DROP TABLE 등) 경우 여러 개의 트랜잭션이 동시에 Intention Lock를 획득할 수 없다. 한 트랜잭션이 작업중이면 다른 트랜잭션은 Exclusvice Lock을 대기한다.

DeadLock

트랜잭션 경합 상태가 재귀적으로 반복되는 상황. 즉, 여러 개의 트랜잭션이 재귀적으로 Lock을 무한정 대기하는 현상을 말한다. (DB 세션을 사용하고 멀티 스레드 환경에서 개발을 하다보면 한번은 만나게 된다.) DB 레벨에서 Timeout 설정과 애플리케이션 레벨에서 Retry 정책을 통해 방어 로직을 둘 수는 있지만, 근본적으로 DeadLock 상황 자체를 방지하는 것이 가장 좋다.

발생 케이스

예를 들어 test1 테이블에 다음과 같이 두 개의 row가 존재한다고 가정해보자.

CREATE TABLE test1(
    pk INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(500)
);

-- 테스트를 위한 Row
INSERT INTO test1 (pk, name) 
VALUES (1, '1'), (2, 'b');

 

먼저 첫번째 트랜잭션을 열고 UPDATE 쿼리를 실행한다. 이 트랜잭션은 아직 트랜잭션을 종료(커밋)하지 않았으므로 현재 pk = 1인 로우에 대해 Exclusive Lock을 얻은 채로 pk = 2인 row에 대해 UPDATE 작업을 진행한다.

START TRANSACTION;

UPDATE test1
SET name = 'new_tx1'
WHERE pk = 1;

SELECT SLEEP(10); -- 10초 대기

UPDATE test1
SET name = 'new_tx2'
WHERE pk = 2;

COMMIT;

 

이후 다음과 같이 트랜잭션을 열고 UPDATE 쿼리를 실행하는 또 다른 로직이 있다고 가정하자. 이 트랜잭션은 pk = 2인 Row에 대해 Exclusive Lock을 얻고 업데이트를 호출한다. 이 로직 또한 아직 트랜잭션을 종료(커밋)하지 않았으므로 현재 pk = 2인 로우에 대해 Exclusive Lock을 얻은 채로 pk = 1인 Row에 대한 Update를 진행한다.

START TRANSACTION;

UPDATE test1
SET name = 'new_tx2'
WHERE pk = 2;

SELECT SLEEP(10); -- 10초 대기

UPDATE test1
SET name = 'new_tx1'
WHERE pk = 1;

COMMIT;

 

결국 다음과 같은 순서로 Lock을 획득하고나서 서로의 Lock을 무한정 대기하게 된다.

 

DeadLock 방지 방안

1. 복잡한 쿼리를 가능한 줄인다.

종종 리소스를 효율적으로 사용한다는 명목으로 하나의 DB 커넥션만으로 서비스 로직을 처리하기 위해 쿼리에 모든 비즈니스 로직을 담고 서비스를 처리하곤 한다. (흔히 말하는 한방쿼리) 하지만 이런 경우 쿼리 자체가 매우 복잡해진다.

 

이처럼 쿼리 자체가 매우 복잡해지는 경우 Lock이 많이 왔다갔다하면서 쉽게 꼬일 수 있고 개발자가 인지하기도 매우 어렵다. 따라서 여러개의 DB 커넥션을 사용하더라도, 간단한 쿼리를 여러번 호출하면서 비즈니스 로직을 구성하는게 중요하다.

2. 트랜잭션 내의 Lock의 순서를 지킨다.

한 트랜잭션에서 여러 데이터를 수정할 때 발생하는 Lock을 획득하는 순서를 일관되게 지킨다.

 

예를 들어 두 트랜잭션 X와 Y가 있다고 가정하자. 트랜잭션 X는 먼저 A 테이블에 대해 Exclusive Lock을 획득하고 이를 수정한다. 동시에 트랜잭션 Y는 B 테이블에 대해 Exclusive Lock을 획득하고 이를 수정한다.

 

이후 한 DB 세션 내서 트랜잭션 X는 A 테이블 수정하고 B 테이블을 수정한다. 이때 트랜잭션 Y가 B 테이블에 대해 Exclusive Lock을 획득하고 있으므로 Y의 작업이 종료될때까지 X는 대기하게 된다. 동일하게 한 DB 세션 내에서 트랜잭션 Y가 B 테이블 수정하고 A 테이블을 수정한다. 이 때 트랜잭션 X가 A 테이블에 대해 Exclusive Lock을 획득하고 있으므로 X의 작업이 종료될때까지 Y도 대기하게 된다.

 

결국 트랜잭션 X는 B 테이블에 대해 Lock을 대기하고, 트랜잭션 Y는 A 테이블에 대해 Lock을 대기하게 된다. 서로가 서로의 Lock을 기다리는 상황이 발생하며, 이로 인해 두 트랜잭션은 무한정 대기하게 된다. (= DeadLock 발생) 따라서 위와 같은 상황을 방지하기 위해 트랜잭션 내에서 테이블 수정할 때 Lock을 획득하는 순서를 모든 트랜잭션에서 동일하게 설정하는 편이 좋다. 특히 여러 트랜잭션이 동시에 다수의 테이블을 수정하는 경우에 Deadlock이 발생할 가능성이 높아진다.

3. ON DUPLICATE KEY UPDATE 주의하기

MySQL에서 UPSERT를 지원하는 방안으로 ON DUPLICATE KEY UPDATE가 있는데, 대규모 배치 작업처럼 여러 필드를 한 번에 업데이트하는 상황에서는 Deadlock이 발생할 수 있다. (일반적으로 단일 필드에 대해서 사용하는 경우 큰 문제가 되지 않는다.)

 

예를 들어 배치 작업에서 여러 레코드를 동시에 삽입 또는 업데이트를 위해 Exclusive Lock을 획득하는 경우, 각 트랜잭션이 동일한 레코드를 동시에 접근하게 되면 서로 다른 트랜잭션이 Lock을 대기하며 Deadlock에 빠질 수 있다.

 

특히 여러 필드가 동시에 업데이트되면 각 트랜잭션이 여러 행에 대해 Lock을 순차적으로 획득하는 과정에서 충돌이 발생하기 쉽다.

Deadlock을 방지하려면 배치 작업의 크기를 조정하거나, Lock을 효율적으로 관리하는 방법을 고려해야 한다.

예를 들어 배치 크기를 줄여 트랜잭션 간 경합을 줄이는 방법이 있다.

 

예를 들어 배치성으로 전송되는 다음과 같은 두 쿼리가 있다고 가정하자.

CREATE TABLE test1(
    pk INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(500)
);

-- 쿼리 1
INSERT INTO test1 (pk, name) 
VALUES
(1, '1'),
(2, 'b') 
ON DUPLICATE KEY UPDATE name=VALUES(name))

-- 쿼리 2
INSERT INTO test1 (pk, name) 
VALUES
(2, 'b'),
(1, '1')
ON DUPLICATE KEY UPDATE name=VALUES(name))

 

서로 다른 모듈 A와 B에서는 각각 DB 커넥션을 획득하고 트랜잭션을 시작한다. 이후 각각의 쿼리를 배치성으로 전달한다. 

 

모듈 A는 첫번째 쿼리를 호출하고 pk = 1인 row에 대해 Exclusive Lock을 획득한다. 동시에 모듈 B는 두번째 쿼리를 호출하고 pk = 2인 row에 대해 Exclusive Lock을 획득한다. 모듈 A는 pk = 1인 row에 대해 작업을 완료한 후 pk = 2인 row에 Exclusive Lock을 획득하기 위해 대기한다. 모듈 B도 pk = 2인 row에 대해 작업을 완료한 후 pk = 1인 row에 Exclusive Lock을 획득하기 위해 대기한다.

 

결국 두 모듈 A와 B는 서로의 Lock을 획득하기 위해 무한정 대기하고 DeadLock에 발생한다. 따라서 배치 작업을 처리할 때는 ON DUPLICATE KEY UPDATE사용에 유의해야한다.