Golang中的事务处理详解

你的代码在事务处理方面有几个可以改进的地方。以下是具体的优化建议和示例代码：

1. 事务错误处理优化

当前代码中多次调用 tx.Rollback(ctx)，但 defer 中的回滚逻辑可能不会按预期工作：

func (ur *userRepository) RegisterUser(ctx context.Context, register *entities.RegisterUsertx) (int32, error) {
    tx, err := ur.storage.DBPool.Begin(ctx)
    if err != nil {
        log.Printf("error beginning transaction: %v", err)
        return 0, fmt.Errorf("begin transaction: %w", err)
    }
    
    // 使用局部变量跟踪错误，确保defer正确执行
    var txErr error
    defer func() {
        if txErr != nil {
            if rbErr := tx.Rollback(ctx); rbErr != nil {
                log.Printf("rollback error: %v", rbErr)
            }
        }
    }()
    
    // 后续操作使用 txErr 而不是 err
    var userID int32
    query := "INSERT INTO users (name, lastname1, lastname2, email, phone, created_at) VALUES($1, $2, $3, $4, $5, $6) RETURNING id"
    txErr = tx.QueryRow(ctx, query, register.Name, register.Lastname1, register.Lastname2, register.Email, register.Phone, register.CreatedAt).Scan(&userID)
    if txErr != nil {
        log.Printf("error inserting user: %v", txErr)
        return 0, fmt.Errorf("insert user: %w", txErr)
    }
    
    // 移除所有手动调用 tx.Rollback(ctx) 的代码
    // 只返回错误，让defer处理回滚
    
    // 提交事务
    if commitErr := tx.Commit(ctx); commitErr != nil {
        log.Printf("error committing transaction: %v", commitErr)
        return 0, fmt.Errorf("commit transaction: %w", commitErr)
    }
    
    return userID, nil
}

2. 使用事务上下文

确保使用事务上下文进行所有数据库操作：

func (ur *userRepository) RegisterUser(ctx context.Context, register *entities.RegisterUsertx) (int32, error) {
    // 开始事务时传递上下文
    tx, err := ur.storage.DBPool.BeginTx(ctx, pgx.TxOptions{})
    if err != nil {
        return 0, fmt.Errorf("begin transaction: %w", err)
    }
    
    defer func() {
        if err != nil {
            tx.Rollback(ctx)
        }
    }()
    
    // 所有数据库操作都使用事务的上下文
    var userID int32
    err = tx.QueryRow(ctx, 
        "INSERT INTO users (...) VALUES(...) RETURNING id",
        register.Name, register.Lastname1, register.Lastname2, 
        register.Email, register.Phone, register.CreatedAt,
    ).Scan(&userID)
    
    if err != nil {
        return 0, fmt.Errorf("insert user: %w", err)
    }
    
    // 继续其他操作...
    
    if err := tx.Commit(ctx); err != nil {
        return 0, fmt.Errorf("commit transaction: %w", err)
    }
    
    return userID, nil
}

3. 查询优化

你的代码中有重复查询和可以优化的地方：

// 优化前：两次查询 subscription_costs 表
query = "SELECT subscription_day FROM subscription_costs WHERE id = $1"
err = tx.QueryRow(ctx, query, register.SubscriptionCostID).Scan(&subscriptionDuration)

// ... 后面又查询
query = "SELECT cost from subscription_costs where id = $1"
err = tx.QueryRow(ctx, query, register.SubscriptionCostID).Scan(&expectedCost)

// 优化后：一次查询获取所有需要的数据
var subscriptionDuration int
var expectedCost float64
query = "SELECT subscription_day, cost FROM subscription_costs WHERE id = $1"
err = tx.QueryRow(ctx, query, register.SubscriptionCostID).Scan(&subscriptionDuration, &expectedCost)
if err != nil {
    log.Printf("error getting subscription details: %v", err)
    return 0, fmt.Errorf("get subscription details: %w", err)
}

4. 状态查询优化

你的状态查询逻辑可以简化：

// 优化前：复杂的查询和循环
query = `SELECT id FROM STATUS WHERE id = 5 OR id = 1`
rows, err := tx.Query(ctx, query)
// ... 循环处理

// 优化后：直接查询需要的状态
var statusPayment, statusAccount int32

