Flight Booking System Design: Avoid Double-Booking and Payment Race Conditions

// io.thecodeforge — System Design tutorial

// Optimistic locking for seat inventory
// Assume seats table: id, flight_id, seat_number, version, status

public boolean bookSeat(long seatId, long expectedVersion) {
    String sql = "UPDATE seats SET status = 'booked', version = version + 1 " +
                 "WHERE id = ? AND version = ? AND status = 'available'";
    int updated = jdbcTemplate.update(sql, seatId, expectedVersion);
    if (updated == 0) {
        // Either seat already booked or version mismatch
        // Retry logic: re-read seat and retry up to 3 times
        return false;
    }
    return true;
}

// io.thecodeforge — System Design tutorial

// Idempotency check before processing payment
public BookingResult processBooking(BookingRequest request) {
    // Check if idempotency key already processed
    Booking existing = bookingRepository.findByIdempotencyKey(request.getIdempotencyKey());
    if (existing != null) {
        return new BookingResult(existing, true); // idempotent return
    }
    // Proceed with payment and booking creation
    PaymentResult payment = paymentGateway.charge(request.getAmount(), request.getIdempotencyKey());
    if (!payment.isSuccess()) {
        throw new PaymentFailedException(payment.getError());
    }
    Booking booking = bookingRepository.save(request.toBooking());
    return new BookingResult(booking, false);
}

// io.thecodeforge — System Design tutorial

// Saga orchestrator for booking
public class BookingSaga {
    private final SagaState state;

    public void execute(BookingRequest request) {
        try {
            state.start();
            lockSeat(request); // step 1
            state.stepCompleted("lockSeat");
            chargePayment(request); // step 2
            state.stepCompleted("chargePayment");
            confirmBooking(request); // step 3
            state.stepCompleted("confirmBooking");
            sendConfirmation(request); // step 4
            state.complete();
        } catch (Exception e) {
            compensate(state.getCompletedSteps(), request);
            state.fail();
        }
    }

    private void compensate(List<String> steps, BookingRequest request) {
        if (steps.contains("confirmBooking")) {
            cancelBooking(request); // compensating action
        }
        if (steps.contains("chargePayment")) {
            refundPayment(request); // compensating action
        }
        if (steps.contains("lockSeat")) {
            releaseSeat(request); // compensating action
        }
    }
}

// io.thecodeforge — System Design tutorial

// Transactional outbox: write event in same transaction as booking
@Transactional
public void createBooking(BookingRequest request) {
    // 1. Update seat inventory
    seatRepository.updateStatus(request.getSeatId(), "booked");
    // 2. Create booking record
    Booking booking = bookingRepository.save(request.toBooking());
    // 3. Write outbox event
    OutboxEvent event = new OutboxEvent("BookingConfirmed", booking.getId());
    outboxRepository.save(event);
    // Transaction commits — both booking and event persisted atomically
}

// Separate process: poll outbox and publish
@Scheduled(fixedDelay = 1000)
public void publishOutboxEvents() {
    List<OutboxEvent> events = outboxRepository.findTop100ByPublishedFalse();
    for (OutboxEvent event : events) {
        try {
            messageQueue.publish(event.getType(), event.getPayload());
            event.setPublished(true);
            outboxRepository.save(event);
        } catch (Exception e) {
            log.error("Failed to publish event: {}", event.getId(), e);
            // will retry on next poll
        }
    }
}

// io.thecodeforge — System Design tutorial

// Reserve seat with TTL
public boolean reserveSeat(long seatId, int ttlMinutes) {
    String sql = "UPDATE seats SET status = 'reserved', reserved_until = NOW() + ?::interval " +
                 "WHERE id = ? AND status = 'available'";
    int updated = jdbcTemplate.update(sql, ttlMinutes + " minutes", seatId);
    return updated > 0;
}

// Confirm booking (called after payment)
public boolean confirmBooking(long seatId, String reservationToken) {
    String sql = "UPDATE seats SET status = 'booked', reserved_until = NULL " +
                 "WHERE id = ? AND status = 'reserved' AND reserved_until > NOW()";
    int updated = jdbcTemplate.update(sql, seatId);
    return updated > 0;
}

// Background job to release expired reservations
@Scheduled(fixedDelay = 60000)
public void releaseExpiredReservations() {
    String sql = "UPDATE seats SET status = 'available', reserved_until = NULL " +
                 "WHERE status = 'reserved' AND reserved_until < NOW()";
    jdbcTemplate.update(sql);
}