[MySQL] InnoDB 인덱스 잠금 테스트

InnoDB는 레코드 기반의 잠금 방식을 사용하는데, 중요한 점은 레코드 자체를 잠그는 것이 아니라 인덱스를 잠근다는 것이다. 이 부분은 잘못 설계하면 데이터베이스 성능에 큰 영향을 미칠 것 같아 확실히 알아보기 위해 테스트를 진행해보기로 했다.

먼저 전체적인 시나리오를 설명하면 (first_name, last_name, age) 속성을 가지는 member 테이블을 만들고 last_name 속성에 인덱스를 설정했다. 테이블에 저장된 ‘김민수’ 회원의 나이를 바꾸기 위해 UPDATE하는 트랜잭션을 시작하였다. 이 경우, 동시에 다른 ‘김’씨 성을 가진 회원 정보를 수정할 수 있을까?

얼핏 생각하면 ‘김민수’ 회원 한명의 레코드를 수정하고 있는 것 뿐인데, 김민수 회원이 아닌 다른 ‘김’씨 성을 가진 회원의 레코드를 동시에 수정하는 것은 이상하지 않아 보인다.

그러나 InnoDB의 인덱스 잠금 방식에 따르면 한 트랜잭션에서 ‘김민수’ 회원의 나이를 변경하는 동안 인덱스는 last_name인 ‘김’에 걸려 있으므로 ‘김’씨 성을 가진 모든 회원의 레코드가 잠긴다.

실제로는 어떻게 동작하는지 테스트를 통해 알아보자.

1. (first_name, last_name, age) 컬럼을 가진 member 테이블을 만들면서 last_name 컬럼에 대해 인덱스를 설정해준다.

CREATE TABLE member (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT;
    INDEX idx_last_name (last_name)
);

2. 테이블에 다수의 회원 목록을 더미데이터로 넣는다. 더미데이터 쿼리는 ChatGPT에게 부탁했다.

INSERT INTO member (first_name, last_name, age)
VALUES
  ('Minsoo', 'Kim', 25),
  ('Joon', 'Kim', 32),
  ('Hojin', 'Park', 38),
  ('Sangmin', 'Kim', 29),
  ('Taeyang', 'Cho', 37),
  ('Eunbi', 'Yoo', 31),
  ...;

3. 생일을 맞은 김민수가 한살 더 먹었으니 나이를 업데이트하는 트랜잭션을 시작한다. 이때 트랜잭션이 자동으로 커밋되지 않도록 autocommit=OFF로 설정해준다.

START TRANSACTION;
UPDATE member SET age=age+1 WHERE last_name='Kim' AND first_name='Minsoo';

4. 앞선 트랜잭션이 commit되거나 rollback되지 않은 상태에서 새로운 트랜잭션에서 김씨 성을 가진 다른 회원의 레코드를 수정해보자.

START TRANSACTION;
UPDATE member SET age=age+1 WHERE last_name='Kim' AND first_name='Joon';

5. 두번째 트랜잭션에서는 잠금 대기 상태가 지속되다가 대기 시간이 초과되었다는 에러가 발생한다.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

이로써 첫번째 트랜잭션에서 업데이트 대상이 되는 레코드(김민수) 뿐만 아니라 같은 인덱스 값을 가지는 레코드(김준) 또한 잠기는 것을 확인할 수 있었다.

그러면 여러 클라이언트가 UPDATE 요청할 때 동시성이 떨어지지 않으려면 어떻게 인덱스를 설계해야할까?

위 예시를 구현해보면서 느낀 점은 이름에서 성(last_name)과 같이 겹치는 값이 많이 생기는 컬럼에 인덱스를 설정하면 동시성이 상당히 떨어질 것 같다. 따라서 읽기(SELECT)보다 수정 작업(UPDATE)이 많이 발생하는 테이블이라면 겹치는 값이 많이 생기는 컬럼에 인덱스 설정하는 것을 지양해야할 것 같다. 이 경우 인덱스를 설정한다면 auto increment, uuid 같이 고유한 값을 가지는 컬럼을 인덱스로 설정하고, 특정 레코드를 수정할 때는 꼭 필요한 경우가 아니라면 인덱스 설정한 고유한 값을 WHERE 절에 두고 찾아서 수정하는 것이 동시성을 향상시키는 방향이 아닐까 생각한다.