![[MySQL] 트랜잭션 격리수준(isolation level)과 이상현상 (with 테스트 코드)](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2FME52v%2FbtsKYfksHvd%2FHmKsw3KxT1yVX1kAqFqg60%2Fimg.png)
기억에 오래남고 이해하기 쉽게 현재 조직의 웨딩 도메인의 적립금을 예시로 간단한 엔터티 설계와 더불어 테스트 코드를 작성하여 각 격리수준과 이에 따른 이상현상을 정리해보았다. 개념들은 MySQL의 공식문서를 활용하여 정리하였고, AUTO_COMMIT은 FALSE를 가정하고 예제들을 작성하였다.
(예제에 필요한 기본적인 엔터티와 데이터 세팅은 아래를 참조)
CREATE TABLE icash (
no INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_no INT UNSIGNED UNIQUE NOT NULL,
icash INT UNSIGNED DEFAULT 0 NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
)
CREATE TABLE icash_transaction (
no INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
icash_no INT UNSIGNED NOT NULL,
amount INT UNSIGNED NOT NULL,
type ENUM('GRANT', 'USE', 'REFUND') NOT NULL,
referenced_transaction_no INT NULL COMMENT '환불 시 트랜잭션 번호',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
INDEX idx_icash_no (icash_no),
INDEX idx_type (type)
)
CREATE TABLE user (
no INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
id VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)
트랜잭션 격리 수준(Isloation Level)
트랜잭션의 격리(ACID의 I)는 여러 트랜잭션이 동시에 데이터를 변경하는 등의 쿼리를 수행할 때 다른 트랜잭션의 작업이 끼어들지 못하도록 보장하며, Lock을 통해 다른 트랜잭션이 접근하지 못하도록 격리할 수 있지만, 잘못 사용하게 되면 교착 상태인 데드락(DeadLock)에 빠질 수 있다.
트랜잭션의 격리 수준은 트랜잭션의 격리를 어디까지 허용할 것인지 설정하는 것이다. 설정한 격리 수준에 따라 여러 트랜잭션이 동시에 처리될 때, 다른 트랜잭션에서 데이터를 변경하거나 조회하는 데이터를 볼 수 있게 허용할 수 있다.
- SERIALIZABLE
- REPEATABLE READ
- READ COMMITTED
- READ UNCOMMITTED
1. 우리는 트랜잭션을 관리해야하기 때문에 아래의 예제들에서는 AUTO_COMMIT이 FALSE인 상황만을 다룬다.
2. InnoDB의 기본값은 REPEATABLE_READ이다.
3. 격리 수준 별 이상 현상은 아래 격리 수준이 위의 격리 수준의 이상 현상을 포함하고 있다.
SERIALIZABLE
SERIALIZABLE은 가장 엄격한 격리수준으로, 트랜잭션을 직렬화된 순서와 동일하도록 보장한다. REPEATABLE READ와 유사하지만, 모든 SELECT문을 SELECT ... FOR SHARE로 간주한다. 다시 말해 모든 조회에도 넥스트 키 락(Next-Key Lock)이 읽기 잠금(Locking Reads)으로 걸린다는 의미이고 이는 곧 조회 조건에 따라 인덱스 레코드와, 레코드 간의 간격(GAP)에 대해 읽기 잠금을 설정한다는 뜻이다.
넥스트 키 락(Next-Key Lock)
조건에 일치하는 인덱스 레코드에 락을 설정하며, 해당 레코드와 연관된 레코드 간의 간격(GAP)에도 잠금을 걸어 다른 트랜잭션이 GAP 내에서 새로운 데이터를 삽입하거나 수정하지 못하도록 방지한다.
SELECT ... FOR SHARE
읽은 행에 공유 모드 잠금(SHARED LOCK)을 설정한다. 다른 트랜잭션이 행을 읽을 수는 있지만, 커밋될 때 까지 변경할 수 없다. 커밋하지 않은 다른 트랜잭션에 의해 레코드가 변경된 경우, 쿼리는 트랜잭션이 끝날 때 까지 기다렸다가 최신 값을 조회한다.
읽기 잠금(Locking Reads)
InnoDB 테이블에 대해 잠금 작업을 수행하는 SELECT문으로, 트랜잭션의 격리 수준에 따라 데드락이 발생할 가능성이 있다.
MySQL의 락 매커니즘
- MySQL에서는 테이블의 레코드가 아닌, 인덱스의 레코드를 잠근다.
- 락이 걸리는 인덱스는 클러스터 인덱스 및 논클러스터 인덱스를 모두 포함한다.
- 만약 PK가 없는 테이블이라면 내부적으로 자동 생성되는 PK를 이용하여 설정한다.
- 테이블에 적절한 인덱스가 없다면, 풀 스캔을 통해 참조되는 모든 레코드에 락을 걸 수 있기 때문에 적절한 인덱스를 설정하여 성능 저하를 고려하여 적절한 인덱스를 설정해야 한다.
이를 통해 다른 트랜잭션에서 해당 레코드나 갭에 대해 절대 쓰기 작업을 할 수 없다. 동시 접근을 차단하여 부정합 문제를 절대 발생시키지 않는다. 가장 안전함과 동시에 가장 성능이 떨어져 데드락에 빠지기 쉽다.
1. 교착 상태(DEADLOCK)
테스트 코드를 통해 SERIALIZABLE을 구현해보고, 데드락을 발생시켜보았다.
it('SERIALIZABLE:: DEAD LOCK', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
let deadLock = false;
try {
// 트랜잭션 A 시작
await runnerA.startTransaction('SERIALIZABLE');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 no = 1에 Lock
await repoA.findOne({ where: { no: 1 } });
// 트랜잭션 B 시작
await runnerB.startTransaction('SERIALIZABLE');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 트랜잭션 B에서 no = 2에 Lock
await repoB.findOne({ where: { no: 2 } });
// 서로 다른 트랜잭션에서 락을 건 레코드에 쓰기 작업 요청
await Promise.all([
repoA.update({ no: 2 }, { icash: 3000 }),
repoB.update({ no: 1 }, { icash: 2000 })
]);
} catch (e: any) {
console.error(e);
if (e.code == 'ER_LOCK_DEADLOCK') {
deadLock = true;
}
} finally {
// 트랜잭션 정리
if (runnerA.isTransactionActive) await runnerA.rollbackTransaction();
if (runnerB.isTransactionActive) await runnerB.rollbackTransaction();
}
// 데드락 발생 여부 검증
expect(deadLock).toBe(true);
}, 10000);
1. 조회를 통한 읽기 잠금 발생
await runnerA.startTransaction('SERIALIZABLE');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 no = 1에 Lock
await repoA.findOne({ where: { no: 1 } });
// 트랜잭션 B 시작
await runnerB.startTransaction('SERIALIZABLE');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 트랜잭션 B에서 no = 2에 Lock
await repoB.findOne({ where: { no: 2 } });
우선, A와 B 트랜잭션에서, 각각 다른 레코드를 조회한다.
위에서 언급한 것 처럼 SERIALIZABLE에서는 단순한 SELECT문도 SELECT ... FOR SHARE로 간주된다.
때문에 트랜잭션 A가 no = 1에 대해 SELECT를 실행하면, 해당 레코드에 대해 읽기 잠금이 발생하며 동일하게 트랜잭션 B가 no = 2에 대해 읽기 잠금을 발생시킨다.
이 상황에서는, 두 트랜잭션이 서로 다른 레코드에 대해 잠금을 걸기 때문에, 충돌이 발생하지 않는다. 위의 그림처럼, 서로 다른 레코드에 락을 걸게 된다. 만약 같은 레코드를 조회했더라도, 읽기 잠금은 참조(읽기)가 가능하기 때문에 아무런 이상현상이 발생하지 않는다.
2. 쓰기(UPDATE) 발생
// 서로 다른 트랜잭션에서 락을 건 레코드에 쓰기 작업 요청
await Promise.all([
repoA.update({ no: 2 }, { icash: 3000 }),
repoB.update({ no: 1 }, { icash: 2000 })
]);
이제, 서로 다른 트랜잭션에서 락을 획득한 레코드에 대해 업데이트해보자.
트랜잭션 A는, B가 획득한 자원에 대해, 트랜잭션 B는 A가 획득한 자원에 대해 쓰기 작업을 시도한다.
각 트랜잭션들은 이미 하나의 락을 획득하였고, 다른 트랜잭션에서 할당된 자원에 대해 쓰기 작업을 시도하여 배타적 잠금을 요청하며 이전 락이 해제될 때 까지 기다리게 된다. 이로 인해 데드락이 발생하게 된다.
2. LOCK_WAIT_TIMEOUT
이번엔, 교착 상태는 아니지만 다른 상황을 만들어보았다.
it('SERIALIZABLE:: TIMEOUT', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// InnoDB의 DEFAULT LOCK WAIT TIMEOUT = 50이기 때문에
// 테스트를 위해 5초로 변경
await runnerB.manager.query('SET SESSION innodb_lock_wait_timeout = 5');
let timeout = false;
try {
// 트랜잭션 A 시작
await runnerA.startTransaction('SERIALIZABLE');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 no = 1에 Lock
await repoA.findOne({ where: { no: 1 } });
await runnerB.startTransaction('SERIALIZABLE');
const repoB = runnerB.manager.getRepository(ICashEntity);
await repoB.update({ no: 1 }, { icash: 2000 });
} catch (e: any) {
if (e.code == 'ER_LOCK_WAIT_TIMEOUT') {
timeout = true;
}
} finally {
// 트랜잭션 정리
if (runnerA.isTransactionActive) await runnerA.rollbackTransaction();
if (runnerB.isTransactionActive) await runnerB.rollbackTransaction();
}
// 타임아웃 발생 여부 검증
expect(timeout).toBe(true);
}, 10000);
// 트랜잭션 A 시작
await runnerA.startTransaction('SERIALIZABLE');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 no = 1에 Lock
await repoA.findOne({ where: { no: 1 } });
await runnerB.startTransaction('SERIALIZABLE');
const repoB = runnerB.manager.getRepository(ICashEntity);
await repoB.update({ no: 1 }, { icash: 2000 });
트랜잭션 A가 락을 획득한 레코드에 대해, B가 쓰기 작업을 시도하였다.
마찬가지로 트랜잭션 B는 트랜잭션 A가 커밋을 하기 전까지 기다리게 된다. 만약 설정된 LOCK_WAIT_TIME을 초과하게되면, LOCK_WAIT_TIMEOUT 에러가 발생하게 되고, 작업을 수행할 수 없다.
정리
SERIALIZABLE은 모든 SELECT에도 잠금을 발생시기 때문에 다른 트랜잭션에서 절대로 쓰기 작업을 수행할 수 없고 이전 트랜잭션에서 작업이 완료되기를 기다려야만 한다. 가장 안전할 것처럼 보이나, 작업이 오래 걸리는 트랜잭션이 겹겹이 쌓여 병목 현상이 발생하고, 원하는 작업이 수행되지 못하는 등 가장 성능이 떨어지기 때문에 극단적으로 안전한 작업이 아니라면 사용을 기피하는 것으로 알려져 있다.
REPEATABLE READ
REPEATABLE READ는 InnoDB의 기본 트랜잭션 격리 수준으로, 하나의 트랜잭션 내에서 같은 SELECT 결과가 항상 동일함을 보장한다. 이는 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어) 매커니즘에 의해 트랜잭션이 시작되면 그 시점의 스냅샷이 생성된다. 이후 해당 트랜잭션 내의 SELECT는 이 스냅샷을 기반으로 데이터를 읽게 된다.
REPEATABLE READ는 트랜잭션의 실행 순서를 참고하여(트랜잭션 번호) 자신보다 먼저 실행된 트랜잭션의 데이터만을 조회한다. 이 때 테이블에 자신보다 이후에 실행된 트랜잭션의 데이터가 존재할 경우 백업된 언두 로그를 활용하여 데이터를 조회한다.
따라서 위 그림과 같이, 트랜잭션 A보다 늦게 시작된 트랜잭션에서 데이터를 변경하더라도 조회 결과는 동일하다. 즉 REPEATABLE READ에서는 다른 트랜잭션에서 데이터를 변경하더라도 일관된 읽기(Consistent Read) 결과를 보장한다. 아래 테스트 결과를 보면, 일관된 읽기가 보장됨을 알 수 있다.
it.only('REPEATABLE READ:: 데이터 변경 시 팬텀리드가 발생하지 않는다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT
const beforeTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 update
await repoB.update({ no: 10 }, { icash: 30000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
const afterTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
expect(beforeTrxBCommited.length).toBe(10);
//A보다 뒤에 실행된 트랜잭션 B가 변경한 레코드는 무시한다.
expect(afterTrxBCommited.length).toBe(10);
expect(afterTrxBCommited[9].icash).toBe(38120);
});
그러나 REPEATABLE READ 수준에서도 특정 조건에서 데이터 부정합, 팬텀 리드와 같은 이상 현상이 발생할 수 있다. 위에서 강조했듯이 REPEATABLE READ는 데이터를 변경할 때 일관된 읽기를 보장한다고 했다. 즉 새로운 레코드가 추가될 때는 아래 그림처럼 팬텀 리드가 발생할 수 있다.
그럼 정말로 새로운 레코드가 추가되면 팬텀 리드가 발생할까? 아래 테스트를 보자.
it.only('REPEATABLE READ:: 팬텀 리드가 발생할 것이다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT
const beforeTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 INSERT
await repoB.save({ userNo: 11, icash: 1000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
const afterTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
expect(beforeTrxBCommited.length).toBe(10);
expect(afterTrxBCommited.length).toBe(11);
});
팬텀 리드가 발생할 것으로 생각되는 상황을 가정하고, 테스트를 돌려보면 테스트는 실패한다. MySQL에서는 레코드가 추가되는 상황에서도 MVCC 덕분에 팬텀 리드가 발생하지 않는다. 위에서 언급했듯, MVCC를 사용하여 동일 트랜잭션 내에서 같은 데이터를 읽을 때 언두 로그 기반의 일관된 스냅샷을 제공하기 때문이다. 그렇다면 언제 팬텀리드가 발생할까? 여러 테스트를 작성하여 팬텀 리드가 발생하는 상황을 알아보려고 했다.
describe('REPETABLE READ', () => {
it('REPEATABLE READ:: 일반 조회 - 팬텀 리드가 발생하지 않는다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT
const beforeTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 INSERT
await repoB.save({ userNo: 11, icash: 1000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
const afterTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
expect(beforeTrxBCommited.length).toBe(10);
//A보다 뒤에 실행된 트랜잭션 B가 추가한 레코드는 무시한다.
expect(afterTrxBCommited.length).toBe(10);
});
it('REPEATABLE READ:: 배타적 잠금을 통한 조회 - 팬텀리드가 발생하지 않는다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
await runnerB.manager.query('SET SESSION innodb_lock_wait_timeout = 3');
let timeout: boolean = false;
try {
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT (배타적 잠금 발생)
const beforeTrxBCommited = await repoA
.createQueryBuilder()
.where('no < :no', { no: 15 })
.setLock('pessimistic_write')
.getMany();
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 INSERT
await repoB.save({ userNo: 11, icash: 1000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
const afterTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
await runnerA.commitTransaction();
//팬텀리드를 테스트하려고 하지만 타임아웃이 발생할 것이다.
expect(beforeTrxBCommited.length).toBe(10);
expect(afterTrxBCommited.length).toBe(11);
} catch (e: any) {
if (e.code == 'ER_LOCK_WAIT_TIMEOUT') {
timeout = true;
}
}
expect(timeout).toBe(true);
}, 10000);
it('REPEATABLE READ:: 읽기 잠금을 통한 조회 - 팬텀리드가 발생하지 않는다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
await runnerB.manager.query('SET SESSION innodb_lock_wait_timeout = 3');
let timeout: boolean = false;
try {
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT (읽기 잠금 발생)
const beforeTrxBCommited = await repoA
.createQueryBuilder()
.where('no < :no', { no: 15 })
.setLock('pessimistic_read')
.getMany();
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 INSERT
await repoB.save({ userNo: 11, icash: 1000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
const afterTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
await runnerA.commitTransaction();
expect(beforeTrxBCommited.length).toBe(10);
expect(afterTrxBCommited.length).toBe(11);
} catch (e: any) {
if (e.code == 'ER_LOCK_WAIT_TIMEOUT') {
timeout = true;
}
}
expect(timeout).toBe(true);
}, 10000);
it('REPEATABLE READ:: 쓰기 이후 배타적 잠금으로 조회 - 팬텀리드가 발생한다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT (배타적 잠금 발생)
const beforeTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 INSERT
await repoB.save({ userNo: 11, icash: 1000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
const afterTrxBCommited = await repoA
.createQueryBuilder()
.where('no < :no', { no: 15 })
.setLock('pessimistic_write')
.getMany();
await runnerA.commitTransaction();
expect(beforeTrxBCommited.length).toBe(10);
expect(afterTrxBCommited.length).toBe(11);
});
it('REPEATABLE READ:: 쓰기 이후 읽기 잠금으로 조회 - 팬텀리드가 발생한다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('REPEATABLE READ');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 SELECT
const beforeTrxBCommited = await repoA.find({ where: { no: LessThan(15) } });
// 트랜잭션 B 시작
await runnerB.startTransaction('REPEATABLE READ');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 팬텀리드를 발생시키기 위해 INSERT
await repoB.save({ userNo: 11, icash: 1000 });
// 트랜잭션 B 커밋
await runnerB.commitTransaction();
// 트랜잭션 B에서 읽기잠금으로 조회
const afterTrxBCommited = await repoA
.createQueryBuilder()
.where('no < :no', { no: 15 })
.setLock('pessimistic_read')
.getMany();
await runnerA.commitTransaction();
expect(beforeTrxBCommited.length).toBe(10);
expect(afterTrxBCommited.length).toBe(11);
});
});
테스트 케이스의 결과를 통해 REPEATABLE READ에서의 팬텀 리드 발생 상황을 정리해볼 수 있었다.
- SELECT > DML > SELECT : 팬텀리드가 발생하지 않음
- SELECT FOR UPDATE(배타적 잠금) > DML > SELECT : 팬텀리드가 발생하지 않음
- SELECT FOR SHARE(읽기 잠금) > DML > SELECT : 팬텀 리드가 발생하지 않음
- SELECT > DML > SELECT FOR UPDATE(배타적 잠금) : 팬텀리드 발생
- SELECT > DML > SELECT FOR SHARE(읽기 잠금) : 팬텀 리드 발생
테스트 결과를 보면, 2번 3번 테스트가 유독 긴 테스트 시간이 소요되었다. 타임아웃이 발생한 것이다.
위 테스트의 경우에 no < 15인 인덱스 레코드에 대해 조회와 동시에 잠금을 걸었기 때문에 다른 트랜잭션에서는 쓰기 작업을 위해 해당 트랜잭션에서 잠금을 해제하여야 한다. 즉, 트랜잭션 A가 해당 레코드 범위에 대해 잠금을 해제할 때 까지 트랜잭션 B는 대기상태에 들어가고, 설정해 둔 3초의 시간이 지나 타임아웃이 발생한 것이다.
MySQL에서 REPEATABLE READ 수준에서의 팬텀 리드는 데이터가 변경된 후 잠금을 사용하는 조회 상황에서는 팬텀 리드가 발생할 수 있다. 스냅샷이 아닌 실제 테이블 상태를 참조하기 때문에 다른 트랜잭션에서 추가한 새로운 행이 보이게 되는 것이다. 마찬가지로, 트랜잭션 없이 실행되는 SELECT에서도 팬텀 리드가 발생할 수 있다.
정리
MySQL의 REPEATABLE READ 수준에서 MVCC를 통해 한 트랜잭션 내에서 거의 모든 상황에 일관된 읽기를 수행할 수 있고 테이블에 잠금을 설정하지 않기 때문에 트랜잭션에서 일관된 읽기를 수행하는 동안 다른 트랜잭션에서 해당 테이블을 자유롭게 수정할 수 있다.
READ COMMITED
커밋된 데이터만 조회할 수 있는 격리수준이다. 아래는 테스트 코드와 이를 풀어낸 그림으로, 트랜잭션 A에서 업데이트 후 커밋하기 전의 조회 결과와 커밋한 후의 조회 결과가 다름을 보여준다.
it('READ COMMITTED:: 커밋된 데이터만 조회가 가능하다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('READ COMMITTED');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 쓰기 작업
await repoA.update({ no: 1 }, { icash: 10 });
// 트랜잭션 B 시작
await runnerB.startTransaction('READ COMMITTED');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 트랜잭션 B에서 SELECT
const beforeTrxACommited = await repoB.findOne({ where: { no: 1 } });
// 트랜잭션 A 커밋
await runnerA.commitTransaction();
// 트랜잭션 B에서 SELECT
const afterTrxACommited = await repoB.findOne({ where: { no: 1 } });
expect(beforeTrxACommited!.icash).toBe(1000);
expect(afterTrxACommited!.icash).toBe(10);
});
이 결과는 트랜잭션 B가 먼저 시작되더라도 동일하다. 다른 트랜잭션에서 커밋이 되었는지 안되었는지, 커밋 결과로 레코드가 업데이트 되었는지가 중요하다. 다른 트랜잭션에서의 커밋 여부에 따라 조회 결과가 계속해서 변할 수 있다.
이처럼 동일한 조건으로 데이터를 조회했음에도 다른 트랜잭션의 커밋 여부에 따라 결과가 달라지는 이상 현상을 반복 읽기 불가능
(Non-Repeatable Read)라고 한다. 예상 가능하겠지만, READ COMMITTED 수준에서는, 트랜잭션 내에서 실행되는 조회와 트랜잭션 밖에서 실행되는 조회의 차이가 거의 없다.
READ UNCOMMITTED
READ UNCOMMITTED는 트랜잭션 처리가 완료되지 않은 데이터까지도 읽을 수 있는 격리 수준이다. READ COMMITTED와 같은 예시에서, READ UNCOMMITTED 수준에서는 반영이 되지 않은 데이터에 접근할 수 있음을 알 수 있다.
it('READ UNCOMMITTED:: 커밋되지 않은 데이터도 조회가 가능하다.', async () => {
runnerA = dataSource.createQueryRunner();
runnerB = dataSource.createQueryRunner();
// 트랜잭션 A 시작
await runnerA.startTransaction('READ UNCOMMITTED');
const repoA = runnerA.manager.getRepository(ICashEntity);
// 트랜잭션 A에서 쓰기 작업
await repoA.update({ no: 1 }, { icash: 10 });
// 트랜잭션 B 시작
await runnerB.startTransaction('READ UNCOMMITTED');
const repoB = runnerB.manager.getRepository(ICashEntity);
// 트랜잭션 B에서 SELECT
const beforeTrxACommited = await repoB.findOne({ where: { no: 1 } });
// 트랜잭션 A 커밋
await runnerA.commitTransaction();
// 트랜잭션 B에서 SELECT
const afterTrxACommited = await repoB.findOne({ where: { no: 1 } });
expect(beforeTrxACommited!.icash).toBe(10);
expect(afterTrxACommited!.icash).toBe(10);
});
이렇게, 작업이 완료되지 않았는데도 데이터를 읽을 수 있는 이상 현상을 Dirty Read라고 한다. 커밋이나 롤백되지 않은, 작업이 완료되지 않은 결과를 읽고 다른 작업을 수행한다. 특히 롤백 상황에서, 롤백 전의 데이터로 무언가 작업이 일어날 우려가 있기 때문에
READ UNCOMMITTED는 데이터 정합성에 문제가 많은 격리 수준이다.
참조
https://dev.mysql.com/doc/refman/8.4/en/innodb-storage-engine.html
https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html
https://dev.mysql.com/doc/refman/8.4/en/glossary.html
https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html
https://dev.mysql.com/doc/refman/8.4/en/innodb-next-key-locking.html
https://mangkyu.tistory.com/
https://velog.io/@onejaejae/DB-MVCC
https://medium.com/daangn/mysql-cats-contention-aware-transaction-scheduling-71fe6956e87e
https://techblog.woowahan.com/2606/
https://www.youtube.com/watch?v=704qQs6KoUk
https://www.youtube.com/watch?v=4wGTavSyLxE&t=148s
2023.04 ~ 백엔드 개발자의 기록
포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!
![[MySQL] Lost Update와 Write Skew](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2FdppIEe%2FbtsLeAo81jz%2FO7MshK7WQ3akMeRwwRCns1%2Fimg.png)
![[데이터베이스] MVCC(다중 버전 동시성 제어 - Multi Version Concurrency Control)](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2Fb4PoMp%2FbtsK326NemX%2FNlFKZttD35YO0mdqSKN4GK%2Fimg.png)
![[데이터베이스] 인덱스(index) 정리](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2F8wCp6%2FbtsIwhDN3lg%2FSEEbrIf3PINoLRrEbJE5Rk%2Fimg.png)