12장 쿼리 종류별 잠금(2)

InnoDB에서 Deadlock 만들기

• InnoDB는 다른 DBMS와는 다르게 레코그 간의 간격을 잠그는 gap lock 이나 next key lock이 있다.순수한 레코드 레벨의 잠금만 사용하는 DBMS보다는 잠금의 범위가 넓은 편이라 데드락이 더 자주 발생하는 편이다.

패턴 1. 상호 거래 관련

A 사용자가 B사용자에게 10 포인트 전달, 그와 동시에 B 사용자도 A사용자에게 10 포인트 전달 하는 시나리오

• 일반적으로 차감 → 증가 순으로 개발
• 테이블의 프라이머리키인 user_id 값을 기준으로 처리
  • 1번 트랜잭션: a - update 진행
  • 2번 트랜잭션: a - update 진행
  • 1번 트랜잭션: b - update 진행
  • 2번 트랜잭션: b - update 진행

패턴 2. 유니크 인덱스 관련

• 트랜잭션 1번이 ROLLBACK을 실행하기 직전까지는 primary key(9)에 대한 exclusive lock을 가지고 있음 ⇒ 트랜잭션 2, 3번은 공유 레코드 잠금을 획득하기 위해 대기
• 트랜잭션 1번이 ROLLBACK을 실행하면 프라이머리키가 9인 레코드가 없어짐과 동시에 트랜잭션1번이 걸었던 베타적 잠금이 해제 ⇒ 2,3번은 동시에 가상의 레코드(실제로 존재하지 않지만 primary key 값이 9인 record )에 대해 shared lock을 획득
• 트랜잭션 2,3번이 INSERT 하기 위해 서로 exclusive lock을 잡으려 함
• 이미 트랜잭션 2,3번이 공유 잠금으로 인해 exclusive lock을 걸지 못하고 서로 대기하게 된다

이러한 종류의 패턴은 자주발생한다. ⇒ 최대한 유니크 인덱스의 사용을 자제

 

패턴 3. 외래키 관패턴 3. 외래키 관련

 

• 트랜잭션1번이 insert 하면 dtb_article에서 article_id=1인 레코드에는 배타적 잠금 걸리고 tb_board테이블의 board_id=1인 레코드가 공유 잠금이 걸림 ⇒ board_id=1 인 레코드는 트랜잭션 1번이 완료하기 전까지는 변경되면 안되기 때문에 공유 잠금이 걸림
• 하지만 article_count를 누적시키기 위해 배타적 잠금 요청

해결 방법

• insert와 update 순서를 반대로 실행해주면 해결된다. ⇒ 하지만 사용량이 많은 곳에서는 update쿼리가 많이 발생해서 잠금경합이 상당히 높아질 수 있다.

패턴 4. 서로 다른 인덱스를 통한 잠금

• update가 하나인데 데드락 발생하는 예제

  

• 트랜잭션 1 - 인덱스를 레인지 스캔해서 user_status = 1 에 배타적 잠금 획득
• 트랜잭션 2 - user_id=2인 레코드를 배타적 잠금 획득
• 트랜잭션 1 - 프라이머리 키값이 2인 레코드의 배타적 잠금을 획득해야하는데 이미 트랜잭션 2번이 점유상태이므로 대기
• 트랜잭션 2 - 값을 변경하기 위해 user_status =1 인 인덱스 레코드의 배타적 잠금을 획득해야하지만 이미 트랜잭션1에서 점유 상태