인프런 커뮤니티 질문&답변

DDL

create table users
(
    user_no  bigint      not null primary key,
    created_at datetime(6) not null,
    updated_at datetime(6) not null
);


create table inventory
(
    id  bigint auto_increment primary key,
    user_no       bigint           not null,
    item_no       bigint           not null,
    quantity        int(11) unsigned not null,
    created_at      datetime(6)      not null,
    updated_at      datetime(6)      not null,
    constraint UK_USER_NO_ITEM_NO 
    	unique (user_no, item_no),
    constraint FK_USER_NO 
    	foreign key (user_no) references users (user_no)
);

ORM을 사용하고 있으며, 두 개의 트랙잭션(tx1, tx2)에서 inventory 테이블에 동시에 select + insert 시 발생합니다.

준비

• user_no: 999

• item_no: 10

   

   

상황

• (tx1과 tx2는 매우 빠르게 동시요청 발생)

• tx1: user_no + item_no로 inventory 테이블 SELECT

  • 데이터 없음

• tx2: user_no + item_no로 inventory 테이블 SELECT

  • 데이터 없음

• tx1: INSERT

• tx2: INSERT

결과

• 유니크 제약조건이 걸려있어서 하나는 정상적으로 INSERT 됨

• 다른 하나의 tx에서 오류 발생

  • Deadlock found when trying to get lock; try restarting transaction

혹시 "user_no + item_no로 inventory 테이블 SELECT"에서 SELECT 문장이 FOR UPDATE를 사용하나요 ?

만약 FOR UPDATE를 사용하지 않는다면, "show engine innodb status" 의 결과에서 Deadlock 관련 정보도 좀 보여주실 수 있을까요 ?

안녕하세요. 성욱님, 답변 감사합니다.

SELECT 시 FOR UPDATE는 사용하지 않고 있습니다.

그리고 요청 주셨던 show engine innodb status 결과는 당장 전달 드리기는 어려울거 같고, 추후에 확인 가능한 시점에 다시 답글로 전달 드리겠습니다.

한 가지 궁금한건 지금과 같은 비즈니스 상황은 두 개의 tx가 inventory 테이블에 동시에 INSERT를 하는건데, row가 없으므로 SELECT .. FOR UPDATE를 이용해서 락을 거는게 가능한건가요?

강의 예시에서는 쿠폰과 같이 기존에 존재하는 데이터에 대해 여러 트랜잭션이 경합할 때 락을 걸어서 사용하는건 이해 됐지만, 존재하지 않는 row를 INSERT 할 때도 유의미할지 궁금합니다.

말씀주신 것처럼 SELECT 쿼리가 FOR UPDATE 없이 실행되었다면, 결국 2개 트랜잭션이 단순히 INSERT 문장 하나로 서로 Deadlock 상황이 발생했다는 것인데요. 사실 단순 INSERT끼리만 경합해서 Deadlock이 될 가능성은 상당히 낮아 보이긴 해요.

Deadlock 상세 정보를 보여주시면, 좀 더 정확한 원인을 확인해봐드릴게요.

데드락 내용이 분석되어 공유 드립니다. ^^

순수 SELECT + INSERT 동시 요청에 의해서 발생한게 맞네요.

결론적으로 MySQL의 기본 설정인 Repeatable-Read 격리 수준에서 발생하는 GapLock 추가 요청에 의해 데드락이 발생 했습니다.

세션 레벨에서 Read-Committed를 사용할지 고민 해봐야 할거 같네요.

안녕하세요.

FOR UPDATE나 FOR SHARE 절이 없는 순수한 SELECT 문장은 잠금을 걸지 않습니다.

(물론 지금의 경우 FK가 있어서, 부모 테이블의 Shared Lock이 필요하지만, Deadlock의 원인은 아닌듯 하고요)

(말씀주신 내용을 토대로 생각해보면) 결국 지금 Deadlock은, 2개 이상의 세션에서 INSERT 문장 하나 실행하면서 Deadlock이 발생한 케이스로 보여요. 다양한 패턴의 구문들(트랜잭션 시작 이후 여러 레코드를 INSERT하거나 UPDATE 하는 등)로 인한 것이 아니라면, 가능성이 상당히 낮게 발생해야 할 것으로 보여요. (deadlock 내용을 보여주시면 요 부분을 확인해보려고 했던 것이었는데요..)

세션 레벨에서 Read-Committed를 사용할지 고민 해봐야 할거 같네요.

어쨋거나, 요 방향으로 진행한다고 하시니... 참고로 격리 수준을 변경하는 부분은 .. 조금 주의가 필요해 보이는데요. 만약 서버의 격리 수준을 통째로 변경하신다면, 기존에 이미 Gap Lock (repeatable-read에서 사용되는)에 의존하는 동기화 기능을 사용하는 쿼리들이 있었다면, 해당 쿼리들이 영향을 받게 될 수도 있으니, 먼저 기존 INSERT UPDATE 문장들을 한번 검토해보시고, 격리 수준을 변경하시는 방법을 권장드려요. 아니면, 요 INSERT 문장을 실행할 때에만 격리 수준을 read-committed로 바꾸시면 더 안전할 듯 하고요.

데드락을 매우 간헐적으로 발생하고 있고, read-committed를 적용하는 것도 많은 검증이 필요하여 검토만 할 뿐 아마도 안할거 같네요. 글로벌 설정은 절대 변경하지 않으려고 합니다.

참고로 데드락을 유발한 Gap Lock은 두 세션에서 동일한 유니크 인덱스를 사용 하기 때문이라고 전달 받았습니다.

제가 DBA가 아니라 내용을 유연하게 전달 드리지 못한거 같아 죄송하네요.

현 시점에서 서비스에 영향을 주는 부분도 아니고, 강의 내용대로 데드락을 100% 해결 하는걸 목표로 하는 것 보다는 8.0 이후에 도입된 락 회피 기능을 이용하여 대기시간을 최소화 하는 방향으로 진행 해볼까 합니다.

질문에 관심 가져주셔서 감사합니다 ^^