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

Question

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!

Answer 1

Multiple transactions

You can split your work in two transactions:

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 != nil {
        return err
    }
    return s.runInTransaction(ctx,func(tx *sqlx.Tx) error {
        // Point B: Do some more database work, which may return an error
    })
}

Answer 2

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.