Golang数据库事务提交失败怎么处理？Golang Tx使用指南

package main

import (
    "database/sql"
    "fmt"
    "log"
    "math/rand"
    "time"

    _ "github.com/go-sql-driver/mysql" // 导入 MySQL 驱动
)

// retry 函数，实现指数退避重试机制
func retry(attempts int, sleep time.Duration, f func() error) (err error) {
    for i := 0; i < attempts; i++ {
        err = f()
        if err == nil {
            return nil
        }

        // 打印重试信息
        fmt.Println("Attempt", i+1, "failed with error:", err)

        // 如果不是最后一次尝试，则等待一段时间
        if i < (attempts - 1) {
            jitter := time.Duration(rand.Int63n(int64(sleep)))
            sleep = sleep + jitter/2

            time.Sleep(sleep)
            log.Println("retrying after", sleep)
        }
    }
    return fmt.Errorf("after %d attempts, the last error was: %s", attempts, err)
}

func main() {
    // 数据库连接信息
    dbUser := "user"
    dbPass := "password"
    dbHost := "127.0.0.1"
    dbPort := "3306"
    dbName := "dbname"

    // 构建连接字符串
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", dbUser, dbPass, dbHost, dbPort, dbName)

    // 打开数据库连接
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 检查数据库连接
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    // 设置重试次数和初始睡眠时间
    attempts := 3
    sleep := 1 * time.Second

    // 定义要执行的事务函数
    txFunc := func() error {
        tx, err := db.Begin()
        if err != nil {
            return fmt.Errorf("failed to begin transaction: %w", err)
        }
        defer func() {
            if p := recover(); p != nil {
                tx.Rollback()
                panic(p) // re-throw panic after Rollback
            } else if err != nil {
                tx.Rollback()
                log.Println("transaction rolled back due to error:", err)
            } else {
                err = tx.Commit()
                if err != nil {
                    log.Println("failed to commit transaction:", err)
                }
            }
        }()

        // 执行数据库操作
        _, err = tx.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "testuser", "test@example.com")
        if err != nil {
            return fmt.Errorf("failed to execute query: %w", err)
        }

        // 模拟一个可能导致事务失败的错误
        if rand.Intn(10) < 3 { // 30% 的概率模拟错误
            return fmt.Errorf("simulated error")
        }

        return nil
    }

    // 使用重试机制执行事务
    err = retry(attempts, sleep, txFunc)
    if err != nil {
        log.Fatalf("failed to execute transaction after multiple retries: %v", err)
    }

    log.Println("Transaction completed successfully!")
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // 数据库连接信息
    dbUser := "user"
    dbPass := "password"
    dbHost := "127.0.0.1"
    dbPort := "3306"
    dbName := "dbname"

    // 构建连接字符串
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", dbUser, dbPass, dbHost, dbPort, dbName)

    // 打开数据库连接
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 检查数据库连接
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p) // re-throw panic after Rollback
        } else if err != nil {
            tx.Rollback()
            log.Println("transaction rolled back due to error:", err)
        } else {
            err = tx.Commit()
            if err != nil {
                log.Println("failed to commit transaction:", err)
            }
        }
    }()

    // 创建 Savepoint
    _, err = tx.Exec("SAVEPOINT my_savepoint")
    if err != nil {
        log.Fatal(err)
    }

    // 执行一些操作
    _, err = tx.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "user1", "user1@example.com")
    if err != nil {
        log.Fatal(err)
    }

    // 模拟一个错误，回滚到 Savepoint
    // err = fmt.Errorf("simulated error")
    // if err != nil {
    //  _, err = tx.Exec("ROLLBACK TO SAVEPOINT my_savepoint")
    //  if err != nil {
    //      log.Fatal(err)
    //  }
    //  log.Println("Rolled back to savepoint")
    // }

    // 如果没有错误，继续执行其他操作
    _, err = tx.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "user2", "user2@example.com")
    if err != nil {
        log.Fatal(err)
    }

    // 提交事务
    // err = tx.Commit()
    // if err != nil {
    //  log.Fatal(err)
    // }

    log.Println("Transaction completed successfully!")
}