04 — 격리 수준

T1: BEGIN
T1: UPDATE balance = 100 WHERE id = 1
T2: BEGIN
T2: SELECT balance WHERE id = 1  -- 100을 본다 (아직 commit 안 됨!)
T1: ROLLBACK  -- T1의 변경은 사라졌는데 T2는 그걸 봤음

T1: BEGIN
T1: SELECT balance WHERE id = 1  -- 100
T2: BEGIN; UPDATE balance = 200 WHERE id = 1; COMMIT
T1: SELECT balance WHERE id = 1  -- 200 — 같은 트랜잭션 안에서 값이 바뀜!
T1: COMMIT

T1: BEGIN
T1: SELECT count(*) FROM orders WHERE user_id = 1  -- 5
T2: BEGIN; INSERT INTO orders (user_id) VALUES (1); COMMIT
T1: SELECT count(*) FROM orders WHERE user_id = 1  -- 6 — 새 row가 끼어들었음!
T1: COMMIT

// 방법 1: dataSource.transaction의 첫 인자
await AppDataSource.transaction('SERIALIZABLE', async (manager) => {
  // ...
});
 
// 방법 2: QueryRunner.startTransaction의 인자
const qr = AppDataSource.createQueryRunner();
await qr.connect();
await qr.startTransaction('REPEATABLE READ');
try {
  // ...
  await qr.commitTransaction();
} finally {
  await qr.release();
}
 
// 방법 3: typeorm-transactional 데코레이터 옵션
@Transactional({ isolationLevel: IsolationLevel.SERIALIZABLE })
async transfer() { /* ... */ }

type IsolationLevel =
  | 'READ UNCOMMITTED'
  | 'READ COMMITTED'
  | 'REPEATABLE READ'
  | 'SERIALIZABLE';

// PostgreSQL · REPEATABLE READ
await AppDataSource.transaction('REPEATABLE READ', async (manager) => {
  await manager.update(Account, { id: 1 }, { balance: 100 });
  // 다른 트랜잭션이 같은 row를 동시에 업데이트하면 → "could not serialize access" 에러
});

async function withRetry<T>(fn: () => Promise<T>, max = 3): Promise<T> {
  for (let i = 0; i < max; i++) {
    try {
      return await fn();
    } catch (err: any) {
      if (err.code === '40001' /* serialization_failure */) continue;
      throw err;
    }
  }
  throw new Error('Max retries exceeded');
}

-- InnoDB · REPEATABLE READ (default)
T1: BEGIN
T1: SELECT * FROM orders WHERE user_id = 1 FOR UPDATE  -- gap lock 획득
T2: INSERT INTO orders (user_id) VALUES (1)  -- ⏳ T1의 gap lock에 막혀 대기
T1: COMMIT  -- T2가 진행됨

// PostgreSQL: SELECT ... FOR UPDATE
await AppDataSource.transaction(async (manager) => {
  const account = await manager
    .createQueryBuilder(Account, 'a')
    .where('a.id = :id', { id: '1' })
    .setLock('pessimistic_write') // FOR UPDATE
    .getOne();
 
  // 이 트랜잭션이 끝날 때까지 다른 트랜잭션의 update/lock 차단
  account.balance -= 100;
  await manager.save(account);
});

@Entity()
class Order {
  @PrimaryGeneratedColumn('uuid')
  id: string;
 
  @Column()
  status: string;
 
  @VersionColumn()
  version: number; // ← 자동 증가
}

const order = await orderRepo.findOne({ where: { id } });
order.status = 'paid';
await orderRepo.save(order);
// UPDATE order SET status = 'paid', version = version + 1
//   WHERE id = ? AND version = ?
// → version이 일치하지 않으면 0 rows affected
// → TypeORM이 OptimisticLockVersionMismatchError 던짐