[DB] Lock 기반으로 트랜잭션 격리수준 이해하기

트랜잭션의 특징 : ACID

- Atomicity(원자성) : All Or Nothing

- Consistency(일관성) : 일관성 있는 DB 상태를 유지해야 함(정해진 DB컬럼에 대한 일관성, 제약조건에 대한 일관성 등)

- Isolation(격리성) : 트랜잭션 간 얼마나 영향을 주고받냐를 설명함(4가지 격리수준 참고)

- Durability(지속성) : 트랜잭션 결과는 항상 DB에 기록되어야 하며 문제가 발생하더라도 복구할 수 있어야 함

 

트랜잭션은 위 4가지 성질을 모두 만족해야 한다. 하지만 Isolation의 경우 완벽한 격리를 하게 될 경우 트랜잭션들 간에 완전히 순차적으로 실행되어야 하기 때문에 성능상 이슈가 발생할 수 있다. 따라서 Isolation의 경우는 4가지 수준으로 구분하며 "얼마나 serialize 한 데이터를 받아올 것이냐에 따라 트랜잭션마다 설정할 수 있다" 만약 대략적인 데이터만 빠르게 가져오고 싶다면 가장 낮은 수준의 Isolation level을 사용하는 것만으로 충분할 것이다.

 

 

---

 

cf. DB세션 기본개념

DB 서버에 커넥션을 맺을 때 DB 내부적으로는 '세션'을 만든다.(커넥션의 개수만큼 세션이 생성됨) 그리고 생성된 커넥션을 통한 모든 쿼리는 세션을 통해서 실행되는 구조이다. 즉, 커넥션을 통해 쿼리가 전달되고, 전달된 후에는 세션을 통해 쿼리가 처리되는 것이다. 세션은 트랜잭션을 시작하고 커밋/롤백을 통해 트랜잭션을 종료한다. 또한 이후의 새로운 트랜잭션을 시작할 수도 있다. 커넥션이 닫힌다면 세션 또한 종료된다. 

 

 

트랜잭션 동작

트랜잭션 커밋을 하기 전까지는 데이터가 실제로 저장되는 게 아니라 임시로 저장된다. 따라서 해당 트랜잭션이 커밋하기 전까지는 다른 트랜잭션에서 변경된 데이터를 볼 수 없다. 단, Isolation level을 가장 낮은 수준(Read Uncommitted)으로 설정할 경우에는 예외. 

 

* 중요: 트랜잭션이 커밋하기 전에는 실제 DB에 반영되지 않기 때문에 A 트랜잭션에서 데이터를 변경했더라도 B 트랜잭션에서는 해당 데이터가 변경 전의 값으로 보일 것이다. 그러다가 A 트랜잭션이 커밋을 해야지만 다른 트랜잭션에서도 변경 완료된 데이터가 보인다. 하지만 InnoDB의 경우에는 낙관적 메커니즘을 가지고 있기 때문에 커밋하지 않는 데이터도 DB에 적용한다.

 

InnoDB uses an optimistic mechanism for commits, so that changes can be written to the data files before the commit actually occurs. This technique makes the commit itself faster, with the tradeoff that more work is required in case of a rollback.

 

-> 커밋되지 않는 데이터를 미리 반영하는 것은 tradeoff가 있음. 롤백이 발생하지 않는다면 빠른 반영을 기대할 수 있지만 롤백이 발생하면 이전 상태로 복구해야 함.

 

트랜잭션을 사용하기 위해서는 커넥션이 필요하고 트랜잭션 도중에는 해당 커넥션이 계속 유지되어야 한다. 그래야 같은 세션을 유지할 수 있기 때문이다. 세션이 트랜잭션을 시작하기 때문에 서로 다른 커넥션은 서로 다른 트랜잭션을 다룰 수밖에 없다. 애플리케이션 단에서 같은 커넥션을 유지하기 위한 방법 중, 파라미터를 활용하는 방법이 있다.

 

cf. Auto Commit

