MySQL Lock과 트랜잭션 격리 수준 정리 | 데이터베이스 Lock & Isolation Level 이해

MySQL 기본적인 Locking & 격리 수준

MySQL의 기본적인 Lock

기본적으로 REPEATABLE READ 격리 수준을 사용한다.

MVCC (Multi-Version Concurrency Control)을 활용하여 데이터 일관성을 유지합니다.

트랜잭션이 진행되는 동안 동일한 데이터를 읽으면 변경된 데이터는 보이지 않습니다. (커밋된 새로운 트랜잭션의 데이터가 반영되지 않음)

1. SELECT 시 Read Lock 걸지 않음
   • select 쿼리는 잠금을 걸지 않음
   • MVCC를 사용해 트랜잭션이 시작된 시점의 데이터 스냅샷 반환
2. UPDATE / DELETE 시 Record Lock (행 단위)
   • 행에 대해 Record Rock을 획득합니다.
   • 해당 행이 다른 트랜잭션에서 수정되지 않도록 방지
3. GAP Lock (Phantom Read 방지)
   • REPEATABLE READ에서는 Phantom Read를 방지하기 위해 인덱스 간격에 대해 잠금을 설정합니다.
   • 예를 들어, WHERE age BETWEEN 20 AND 30 같은 범위 조회가 발생하면, 해당 범위에 새로운 값이 삽입되지 않도록 방지

---

Transaction

1. 작업의 완전성을 보장
2. 사용자의 작업셋을 모두 완벽하게 처리하거나
3. 처리하지 못하면 원 상태로 복구

ACID - Atomicity

1. 더 이상 쪼개질 수 없는 가장 작은 단위
   • 트랜잭션의 단위
2. 모두 성공하거나 모두 실패, (중간은 없음)

ACID - Consistency

1. 트랜잭션 전후로 데이터의 무결성이 유지되어야 함
   • 트랜잭션이 실행되면, 그 과정에서 데이터가 임시적으로 변경될 수 있지만 최종적으로 데이터의 정합성이 유지되어야 함
   • 데이터베이스는 제약 조건(UNIQUE, NOT NULL, FOREIGN KEY)을 통해 기본적인 일관성을 보장하지만, 비즈니스 로직 수준에서 발생하는 정합성 문제는 애플리케이션에서 추가적인 관리가 필요

Ex)
1. 마지막 한 개 남은 상품을 동시에 2명의 고객이 주문할 수 없도록
2. 카드 한도 초과가 되려고 하는데, 동시에 2가지 아이템을 주문할 수 없도록

ACID - Isolation

1. 동시에 발생하는 트랜잭션은 서로 독립적이어야 함
2. 독립되지 않으면 발생할 수 없음

 

ACID - Durability

1. Commit 된 트랜잭션은 손실되지 않는다.
   • 트랜잭션이 완료되면 그 변경 사항이 영구적으로 저장된다.

 

트랜잭션 지원하는 스토리지 엔진(InnoDB) vs 지원하지 않는 스토리지 엔진(MyISAM)

0. Create Table

CERATE TABLE no_transaction (id INT NOT NULL, PRIMARY KEY (id)) ENGINE = MyISAM;

CREATE TABLE transaction (id INT NOT NULL, PRIMARY KEY (id));

Insert table

Select table

아직까지 그냥 데이터를 입력하는 부분에서는 트랜잭션을 사용하는 스토리지 엔진과 아닌 것의 차이는 없다.

 

But, PK에 중복값을 입력하면 상황이 달라진다.

PK인 id 속성의 중복값을 입력하는 경우

 

Select table

👉 트랜잭션을 지원하는 경우 에러가 발생하는 쿼리는 커밋하지 않음.
하지만, 트랜잭션을 지원하지 않는 경우 에러가 발생하기 전인 (1), (2)의 데이터는 입력됨

✔️ 생기는 문제점
id 4,5의 레코드를 나중에 출력하고 1,2,3의 레코드만 사용하고 있을 경우 데이터가 제대로 들어갔다고 착각할 수 있음.

 

Transaction은 Undo log 사용

Undo log는 데이터베이스에서 트랜잭션의 원자성을 보장하고 데이터 일관성을 유지하는 데 사용됩니다.

 

Undo Log란

트랜잭션이 변경한 데이터를 원래 상태로 되돌릴 수 있도록 저장하는 로그입니다.

➡️ 원자성과 일관성 보장

 

Undo log 역할

1. 트랜잭션 롤백
   • 트랜잭션이 중단되거나 롤백되어야 할 때, Undo log를 사용하여 이전 상태로 데이터를 복원
2. 동시성 제어
   • 다중 사용자 환경에서 여러 트랜잭션이 동시에 데이터를 변경할 때, Undo log는 트랜잭션 간의 충돌을 방지합니다.
   • 트랜잭션을 수행하기 전에 Undo Log를 검사하여 다른 트랜잭션과의 충돌을 감지하고 처리
