You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
66 lines
2.5 KiB
Go
66 lines
2.5 KiB
Go
// This file simply illustrates the use of transactions when performing operations on your sql db.
|
|
package model
|
|
|
|
import "database/sql"
|
|
|
|
type ExampleModel struct {
|
|
DB *sql.DB
|
|
// Prepared statements
|
|
//
|
|
// Prepared statements exist on database connections. Statement objects will therefore attempt to reuse the connection
|
|
// object from the connection pool that the statement was created on. If the connection was Closed or in use, it will
|
|
// be re-prepared on a new connection. This can increase load, create more connections than expected. Etc. Really its
|
|
// and optimization that you may not need to start looking at. When you do you have to look at load test data to get an
|
|
// idea for how it actually behaves.
|
|
//
|
|
// Another pattern is avoid recreated prepared statements on each invocation and instead attach them
|
|
// to the service instead. This doesn't really work well with transactions which have thier own tx.Prepare
|
|
// method
|
|
// InsertStmt *sql.Stmt
|
|
}
|
|
|
|
// func NewExampleModel(db *sql.DB) (*ExampleModel, error) {
|
|
// insertStmt, err := db.Prepare("INSERT INTO example (message, thought) VALUES (?, ?)")
|
|
// if err != nil {
|
|
// return nil, err
|
|
// }
|
|
// return &ExampleModel{DB: db, InsertStmt: insertStmt}, nil
|
|
// }
|
|
|
|
func (m *ExampleModel) ExampleTransaction() error {
|
|
// Calling the Begin() method on the connection pool creates a new sql.Tx
|
|
// object, which represents the in-progress database transaction.
|
|
tx, err := m.DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// Defer a call to tx.Rollback() to ensure it is always called before the
|
|
// function returns. If the transaction succeeds it will be already be
|
|
// committed by the time tx.Rollback() is called, making tx.Rollback() a
|
|
// no-op. Otherwise, in the event of an error, tx.Rollback() will rollback
|
|
// the changes before the function returns.
|
|
defer tx.Rollback()
|
|
|
|
// Call Exec() on the transaction, passing in your statement and any
|
|
// parameters. It's important to notice that tx.Exec() is called on the
|
|
// transaction object just created, NOT the connection pool. Although we're
|
|
// using tx.Exec() here you can also use tx.Query() and tx.QueryRow() in
|
|
// exactly the same way.
|
|
_, err = tx.Exec("INSERT INTO ...")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// Carry out another transaction in exactly the same way.
|
|
_, err = tx.Exec("UPDATE ...")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// If there are no errors, the statements in the transaction can be committed
|
|
// to the database with the tx.Commit() method.
|
|
err = tx.Commit()
|
|
return err
|
|
}
|