데이터베이스 설정 중에 자동커밋(Auto Commit)이 있는데, (set autocommit true) 해당 옵션을 true로 할 경우 쿼리 한 줄 단위로 트랜잭션이 발생한다. 따라서 논리적인 쿼리 묶음을 트랜잭션으로 묶어 사용하기 위해선 Auto Commit을 false로 두고 사용해야 한다. 보통 DB마다 트랜잭션 타임아웃 설정이 있는데 트랜잭션이 해당 시간을 넘기면 자동으로 롤백된다.(설정 사항) 트랜잭션을 직접 시작하기 위해서는 set autocommit false 명령어로 시작하며 트랜잭션 종료 시에는 commit 명령어를 수행한다.

 

 

---

 

Lock

트랜잭션을 시작할 때 변경 대상이 되는 row에 lock을 설정한다. lock을 획득해야지만 데이터를 변경할 수 있다. 이미 변경 대상이 되는 row에 lock이 잡혀있다면 lock이 풀릴 때까지 대기를 하는데, 얼마나 기다릴지에 대한 '락 타임아웃'을 설정을 할 수 있으며 '락 타임아웃' 기간이 지나면 오류가 발생한다. 트랜잭션이 커밋이나 롤백을 할 때 lock을 반납하고 변경된 데이터는 반영된다.

 

- lock을 획득하는 주체는 '세션'이다.

- 단순 데이터 조회(select 쿼리)시에는 lock을 획득할 필요가 없다. 하지만 커밋되지 않는 데이터를 DB에 반영하는 InnoDB 엔진의 경우, 조회시에도 lock을 걸지 않으면 Dirty Read가 발생할 수 있다.

- 만약 조회시에도 lock을 획득하고 싶다면 'select ~~~ for update' 구문을 사용하면 된다. 예를 들어 특정 트랜잭션 안에서 중요한 값을 조회해서 사용한다고 가정할 때 해당 값은 트랜잭션 도중에 변경이 발생하면 안 된다. 따라서 이 경우에는 조회이지만 Lock을 획득하여 트랜잭션을 처리한다.

- 트랜잭션을 시작할 때 lock을 잡으며 해제하는 시점은 벤더사, Isolation Level 등에 따라 다르다. (아래 참고)

 

 

---

Lock의 종류

벤더사마다 다르다. 통용적으로 사용되는 X lock, S lock, 그리고 InnoDB 엔진에서 사용하는 Lock에 대해 알아본다.

 

1. Exclusive Lock(X Lock, LX), 배타잠금

- X lock은 read/write에 대한 lock이다. 따라서 X lock을 잡으면 read/write를 모두 할 수 있다.

- 하나의 row에 하나의 X lock만이 걸릴 수 있다.

- X lock이 걸린 상태에서 다른 트랜잭션은 Lock을 걸 수 없다. (S lock이든 X lock이든)

 

 

2. Shared Lock(S lock, LS), 공유잠금

- 쿼리 실행 시 lock in share mode(또는 for share)를 명시하면 S lock 기반이다.

- S lock을 잡으면 다른 트랜잭션에서도 S lock을 잡을 수 있으며 데이터 읽기도 가능하다. 하지만 write 할 수 없다.

- S lock이 걸려있어도 다른 트랜잭션이 해당 row에 S lock을 걸 수 있다. (X lock은 불가능, 다른 트랜잭션과 동시에 lock을 잡을 수 있다는 것은 다른 트랜잭션도 단순 조회를 할 경우이기 때문)

 

SELECT name FROM user WHERE id = 1 LOCK IN SHARE MODE; # Mysql v5.7
SELECT name FROM user WHERE id = 1 FOR SHARE; # Mysql v8.0

 

 

* S lock과 X lock은 거의 대부분의 엔진에서 제공하는 Lock이다.

 

 

		current state of locking of date items
		unlocked	shared	exclusive
lock mode of request	unlocked		가능	가능
	shared	가능	가능	불가능
	exclusive	가능	불가능	불가능

 

 

