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.

CodePudding user response：

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 cleand 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
}

CodePudding user response：

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 .