MYSQL - LOCK

Lock

• 락은 기본적으로 동시성을 보장한다. 즉. 락은 동시성을 제어할 때 락의 범위를 최소화를 하는게 중요하다.

왜냐하면 락의 범위가 길어지면 대지중인 커넥션이 많아지고 나중에는 커넥션 풀이 고갈로 이어질 수 있다.

• 기본적으로 MySQL에서는 트랜잭션 커밋 , 록백시점에 락이 풀린다.
• 트랜잭션이 곧 락의 범위가 된다고 생각하면 된다.
• MySQL에서 락은 크게 InnoDB 엔진 , MySQL 엔진 레벨로 나눌 수 있다.

 

📌 비관적 동시성 제어(PCC, Pessimistic Concurrency Control)

비관적 락이라고도 하며 트랜잭션이 충돌하는 가정하에 잠금을 거는 방식

• 일반적으로 Shared Lock, Exclusive Lock을 통해 이를 구현한다.

📌 낙관적 동시 수행 제어(OCC, Optimistic concurrency control)

• 낙관적 락이라고도 하며 트랜잭션이 서로 충돌하지 않는다고 가정하는 방식

레코드 잠금을 사용하지 않는 관계형 데이터베이스에 사용되는 동시성 제어 방법이다.

 

MySQL 엔진이 제공하는 Lock

---

1. 글로벌 락

Flush Tables with Read Lock

• 가장 범위가 큰 Lock이다.
• 락을 걸면 Read, Write가 실행이 불가능하다.
• Flush Tables with Read Lock을 실행을 하려면 기존의 쿼리가 다 실행이 되고 가능하다
  • 만약에 긴 select쿼리를 실행하고 있으면 락을 사용하지 못한다.
• 락은 실행한 서버에서 풀어야지 쿼리가 실행이 가능하다.

 

2. 테이블 락

• 개별 테이블 단위로 설정되는 잠금
• Lock Tables 테이블 이름 [Read | Write ] 를 하여 Read락 or Write락을 설정한다.
• Read Lock은 다른 사람들이 Insert를 하지 못한다.
• Write Lock은 다른 사람이 락 사용자가 Insert 하기 전까지 Select를 하지 못한다.

 

3. 네임드 락

• Get_Lock()을 통하여 임의의 문자열에 대해 잠금을 설정이 가능하다.

SELECT GET_LOCK('my_lock',2); //락을 거는데 이미 잠금이면 2초 동안만 대기
SELECT IS_FREE_LOCK('my_lock');//문자열이 잠금이 되었는지 확인
SELECT RELEASE_LOCK('my_lock');//락을 반납 한다.

 

 

InnoDB 에서 제공하는 락

---

Record Locks

• 레코드 자체만을 잠그는 락이다.
• 트랜잭션에서 데이터를 읽거나 수정하는 경우에 사용된다. 트랜잭션A가 데이터를 읽으면 트랜잭션B는 해당 레코드를 수정하지 못하여 데이터의 일관성을 보장한다.
• 레코드 락은 공유 락, 배타적 락으로 구분한다.

공유락	여러 개의 트랜잭션이 해당 레코드를 읽을 수 있다.
배타적 락	해당 레코드를 소유한 트랜잭션만 수정이나 삭제가 가능하다.

 

 

Gap Locks

• 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 락이다.
• 주로 범위 검색에서 사용이 된다.
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하고, 넥스트 키 락의 일부로 사용된다.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

유저 테이블에 1~10의 데이터가 있다고 가정하자

-- 트랜잭션 1에서 범위 검색을 수행합니다.
START TRANSACTION;
SELECT * FROM users WHERE id > 5 AND id < 8 FOR UPDATE;

-- 트랜잭션 2에서 id 값이 6인 데이터를 삽입합니다.
START TRANSACTION;
INSERT INTO users (id, name) VALUES (6, 'Alice');

위 코드를 보면 5~8까지 갭락을 설정하였고 트랜잭션 2에서 갭락 범위에 있는 6을 추가를 하려고 한다.

이때 갭락에 의해 트랜잭션2는 대기 상태에 빠지게 된다.

 

Next-Key Locks

• 데이터의 일관성을 보장하기 위해 주로 InnoDB 스토리지 엔진에서 기본으로 사용한다.
• 주로 인덱스 검색에서 사용이 된다.
• 레코드, 갭락을 조합한 것으로 트랜잭션에서 인덱스를 검색할 때 해당 레코드에 대하여 다른 트랜잭션이 수정이나 삭제를 제한이 가능하다.
• REPEATABLE READ 격리 수준에서 팬텀 리드를 방지하기 위한 잠금이다.

아래와 같은 테이블이 있다고 가정하자

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

트랜잭션A가 명령어를 입력을 하였다.

SELECT * FROM users WHERE id = 5 FOR UPDATE;

이때 해당 레코드는 베타적 락이 설정이 되는데 그러면 다른 트랜잭션N이 수정이나 삭제를 하려고 하면 대기 상태로 변경된다. 이것은 데이터의 일관성을 유지 시켜준다.

Next-Key Lock이 발생하는 예시 코드

-- 트랜잭션 1에서 id 값이 5인 레코드를 락(lock)을 설정합니다.
START TRANSACTION;
SELECT * FROM users WHERE id = 5 FOR UPDATE;

-- 트랜잭션 2에서 id 값이 6인 레코드를 삽입합니다.
START TRANSACTION;
INSERT INTO users (id, name) VALUES (6, 'Bob');

• ID가 5인 레코드에 락을 설정하면 해당 레코드에 대한 Next-Key Lock이 발생한다.
• 트랜잭션2가 id가 6인 레코드를 삽입하면 인덱스 갭에 대한 Next-Key Lock이 발생한다.
• 즉 ID가 5의 양쪽 인덱스에 락이 걸리게 된다. → 4,6에 락이 걸린다.

 

 

AUTO-INC Locks

• AUTO_INCREMENT 칼림이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
• InnoDB 는 내부적으로 AUTO-INC 락이라고 하는 테이블 수준의 잠금을 사용한다.
• 트랜잭션과 관계 없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 해제된다.