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
How to Schedule a SQL Job in Microsoft Azure SQL Database
Return Only One Row from the Right-Most Table for Every Row in the Left-Most Table
Postgresql Not Ilike Clause Does Not Include Null String Values
Are Left Outer Joins Associative
How to Count the Number of Columns in a Table Using SQL
Copy Data Between Two Server Instances
How to Split the Results of a Select Query into Two Equal Halfs
Sequentially Number Rows by Keyed Group in SQL
Which Lock Hints Should I Use (T-Sql)
How to Search All Text Fields in a Db for Some Substring with T-Sql
Sqlite: Preventing Duplicate Rows
Fastest Way Merge Two SQLite Databases
Mysql: Which to Use When: Drop Table, Truncate Table, Delete from Table
Need a Tool to Automatically Indent and Format SQL Server Stored Procedures
How to Get Multiple Rows into One Line as a String