[230916] MySQL InnoDB의 베타 락

✅ MySQL InnoDB의 베타 락

헷갈려서 스스로에게 내본 문제.

(1) 인덱스가 걸려있지 않은 컬럼으로 베타 락을 거는 경우

Q. 위와 같은 test 테이블이 있다. id는 기본 키이고, name은 인덱스가 걸리지 않은 컬럼이다.

이 때 select * from test where name = 'a' for update 쿼리로 베타 락을 걸면 어떤 컬럼에 어떤 락이 걸릴까?

 

A.

*************************** 1. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 4. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
*************************** 5. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5

모든 기본 키 인덱스에 베타 락이 걸린다. 

name으로 만들어진 인덱스가 없기 때문에 유일하게 존재하는 기본 키 인덱스에 락을 걸어야 한다. 이 때 기본 키 인덱스만 가지고선 원하는 데이터가 어디있는 지 파악할 수 없기 때문에 모든 레코드에 락을 걸게 된다.

 

Q. 앞 상황에서 (락이 걸린 상황) insert into test (id, name) values (3, 'f') 쿼리로 2번과 5번 row 사이에 데이터 삽입을 시도하면?

 

A. 대기가 걸린다. 

 

(2) 인덱스가 걸려있는 컬럼(기본 키 X)으로 쓰기 락을 거는 경우

같은 테이블이다. 현재는 name 컬럼으로 단일 인덱스(idx_name)를 추가했다.

Q. 이 때 똑같이 select * from test where name = 'a' for update 쿼리로 쓰기 락을 걸면 어떤 컬럼에 어떤 락이 걸릴까?

 

A.

*************************** 1. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: idx_name
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'a', 2
*************************** 3. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 4546756464
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
*************************** 4. row ***************************
          OBJECT_NAME: test
           INDEX_NAME: idx_name
OBJECT_INSTANCE_BEGIN: 4546756808
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'c', 1

우선 idx_name에서 'a' 인덱스에 베타 락이 걸렸다.

그리고 2번 레코드에 기본 키 인덱스로 (갭 없이) 베타 락이 걸렸다. idx_name 인덱스 말고도 다른 경로로 해당 레코드를 조회하고 수정하는 걸 막기 위함이다.

마지막으로 idx_name에서 'c' 인덱스에 갭 락이 걸렸다. idx_name 인덱스에서는 'a' 다음에 'c'가 있기 때문이다.