Ismail Khan Ismail Khan - 5 months ago 17
SQL Question

What is an easy way to commit work half way through a transaction, but then continue to

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")
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!

Answer Source

I had the problem alike: I had a lots of steps in one transaction. After starting transaction:

  • BEGIN
  • In loop:

    • SAVEPOINT s1
    • Some actions ....
    • If I get an error: ROLLBACK TO SAVEPOINT s1
    • If OK go to next step
  • Finally COMMIT

This approach gives me ability to perform all steps one-by-one. If some steps got failed I can throw away only them, keeping others. And finally commit all "good" work.