전체 글

[MySQL] 갭 락과 넥스트키 락 재현: 없는 값을 조회해도 INSERT가 막히는 이유

데이터베이스를 다루다 보면 분명히 존재하지 않는 값을 조회했을 뿐인데, 다른 세션에서 실행한 INSERT 문이 아무 이유 없이 멈춰버리는 상황을 마주하게 됩니다. 개발자 입장에서는 "그 값은 테이블에 없는데 왜 잠기지?"라는 의문이 들 수밖에 없습니다. 이때 원인은 대부분 InnoDB 스토리지 엔진의 갭 락(Gap Lock)과 넥스트키 락(Next-Key Lock)입니다. 이 락들은 존재하는 레코드가 아니라 인덱스 레코드와 레코드 사이의 빈 공간에 걸리기 때문에, 조회 결과가 0건이어도 다른 트랜잭션의 INSERT를 차단할 수 있습니다. 이번 글에서는 갭 락과 넥스트키 락의 동작 원리를 정리하고, 두 세션을 이용한 재현 SQL과 잠긴 구간을 실제로 확인하는 방법, 그리고 실무에서 이 락을 다룰 때의 주의..

[MySQL] 메타데이터 락 때문에 멈춘 DDL 진단과 안전한 해소 절차

데이터베이스를 다루다 보면 분명 무거운 쿼리도 없고 InnoDB 락 경합도 안 보이는데, ALTER TABLE 같은 DDL 문이 실행조차 되지 못하고 그대로 멈춰 있는 상황을 만나게 됩니다. SHOW PROCESSLIST를 봐도 딱히 원인이 될 만한 것이 없어 보이고, 담당자는 배포 창구 시간이 흘러가는데도 손을 못 대는 경우가 많습니다. 이때 가장 흔하게 의심해야 할 것이 바로 메타데이터 락(Metadata Lock, 이하 MDL)입니다. 이 글에서는 MDL이 무엇이고 왜 걸리는지부터, performance_schema를 이용해 원인이 되는 세션을 정확히 찾아내는 방법, 그리고 서비스에 영향을 최소화하면서 안전하게 대기를 해소하는 절차까지 실무 순서대로 깊이 있게 다루어 보겠습니다.1. 메타데이터 락(..

[MySQL] NOWAIT와 SKIP LOCKED 활용: 대기 없는 작업 큐 구현과 한계

데이터베이스를 다루다 보면 여러 개의 프로세스가 같은 작업 테이블을 두고 경쟁하는 상황을 자주 만나게 됩니다. 흔히 "작업 큐(Job Queue)"라고 부르는 구조인데, 배치 워커 여러 대가 동시에 떠서 대기 중인 작업(PENDING) 행을 하나씩 가져가 처리하는 패턴입니다. 이때 가장 먼저 떠올리는 방법이 SELECT ... FOR UPDATE로 행을 잠근 뒤 상태를 변경하는 것인데, 워커 수가 늘어나는 순간 문제가 생깁니다. 한 워커가 이미 잠근 행을 다른 워커가 FOR UPDATE로 또 조회하면, 뒤따라온 워커는 잠금이 풀릴 때까지 그대로 대기(Lock Wait)하게 됩니다. 워커가 많아질수록 대기 행렬이 길어지고, 결국 innodb_lock_wait_timeout에 걸려 타임아웃 에러를 뿜어내는 ..

[MySQL] SELECT FOR UPDATE와 FOR SHARE 비교: 예약·재고 처리 잠금 설계

데이터베이스를 다루다 보면 재고 차감이나 좌석 예약처럼 '먼저 값을 읽고, 조건을 확인한 뒤, 다시 값을 갱신하는' 로직을 자주 작성하게 됩니다. 문제는 이 읽기와 쓰기 사이에 다른 트랜잭션이 끼어들면 초과 판매나 중복 예약 같은 정합성 문제가 발생한다는 점입니다. 이때 가장 흔하게 사용되는 것이 바로 비관적 잠금(Pessimistic Locking)이며, MySQL의 InnoDB 스토리지 엔진은 이를 위해 SELECT ... FOR UPDATE와 SELECT ... FOR SHARE라는 두 가지 잠금 읽기(Locking Read) 구문을 제공합니다. 이 글에서는 두 구문의 기본 개념과 잠금 종류부터 격리 수준별 동작 차이, 재고 차감 로직에 어떤 것을 선택해야 하는지, 그리고 데드락을 피하기 위한 NO..

[MySQL] 긴 트랜잭션이 Undo와 Purge를 지연시키는 과정 측정하기

데이터베이스를 다루다 보면 어느 날 갑자기 데이터 디렉터리의 undo 테이블스페이스 파일이 눈에 띄게 커져 있는 것을 발견할 때가 있습니다. 테이블 데이터는 그대로인데 디스크 사용량만 계속 늘어난다면, 대부분의 경우 범인은 어딘가에서 며칠째 커밋되지 않고 열려 있는 트랜잭션입니다. 이때 흔히 놓치는 사실은, 문제를 일으키는 트랜잭션이 반드시 대량의 쓰기를 하는 트랜잭션일 필요가 없다는 점입니다. 단순히 SELECT 한 번을 실행한 뒤 커밋도 롤백도 하지 않은 세션 하나가, 전혀 다른 테이블에서 다른 트랜잭션들이 계속 만들어내는 undo 레코드의 회수(Purge)를 통째로 막아버릴 수 있습니다. 이 글에서는 InnoDB의 Undo Log와 Purge가 어떻게 맞물려 동작하는지 기본 원리부터 짚고, Hist..

[MySQL] performance_schema로 현재 잠금 대기와 차단 세션 찾기

들어가며: 잠금 대기, 범인을 못 찾으면 못 풉니다데이터베이스를 다루다 보면 갑자기 특정 쿼리가 멈춘 것처럼 응답하지 않는 상황을 자주 마주하게 됩니다. 애플리케이션 로그에는 타임아웃만 찍혀 있고, SHOW PROCESSLIST를 열어봐도 State: waiting for handler commit이나 Waiting for table metadata lock 같은 문구만 보일 뿐, 정작 어떤 세션이 어떤 세션을 막고 있는지는 알려주지 않습니다. 이때 실무에서 흔히 하는 실수가 감으로 의심 가는 세션을 KILL부터 해버리는 것입니다. 하지만 진짜 차단 세션(blocker)이 아니라 대기 세션(waiter)을 죽여버리면 문제는 그대로 남고, 반대로 이미 곧 커밋될 트랜잭션을 성급하게 종료시키면 애플리케이션에..

[MySQL] 데드락 로그 읽는 법: 피해 트랜잭션과 잠금 순서 복원하기

데이터베이스를 다루다 보면 어느 날 애플리케이션 로그에 Deadlock found when trying to get lock이라는 에러가 찍히는 순간을 만나게 됩니다. 개발자 입장에서는 당황스럽지만, DBA 입장에서는 오히려 반가운 신호이기도 합니다. MySQL의 InnoDB 스토리지 엔진은 데드락을 스스로 감지해서 둘 중 한 트랜잭션을 강제로 롤백시키기 때문에, 서비스가 완전히 멈추는 대신 한쪽만 실패로 끝나기 때문입니다. 문제는 그다음입니다. "어떤 SQL과 어떤 레코드가 충돌했는가", "왜 하필 이 트랜잭션이 롤백됐는가"를 설명하지 못하면 똑같은 데드락이 반복해서 터지게 됩니다. 이때 가장 먼저 확인해야 하는 것이 바로 SHOW ENGINE INNODB STATUS가 남기는 LATEST DETECT..

[MySQL] 옵티마이저 힌트 사용 기준: JOIN_ORDER와 INDEX 힌트의 선택·제거 원칙

데이터베이스를 다루다 보면 같은 SQL인데도 데이터 분포나 통계 정보가 달라진 뒤 갑자기 실행 시간이 늘어나는 상황을 만나게 됩니다. 실행 계획을 확인해 보니 큰 테이블부터 조인하거나 선택도가 낮은 인덱스를 고르는 경우도 있습니다. 이때 가장 흔하게 검토하는 대응이 바로 옵티마이저 힌트(Optimizer Hint)를 이용한 실행 계획 강제입니다.하지만 힌트는 느린 SQL에 무조건 붙이는 성능 옵션이 아닙니다. 잘못 선택하면 현재 데이터에서는 빨라 보여도 데이터가 증가한 뒤 더 나쁜 계획을 고정할 수 있으며, 인덱스 이름 변경이나 쿼리 블록 변형으로 의도대로 적용되지 않을 수도 있습니다. 따라서 가장 중요한 것은 어떤 계획 요소가 잘못되었는지 구분하고, 적용 전후의 계획과 실제 실행 시간을 검증하며, 제거..

[MySQL] 중복 인덱스 찾기: 좌측 접두사 관계와 삭제 전 검증 절차

데이터베이스를 다루다 보면 하나의 테이블에 비슷한 컬럼으로 구성된 인덱스가 계속 추가되는 경우가 많습니다. 장애 대응이나 특정 쿼리 튜닝 과정에서 인덱스를 하나씩 만들다 보면 INDEX(a), INDEX(a, b), INDEX(a, b, c)가 함께 남기도 합니다. 이때 가장 흔히 하는 실수는 컬럼 앞부분이 같다는 이유만으로 짧은 인덱스를 즉시 삭제하는 것입니다.중복 인덱스는 조회 성능을 반드시 향상시키는 것이 아니라 저장 공간을 차지하고, INSERT·UPDATE·DELETE마다 함께 갱신되어 쓰기 비용을 높입니다. 하지만 짧은 인덱스가 특정 조회의 커버링 인덱스(Covering Index)로 사용되거나 더 작은 구조 덕분에 선택될 수 있으므로 단순 비교만으로 삭제해서는 안 됩니다. 이번 글에서는 중복..

[MySQL] 혼합 정렬 ORDER BY 최적화: 내림차순 인덱스로 filesort 피하기

데이터베이스를 다루다 보면 최신 데이터부터 보여 주되, 같은 시각의 데이터는 작은 식별자 순서로 정렬하거나 우선순위는 높게, 마감일은 빠르게 나열해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 것이 바로 ORDER BY created_at DESC, id ASC와 같은 혼합 정렬입니다.정렬할 행이 적으면 큰 문제가 드러나지 않지만, 조회 범위가 커지면 MySQL이 인덱스 순서를 그대로 이용하지 못하고 별도의 정렬 단계인 filesort를 수행할 수 있습니다. 이름과 달리 filesort가 항상 디스크 파일을 사용한다는 의미는 아니지만, 후보 행을 모으고 정렬해야 한다는 점은 같습니다. 따라서 대용량 목록 조회에서는 실행 시간과 메모리 사용량, 임시 파일 발생 가능성까지 함께 커질 수 있어 주의가 필요..