3. 데이터베이스 스냅샷 및 복구
   • Undo log에는 이전 상태의 데이터 변경 내역이 포함되어 있으므로 스냅샷을 생성하여 오류 시 데이터를 복원

Undo log 동작 과정

1. 트랜잭션 시작이 스냅샷에는 해당 트랜잭션이 시작되기 전의 데이터 상태가 포함됩니다.
2. Undo log에 현재 상태의 스냅샷을 저장합니다.
3. 데이터 변경변경된 데이터의 원래 값과 변경된 값을 Undo log에 저장합니다.
4. 트랜잭션 내에서 데이터가 변경될 때,
5. 커밋 or 롤백롤백이 필요한 경우, Undo log의 변경 내용을 사용하여 이전 상태로 데이터를 복원합니다.
6. 트랜잭션이 성공적으로 완료될 경우 커밋이 발생하여 Undo log의 해당 변경 내용이 데이터베이스에 반영됩니다.

데이터 입력 시, 변경된 값을 undo log에 저장

 

롤백이 필요한 경우, undo log로 데이터 복구

 

Database Lock

동시성 제어를 위해 사용되는 기술입니다.

여러 트랜잭션이 동시에 데이터를 수정할 때 데이터 무결성을 보장하는 역할을 합니다.

1. 하나의 데이터를 동시에 여러 명이 조작할 수 없도록 함
2. 동시성 보장

단순 조회인 경우에는 Lock 할 필요 없음.

Global Lock

• 데이터베이스 전체를 잠근다.
• READ만 가능하고, WRITE는 불가능

FLUSH TABLES WITH READ LOCK;

Table Lock

• 테이블을 잠그는 것
• 한 트랜잭션이 테이블을 수정하는 동안, 다른 트랜잭션은 해당 테이블을 수정할 수 없음
  • 성능 저하로 이어질 수 있음

LOCK TABLES users WRITE;

Named Lock

• 사용자가 지정한 이름으로 잠금을 설정
• 특정 로직에서 트랜잭션과 무관하게 임의로 Lock을 걸 수 있음
• 다양한 로직 처리에 사용 가능

SELECT GET_LOCK('my_custom_lock', 10);

• 한 번에 하나의 프로세스만 실행해야 하는 경우의 사용
• 특정 리소스를 보호해야 하는 경우

Metadata Lock

• 테이블의 구조(메타데이터)를 변경할 때 취득
  1. 테이블 이름
  2. 컬럼 이름이나 컬럼 정보 등을 수정할 때

ALTER TABLE users ADD COLUMN age INT;

Record Lock

• 특정 행 단위로 Lock을 거는 방식
  1. Row에 lock을 획득
  2. 정확히는 index에 lock을 잡음

UPDATE users SET balance = balance - 50000 WHERE user_id = 1;

 

 

Isolation level - 격리 수준

READ UNCOMMITTED

다른 트랜잭션이 데이터를 수정하는 동안 해당 데이터를 읽을 수 있으므로 일관성이 보장되지 않습니다. (Dirty Read)

A 트랜잭션은 아직 커밋되지 않은 다른 B 트랜잭션의 변경 사항을 볼 수 있습니다.

 

READ COMMITTED

A트랜잭션이 다른 B 트랜잭션에서 변경한 데이터를 볼 수 없게 합니다.

트랜잭션이 데이터를 읽을 때,

조회하려는 데이터는 이미 커밋된 트랜잭션에서만 읽을 수 있으며, 커밋되지 않은 트랜잭션에서는 읽을 수 없습니다.

1. COMMIT 된 record들만 READ할 수 있는 것
   • Commit 되지 않은 사항들을 읽을 수 있는 dirty read를 방지함
2. COMMIT된 record들만 WRITE 할 수 있는 것
   • Commit 되지 않은 사항들을 수정할 수 있는 dirty write를 방지함
   • 하지만, 여러 명이 동시에 같은 record를 수정하는 것을 방지할 수는 없음

- Row-level lock을 사용해서 dirty write를 예방함
- write를 시도할 때, lock을 획득해야 함
해당 lock은 transaction이 commit; / abort; 될 때까지 가지고 있음. 다른 transaction이 해당 row를 수정하고자 할 때 해당 lock을 기다려야 한다.
하지만, lock을 가지고 있는 transaction이 오래 걸리면 client가 오래 기다려야 하는 문제가 존재한다.

 

NON-REPEATABLE READ (read skew - 읽기 왜곡)

하나의 트랜잭션 작업이 같은 데이터를 2 번 조회했을 때, 그 사이에 다른 트랜잭션이 데이터를 변경하면 읽어오는 값이 달라지는 현상

1. READ가 repeatable 하지 않다.
2. READ COMMITTED의 한계

 

발생 조건

• READ COMMITTED 격리 수준에서는 트랜잭션이 수행되는 동안 커밋된 최신 데이터를 읽어 문제가 발생할 수 있다.
• 트랜잭션이 진행 중이라도 다른 트랜잭션에서 해당 데이터를 변경하여 커밋하면, 이후 조회 시 달라진 데이터가 조회된다.

 

