unique index가 걸린 상황에서 s-lock, x-lock 질문

Question

안녕하세요? 먼저 좋은 강의 감사합니다.   7:50쯤 unique 제약조건이 걸린 상황에서 deadlock이 발생하는 경우에 질문이 있어서 글 남깁니다.   말씀주신 시나리오는 unique index가 걸린 컬럼이 delete가 수행되면서, 동시에 insert into 구문이 들어오는 상황으로 말씀주셨는데요.   unique index는 s-lock을 꼭 필요로 한다면, delete가 선행되지 않는 상황에서도 deadlock이 발생해야되는거 아닌가? 싶습니다.   상상하는 예시는 다음과 같습니다. tx-1 : begin; insert into tab(pk) values(2) (index 2 또는 그 범위에 s-lock) tx-2 : begin; insert into tab(pk) values(2) (index 2 또는 그 범위에 s-lock) tx-1 : commit; -> index 2에 x-lock을 잡으려고 하지만 tx-2가 s-lock을 잡고 있어서 잡을 수 없음   하지만, 실제로 테스트 해보았을 때는 tx-1이 commit시에 정상적으로 insert 되고, tx-2는 duplicated key 오류를 반환합니다.   왜 이런지 알 수 있을까요? 감사합니다 😃   다시 한 번 생각해보니, tx-1은 pk=2 가 없기 때문에 insert 후 x-lock으로 전환하고, tx-2는 x-lock으로 인해 lock_wait인 것 같습니다. 혹시 맞을까요? delete 가 선행된 경우는 이미 있는 레코드에 tx-1,2가 s-lock이을 잡으면서 delete가 commit된 시점에 tx-1,2가 x-lock을 획득하려는데서 dead lock이 발생하는 것이고요

sungucklee2062 · Answer

안녕하세요   unique index는 s-lock을 꼭 필요로 한다면, delete가 선행되지 않는 상황에서도 deadlock이 발생해야되는거 아닌가? 싶습니다. 넵, 맞습니다. 이 예제에서 첫번째 트랜잭션이 DELETE를 실행하는 건, 두번째와 세번째 트랜잭션이 동시에 시작되면서 두번째와 세번째 트랜잭션이 시점이 딱 맞아 떨어지도록 해주는 트리거 역할을 해주는 것입니다. 그래서 실제 Deadlock은 첫번째 트랜잭션에서 COMMIT을 실행하는 시점에 발생하게 되는 것입니다.   감사합니다.

communityai8509 · Answer

안녕하세요, 인프런 AI 인턴입니다. 연어님, 질문해주신 내용을 바탕으로 설명드리겠습니다. unique 인덱스가 걸린 컬럼에서 Deadlock이 어떻게 발생할 수 있는지를 이해하려면, MySQL의 잠금 메커니즘(S-lock과 X-lock)과 트랜잭션 격리 수준에 대해 깊이 있는 이해가 필요합니다. Deadlock이 발생하는 시나리오 S-lock과 X-lock 차이 이해 : S-lock(Shared Lock)은 여러 트랜잭션에서 동시에 읽는 것은 가능하게 합니다. 반면, X-lock(Exclusive Lock)은 데이터의 읽기와 쓰기가 독점되어야 하는 경우 적용되어 다른 트랜잭션이 접근할 수 없습니다. 검증과 업데이트 과정 : UNIQUE 제약 조건이 있는 컬럼에 대해 삽입을 시도할 때, MySQL은 먼저 그 값이 이미 있는지를 확인하기 위해 S-lock을 얻습니다. 문제가 확인되지 않으면 X-lock을 획득하여 실제 데이터를 삽입하게 됩니다. Deadlock 회피 전략 : tx-1 에서 insert 가 성공적으로 완료되었는지 여부는 다른 트랜잭션( tx-2 )의 insertion 이 이미 완료되지 않았거나, 중복된 키 오류가 발생했을 가능성이 높습니다. 중복된 키 오류 시점에서, MySQL은 해당 트랜잭션을 롤백하여 deadlock을 예방합니다. 실제 동작 상의 설명 중복 키 오류 처리 : tx-1 이 commit되면서 성공적으로 INSERT 를 완료했다면, 이미 2 라는 값을 삽입한 것이므로, tx-2 가 똑같은 값을 삽입하려 할 때 UNIQUE 제약 조건에 의해 중복 키 오류를 반환하게 됩니다. 이를 통해, MySQL은 Deadlock 상황을 사전에 방지하는 메커니즘을 갖추고 있으며, 이 때문에 tx-1 및 tx-2가 사실상 경쟁 상태로 인해 Live Lock이나 교착 상태가 발생하지 않고 중복된 key 오류로 처리되는 것입니다. 관련 질문 링크: UNIQUE 인덱스와 Deadlock 시나리오 파악하기 에 대한 문서를 참조하시면 좋을 것 같습니다. 보다 깊이 있는 이해를 위해 트랜잭션 격리 수준에 대한 공식 문서도 참조하시면 도움이 될 것입니다. 저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.