[MYSQL] InnoDB 인덱스 락 테스트

Mysql의 InnoDB는 인덱스 기반의 레코드락 잠금을 지원한다.

즉, 변경할 행을 인덱스 기준으로 조회하고 조회된 행 모두에 잠금을 건 다음 변경을 진행하는데, 이 때문에, InnoDB에서는 인덱스를 잘 정의하는것이 성능과 직결된다. 

 

이 게시글 에서는 InnoDB에서 업데이트를 진행시 잠기는 과정을 실습해볼것이다.

---

[테스트 환경]

Server version: 8.0.29 Homebrew

Storage engine : InnoDB

transaction isolation : REPEATABLE-READ

 

테스트 목적 : 스토리지엔진이 InnoDB인 테이블에 업데이트 쿼리시 잠기는 행 조사

---

- 테스트 해볼 테이블 정보

테스트 대상 테이블이다

 m_id와 m_area열은 인덱싱 되어있고, m_name은 인덱싱 되어 있지 않음을 기억하자

 

1. 자동 커밋을 방지하기위해 트랜잭션을 열어주자 - global 변수의 autocommit을 off해도 무방하다

트랜잭션 시작

2. m_id는 16이하면서, m_name이 '홍길동'인 행의 m_area를 '하와이' 으로 변경할 것 이다.

예상대로라면, m_id = 12 이면서 m_name이 '홍길동'인 첫번째 행만 잠금할 것 처럼 보인다. (다른 테이블은 조건을 만족하지 않으므로 잠그지 않아도 될거같다)

변경 쿼리

쿼리를 날리고 잘 변경되었나 조회를 해보자. 

잘 변경이 되었음을 볼 수 있다.

* 이 글의 주제와 상관없지만, 커밋하지 않았음에도 데이터가 변경된 모습으로 보이는것은 커밋하지 않았지만 데이터 베이스는 변경된 상태이기 때문이다. 만일 다른 트랜잭션에서 member테이블을 조회할 경우 undo log에서 변경되기 전의 데이터 '서울'을 출력한다. (이는 isolation 레벨에 따라 다를 수 있음) - 틀린내용이 있다면 말해주세요

 

3. 아직 커밋하지 않았음을 명심하며 락이 걸린 행을 조회해 보자.

예상과는 다르게 m_id가 12 에서 16까지 모두 잠겼음을 볼 수 있다.

 

4. 다른 세션에서 트랜잭션을 열어 member테이블의 '하와이'로 변경된 정보를 '서울'로 다시 변경하는 쿼리를 날려보자.

 

첫번째 트랜잭션에서 m_id = 12 에서 m_id = 16인 행에 락이 걸려있기 때문에, 두번째 트랜잭션은 멈춰있다 lock wait timeout이 발생한다.

5.  이번에는 update 조건을 변경해서, m_id <= 16 and m_area like '하와이'로 변경해보자.

처음 변경과의 차이점은 

- m_name은 인덱싱 되어있지않음

- m_id와 m_area는 인덱싱 되어있음

즉, 이번에는 두 조건 모두 인덱싱 되어있는 열로 조회한다.

6. 락이 걸리는 행을 조회해보자.

m_id는 12 m_area = '하와이'인 행에만 락이 걸리는 것 을 볼 수 있다

 

---

결론 

InnoDB는 인덱스 기반의 레코드락을 지원하는데, 데이터 변경시 조회된 인덱스 모두에 잠금을 건다.

따라서, 성능을 위해서 인덱스 구조를 잘 설계하는것이 중요해 보인다.