Java 入门进阶 利用 MySQL 事务保证「电影票选座购票系统」的数据一致性

事务基础概念

MySQL 事务是一组原子性的 SQL 查询，要么全部执行成功，要么全部失败回滚。事务的四大特性（ACID）：

• 原子性（Atomicity）：事务是不可分割的工作单位
• 一致性（Consistency）：事务执行前后数据库状态保持一致
• 隔离性（Isolation）：事务执行不受其他事务干扰
• 持久性（Durability）：事务提交后对数据库的改变是永久性的

数据库表设计

票务系统核心表结构示例：

CREATE TABLE seats (
    seat_id INT PRIMARY KEY,
    movie_id INT,
    showtime DATETIME,
    status ENUM('available', 'locked', 'sold') DEFAULT 'available',
    locked_by VARCHAR(36),
    locked_at TIMESTAMP,
    INDEX idx_movie_showtime (movie_id, showtime)
);

CREATE TABLE orders (
    order_id VARCHAR(36) PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'completed', 'cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(36),
    seat_id INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (seat_id) REFERENCES seats(seat_id)
);

Java 事务实现方案

使用 JDBC 的事务管理：

Connection conn = null;
try {
    conn = dataSource.getConnection();
    conn.setAutoCommit(false); // 开启事务
    
    // 1. 检查座位状态
    PreparedStatement checkStmt = conn.prepareStatement(
        "SELECT status FROM seats WHERE seat_id = ? FOR UPDATE");
    checkStmt.setInt(1, seatId);
    ResultSet rs = checkStmt.executeQuery();
    
    if (!rs.next() || !"available".equals(rs.getString("status"))) {
        throw new SeatNotAvailableException();
    }
    
    // 2. 锁定座位
    PreparedStatement lockStmt = conn.prepareStatement(
        "UPDATE seats SET status = 'locked', locked_by = ?, locked_at = NOW() WHERE seat_id = ?");
    lockStmt.setString(1, orderId);
    lockStmt.setInt(2, seatId);
    lockStmt.executeUpdate();
    
    // 3. 创建订单
    PreparedStatement orderStmt = conn.prepareStatement(
        "INSERT INTO orders (order_id, user_id, total_amount, status) VALUES (?, ?, ?, 'pending')");
    orderStmt.setString(1, orderId);
    orderStmt.setInt(2, userId);
    orderStmt.setBigDecimal(3, totalAmount);
    orderStmt.executeUpdate();
    
    // 4. 添加订单明细
    PreparedStatement itemStmt = conn.prepareStatement(
        "INSERT INTO order_items (order_id, seat_id, price) VALUES (?, ?, ?)");
    itemStmt.setString(1, orderId);
    itemStmt.setInt(2, seatId);
    itemStmt.setBigDecimal(3, price);
    itemStmt.executeUpdate();
    
    conn.commit(); // 提交事务
} catch (SQLException e) {
    if (conn != null) conn.rollback(); // 回滚事务
    throw e;
} finally {
    if (conn != null) conn.close();
}

Spring 事务管理方案

使用 @Transactional 注解简化事务管理：

@Service
public class TicketService {
    
    @Transactional
    public String purchaseTickets(List<Integer> seatIds, int userId) {
        String orderId = UUID.randomUUID().toString();
        
        // 检查并锁定座位
        seatIds.forEach(seatId -> {
            Seat seat = seatRepository.findById(seatId)
                .orElseThrow(() -> new SeatNotFoundException(seatId));
            
            if (!seat.isAvailable()) {
                throw new SeatNotAvailableException(seatId);
            }
            
            seat.lock(orderId);
            seatRepository.save(seat);
        });
        
        // 创建订单
        Order order = new Order();
        order.setOrderId(orderId);
        order.setUserId(userId);
        order.setStatus(OrderStatus.PENDING);
        order.setTotalAmount(calculateTotal(seatIds));
        orderRepository.save(order);
        
        // 添加订单项
        seatIds.forEach(seatId -> {
            OrderItem item = new OrderItem();
            item.setOrderId(orderId);
            item.setSeatId(seatId);
            item.setPrice(getSeatPrice(seatId));
            orderItemRepository.save(item);
        });
        
        return orderId;
    }
}