참고) Table Lock

테이블 락은 테이블의 모든 row에 대해 lock을 설정한다. 보통은 DDL(create, alter, drop 등)의 경우에 사용된다.

 

 

InnoDB에서 제공하는 특수한 Lock

1.  Record Lock

InnoDB에서는 다른 RDBMS 엔진과 달리 인덱스 레코드 lock을 기반으로 한다. 인덱스 레코드 lock은 테이블의 row에 lock을 설정하는 것이 아닌, 인덱스 레코드(row)에 lock을 설정하는 것을 의미한다. 테이블에 인덱스가 설정되어 있지 않더라도 Record Lock을 사용하면 InnoDB상에서 숨겨져 있는 clustered index(PK 등)를 사용하여 Lock을 설정한다.

 

테이블의 row-level lock과 마찬가지로 S lock과 X lock이 있다. 

 

예시)

Transaction A
SELECT age FROM People WHERE age = 10 FOR UPDATE;

--- 

Transaction B
DELETE FROM People WHERE age = 10;

위와 같이 트랜잭션 A가 먼저 실행된다고 보면, age 컬럼이 10인 인덱스에 X lock이 걸린다. 이때 트랜잭션 B가 실행되면 age가 10인 인덱스에 X lock을 걸려고 시도하지만 이미 X lock이 걸려있는 상태이므로 트랜잭션 A가 커밋 또는 롤백되기 까지를 기다린다. 이렇듯 Record Lock은 인덱스를 기반으로 X lock, S lock을 수행한다.

 

Record Lock을 활용하면 Index Tree를 타려는 다른 쿼리의 접근을 막을 수 있다. 또한 인덱스(B Tree)의 기본 특징인 정렬된 상태로 저장한다는 점에서 lock을 걸 레코드들을 더 빠르게 찾을 수 있다는 장점이 있다. 예를 들어, where price < 100 인 조건에 대해 기본 S/X lock을 건다고 가정하고 price 컬럼에 인덱스가 설정되어 있지 않다면 lock의 대상이되는 레코드를 찾는데에도 어느정도 시간이 소요될 것이다. 하지만 만약 price에 인덱스가 설정되어 있고 Record Lock을 사용한다면 price 기준으로 정렬되어 인덱스에 저장되기 때문에 lock 대상의 레코드를 빠르게 찾을 수 있다. 하지만 이는 아래 'Gap Lock'으로 대체된다.

 

 

2. Gap Lock

- Gap이란 '인덱스 중에서' DB에 실제 레코드가 없는 부분을 의미한다.
- 하지만 비어있는 부분 뿐만 아니라 단일 인덱스 값, 여러 인덱스 값에 걸쳐 설정될 수도 있다.
- Gap Lock을 활용하면 조건에 해당하는 새로운 row가 추가되는 것을 방지할 수 있다.
- 단일, 다중 인덱스, 또는 인덱스가 없을 경우에도 사용할 수 있다.
- 만약 where id = 1 처럼 단일 인덱스 값에 대해서도 Gap Lock을 설정할 순 있지만 대체로 이러한 경우에는 Record Lock이 사용된다. 하지만 이 경우에도 id 컬럼에 인덱스가 2개 이상 설정되어 있거나 또는 인덱스가 전혀 설정되어 있지 않다면 Gap Lock이 사용된다.
- Gap Lock은 대체로 트랜잭션 고립레벨 중 Repeatable Read 이상의 수준에서만 사용된다.

 

 

예시 )
특정 쿼리의 where 절이 'where price between 10000 and 20000' 라고 가정하면 price가 10000부터 20000까지 Gap Lock이 걸린다. 만약 price가 15000인 데이터가 없다고 가정하더라도 다른 트랜잭션은 price가 15000인 데이터를 추가할 수 없다. 이는 해당 쿼리가 단순 Select 문일 경우에도 반영된다. 따라서 Gap Lock은 해당 Gap에 접근하려는 다른 트랜잭션의 접근을 방지한다. 

 

 

