MySQL
오픈소스 관계형 데이터베이스 관리 시스템
- InnoDB: ACID 모델을 따르는 기본 스토리지 엔진
InnoDB Architecture
- Buffer Pool: 테이블과 인덱스 데이터를 캐싱하는 메모리 영역
- 자주 사용되는 데이터를 메모리에서 얻을 수 있도록 만듬 (LRU 기반)
- 일반적으로 80%의 메모리를 할당할 정도로 중요함
- Redo Log: 불완전한 트랜잭션에 대한 복구를 위한 디스크 기반 자료구조
- 복구는 MySQL이 시작될 때 진행됨
- Undo Logs: 각 단일 read-write 트랜잭션의 Undo 방법을 기록한 컬렉션
- 다른 트랜잭션이 Consistent read를 위해 과거 상태를 참조할 때 활용됨
InnoDB Locking
Lock 종류
| X | IX | S | IS | |
|---|---|---|---|---|
| X | Conflict | Conflict | Conflict | Conflict |
| IX | Conflict | Compatible | Conflict | Compatible |
| S | Conflict | Conflict | Compatible | Compatible |
| IS | Conflict | Compatible | Compatible | Compatible |
Shared Locks (S-lock)
행 읽기를 위한 공유 가능 잠금
- 다른 트랜잭션이 동일 레코드에
S-lock을 동시에 획득할 수 있음 - 단,
X-lock이 걸려있다면 풀릴 때까지 대기해야 함
Exclusive Locks (X-lock)
행 쓰기를 위한 베타적 잠금
- 동일 레코드에
S-lock이나X-lock이 걸려있다면 풀릴 때까지 대기해야 함
Intention Locks
테이블의 행에 X-lock 혹은 X-lock을 획득할 의도를 표현하기 위한 잠금 (multiple granularity locking)
- 트랜잭션이
S-lock을 획득하기 위해서는, 테이블의SI-lock혹은 더 강한 잠금을 가져야만 함 - 트랜잭션이
X-lock을 획득하기 위해서는, 테이블의XI-lock을 가져야만 함 - 일부 레코드에 대한 잠금은 허용하고, 테이블 전체를 잠궈야 하는 상황을 Blocking하기 위한 목적
Record Locks
인덱스 레코드에 거는 잠금
- 단일 레코드를 잠그기 위한 목적
Gap Locks
인덱스 레코드들 간 간격에 거는 잠금
- 특정 범위 내, 존재하는 모든 값들 간의 간격마다 잠금이 걸림
- 범위 조회 시 새로운 레코드의 출현(Phantom rows)을 막기 위한 목적
Next-Key Locks
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)Record lock과 Gap lock을 모두 거는 잠금
- 특정 레코드와, 해당 레코드 직전의 Gap에 잠금을 검
- 범위 조회 시 새로운 레코드의 출현(Phantom rows)을 막기 위한 목적
Insert Intention Locks
행 삽입을 의도하는 일종의 Gap lock
- 행 삽입 시 Next-key lock을 대신하여 사용함 (갱신이나 삭제 시에는 Next-key lock을 사용)
- 여러 트랜잭션들이 동일 Gap에 서로 다른 값의 행을 삽입할 때, 충돌이 비교적 덜 발생하도록 만들기 위한 목적
AUTO-INC Locks
AUTO_INCREMENT 컬럼을 갖는 테이블에 행을 삽입할 때 거는 테이블 레벨 잠금
- 한 트랜잭션이 새 행을 삽입하고 있으면, 다른 트랜잭션은 이를 기다려야 함
- 연속된 PK를 가질 수 있도록 만들기 위한 목적
SQL문에 의한 Lock들
SELECT ... FROM- 일반적인 경우 Consistent read로 Lock을 걸지 않으나,
SERIALIZABLE격리 수준에서는FOR SHARE로써 작동함
SELECT ... FOR SHARE
SELECT ... FOR UPDATE
UPDATE ... WHERE ...
DELETE FROM ... WHERE ...unique인덱스에 대한 단일 레코드 특정: 해당 인덱스 레코드에 Record lock 적용- 그 외(단일 레코드를 특정할 수 없는 조건문): 스캔한 인덱스 범위 모두에 Next-key lock 적용
- 인덱스가 없는 컬럼이라면 테이블 내 모든 범위를 잠그게 됨
- 이때,
SELECT ... FOR SHARE는 S-lock, 그 외에는 X-lock 적용
INSERT ...- 삽입 전 Insert intention lock을 획득하고, 삽입 후 Record X-lock 적용
duplicate-key에러가 발생하면 Record S-lock을 획득함- 데드락 위험: 여러 세션이 같은 값을 동시에 삽입하여 S-lock을 획득하면, 어느 한 쪽도 X-lock을 획득할 수 없음
INSERT ... ON DUPLICATE KEY UPDATEduplicate-key에러 발생 시 S-lock이 아닌 X-lock을 획득하도록 함
INSERT ...
UPDATE ...
DELETE FROM ...
// WHERE: `FOREIGN KEY` 제약 조건이 적용된 테이블- 제약 조건 확인이 필요한 경우, 참조하는 레코드에 Record S-lock을 적용함
