InnoDB의 레코드락에 대한 궁금점

최근 운영체제를 복습하며 reader-writer problem 에 대한 토픽을 알게 되었다.

 

MySQL8의 기본 스토리지 엔진 InnoDB는 DDL이 아닌 이상 테이블 잠금이 아니라 레코드 기반 락을 사용한다.

문득, 어떤 식으로 락이 걸리고 어느 정도까지 제한을 두는지 궁금해졌다.

 

가정은 이렇다. 사실 레코드 하나의 전체에 락을 거는 것이 아니라 인덱스에 락을 거는 방식인데, 그럼 이 때, 해당 인덱스가 아닌 다른 속성들에는 락이 걸리지 않는 것인가?

만약 그렇다면, 해당 인덱스A를 제외한 커버링 인덱스B로 처리되는 또 다른 쿼리문은 해당 인덱스A 락이 풀리지 않아도 처리가 가능할까?

뭔말인지 잘 모르겠다. 일단 해보자

 

우선 인덱스 생성부터!

create index test1 on post (post_subject(100));
create index test2 on post (post_content(100));

post라는 테이블에 post_subject(글 제목)에 test1이라는 인덱스, post_content(글 내용)에 test2라는 인덱스를 생성한다.

왜 인덱스를 저렇게 이상하게 생성하는지에 대해서는 아랫 글을 참조하세요.

https://leezzangmin.tistory.com/36

MySQL8.0 Error Code: 1071. Specified key was too long 해결법

 

그 후, 두 개의 터미널을 띄워놓고 

START TRANSACTION;

명령으로 각각 명시적으로 트랜잭션을 시작한다. 오토커밋이 비활성화된다.

 

select 문 끼리는 충돌이 일어나지 않으므로, 

' SELECT ...... FOR UPDATE ' 문법을 사용해서 실험을 진행한다.

게시물 내용을 하나 찍어서 가져온다.

게시물 제목 인덱스를 통해 게시물 한 레코드를 다 가져온다. 먼저 날린 쿼리와 동일한 PK를 가진 레코드다.

두 쿼리를 날리고 나면, 두 클라이언트는 commit이 될 때까지 block 되어있다.

MySQL의 트랜잭션이 어떤 상태에 있는지 살펴보자.

 

SELECT * FROM information_schema.INNODB_TRX;

트랜잭션들의 상태를 볼 수 있는 명령어다.

 

날려보면,

뒤에 날린 쿼리가 LOCK WAIT 상태로 잠겨있다.

결과로 보면, 두 쿼리로 인해 뒤에 날린 쿼리에 LOCK WAIT가 발생했다.

 

---

 

락이 발생한 원인을 추측해보자. 

 

첫 번째 쿼리:

select post_content from post where post.post_content like "Similique quia iure necessitat%" for update;

해당 쿼리에서 PK 102번에 해당하는 레코드의 post_content 인덱스(아까 만들었던 test2 인덱스)를 LOCK 한다. 커밋이 되기 전에는 접근할 수 없다.

 

 

그 후, 두번째 쿼리:

select * from post where post.post_subject like "Ab culpa laboriosam q%" for update;

해당 쿼리도 PK 102번째에 해당하는 레코드를 post_subject 인덱스(아까 만들었던 test1 인덱스)를 통해 접근한다.

위 쿼리와 다른 것은, select 바로 뒤에 ' * '가 있다는 점이다. 따라서 인덱스가 아닌 레코드 전체에 LOCK을 걸어야 한다.

그런데, 첫 번째 쿼리에서 이미 post_subject 에 LOCK이 걸려있다.

그래서 첫 번째 쿼리가 post_subject를 반납할 때 까지 무작정 기다려야 한다.

 

 

이 상황을 개선해보자!

다시, 두 쿼리를 다르게 날려보자.

select post_content from post where post.post_content like "Similique quia iure necessitat%" for update;
select post_subject from post where post.post_subject like "Ab culpa laboriosam q%" for update;

두 쿼리를 날려봤다.

 

그런데, 여전히 LOCK WAIT가 발생한다......?

왜지...?

 

---

 

한시간 정도 삽질하며 고민한 결과. 인덱스 생성에 문제가 있었다.

 

아까 만들어놓았던 test1, test2 인덱스는 크기 제한때문에 텍스트의 일부 앞부분만 떼서 인덱스로 만들어놓았다.

그런데, select 질의 결과에는 모든 데이터가 나와야 하므로 인덱스를 타기는 했지만 결국 커버링 인덱스가 아닌 다시 PK를 타고 들어가서 리프노드까지 가야 한다....

요약하자면 동일한 PK를 타는 질의를 날리고 있어서 PK가 잠겨있었기 때문에 계속 LOCK WAIT가 관측되었던 것이다...!

 

해당 쿼리의 실행계획의 Extra 항목을 보면 'Using where'로 되어있다. 커버링 인덱스가 아니라는 뜻이다....

실행계획좀 미리미리 봐둘걸 에휴

 

 

 

그럼, 어떻게 하란 말이느냐?

 

다른 속성에 다른 인덱스 두개를 다시 생성해보자.. 이번엔 일부가 아닌 데이터 전체를 커버할 수 있는 인덱스,,,,,,

create index idx_create_time on post(created_at);
create index idx_update_time on post(updated_at);

 

게시글의 생성시간과 수정시간을 뜻하는 두 개의 속성에 인덱스를 생성..

 

 

그런데,

Error Code: 2013. Lost connection to MySQL server during query 가 발생한다.

인덱스 생성이 너무 오래 걸려 타임아웃이 걸리는 이유라고 한다.

https://stackoverflow.com/questions/28742446/create-index-causes-lost-connection-to-mysql

https://stackoverflow.com/questions/15712512/mysql-workbench-how-to-keep-the-connection-alive

두 글을 보고

넉넉하고 든든하게 3000초

설정 완료. 다시 인덱스 생성..

성공..

 

---

 

다시 쿼리 두개를 날려보자.

start transaction;
select created_at from post where created_at='2021-08-04 12:46:17' for update;

start transaction;
select updated_at from post where updated_at='2022-01-17 18:32:03' for update;

 

두근두근 결과는 ?

아 왜 LOCK WAIT

...왜 안될까.... 인덱스만 잠길텐데 왜 LOCK WAIT 일까...

 

실행계획도 커버링 인덱스인데....

 

여튼 많이 해봤으니 안되는 것으로 결론..

update문에 비밀이 있나보다!

 

 

스승님들께 질문하고 나중에 다시 왜 그런지 살펴봐야겠다..