cf. MySQL 공식문서 참고글

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

 

-> Gap Lock은 오직 갭 사이에 새로운 데이터가 추가되는 것을 방지하기 위해 존재하는 lock이다. Gap Lock이 S Lock으로 구성되든 X Lock으로 구성되든 상관없고 이들은 서로 공존할 수 있다. 심지어 서로다른 트랜잭션에 의해서도 공존될 수 있다.

 

 

 

cf. Record Lock과 Gap Lock의 차이점

Record Lock은 이미 존재하는 row가 변경되지 않도록 보호하지만 Gap Lock은 조건에 해당하는 새로운 row가 추가되는 것을 방지한다. Gap Lock과 Record Lock을 활용해 Phantom Read를 방지할 수 있다.

 

cf. 그 외 lock

- Next Key Lock(Record Lock + Gap Lock)

- 테이블 lock

 

cf. 현재 실행 중인 lock 확인

select * from information_schema.INNODB_LOCKS

 

cf. 'select for update'와 Shared Lock의 차이

단순 select 문일 경우에는 S lock이 걸리지만 select ~~~ for update문을 사용하면 조회이지만 X lock이 걸린다. (?) 조회이지만 X lock을 거는 이유는 중요한 데이터를 다루는 도중에 다른 트랜잭션에서 읽기 조차 허용하지 않게 하기 위함이다.

 

 

 

 

---

 

 

Lock 기반으로 트랜잭션 Isolation Level 이해하기

 

1. Read Uncommitted

- "읽기 쿼리에 대해서는 S lock을 걸지 않으며(Non-Locking) 변경 쿼리때에만 X lock을 건다."

- 읽기 쿼리에 S lcok을 걸지 않으므로 다른 트랜잭션이 해당 데이터를 읽거나 변경할 수 있다.

- InnoDB의 경우 수정된 데이터가 커밋 전에 DB에 반영되기 때문에 한 번도 커밋되지 않은, 존재하지 않는 데이터를 읽어 들이는 Dirty Read가 발생할 수 있다.

- 하나의 트랜잭션 내에서 같은 데이터를 읽을 때마다 달라지는 Non-repeatable Read가 발생할 수 있다.

- Phantom Read도 발생할 수 있다.

 

cf. Phantom Read 

하나의 트랜잭션 안에서 첫 번째 쿼리와 두 번째 쿼리 수행 결과가 각각 다르게 발생하는 경우를 의미하는데, Non-repeatable Read와의 차이점은 Phantom Read의 경우에는 외부에서 동시에 실행 중인 트랜잭션의 'Insert 작업'으로 인해 발생하는 문제점을 말한다. 예를들어, 특정 범위 안의 price라는 정수형 컬럼 값을 더하는 연산을 하는 쿼리가 있을 때 이 쿼리를 연속 2번 수행한다고 가정하면, 첫번째 실행 후 두번째 실행 전에 다른 트랜잭션이 해당 범위 사이에 다른 데이터를 삽입한다면 똑같은 쿼리이더라도 결과는 달라질 수 있다. 이러한 Phantom Read는 외부 트랜잭션의 'insert 쿼리'로 인해 발생하는 문제이며 접근 대상이 되는 데이터 후보 범위에 lock을 걸고 유지하는 방식으로 해결할 수 있다.

 

 

2. Read Committed

- 개별 데이터를 읽는 동안에 S lock을 걸지만 읽은 후에는 바로 lock을 해제한다. 즉, 접근할 데이터 집합에 대해 Lock을 거는 것이 아닌, 개별 데이터에 대해 lock을 걸기 때문에 Non-repeatable Read나 Phantom Read가 발생할 수 있다. (Gap Lock을 사용하지 않기 때문에 Phantom Read가 발생할 수 있다.)

- update 쿼리에 대해서는 X lock을 건다.

- 읽기시에도 S lock을 걸기 때문에 Dirty Read가 발생하지 않는다. 

