How Can I Reliably Detect Database Transaction Commit or Rollback in Go's `database/sql` Package?

Detecting Transaction Commit or Rollback in database/sql

In the database/sql package with its driver interface and Tx type, it is not explicitly possible to determine whether a transaction has been committed or rolled back without attempting another transaction. The error returned from the subsequent attempt can then be examined to infer the transaction's status.

To avoid additional overhead, one might consider setting the Tx variable to nil after a commit or rollback. However, this approach is generally discouraged as it may cause unexpected behavior and memory leaks.

A recommended solution is to use a transaction handler to wrap around the transaction logic. This ensures that Begin(), Commit(), and Rollback() calls are always within the same function, simplifying tracking and ensuring proper transaction handling using defer statements.

For instance:

func Transact(db *sql.DB, txFunc func(*sql.Tx) error) (err error) {
    tx, err := db.Begin()
    if err != nil {
        return
    }
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p) // re-throw panic after Rollback
        } else if err != nil {
            tx.Rollback() // err is non-nil; don't change it
        } else {
            err = tx.Commit() // err is nil; if Commit returns error update err
        }
    }()
    err = txFunc(tx)
    return err
}

With this handler, transaction logic can be encapsulated as follows:

func (s Service) DoSomething() error {
    return Transact(s.db, func (tx *sql.Tx) error {
        if _, err := tx.Exec(...); err != nil {
            return err
        }
        if _, err := tx.Exec(...); err != nil {
            return err
        }
        return nil
    })
}

This approach keeps transaction code concise and ensures consistent handling. Note that the transaction handler uses recover() to intercept panics and initiates a rollback promptly. However, it should be emphasized that returning errors is preferred over panicking.

The above is the detailed content of How Can I Reliably Detect Database Transaction Commit or Rollback in Go's `database/sql` Package?. For more information, please follow other related articles on the PHP Chinese website!