database/sql Tx - detecting Commit or Rollback

Question

Using the database/sql and driver packages and Tx, it is not possible it appears to detect whether a transaction has been committed or rolled-back without attempting another and receiving an error as a result, and then examining the error to determine the type of error. I would like to be able to determine from the Tx object whether committed or not. Sure, I can define and set another variable in the function that uses Tx, but I have quite a number of them, and it is times 2 every time (variable and assignment). I also have a deferred function to do a Rollback if needed, and it needs to be passed the bool variable.

Would it be acceptable to set the Tx variable to nil after a Commit or Rollback, and will the GC recover any memory, or is that a no-no, or is there a better alternative?

Answer 1

You want to make sure that Begin(), Commit(), and Rollback() appear within the same function. It makes transactions easier to track, and lets you ensure they are closed properly by using a defer.

Here is an example of this, which does a Commit or Rollback depending on whether an error is returned:

func (s Service) DoSomething() (err error) {
    tx, err := s.db.Begin()
    if err != nil {
        return
    }
    defer func() {
        if err != nil {
            tx.Rollback()
            return
        }
        err = tx.Commit()
    }()
    if _, err = tx.Exec(...); err != nil {
        return
    }
    if _, err = tx.Exec(...); err != nil {
        return
    }
    // ...
    return
}

This can get a bit repetitive. Another way of doing this is by wrapping your transactions using a transaction handler:

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
}

Using the transaction hander above, I can do this:

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 keeps my transactions succinct and ensures by transactions are properly handled.

In my transaction handler I use recover() to catch panics to ensure a Rollback happens right away. I re-throw the panic to allow my code to catch it if a panic is expected. Under normal circumstances a panic should not occur. Errors should be returned instead.

If we did not handle panics the transaction would be rolled back eventually. A non-commited transaction gets rolled back by the database when the client disconnects or when the transaction gets garbage collected. However, waiting for the transaction to resolve on its own could cause other (undefined) issues. So it's better to resolve it as quickly as possible.

One thing that may not be immediately clear is that defer can change the return value within a closure if the return variable is captured. In the transaction handler the transaction is committed when err (the return value) is nil. The call to Commit can also return an error, so we set its return to err with err = tx.Commit(). We do not do the same with Rollback because err is non-nil and we do not want to overwrite the existing error.