5. 잠금

table lock

•

개별 테이블 단위로 설정되고 명시적, 묵시적으로 락을 획득

•

InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금 제공 -> 단순 데이터 변경 쿼리로 인해 묵시적 테이블 락 설정 X, DDL 경우에만 테이블 락 설정

# session 1
lock tables morak.appointment read;
# session 2
select * from morak.appointment;
# 성공
update morak.appointment set status='OPEN' where id=1;
# 대기중
# session 1
unlock tables;
# update 쿼리 실행
SQL
복사

# session 1
lock tables morak.appointment write;

# session 2
update morak.appointment set status='OPEN' where id=1;
# 대기중
select * from morak.appointment;
# 대기중

# session 1
unlock tables;
#쿼리 실행
SQL
복사

named lock

•

get_lock() 함수를 이용해 임의의 문자열에 대해 잠금 설정

•

테이블, 레코드 등에 거는 것이 아니라 특정 문자열에 대해 잠금

•

8.0 부터 중첩해서 사용 가능

# was 라는 이름의 락을 획득, 락이 이미 있으면 2초만 대기
select get_lock('was', 2);
# was 라는 이름의 락이 free 한지 확인 0이면 X, 1이면 O
select is_free_lock('was');
# was 라는 이름의 락 해제
select release_lock('was');
SQL
복사

• 분산 환경에서 사용, 요청에 대한 key 값으로 해석 가능

metadata lock

•

잘 모르겠다. 조금 더 공부한 뒤에 다시 보자

InnoDB 스토리지 엔진 잠금

•

스토리지 엔진 내부에서 레코드 기반의 잠금 방식 탑재

•

동시성 처리 가능

•

information_schema 와 performance_schema 로 잠금 확인 가능

record lock

•

레코드 자체가 아닌 인덱스의 레코드를 잠금

•

인덱스가 하나도 없는 테이블도 내부적으로 자동 생성된 클러스터드 인덱스를 이용해 잠금 설정

•

PK 또는 unique 인덱스에 의한 변경 작업에서는 레코드에 락

gap lock

•

레코드와 바로 인접한 레코드 사이의 간격만을 잠금

•

레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어

•

넥스트 키 락의 일부로 자주 사용

next key lock

•

레코드 락 + 갭 락

•

바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적

•

데드락, 다른 트랙잭션 대기 상태 자주 발생

•

바이너리 로그 포맷을 ROW 포맷 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋음

◦

statement 포맷의 바이너리 로그가 가지는 단점을 많이 해결해줄 수 있어서 MySQL 8.0 에서는 ROW 포맷의 바이너리 로그가 기본 설정

auto increment lock(자동 증가 락)

•

auto increment 를 위한 테이블 수준의 잠금

•

insert, replace 같은 새로운 레코드를 저장하는 쿼리에서만 필요, update, delete 에서는 락 X

•

트랜잭션과 관계없이 insert, replace 문장에서 auto increment 값을 가져오는 순간만 락이 걸렸다가 해제

•

지금까지 MySQL 5.0 이하 방식

•

5.1 이상부터는 innodb_auto_inc_lock_mode 변수를 이용하여 작동 방식 변경 가능

◦

8.0 부터는 innodb_auto_inc_lock_mode=2 가 기본 -> 바이너리 로그 포맷이 STATEMENT 에서 ROW 로 기본값이 변경되었기 때문

index 와 잠금

•

변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 건다

인덱스 리프 노드

city	id
daejeon	5
dongtan	6
seoul	1
seoul	2
seoul	4
ulsan	3

테이블

id	name	city
1	eden	seoul
2	ellie	seoul
3	winnie	ulsan
4	kkara	seoul
5	chalee	daejeon
6	albur	dongtan

# session 1
start transaction;

update member set name='eden2' where id=1;
# id = 1 에 레코드 락
# city = seoul 에 레코드 락
# session 2
update member set name='eden2' where id=1;# 대기
update member set name='eden2' where city='seoul';
# 대기
update member set name='eden2' where name='ellie';
# 대기
update member set name='eden2' where id=2;
# complete
update member set name='eden2' where city='ulsan';
# complete
SQL
복사

•

인덱스가 없다면 테이블 풀 스캔하면서 update 작업 -> 모든 레코드가 잠김

•

where 조건절 컬럼으로 A, B 가 있고 A 만 index 가 걸려 있으면 B 로 인한 전체 레코드 잠금은 일어나지 않는다.

•

performance_schema 의 data_locks 테이블로 확인 가능

◦

lock_mode column

▪

IX -> Internal eXclusive

▪

REC_NOT_GAP -> 갭이 포함되지 않은 순수 레코드 잠금