- Dirty Read는 발생하지 않지만, 개별 데이터에 Lock을 걸고 바로 해제하기 때문에 Non-repeatable Read는 발생할 수 있다. 예를 들어, row1에 S lock 설정 후 읽고 S lock을 바로 해제한 뒤, 다른 트랜잭션이 row1을 수정했을 때 다시 row1을 읽는다면 Non-repeatable Read가 발생한 것이다.

 

 

 

3. Repeatable Read (InnoDB의 default)

- Repeatable Read와 Read Committed의 차이점은 Repeatable Read를 설정한 트랜잭션은 다른 트랜잭션이 데이터를 수정하는 것은 금지하지만 새로운 레코드를 추가하는 것은 허용하는 설정이다. 즉, Read Committed에서는 트랜잭션이 끝나지 않으면 전혀 접근이 되지 않는데 Repeatable Read에서는 트랜잭션이 돌고 있다 하더라도 새로운 레코드 추가는 가능한 것이다.

- 읽기 쿼리를 할 때 S lock을 걸고 트랜잭션 끝까지 유지한다. 따라서 Non-repeatable Read는 발생하지 않는다.

- 하지만 접근할 데이터 집합에 대해 lock을 거는 것이 아니므로(여전히 개별 데이터에 대한 Lock인 상황) Phantom Read는 발생 가능하다.

- 변경 시에는 X lock을 건다.

- MySQL 기준으로 (InnoDB를 사용할 경우) Gap Lock과 Record Lock을 동시에 사용(Next-Key Lock)된다. 즉, 접근 대상이 되는 데이터 후보군의 범위에 대해 Lock을 걸어 다른 트랜잭션이 접근하지 못하도록 한다. 따라서 Gap Lock과 Record Lock을 활용하면 Phantom Read를 방지할 수 있긴 하다.

 

 

4. Serializable

- 인덱스에 S lock을 걸어 다른 트랜잭션이 변경할 수 없다. (X lock을 걸 수 없다.)

- 모든 트랜잭션이 순차적으로 처리되는 결과를 보일 수 있다. 대신 성능 이슈는 존재한다.

- 따라서 매우 중요한 데이터를 다루는 게 아니라면 사용하지 않는 것이 좋다. (사실 트랜잭션의 Serializable 특성을 어느 정도 포기하기 위해 등장한 것이 Isolation Level인 것이기 때문)

- Dirty Read / Non-repeatable Read / Phantom Read 모두 방지할 수 있다.

 

 

 

* Non-repeatable Read / Phantom Read / Dirty Read 상황을 고려해 어디까지 허용을 할 것인지를 판단하고 Isolation Level을 설정해야 한다.

 

 

위 네가지 격리 수준에서 발생할 수 있는 문제를 정리하며 다음과 같다.

https://alxibra.medium.com/isolation-level-in-rails-847edc9e347d

 

cf. Read Committed vs Repeatable Read

1) 차이점: lock을 푸는 시점

Repeatable Read는 row에 lock을 잡으면 트랜잭션이 끝날 때까지 유지한다. 그러나 Read Committed는 lock을 잡은 뒤 연산이 끝나면 바로 풀어버린다. 따라서 Repeatable Read의 경우 한 번 잡은 lock을 풀지 않기 때문에 repeatable read 문제는 발생하지 않는다.

2) 공통점: lock의 범위 -> Phantom Read

두 격리수준 모두 개별 데이터(row)에 lock을 걸기 때문에 특정 쿼리가 접근하는 데이터 범위 안에 다른 트랜잭션이 새로운 데이터를 '삽입'할 수 있다. 따라서 Phantom Read는 발생가능하다. 참고로 InnoDB를 사용할 경우, Repeatable Read는 Gap Lock과 Record Lock을 동시에 사용하여는 Next-Key Lock을 활용해서 접근 대상이 되는 데이터 '후보군의 범위'에 대해 Lock을 걸어 Phantom Read를 방지할 순 있다고 한다.

 

 

 

 

Reference

- MySQL Docs, https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

- https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/