并发控制策略

悲观锁实现方案：

-- 查询时直接加锁
SELECT * FROM seats WHERE seat_id = ? FOR UPDATE;

-- 更新时检查版本
UPDATE seats SET status = 'sold' 
WHERE seat_id = ? AND status = 'locked' AND locked_by = ?;

乐观锁实现方案：

@Transactional
public void confirmOrder(String orderId) {
    Order order = orderRepository.findById(orderId)
        .orElseThrow(() -> new OrderNotFoundException(orderId));
    
    // 验证订单状态
    if (order.getStatus() != OrderStatus.PENDING) {
        throw new IllegalOrderStateException();
    }
    
    // 获取关联座位
    List<Seat> seats = seatRepository.findByLockedBy(orderId);
    
    // 检查座位是否仍然被锁定
    seats.forEach(seat -> {
        if (!seat.isLockedBy(orderId)) {
            throw new SeatLockLostException(seat.getSeatId());
        }
    });
    
    // 更新订单和座位状态
    order.setStatus(OrderStatus.COMPLETED);
    orderRepository.save(order);
    
    seats.forEach(seat -> {
        seat.setStatus(SeatStatus.SOLD);
        seatRepository.save(seat);
    });
}

异常处理机制

建立事务补偿机制处理异常情况：

@Scheduled(fixedDelay = 60000) // 每分钟执行一次
public void releaseExpiredLocks() {
    List<Seat> expiredSeats = seatRepository
        .findByStatusAndLockedAtBefore(
            SeatStatus.LOCKED,
            LocalDateTime.now().minusMinutes(15));
    
    expiredSeats.forEach(seat -> {
        try {
            seat.setStatus(SeatStatus.AVAILABLE);
            seat.setLockedBy(null);
            seatRepository.save(seat);
            
            // 取消关联的订单
            orderRepository.findByOrderId(seat.getLockedBy())
                .ifPresent(order -> {
                    order.setStatus(OrderStatus.CANCELLED);
                    orderRepository.save(order);
                });
        } catch (Exception e) {
            log.error("Failed to release seat {}", seat.getSeatId(), e);
        }
    });
}

性能优化建议

使用批量操作减少数据库交互：

@Transactional
public void batchConfirmOrders(List<String> orderIds) {
    List<Seat> allSeats = new ArrayList<>();
    List<Order> allOrders = new ArrayList<>();
    
    orderIds.forEach(orderId -> {
        Order order = orderRepository.findById(orderId)
            .orElseThrow(() -> new OrderNotFoundException(orderId));
        
        List<Seat> seats = seatRepository.findByLockedBy(orderId);
        seats.forEach(seat -> seat.setStatus(SeatStatus.SOLD));
        
        order.setStatus(OrderStatus.COMPLETED);
        allSeats.addAll(seats);
        allOrders.add(order);
    });
    
    seatRepository.saveAll(allSeats);
    orderRepository.saveAll(allOrders);
}

分布式事务考虑

对于跨服务调用场景，可采用 Saga 模式：

1. 创建订单服务（Saga 协调器）
2. 锁定座位服务（参与者）
3. 支付服务（参与者）
4. 每个步骤都有对应的补偿操作

public class OrderSaga {
    
    public void execute(OrderCommand command) {
        try {
            // 1. 创建订单
            Order order = createOrder(command);
            
            // 2. 锁定座位
            lockSeats(command.getSeatIds(), order.getOrderId());
            
            // 3. 处理支付
            processPayment(order.getOrderId(), command.getPaymentInfo());
            
            // 4. 确认订单
            confirmOrder(order.getOrderId());
            
        } catch (Exception e) {
            // 执行补偿操作
            compensate(order.getOrderId());
            throw e;
        }
    }
    
    private void compensate(String orderId) {
        // 1. 取消支付（如有）
        paymentService.cancel(orderId);
        
        // 2. 释放座位锁
        seatService.releaseSeats(orderId);
        
        // 3. 取消订单
        orderService.cancel(orderId);
    }
}