🔒Mysql의 잠금(Lock)과 데드락(DeadLock) 발생

멀티 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하려면 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화 장치가 필요하다. DB는 여러 격리 수준과 Lock 방식이 존재한다.

1. 트랜잭션 격리 수준(isolation level)

DB가 설정하는 격리 수준은 아래 4가지 방식으로 정의된다.

• READ UNCOMMITTED (커밋되지 않은 읽기)
• READ COMMITTED (커밋된 읽기)
• REPEATABLE READ (반복 가능한 읽기)
• SERIALIZABLE (직렬화 가능)

각각의 격리 수준에서 발생하는 문제점들이 존재하니 한 번 찾아보는 것도 도움이 될 것이다.

Mysql 격리 수준

Mysql은 REPEATABLE READ를 기본값으로 사용한다. 해당 격리 수준에서 발생하는 PANTHOM READ 문제가 발생하는데 InnoDB 스토리지 엔진은 넥스트 키 락 (Next key lock)을 이용하여 해결하였다.

MySQL 트랜잭션과 락 - InnoDB 락, 이렇게 동작한다!

2. Lock의 종류

대표적으로 아래 2가지의 Lock이 존재한다.

• S-Lock은 공유 잠금(Shared-Lock), 읽기 잠금(Read Lock)
• X-Lock은 배타적 잠금(Exclusive-Lock), 쓰기 잠금(Write Lock)

S-Lock (읽기 잠금)

특정 Row를 읽을(Read) 때 사용된다. A 트랜잭션에서 특정 Row에 S-Lock을 걸었다면, B 트랜잭션에서는 읽기는 가능하지만 쓰기는 불가능하다.

즉, 다른 트랜잭션에서 S-Lock은 걸 수 있지만 X-Lock은 걸 수 없다.

X-Lock (쓰기 잠금)

특정 Row를 변경(Write)할 때 사용된다. A 트랜잭션에서 특정 Row에 X-Lock을 걸었다면, B 트랜잭션에서는 해당 Row를 읽기, 쓰기 모두 불가능하다. 해당 A 트랜잭션이 건 Lock이 종료되길 기다린다.

즉, 다른 트랜잭션에서 S-Lock, X-Lock을 걸 수 없다.

X-Lock은 SELECT ... FOR UPDATE, UPDATE, DELETE 등의 변경 쿼리를 날리면 각각의 Row마다 잠금이 걸리는 Row-Level-Lock이다.

Intention lock

테이블 안의 특정 Row에 대해서 나중에 어떤 Row-Level Lock을 걸 것이라는 의도를 알려주기 위해 미리 Table-Level에 걸어두는 Lock이다.

Intention Shared Lock (IS), Intention Exclusive Lock (IX) 존재한다.

• SELECT ... LOCK IN SHARE MODE 이 실행되면,
  - 먼저 Intention Shared Lock (IS) 이 테이블에 걸린다.
  - 그 후 Row-Level에 S lock이 걸린다.
• SELECT ... FOR UPDATE, INSERT, DELETE, UPDATE 이 실행되면,
  - 먼저 Intention Exclusive Lock (IX) 이 테이블에 걸린다.
  - 그 후 Row-Level에 X lock이 걸린다.

참고

Q. Table-level에서 한번, Row-Level에서 한번, 2단계(2-phase)로 Lock을 적용하는 이유는?

A. 트랜잭션 A에서 이미 테이블에 대해 락이 걸려있는데, 트랜잭션 B에서 해당 테이블의 특정 Row에 Lock을 거는것을 원천적으로 방지 할 수 있다. 반대의 경우도 마찬가지이다.

• ex) Row-Level의 write이 일어나고 있을때 테이블 스키마가 변경되어서는 안된다. Write query의 경우 이미 IX 락을 획득한 상태이기 때문에 해당 테이블의 스키마가 변경되는것을 막을 수 있다.

💡 UPDATE 할 때!
보통은 UPDATE되는 대상 row에  Lock이 걸리지만, WHERE 절에 인덱스를 적절히 태우지 않거나 인덱스가 없는 경우는 테이블 전체에 Lock이 걸린다.

Lock의 종류 (Shared Lock, Exclusive Lock, Record Lock, Gap Lock, Next-key Lock)

3. SELECT와 S-Lock ✔

나는 SELECT를 하면 해당 Row에 S-Lock이 걸린다고 생각한 적이 있다. S-Lock을 개념적으로 이해한 후 읽기는 SELECT 절을 의미한다고 오해했다.😥 이후 DeadLock 발생 원인을 트래킹 하면서 이해했다고 생각했던 S-Lock에 굉장히 혼동이 와서 지금 다시 학습하고자 한다.

일반적인 SELECT 쿼리는 잠금을 사용하지 않고 DB를 읽어 들인다. SELECT는 특정 Row를 읽은 행위일뿐이지 잠금을 의미하는 것은 아니다 라는 말이 깊이 와닿았다.

