Do I need to explicitly rollback a transaction?

Question

I would like to know how Go handles failed DB transaction. My code looks like:

func assert(e interface{}) {
    if e != nil {
        panic(e)
    }
}

//the caller will handle panics
func SomeDBOperation(db *sql.DB) {
    tx, err := db.Begin()
    assert(err)
    defer func() {
        if e := recover(); e != nil {
            tx.Rollback()
            panic(e)
        }
        assert(tx.Commit())
    }()
    // some code that can possibly panic...
}

Can I simplify the error checking like this:

func SomeDBOperation(db *sql.DB) {
    tx, err := db.Begin()
    assert(err)
    defer func() { assert(tx.Commit()) }()
    // some code that can possibly panic...
}

BTW, I am using SQLite, if any answer is db-specific, I would also like to know the behavior with MySQL.

Jonathan Hall · Answer 1 · 2021-12-17T12:42:56.903

By default, any database error will automatically cancel and rollback the transaction. That's what transactions are for. So strictly speaking, in the case of a database error (i.e. foreign key violation or something), there's no need to rollback the transaction yourself.

However, you should always defer a rollback immediately after creating the transaction. This is so that if there are any errors not related to the database, that the transaction is rolled back and cleaned up. In such a case, rolling back a transaction that has already been aborted will be a no-op, so harmless.

The way this looks in code is something like this:

func SomeDBOperation(db *sql.DB) error {
    txn, err := db.Begin()
    if err != nil {
        return fmt.Errorf("failed to start transaction: %w", err)
    }
    defer txn.Rollback() // nolint:errcheck
    /* ... whatever other logic and DB operations you care about */
    return nil
}

score -1 · Accepted Answer · answered Dec 16 '21 at 04:47

-1

It is important to rollback the tx if there is an error while executing any query, otherwise it is still running and holding locks. Check out this post .

answered Dec 16 '21 at 04:47

shubham_asati

623
5
14

I know this. What I am asking is actually a Go question, not DB question. i.e. I wonder if the `tx` will be rolled back on the exit of the function "automatically", if no commit is executed. :-) – xrfang Dec 16 '21 at 05:42
1

A non committed transaction gets rolled back automatically once the client disconnects or the transaction is garbage collected. More info https://stackoverflow.com/a/23502629/10984192 . – shubham_asati Dec 16 '21 at 07:40

Do I need to explicitly rollback a transaction?

2 Answers2