INSERT INTO SELECT SHARED LOCK(row LOCK)

• 마켓보로에서 일하면서 생긴 트랜잭션 문제에 대해 시니어님과 교류한 내용입니다.

• 이 글은 블로그로 이관되었습니다.

INSERT INTO SELECT SHARED LOCK은 레코드 락으로 작동하는가? with MySql개린이의 일기장

테스트 1

목표

• Table단위의 LOCK이 걸리는가? 아니면 ROW단위의 LOCK이 걸리는가? (레코드 락)

• ROW단위: 특정 데이터에만 SHARED LOCK이 걸리고, 다른데이터의 CRUD에는 영향이 없는가?

  • SHARED LOCK: 읽기 가능. 수정/삭제 불가

시나리오

• Transaction Mode: Manual

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

1. Session 1

• col1=1 S락 획득 시도

• primaryKey=1 S락 획득 성공

begin;

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

2. Session 2

• col1=2 X락 획득 시도

• primaryKey=2 X락 획득 성공

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

3. Session 2

• col1=1 X락 획득 시도

• 하지만 Session 1 에서 S락을 걸고 있기에 호환되지 않아 WAITING.

update test1
set col2 = '1111'
where col1 = 1;

4. Session 1 commit, Session 2 commit

결과

• 락이 걸리지 않은 레코드에는 update가 가능하다.

• S락이 걸린 레코드에 X락이 들어오면 S락이 해제될 때까지 기다린다.

  • S락과 X락은 호환되지 않는다.

  • timeout의 주범.

• 즉 InnoDB는 레코드락으로 움직인다.

하지만..

• 정리하면서 최근 읽은 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 조건으로 실행하는 경우 레코드락이 어떻게 잡히는가?

시나리오

• 초기 세팅 동

1. Session 1

• col2=bbb S락 획득 시도.

• 하지만 모든 레코드에 대해 S락 획득 성공.

begin;

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

2. Session 2

• col2=ccc X락 획득 시도.

• 하지만 Session 1 에서 모든 레코드에 S락을 걸고 있기에 호환되지 않아 WAITING.

update test1
set col2 = 'fff'
where col2 = 'ccc';

3. Session 1 commit, Session 2 commit

결과

• 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); ## 인덱스 생성

1. Session 1

• col2=bbb S락 획득 시도

• primaryKey=3,4,5 S락 획득 성공

• col2는 non unique index 이지만 primaryKey=3,4 S락 획득 성공.

• primaryKey=5도 S락이자 GAP락으로 획득했는데 이부분은 좀 더 공부해야됩니다.

begin;

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

2. Session 2

• col2=ccc X락 획득 시도

• primaryKey=5,6 X락 획득 성공

• 근데 unique index이던 것과 다르게 REC_NOT_GAP, GAP 락들이 추가로 획득됩니다.

update test1
set col2 = 'fff'
where col2 = 'ccc';

3. Session 2

• col2=bbb X락 획득 시도

• 하지만 Session 1 에서 S락을 걸고 있기에 호환되지 않아 WAITING.

update test1
set col2 = 'ggg'
where col2 = 'bbb';

4. Session 1 commit, Session 2 commit

결과

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

최종 결론

innoDB에서 unique index에 대한 INSERT INTO SELECT SHARED LOCK 는 레코드 락이 맞습니다.

• 하지만 non unique index의 경우 락이 걸리는 형태가 매우 다릅니다.

  • IS, IX, 갭 락등에 대한 공부가 부족해 정확히 어떤 차이가 있는지 잘 모르겠습니다.

• 심지어 no index 인 경우는 전체 레코드 락으로 테이블 락과 같은 상태가 됩니다.

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

• 이 이슈는 일반적이지 않은 업무 흐름에서 수기 작업 간 트랜잭션 조작을 잘못한 인적 실수가 더 큽니다. (영업의 요청)

• 실무에서 사용되는 테이블은 최소한 non unique index가 걸려 있을 것입니다.

MySQL InnoDB lock & deadlock 이해하기Knowledge Logger