Database/SQL Tx - Detecting Commit or Rollback

Do I need to explicitly rollback a transaction?

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 .

Does ROLLBACK TRANSACTION imply a commit if not rolled-back

No. The transaction will remain open and uncommitted until it is committed, which will probably eventually cause blocking on your database.

If transactions are nested, rollback will rollback to the beginning of the outer transaction, not the inner transaction - See http://msdn.microsoft.com/en-us/library/ms181299.aspx

I would consider a structure something like

 begin try
begin tran
-- do query
commit tran
end try
begin catch
if @@trancount>0
begin
rollback
end
-- handle error here
end catch

Do I need to call rollback if I never commit?

It should roll back on close of connection. Emphasis on should for a reason :-)

Proper transaction and error handling should have you always commit when the conditions for commit are met and rollback when they aren't. I think it is a great habit to always commit or rollback when done and not rely on disconnect/etc. All it takes is one mistake or incorrectly/not closed session to create a blocking chain nightmare for all :-)

Why defer a Rollback?

The example is a little bit misleading. It uses log.Fatal(err) for error handling. You wouldn't normally do that, and instead return err. So the deferred rollback is there to ensure that the transaction is rolled back in case of an early return.

Golang: Why sql.Tx does not implement driver.Tx

Your implementation needs to match exactly, so Store() must accept a driver.TX type. Not only a *sql.Tx.

Because sql.Tx implements the driver.Tx interface it can be provided as the input.

import (
"database/sql"
"database/sql/driver"
)

func main() {
var myDB store = db{}
sqlTx := &sql.Tx{}
myDB.Store(sqlTx)
}

type store interface {
Store(tx driver.Tx)
}

type db struct{}

func (db) Store(tx driver.Tx) {}


Related Topics



Leave a reply



Submit