Raw sql transactions with golang prepared statements

Question

I'm having trouble finding some examples that do three of the following things:

1) Allow raw sql transactions in golang.

2) Use prepared statements.

3) Rollback on query failures.

I would like to do something like this, but with prepared statements.

    stmt, stmt_err := db.Prepare(`
            BEGIN TRANSACTION;

            -- Insert record into first table.

            INSERT INTO table_1 (
                    thing_1,
                    whatever)
            VALUES($1,$2);

            -- Inert record into second table.

            INSERT INTO table_2 (
                    thing_2,
                    whatever)
            VALUES($3,$4);

            END TRANSACTION;
            `)
    if stmt_err != nil {
            return stmt_err
    }   
    res, res_err := stmt.Exec(
            thing_1,
            whatever,
            thing_2,
            whatever)

When I run this, I get this error: pq: cannot insert multiple commands into a prepared statement

What gives? Are ACID compliant transactions even possible in golang? I cannot find an example.

EDIT no examples here.

Guinna

Answer 1

Yes Go has a great implementation of sql transactions. We start the transaction with db.Begin and we can end it with tx.Commit if everything goes good or with tx.Rollback in case of error.

type Tx struct { }

Tx is an in-progress database transaction.

A transaction must end with a call to Commit or Rollback.

After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.

The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.

Also note that we prepare queries with the transaction variable tx.Prepare(...)

Your function may looks like this:

func doubleInsert(db *sql.DB) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }

    stmt, err := tx.Prepare(`INSERT INTO table_1 (thing_1, whatever)
                         VALUES($1,$2);`)
    if err != nil {
        tx.Rollback()
        return err
    }
    defer stmt.Close()

    if _, err := stmt.Exec(thing_1, whatever); err != nil {
        tx.Rollback() // return an error too, we may want to wrap them
        return err
    }

    stmt, err := tx.Prepare(`INSERT INTO table_2 (thing_2, whatever)
                         VALUES($1, $2);`)
    if err != nil {
        tx.Rollback()
        return err
    }
    defer stmt.Close()

    if _, err := stmt.Exec(thing_2, whatever); err != nil {
        tx.Rollback() // return an error too, we may want to wrap them
        return err
    }

    return tx.Commit()
}

I have a full example here

Magness

Really helpful post, thanks. But what I do not understand though, A transaction must end with a call to Commit or Rollback., when you return an error caused by tx.Prepare(some sql..) you do not perform a Commit or a Rollback. Why is that? Is the transaction in question properly closed in that scenario? Even though no changes have been made to the underlying Database, don't we still need to properly close the transaction? – Halibut

This code is not correct for Go1.4 or earlier version, tx.Commit() will release the connection associated with it back to the pool, which will happen before stmt.Close(), this could lead to concurrent access to the underlying connection, rendering the connection state inconsistent. as stated here: go-database-sql.org/prepared.html – Baccalaureate

@YandryPozo That doesn't sound right to me. As pointed out above, according to golang.org/pkg/database/sql/#Tx, a transaction must end in Rollback or Commit. Per the same doc, Tx is a transaction, so as soon as you have a Tx, you have a transaction. – Perique

@MarceloCantos you're right if we have a created transaction we have to finish it anyways. I just edited my answer, thanks for your catch! – Magness

@Halibut you right, I edited my answer, thanks for your comment! – Magness

Why use use braces to divide two actions? – Durbar

what are (thing_1, whatever) ?? – Socalled

@OlegzandrDenman the parameters passed in the original question. – Magness

Answer 2

I came up with a possible solution to rollback on any failure without any significant drawbacks. I am pretty new to Golang though, I could be wrong.

func CloseTransaction(tx *sql.Tx, commit *bool) {
  if *commit {
    log.Println("Commit sql transaction")
    if err := tx.Commit(); err != nil {
      log.Panic(err)
    }
  } else {
    log.Println("Rollback sql transcation")
    if err := tx.Rollback(); err != nil {
      log.Panic(err)
    }
  }
}

func MultipleSqlQuriesWithTx(db *sql.DB, .. /* some parameter(s) */)  (.. .. /* some named return parameter(s) */, err error) {
  tx, err := db.Begin()
  if err != nil {
    return
  }
  commitTx := false
  defer CloseTransaction(tx, &commitTx)

  // First sql query
  stmt, err := tx.Prepare(..) // some raw sql
  if err != nil {
    return
  }
  defer stmt.Close()

  res, err := stmt.Exec(..) // some var args
  if err != nil {
    return
  }

  // Second sql query
  stmt, err := tx.Prepare(..) // some raw sql
  if err != nil {
    return
  }
  defer stmt.Close()

  res, err := stmt.Exec(..) // some var args
  if err != nil {
    return
  }

  /*
    more tx sql statements and queries here
  */

  // success, commit and return result
  commitTx = true
  return
}

Halibut