Background
I am using the github.com/jmoiron/sqlx golang package with a Postgres database.
I have the following wrapper function to run SQL code in a transaction:
func (s *postgresStore) runInTransaction(ctx context.Context, fn func(*sqlx.Tx) error) error {
tx, err := s.db.Beginx()
if err != nil {
return err
}
defer func() {
if err != nil {
tx.Rollback()
return
}
err = tx.Commit()
}()
err = fn(tx)
return err
}
Given this, consider the following code:
func (s *store) SampleFunc(ctx context.Context) error {
err := s.runInTransaction(ctx,func(tx *sqlx.Tx) error {
// Point A: Do some database work
if err := tx.Commit(); err != nil {
return err
}
// Point B: Do some more database work, which may return an error
})
}
Desired behavior
- If there is an error at Point A, then the transaction should have done zero work
- If there is an error at Point B, then the transaction should still have completed the work at Point A.
Problem with current code
The code does not work as intended at the moment, because I am committing the transaction twice (once in runInTransaction, once in SampleFunc).
A Possible Solution
Where I commit the transaction, I could instead run something like tx.Exec("SAVEPOINT my_savepoint"), then defer tx.Exec("ROLLBACK TO SAVEPOINT my_savepoint")
After the code at Point B, I could run: tx.Exec("RELEASE SAVEPOINT my_savepoint")
So, if the code at Point B runs without error, I will fail to ROLLBACK to my savepoint.
Problems with Possible Solution
I'm not sure if using savepoints will mess with the database/sql package's behavior. Also, my solution seems a bit messy -- surely there is a cleaner way to do this!
