INSERT INTO SELECT SHARED LOCK은 레코드 락으로 작동하는가? with MySql

회사에서 수기 작업 간 발생한 이슈를 해결하면서 회사 시니어님이 공유해주신 자료를 보고 이론으로 공부만 했던 것이 문득 궁금해져 테스트해 본 기록을 남깁니다.

환경

• MySql 8.0
• Datagrip
• Transaction Mode - Manual 

 

테스트 1

목표

• Table 단위의 LOCK이 걸리는가? 아니면 ROW 단위의 LOCK이 걸리는가? - 레코드 락
• 특정 ROW에만 SHARED LOCK이 걸리고, 다른 ROW의 CRUD에는 영향이 없는가?
  • SHARED LOCK - 읽기 가능. 수정/삭제 불가

초기 세팅

create table test1 (
    col1 int primary key auto_increment,
    col2 char(10)
);
create table test2 select * from test1 limit 0;

insert into test1 (col2) values ('aaa'),('bbb'),('ccc');

## 현재 걸린 lock 조회
select * from performance_schema.data_locks

 

시나리오

# Session 1
begin;

insert into test2
select *
from test1
where col1 = '1';

• col1=1 S락 획득 시도
• primaryKey=1 S락 획득 성공

 

 

# Session 2
update test1
set col2 = '1111'
where col1 = 2;

• col1=2 X락 획득 시도
• primaryKey=2 락이 존재하지 않았기에 X락 획득 성공

 

 

# Session 2
update test1
set col2 = '1111'
where col1 = 1;

• col1=1 X락 획득 시도
• 하지만 Session 1 에서 S락을 걸고 있었기에 호환되지 않아 WAITING.

 

결과

• 락이 걸리지 않은 레코드에는 update가 가능하다.
• 즉 레코드락으로 작동하는 것을 확인할 수 있다.
• S락이 걸린 레코드에 X락이 획득 요청이 들어오면 S락이 해제될 때까지 기다린다.
  • S락과 X락은 호환되지 않기 때문이다. timeout, deadlock의 주범.

 

하지만..

 

정리하면서 최근 읽은 REAL MySQL의 잠금 파트가 생각나서 다시 읽어봤습니다.

이 테이블에 인덱스가 하나도 없다면 어떻게 될까?
이러한 경우에는 테이블을 풀 스캔 하면서 UPDATE 작업을 하는데,
이 과정에서 테이블에 있는 30여만 건의 모든 레코드를 잠그게 된다.
이것이 MySQL의 방식이며, MySQL에서 인덱스 설계가 중요한 이유 또한 이것이다.

- REAL MySQL 1권. 5.3.2 인덱스와 잠금 (172p)

• 이 내용을 읽고 앞의 테스트를 다시 보니 의문이 생겼습니다.
• where 조건의 col1은 PK이기에 unique index로 이미 잡혀있어 단일 레코드 락이 걸립니다.
  • 하지만 index가 없는 col2라면?
  • col2에 non unique index를 걸고 조회한다면?

 

테스트 2

목표

index가 아닌 col2를 where 조건으로 실행하는 경우 락이 어떻게 잡히는가?

초기 세팅 동일

시나리오

# Session 1
begin;

insert into test2
select *
from test1
where col2 = 'bbb';

• col2=bbb S락 획득 시도. 
• 하지만 모든 레코드에 대해 S락 획득 성공.

 

 

# Session 2
update test1
set col2 = 'fff'
where col2 = 'ccc';

• col2=ccc X락 획득 시도. 
• 하지만 Session 1 에서 모든 레코드에 S락을 걸고 있기에 호환되지 않아 WAITING.

 

결과

index가 없다면 모든 레코드가 락에 걸린다.

 

테스트 3

목표

col2에 non unique index를 걸고 where 조건으로 실행하는 경우 락이 어떻게 잡히는가?

초기 세팅

create table test1
(
    col1 int primary key auto_increment,
    col2 char(10)
);

create table test2
select *
from test1
limit 0;

insert into test1 (col2)
values ('aaa'), ('aaa'),
       ('bbb'), ('bbb'),
       ('ccc'), ('ccc');

create index test1_col2_index on test1(col2); ## 인덱스 생성

 

시나리오

# Session 1
begin;

insert into test2
select *
from test1
where col2 = 'bbb';

• col2=bbb S락 획득 시도.
• primaryKey=3,4,5  S락 획득 성공.
• col2는 non unique index이지만 primaryKey=3,4 S락 획득 성공.
• primaryKey=5  S락이자 GAP락 획득. 

 

 

# Session 2
update test1
set col2 = 'fff'
where col2 = 'ccc';

• col2=ccc X락 획득 시도.
• primaryKey=5,6  X락 획득 성공.
• 하지만 unique index 때와는 다르게 REC_NOT_GAP, GAP 락들이 추가로 획득됩니다. 

 

 

# Session 2
update test1
set col2 = 'ggg'
where col2 = 'bbb';

• col2=bbb X락 획득 시도.
• 하지만 Session 1에서 S락을 걸고 있기에 호환되지 않아 WAITING.

 

결과

테스트 1과 결과는 같지만 락이 걸리는 형태가 매우 다릅니다.

 

결론 요약

MySQL(InnoDB)에서 INSERT INTO SELECT SHARED LOCK은 index가 있는 경우에만  레코드 락으로 작동합니다.

• non unique index의 경우 락이 걸리는 형태가 매우 다릅니다.
• 심지어 no index 인 경우는 전체 레코드 락으로 테이블 락과 같은 상태가 됩니다. 

 

하지만 실무에서는 큰 문제가 없을 것으로 예상됩니다. 

• 이 이슈는 일반적이지 않은 업무 흐름에서 수기 작업 간 조작을 잘못한 인적 실수입니다. (영업의 요청)
• 실무에서 사용되는 테이블과 대상 컬럼은 최소한 non unique index가 잡혀 있을 것으로 기대합니다.

 

아쉽게도 IS, IX, GAP Lock 등에 대해서는 좀 더 공부가 필요합니다.
관련되어 잘 정리되어 있는 블로그를 공유합니다.

Lock의 종류 (Shared Lock, Exclusive Lock, Record Lock, Gap Lock, Next-key Lock)