InnoDB 인덱스 잠금 실습해보기 (MySQL)

MySQL InnoDB의 인덱스 잠금 메커니즘 테스트 및 분석

들어가며

MySQL의 InnoDB 스토리지 엔진은 레코드 기반의 잠금 방식을 사용하지만, 흥미로운 점은 레코드 자체를 잠그는 것이 아니라 인덱스를 잠근다는 것입니다. 이 특성이 실제로 어떻게 동작하는지, 그리고 데이터베이스 설계와 성능에 어떤 영향을 미치는지 직접 테스트해보았습니다.

테스트 환경 준비

테스트를 위해 간단한 회원 정보 테이블을 만들고, last_name 컬럼에 인덱스를 설정했습니다.

CREATE TABLE user (
    first_name VARCHAR(30) CHARACTER SET utf8mb4,
    last_name VARCHAR(30) CHARACTER SET utf8mb4,
    age INT,
    INDEX idx_last_name (last_name)
);

그리고 다양한 성을 가진 회원 데이터를 삽입했습니다.

INSERT INTO user (first_name, last_name, age) VALUES
('하늘', '김', 22),
('도현', '김', 28),
('유진', '김', 24),
('재현', '김', 33),
('수빈', '김', 27),
('지안', '김', 26),
('태현', '김', 31),
('나윤', '김', 29),
('예린', '김', 23),
('시우', '김', 30),

('하람', '이', 25),
('연우', '이', 26),
('가온', '이', 32),
('서윤', '이', 28),
('예진', '이', 27),

('주원', '박', 29),
('하진', '박', 31),
('수아', '박', 22),

('채원', '최', 24),
('하린', '최', 30),
('도윤', '최', 33),

('세아', '정', 26),
('민재', '정', 34),

('라온', '조', 25),
('다은', '조', 29),

('은우', '유', 28),
('지후', '유', 27),

('하윤', '강', 30),
('시윤', '강', 26);

트랜잭션의 격리 수준은 테스트를 위해 REPEATABLE-READ로 변경했습니다.

이후 AUTO-COMMIT 옵션도 꺼주었습니다.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

테스트 시나리오

1. last_name 컬럼에 인덱스가 있는 상태에서 ‘김하늘’ 회원의 나이를 업데이트하는 트랜잭션을 시작
2. 첫 번째 트랜잭션이 완료되지 않은 상태에서, 다른 세션에서 ‘김도현’ 회원의 나이를 업데이트 시도
3. 트랜잭션과 락의 동작 방식 확인
   

테스트 진행

1. 첫 번째 세션에서 트랜잭션 시작

세션1 실행

세션1 설정된 잠금 확인

2. 두 번째 세션에서 업데이트 시도

세션2 무한 실행상태 (완료 안되고 진행아이콘 원이 계속 돌아감)

이후 에러코드

프로세스 목록 확인

트랜잭션 상태 확인

세션 2의 트랜잭션이 LOCK WAIT 상태에 들어갔습니다.

이는 세션 1이 ‘김’씨 성에 해당하는 인덱스에 대한 잠금을 보유하고 있어, 세션 2는 이 잠금이 해제될 때까지 기다려야 한다는 것을 의미합니다.

3. 잠금 해제와 트랜잭션 완료

세션1 커밋

커밋 후 다시 확인

결론

• InnoDB는 레코드가 아닌 인덱스를 잠금

  • 레코드 자체가 아닌, 해당 레코드를 찾는 데 사용된 인덱스에 잠금을 설정

• ‘김하늘’ 한 명의 레코드만 업데이트했지만, ‘김’씨 성에 해당하는 인덱스가 잠겨 같은 성을 가진 ‘김도현’의 업데이트도 대기 상태에 진입했습니다.

• 서로 다른 인덱스 값은 독립적으로 작동

  • ‘김’씨 성에 대한 인덱스 잠금이 ‘박’씨 성에는 영향을 미치지 않습니다.

데이터베이스 설계 시 고려사항

수정 작업(UPDATE)이 많이 발생하는 테이블에서는 중복 값이 많은 컬럼에 인덱스 설정을 지양하고, ‘성’과 같이 중복 값이 많은 컬럼에 인덱스를 설정하면 동시성이 크게 저하될 수 있을것 같다.