➡️ Data Consistency이 깨진다

NON-REPEATABLE READ 고려 사항
1. backup 할 때
- backup하는 중에 데이터가 계속 변경된다면?
- backup 중에 변경된 데이터가 rollback 된다면?

2. Analytic queries and integrity checks
- 데이터 분석할 때 데이터를 불러오는 데 일부는 수정된 데이터고, 일부는 기존 데이터라면?
- read 하는 중에 에러가 발생해서 갑자기 데이터가 변경된다면?

 

 

REPEATABLE READ

동일한 트랜잭션 내에서 동일한 데이터를 읽을 때 일관성을 보장합니다.

즉, 트랜잭션 내에서 동일한 쿼리를 실행하더라도 결과는 일관성을 유지합니다.

 

트랜잭션이 시작될 때 읽은 데이터는 해당 트랜잭션이 종료될 때까지 동일한 결과를 유지합니다.

 

Phantom Read 여전히 발생

• 하나의 트랜잭션에서 일정 범위의 레코드를 2번 이상 읽을 때, 똑같은 쿼리임에도 첫 번째 쿼리에서 없던 레코드가 두 번째 쿼리에 나타나는 현상
  • REPEATABLE READ에서는 MVCC(Multi-Version Concurrency Control)를 사용하여 “행 단위”의 스냅샷을 유지
  • 하지만 “새로운 행(INSERT)“은 기존 스냅샷에 존재하지 않으므로, 스냅샷을 통해 해결할 수 없음!
  • 즉, 스냅샷이 기존 데이터의 UPDATE/DELETE에는 효과적이지만, 새로운 데이터의 삽입(INSERT)은 막을 수 없음

 

Snapshot isolation 개념 정리

1. Snapshot 생성
   • transaction이 발생하는 시점에 snapshot 저장
   • 이후 트랜잭션이 종료될 때까지 해당 스냅샷을 기준으로 읽기(Read) 수행
   • 다른 트랜잭션의 변경 내용(Commit되지 않은 데이터)은 보이지 않음
2. MVCC (Multi Version Concurrency Control)
   • 각 트랜잭션마다 ID(version)을 부여
   • 자신보다 낮은 ID의 데이터만 읽을 수 있도록 제한
   • 불필요한 오래된 스냅샷은 자동 삭제하여 관리

 

Write lock을 사용해서 dirty write를 방지

1. 쓰기(Write) 작업 시 Lock 적용
   • 트랜잭션이 데이터를 수정할 때 Write Lock을 획득하여 다른 트랜잭션이 동시에 수정하지 못하도록 차단
   • Dirty Write(한 트랜잭션의 변경을 다른 트랜잭션이 덮어쓰는 문제) 방지
2. 읽기(Read) 작업은 스냅샷 기반으로 수행 (Lock 필요 없음)
   • Read 작업은 트랜잭션이 시작될 때 만든 스냅샷을 사용하여 수행
   • 따라서 읽기(Read)는 쓰기(Write)를 막지 않음
3. 쓰기(Write)는 읽기(Read)를 막지 않음
   • Read는 스냅샷에서 데이터를 가져오므로, Write가 진행 중이더라도 Read 작업이 가능
   • Write는 다른 Write 작업과 충돌할 경우에만 Lock

 

Write Skew

• 서로 다른 트랜잭션이 같은 조건을 만족하는 데이터를 조회 후, 일부만 변경하는 경우 Write Skew 문제가 발생한다.
• 여러 트랜잭션이 동시에 동일한 데이터를 수정할 때 데이터베이스의 무결성을 위협하는 병행 제어 문제이다.
  • 두 개 이상의 트랜잭션이 서로 다른 데이터 항목을 수정하거나 동일한 데이터 항목을 수정할 때 발생

ex) 병원 근무 교대 시스템
- 두 명의 의사(A, B)가 있고, 최소 한 명의 의사가 근무해야 하는 규칙
- 두 의사가 동시에 근무 취소를 시도할 경우 문제 발생

 

REPEATABLE READ 레벨에서는 Write Skew 문제가 발생할 수 있다. Write Skew 문제는 다중 트랜잭션에서 동시에 데이터를 변경할 때 발생하며, 보다 엄격한 고립성 수준(예: SERIALIZABLE)을 사용해야 한다.

 

SERIALIZABLE 수준에서는 Write Skew 문제를 방지하려고 데이터베이스 시스템이 모든 트랜잭션을 직렬화하여 동시에 동일한 데이터를 변경하는 경우를 방지합니다.

 

SERIALIZABLE

• 하나의 트랜잭션에서 lock을 가지고 있는 레코드에 다른 트랜잭션에서 접근할 수 없음
• 무조건 lock을 획득해야 함
• 다른 트랜잭션에서 변경하는 데이터를 읽을 수 없으며, 팬텀 리드 또는 데이터 손상을 방지하기 위해 가장 높은 고립성 수준을 제공합니다.