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?

Solution

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.

Comments

[Volker]

Not sure if I understand the problem. You have to end a transaction with either Commit or Rollback so you know what you did but you do not want to remember this in an extra variable? You could wrap Tx and the bool in your own RememberingTx, this would reduce line count a bit. Regarding the GC question: It doesn't matter if you set to nil or not: Memory will be reclaimed once no reference is left to it. So: Yes you can have var tx *Tx; snip; if cond { tx.Commit; tx=nil } else { tx.Rollback}; snip; if tx==nil { was commited } else { was rollbacked} but it feels ugly.

[Brian Oh]

That's sort of what it's about, but there is a deferred func that does a rollback if Tx is not nil. Once a transaction is committed, the Tx can not be used anyway, so I plan to set it to nil. It's not pretty, however attempting a rollback and testing the error message is not pretty either. The problem is that AFAIK there is no way to test if the transaction is "done" from Tx. I'm not sure why it was done that way, perhaps performance.

[splinter123]

nice answer! I think you missed a "return nil" towards the end of your second doSomething() implementation.

[mirage]

Luke, how and when does err get evaluated? As per the documentation, "err" should get it's value when it's first declared in the defer call. So this is actually a bit confusing for me, since the value for "err", as used in defer, seems to change.

[Luke]

err is declared before the defer via := (colon equal). The anon func captures it. Defer is called just before the value is returned. That allows it to be set. When a panic occurs it is recovered, turned into an error, then returned. If an error happens in any way a roll back happens. Finally a commit happens if there are no errors and err (currently nil) is set to Commits return value in case it errors.

[Luke]

@ChadGilbert while the recent edit makes it more clear what's going on to the reader, just wanted to let you know it worked before. return sets the err variable. See play.golang.org/p/66wWTJl0pH

[Chad Gilbert]

@Luke - ah, got it. I misunderstood the named return variable. Thanks for the clarification.

[Dave C]

Turning all underlying panics into plain errors is, at best, incredibly foolhardy.

[Luke]

@DaveC I agree. I updated the example to re-throw the panic after doing a Rollback. More often than not a panic is due to a programming error, and should be corrected. The http server swallows panics as well, but it ends up tearing down the entire request. In this case is probably best to handle the panic elsewhere.

[Tiega]

@DaveC I really wonder why that is. I'm new to go and looking at error handling at the moment. In my understanding there are not a lot of cases where panic seems viable in a webapp i usually send a response, even if something went wrong, why should this be terminated. Please tell me why you think its a bad design/habit?

[Ratatouille]

@Luke Help me understand what happens if the Rollback or Commit statement panic or raise error how to cascade that error back.

[Luke]

@Ratatouille The Rollback and Commit statements should not panic. The recover() statement is meant to catch panics caused by code within the transaction. Ideally you would never panic - you would return an error, which would also cause a Rollback. If neither Rollback or Commit is called and the transaction is never resolved (a disconnect happens; or transaction is garbage collected) the db will perform a rollback. Commit returns an error if there is one (you can change err within defer before the return finishes). Rollback does not overwrite the existing error that caused it.

[Frank AK]

What's the Service type? Where does it define?