// 查询支付状态
err = tx.QueryRow(ctx, "SELECT id FROM status WHERE id = 5").Scan(&statusPayment)
if err != nil {
    return 0, fmt.Errorf("get payment status: %w", err)
}

// 查询账户状态
err = tx.QueryRow(ctx, "SELECT id FROM status WHERE id = 1").Scan(&statusAccount)
if err != nil {
    return 0, fmt.Errorf("get account status: %w", err)
}

5. 完整优化示例

func (ur *userRepository) RegisterUser(ctx context.Context, register *entities.RegisterUsertx) (int32, error) {
    tx, err := ur.storage.DBPool.BeginTx(ctx, pgx.TxOptions{})
    if err != nil {
        return 0, fmt.Errorf("begin transaction: %w", err)
    }
    
    defer func() {
        if err != nil {
            tx.Rollback(ctx)
        }
    }()
    
    // 1. 插入用户
    var userID int32
    err = tx.QueryRow(ctx,
        "INSERT INTO users (name, lastname1, lastname2, email, phone, created_at) VALUES($1, $2, $3, $4, $5, $6) RETURNING id",
        register.Name, register.Lastname1, register.Lastname2, register.Email, register.Phone, register.CreatedAt,
    ).Scan(&userID)
    if err != nil {
        return 0, fmt.Errorf("insert user: %w", err)
    }
    register.ID = userID
    
    // 2. 创建账户
    accountID := uuid.New()
    var account uuid.UUID
    err = tx.QueryRow(ctx,
        "INSERT INTO accounts (user_id, account_id, account_type_id, created_at) VALUES($1, $2, $3, $4) RETURNING account_id",
        userID, accountID, register.AccountTypeID, time.Now(),
    ).Scan(&account)
    if err != nil {
        return 0, fmt.Errorf("insert account: %w", err)
    }
    
    // 3. 获取订阅详情（一次查询）
    var subscriptionDuration int
    var expectedCost float64
    err = tx.QueryRow(ctx,
        "SELECT subscription_day, cost FROM subscription_costs WHERE id = $1",
        register.SubscriptionCostID,
    ).Scan(&subscriptionDuration, &expectedCost)
    if err != nil {
        return 0, fmt.Errorf("get subscription details: %w", err)
    }
    
    // 4. 验证金额
    if register.Ammount != expectedCost {
        return 0, fmt.Errorf("amount incorrect: expected %v, got %v", expectedCost, register.Ammount)
    }
    
    // 5. 创建订阅
    startDate := time.Now()
    endDate := startDate.AddDate(0, 0, subscriptionDuration)
    err = tx.QueryRow(ctx,
        "INSERT INTO subscriptions (account_id, subscription_cost_id, start_date, end_date) VALUES($1, $2, $3, $4) RETURNING id",
        account, register.SubscriptionCostID, startDate, endDate,
    ).Scan(&register.SubscriptionID)
    if err != nil {
        return 0, fmt.Errorf("insert subscription: %w", err)
    }
    
    // 6. 插入支付记录
    _, err = tx.Exec(ctx,
        "INSERT INTO payments (account_id, payment_type_id, cost, payment_date) VALUES($1, $2, $3, $4)",
        account, register.PaymentTypeID, register.Ammount, time.Now(),
    )
    if err != nil {
        return 0, fmt.Errorf("insert payment: %w", err)
    }
    
    // 7. 更新支付状态
    _, err = tx.Exec(ctx,
        "UPDATE payments SET status_id = 5 WHERE account_id = $1",
        account,
    )
    if err != nil {
        return 0, fmt.Errorf("update payment status: %w", err)
    }
    
    // 8. 更新账户状态和订阅ID
    _, err = tx.Exec(ctx,
        "UPDATE accounts SET subscription_id = $1, status_id = 1 WHERE account_id = $2",
        register.SubscriptionID, account,
    )
    if err != nil {
        return 0, fmt.Errorf("update account: %w", err)
    }
    
    // 提交事务
    if err = tx.Commit(ctx); err != nil {
        return 0, fmt.Errorf("commit transaction: %w", err)
    }
    
    return userID, nil
}

主要改进点：

1. 统一错误处理，避免重复回滚调用
2. 优化查询，减少数据库往返次数
3. 简化状态查询逻辑
4. 使用事务上下文确保一致性
5. 代码结构更清晰，易于维护