실제 테스트를 진행했을 때 다른 트랜잭션에서 변경(Write)가 가능한 것을 직접 확인했다.

4. SELECT … FOR UPDATE

특정 Row를 조회 시에 X-Lock을 걸 수 있는 방법이 존재한다. 명칭을 보더라도 UPDATE 절을 위해 Row 조회를 하였고 트랜잭션이 종료될 때까지 변경되길 원치 않는다! 라는 것을 알 수 있다.

SQL 문법으로는 SELECT 절 뒤에 FOR UPDATE를 추가한다.

SELECT * FROM USERS FOR UPDATE

JPA에서는 Repository의 @Lock 어노테이션으로 잠금을 설정한다.

public interface UserRepository extends JpaRepository<User, Long> {
  @Lock(LockModeType.PESSIMISTIC_READ)
  User findById(Long id);
}

참고로 @Lock 어노테이션에는 아래와 같은 잠금이 존재한다.

• @Lock(LockModeType.PESSIMISTIC_READ)
  해당 리소스에 공유 잠금을 건다. 다른 트랜잭션에서 읽기는 가능하지만 쓰기는 불가능하다.

• @Lock(LockModeType.PESSIMISTIC_WRITE)
  해당 리소스에 배타적 잠금을 건다. 다른 트랜잭션에서는 읽기와 쓰기 모두 불가능하다.

• @Lock(LockModeType.PESSIMISTIC_FORCE_INCREMENT)
  PESSIMISTIC_WRITE와 유사하게 작동하지만 추가적으로 낙관적 잠금처럼 버저닝을 한다. 따라서 버전에 대한 칼럼이 필요하다.

Select 쿼리는 S락이 아니다. (X락과 S락의 차이)

5. 외래키(Foreign Key) 잠금 전파

외래키 칼럼의 변경(INSERT, UPDATE) 시 부모 테이블이나 자식 테이블에 데이터가 존재하는 지 확인 절차가 필요하다. 잠금이 연관 관계를 맺고 있는 여러 테이블로 잠금이 전파된다. 해당 변경 작업을 위해 외래키 컬럼에 S-Lock이 걸리게 되고 그로 인해 데드락(DeadLock) 발생 원인이 될 수 있음으로 주의해야 한다.

6. 데드락(DeadLock)

멀티 트랜잭션 간 교착 상태를 의미한다. 잠금 때문에 서로의 잠금이 풀리기를 무한히 대기하는 상황이다. 보통은 트랜잭션의 쿼리 순서를 조율하여 처리할 수 있고 낙관적 잠금(Optimistic Lock)으로 어느 정도의 롤백을 허용하며 처리할 수 있다.

데드락 발생 트래킹🚧

데드락을 발생 원인을 한번에 추적하기는 쉽지 않다. 상태를 보여주는 SQL문을 실행하면 latest detection deadlock 부분이 존재하여 최근에 데드락이 발생한 쿼리를 확인 가능하다.

SHOW ENGINE INNODB STATUS

APM(Application Performance Management)

이번 경우에는 APM을 사용하여 데드락이 발생한 트랜잭션과 같은 시기에 실행되고 충돌이 발생할 만한 쿼리를 예상해볼 수 있었다.

(Exception이 발생할 때 데드락이 발생한 2개의 쿼리 모두 로그를 찍어주면 원인 분석이 쉬울텐데…😥)

해결 방안

쿼리 순서를 확인해보니 외래키에 의한 잠금 전파가 이번 데드락의 발생 원인이었다.

쿼리 실행 순서를 순차적으로 확인해보자.

• 트랜잭션 A는 포지션(Position) 테이블의 특정 Row 변경을 위해 X-Lock을 건다.
  - PK에 대한 인덱스를 탔기 때문에 Table-Level Lock은 걸리지 않는다.

• 트랜잭션 B는 Row의 INSERT 절을 실행하려 한다. 메시지(Message) 테이블은 지원자(Applicant), 포지션(Position) 테이블에 외래키를 가지고 있다.
  - 16535 PK의 지원자를 조회하여 S-Lock이 전파된다.
  - 1446 PK의 포지션을 조회하여 S-Lock이 전파되는데 트랜잭션 A에서 이미 X-Lock이 걸렸으므로 대기 상태가 된다.

• 트랜잭션 A에서 16535 PK의 지원자(Applicant)를 변경하려 한다. 트랜잭션 B에서 X-Lock이 걸렸으므로 대기 상태가 된다.

• 트랜잭션 A, B가 서로의 잠금이 풀리기를 기다리는 교착 상태에 빠진다.

간단하게 트랜잭션 A의 쿼리 실행 순서를 뒤바꿔주며 교착 상태를 처리할 수 있다.

지원자(Applicant)의 UPDATE 절을 먼저 실행하고 포지션(Position)의 UPDATE 절을 처리한다. 16535 PK의 지원자 Row에 대해 먼저 X-Lock을 획득하기 때문에 트랜잭션 B의 INSERT는 외래키인 지원자를 조회하는 과정에서 대기 상태가 된다.