database/sql Tx - detecting Commit or Rollback

sql go

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.

database/sql Tx - detecting Commit or Rollback

Related

Recent Posts