[MySQL] The total number of locks exceeds the lock table size

대규모 데이터를 삽입해서 테스트를 하기 위해 JDBC로 Batch Insert를 하고 있는데, 갑자기 제목과 같은 에러가 나타났다.

 

user 데이터 1억 개, user 테이블을 참조하는 device_token 데이터 10억 개를 넣는데,

이해가 안 가는 게 Batch size를 10,000으로 잡았을 때, user 데이터는 잘 삽입하더니 device_token 삽입할 때는 에러가 발생한다.

 

MySQL Error 1206, 데이터 엔지니어의 눈으로 바라보기

 

The total number of locks exceeds the lock table size 이슈 해결

 

우선 위 에러는 보통 너무 많은 데이터를 한 번에 읽으려고 할 때 발생한다.

 

select의 결과를 메모리(buffer)로 가져오는데, buffer_size 옵션에 할당된 값보다 큰 데이터가 넘어오니 실패하는 것이다.

에러 문구가 "out of buffer"가 아닌 "the total number of locks exceeds the lock table size"인 이유는 읽거나 쓰는 과정에서 격리 수준에 따라 lock을 거는데, 이 한계를 초과해버렸기 때문이다.

 

가장 간단한 해결책으로 buffer pool size를 늘려버리라는데, 배우는 입장에서 이런 재미없는 해결책은 원치 않는다.

 

우선 두 테이블의 데이터 크기를 생각해보자.

1. user
   • id: bigint (8 bytes)
   • account_book_notify, chat_notify, feed_notify : bit(1) each (1 byte each, 3 bytes total)
   • created_at, updated_at, deleted_at: datetime(6) (8 bytes each, 24 bytes total)
   • name, username: varchar(255) (최대 255 bytes each, 평균 50 bytes로 가정하면 100 bytes total)
   • 총 데이터 크기: 약 135 bytes/row
   • 인덱스(id) 크기: 8 bytes/row
   • user table 총 크기 : 약 143 bytes/row
2. device_token
   • id: bigint (8 bytes)
   • activated: bit(1) (1 byte)
   • created_at, updated_at, datetime(6) (8 bytes each, 16 bytes total)
   • user_id: bigint (8 bytes)
   • token: varchar(255) (최대 255 bytes, 평균 100 bytes 가정)
   • 총 데이터 크기: 약 133 bytes/row
   • 인덱스(PK, FK): 16 bytes/row
   • 총 인덱스 크기: 16bytes/row
   • device_token 테이블 총 크기 : 약 149 bytes/row

여기서 user 테이블 데이터 1억 개, device token에 데이터 10억 개를 추가하면 아래와 같다.

• user 테이블 1억 행: 143 bytes/row * 100,000,000 = 약 13.3 GB
• device_token 테이블 10억 행: 149 bytes/row * 1,000,000,000 = 약 138.4 GB

총 151.7 GB...? 아니 이렇게 클 줄 몰랐는데, 내 PC 어떻게 버티고 있는 거지. ㅋㅋ

 

그런데 이상하지 않나? 난 Batch Insert를 하고 있을 뿐인데, 왜 여기서 "the total number of locks exceeds the lock table size" 에러가 발생하는 거고, user 데이터는 아무런 문제가 없던 걸까?

 

 

이는 user 테이블과 device token 테이블의 차이점 때문인데, 바로 fk의 존재 유무 때문이다.

쓰기 연산을 수행하기 위해 device token에 lock을 거는데, user 테이블을 참조하고 있기 때문에 user 테이블의 관련 행 또한 같이 잠근다.

 

따라서 똑같이 10,000개 씩 batch insert를 하려고 해도, user 테이블은 user 테이블만 잠그면 끝나지만,

device token은 최악의 경우 20,000개의 lock을 필요로 하기 때문이다.

 

8MB니까 기본값이 맞다.

기본으로 설정된 innodb_buffer_pool_size는 8,388,608 byte라고 하는데, 이 값을 늘리면 lock을 관리한 메모리도 증가하므로 문제를 해결할 수 있는 것이었다.

 

어차피 로컬 환경 테스트니까 buffer pool을 늘려서 빠르게 데이터를 삽입하는 것도 나쁘지 않은 생각일지도...

왜냐면 지금 데이터 10억 개 넣는데, 1시간 동안 고작 2천 만 개 넣었다 ㅎㅎ^^