ACID
ACID란 데이터베이스의 트랜잭션이 안전하게 수행되도록 보장하는 특징들의 약어이다.
- 원자성(Atomicity)
- 트랜잭션에 관련된 작업들은 COMMIT되거나 ROLLBACK되거나 둘 중 하나이다. 중간까지의 작업들만 완료되는 경우는 없다.
- 일관성(Consistency)
- 트랜잭션이 완료된 이후에도 데이터베이스의 제약이나 규칙을 만족하는 상태여야한다. 만약 트랜잭션을 통해 계좌에서 돈을 인출한 이후 잔고가 마이너스가 된다면 해당 데이터베이스의 일관성을 해치게 된다.
- 독립성(Isolation)
- 여러 트랜잭션이 동시에 수행될 때 서로 영향을 주어서는 안된다. 따라서 일정한 격리 수준으로 각 트랜잭션을 격리해야 한다.
- 지속성(Durability)
- COMMIT된 트랜잭션은 반영되어야하고 그 결과가 기록되어야 한다. 시스템에서 문제가 발생하면 로그를 통해 성공한 트랜잭션을 복구할 수 있다.
Isolation을 만족시키기 위하여 트랜잭션에 어떠한 형태의 고립을 적용시키느냐에 따라서 성능과 독립 수준이 달라진다.
예를 들어 서로에게 영향을 주지 못하도록 각 트랜잭션을 순차적으로 처리하게 한다면 완벽한 독립성이 지켜지지만, 동시성이 떨어져 성능이 매우 낮아지게 될 것이다.
따라서 데이터베이스는 Isolation Level이라는 옵션을 선택할 수 있게 하여 독립성(격리성)과 동시성 사이에서 저울질을 가능하게 해준다.
Isolation Level
Isolation Level은 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE이 존재하며 갈수록 동시성(성능)이 낮아지지만 독립성과 정합성이 높아질 수 있다.
각 격리수준별로 발생할 수 있는 문제는 다음과 같으며 아래에서 설명하겠다.
READ UNCOMMITTED
COMMIT되지 않은 읽기로 ROLLBACK이 되더라도 이미 다른 트랜잭션에서 읽을 수 있기 때문에 데이터의 정합성이 파괴된다.
A 트랜잭션과 B 트랜잭션이 동시에 수정된다고 가정해보자.
- A 트랜잭션에서 데이터를 수정하고 COMMIT 전 B 트랜잭션이 데이터를 읽었다.
- A 트랜잭션은 오류가 발생하여 ROLLBACK 했지만 B 트랜잭션은 그대로 수정된 데이터를 읽어온다.
- B 트랜잭션이 읽은 데이터와 현재 데이터베이스의 데이터가 다르다.
따라서 잘 쓰이지 않는 격리 수준이다.
COMMIT 전에 변경된 내용을 다른 트랜잭션에서 읽을 수 있는 현상을 DIRTY READ라 한다.
READ COMMITTED
COMMIT 전까지 데이터베이스가 변경되면 Undo 영역에 이전 데이터를 백업하고 다른 트랜잭션에서 읽기 수행 시 Undo 영역으로 백업된 데이터를 가져온다.
ROLLBACK 되더라도 트랜잭션 발생 전의 데이터를 읽기 때문에 DIRTY READ는 해결된다.
- A 트랜잭션이 데이터를 수정하여 이전 데이터가 Undo 영역으로 백업된다.
- B 트랜잭션은 A 트랜잭션이 아직 COMMIT되지 않았기 때문에 Undo 영역에서 이전 데이터를 가져온다.
- 이제 A 트랜잭션의 성공 여부와 관계 없이 동일한 데이터를 가져온다.
하지만 다음과 같은 문제가 발생할 수 있다.
- A 트랜잭션이 COMMIT되기 전 B 트랜잭션에서 이전 데이터를 가져온다.
- A 트랜잭션 COMMIT 후 B 트랜잭션이 다시 변경된 데이터를 가져온다.
- B 트랜잭션이 읽어온 두 데이터가 다르다.
따라서 SELECT 시 항상 같은 결과를 보장해야 한다는 NON-REPEATABLE READ가 발생한다.
REPEATABLE READ
데이터에 트랜잭션 번호를 부여하고, 트랜잭션 번호가 더 낮은 데이터만 테이블에서 읽을 수 있도록 하여 NON-REPEATABLE READ를 해결한다.
A, B 트랜잭션 상황에서 B가 먼저 시작되어 더 낮은 번호를 부여 받았다고 가정하자.
- 데이터는 이전에 수정되어 B 트랜잭션의 번호보다 낮기 때문에 테이블에서 직접 읽는다.
- 이후 A 트랜잭션이 데이터를 수정하고 커밋하여 Undo 영역에 데이터가 백업되고 데이터의 트랜잭션 번호는 A 트랜잭션의 번호로 변경된다. (Undo 영역의 트랜잭션 번호는 이전 그대로)
- B 트랜잭션은 다시 데이터를 읽어오는데, 데이터의 트랜잭션 번호가 자신보다 높기 때문에 읽어올 수 없어 Undo 영역에서 읽어온다.
- 따라서 읽어온 두 데이터는 동일하다.
만약 B 트랜잭션이 SELECT FOR UPDATE 쿼리를 사용한다면 문제가 발생할 수 있다.
위와 같은 상황에서 SELECT FOR UPDATE를 사용한 경우이다.
- B 트랜잭션이 먼저 잠금을 걸고 읽어온 후 A 트랜잭션이 실행된다.
- A 트랜잭션 COMMIT 후 B 트랜잭션이 SELECT FOR UPDATE 쿼리로 데이터 조회를 시도한다.
- 변경된 데이터의 트랜잭션 번호가 더 높기 때문에 Undo 영역에서 읽어야 하지만, Undo 영역은 잠금을 걸 수 없기 때문에 어쩔 수 없이 테이블에서 데이터를 읽어온다.
- 동일한 두 쿼리에서 하나는 변경 전 데이터를, 하나는 변경 후 데이터를 읽었다.
이러한 문제점을 PHANTOM READ라 한다.
특이하게, MySQL InnoDB은 REPEATABLE READ가 default이지만 PHANTOM READ가 발생하지 않는다.
이유는 다음과 같다.
InnoDB는 next key lock(record lock + gap lock)을 사용한다.
- 하나의 트랜잭션에서
SELECT ... WHERE pk > 20 AND pk < 30 FOR UPDATE라는 쿼리를 실행했다고 가정하자.- pk는 현재 23, 26이 존재하며 record lock에 걸린다.
- 트랜잭션이 COMMIT 될 때까지 비어있는 영역인 21 <= pk <= 22, 24 <= pk <= 25, 27 <= pk <= 29 는 gap lock에 걸린다.
- 따라서 SELECT가 발생한 영역의 데이터를 수정할 수 없어 PHANTOM READ가 발생하지 않는다.
SERIALIZABLE
읽기와 쓰기 모두 해당 레코드를 잠근다.
따라서 말그대로 동시성 없이 순차적으로 트랜잭션이 수행되기 때문에 잘 사용하지 않는 격리 수준이다.
MySQL에서 Isolation Level 확인, 변경
위와 같이 SELECT @@GLOBAL(or SESSION).transaction_isolation으로 확인 가능하다.
InnoDB의 경우 기본적으로 REPEATABLE_READ인 것을 확인할 수 있다.
Isolation Level 변경 방법은 다음과 같다.
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;