Transactions and prepared statements

A transaction is a group of statements that either all take effect or none do. database/sql gives it four calls: Begin, the statements on the returned *sql.Tx, and Commit or Rollback. The driver here is modernc.org/sqlite v1.62.0, fetched by the module proxy; the programs ran in a module with that requirement.

Begin, Commit and Rollback

The first transaction inserts two rows and commits; the second inserts one and rolls back. The count at the end is two, which is the whole contract in one number. A rollback after a successful commit, or a commit after a rollback, returns an error; a deferred rollback after a commit is the idiom that makes both paths safe.

package main import ( "database/sql" "fmt" _ "modernc.org/sqlite" ) func main() { db, err := sql.Open("sqlite", ":memory:") if err != nil { panic(err) } defer db.Close() db.SetMaxOpenConns(1) if _, err := db.Exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`); err != nil { panic(err) } tx, err := db.Begin() if err != nil { panic(err) } if _, err := tx.Exec(`INSERT INTO notes (title) VALUES (?)`, "first"); err != nil { panic(err) } if _, err := tx.Exec(`INSERT INTO notes (title) VALUES (?)`, "second"); err != nil { panic(err) } if err := tx.Commit(); err != nil { panic(err) } tx, err = db.Begin() if err != nil { panic(err) } if _, err := tx.Exec(`INSERT INTO notes (title) VALUES (?)`, "third"); err != nil { panic(err) } if err := tx.Rollback(); err != nil { panic(err) } var count int if err := db.QueryRow(`SELECT COUNT(*) FROM notes`).Scan(&count); err != nil { panic(err) } fmt.Println("rows after commit and rollback:", count) }

go build -o app . && ./app

rows after commit and rollback: 2

The third row never existed outside the second transaction. A program that returns early on an error does a deferred tx.Rollback() right after Begin: after a commit the rollback is a no-op error that is ignored, and on every error path the transaction is released instead of holding a connection.

A failed statement rolls back

A constraint fires inside the transaction, not at the commit. The table below keeps titles unique, so the second insert of the same title fails with the driver's error and the rollback discards the transaction. The row count stays one, and a program that had ignored the error would have committed nothing and reported success.

package main import ( "database/sql" "fmt" _ "modernc.org/sqlite" ) func main() { db, err := sql.Open("sqlite", ":memory:") if err != nil { panic(err) } defer db.Close() db.SetMaxOpenConns(1) if _, err := db.Exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE)`); err != nil { panic(err) } if _, err := db.Exec(`INSERT INTO notes (title) VALUES (?)`, "buy milk"); err != nil { panic(err) } tx, err := db.Begin() if err != nil { panic(err) } _, err = tx.Exec(`INSERT INTO notes (title) VALUES (?)`, "buy milk") fmt.Println("second insert error:", err) if err := tx.Rollback(); err != nil { panic(err) } var count int if err := db.QueryRow(`SELECT COUNT(*) FROM notes`).Scan(&count); err != nil { panic(err) } fmt.Println("rows after rollback:", count) }

go build -o app . && ./app

second insert error: constraint failed: UNIQUE constraint failed: notes.title (2067)
rows after rollback: 1

The error text and the code come from SQLite. A program maps the class of the error to a 409 conflict and a duplicate to a 400, and a transaction that failed stays failed: every later statement on the same *sql.Tx returns sql.ErrTxDone.

Prepared statements

Prepare sends the statement to the database once and returns a handle that can run it many times with different arguments. The placeholders are still parameters, and the driver reuses the parsed statement. For a loop of inserts the saving is real; for one call the Prepare round trip costs more than it saves, and Exec with the arguments prepares and caches behind the scenes.

package main import ( "database/sql" "fmt" _ "modernc.org/sqlite" ) func main() { db, err := sql.Open("sqlite", ":memory:") if err != nil { panic(err) } defer db.Close() db.SetMaxOpenConns(1) if _, err := db.Exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`); err != nil { panic(err) } stmt, err := db.Prepare(`INSERT INTO notes (title) VALUES (?)`) if err != nil { panic(err) } defer stmt.Close() for _, title := range []string{"one", "two", "three"} { if _, err := stmt.Exec(title); err != nil { panic(err) } } var count int if err := db.QueryRow(`SELECT COUNT(*) FROM notes`).Scan(&count); err != nil { panic(err) } fmt.Println("inserted with one statement:", count) }

go build -o app . && ./app

inserted with one statement: 3

The handle is safe for concurrent use and belongs to the pool, not to one connection, so a prepared statement in a service is prepared once at start-up. A statement prepared inside a transaction belongs to that transaction and is gone with it, which is why tx.Prepare returns its own handle.

Context on every call

Every method has a Context variant, and a service uses it: the context of the HTTP request bounds the query, so a client that gave up stops the database work too. The program below runs two statements under a live context and one under a cancelled one, and the cancelled call fails without touching the table.

package main import ( "context" "database/sql" "fmt" "time" _ "modernc.org/sqlite" ) func main() { db, err := sql.Open("sqlite", ":memory:") if err != nil { panic(err) } defer db.Close() db.SetMaxOpenConns(1) ctx, cancel := context.WithTimeout(context.Background(), time.Second) defer cancel() if _, err := db.ExecContext(ctx, `CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`); err != nil { panic(err) } if _, err := db.ExecContext(ctx, `INSERT INTO notes (title) VALUES (?)`, "from context"); err != nil { panic(err) } var title string err = db.QueryRowContext(ctx, `SELECT title FROM notes WHERE id = 1`).Scan(&title) fmt.Println("title:", title, "err:", err) cancelled, stop := context.WithCancel(context.Background()) stop() _, err = db.ExecContext(cancelled, `INSERT INTO notes (title) VALUES (?)`, "never") fmt.Println("cancelled insert failed:", err != nil) }

go build -o app . && ./app

title: from context err: <nil>
cancelled insert failed: true

The cancelled call never reached the database, so the table still has one row. A transaction also takes a context: db.BeginTx(ctx, nil) rolls the transaction back when the context ends, which is the difference between a request that times out cleanly and one that leaves a lock behind.

The transaction rules

Four rules cover most of the mistakes. A transaction is short, because it holds a connection and the locks of the rows it touched. Every error path rolls back. The rollback is deferred immediately after Begin, so a panic also releases it. And a transaction is not shared between goroutines: *sql.Tx belongs to the goroutine that began it, and a concurrent use is a race on the connection.

A retry is the fifth rule and the hardest: a transaction that fails on a deadlock or a serialization conflict is safe to run again, and a program that retries a whole HTTP handler has to make sure the side effects outside the database are idempotent too. The capstone service keeps its writes in one transaction and its retries at the client, which is the simpler split.

Exercises

  1. Remove the rollback from the failed-insert program and read the row count. Why is the transaction still open, and what does the deferred rollback prevent?
  2. Move the second insert outside the transaction. Does the duplicate still stop the first insert, and why not?
  3. Run the prepared statement loop inside a transaction and compare the time with the same loop outside. What does the transaction save, and what does it cost?

Questions

A new set of questions in a new order every time. Choose one answer for each, then check.

  1. Which calls form a transaction?

    Answer

    Begin, statements on the Tx, Commit or Rollback

    The Tx carries the transaction.

  2. Why defer a Rollback right after Begin?

    Answer

    It releases the transaction on every error path and after a panic

    After a commit the rollback is a harmless error.

  3. When does a constraint fail inside a transaction?

    Answer

    At the statement, not at the commit

    The driver reports it from Exec.

  4. What does a failed Tx do to later statements?

    Answer

    They return sql.ErrTxDone

    The transaction is finished.

  5. What does db.Prepare return?

    Answer

    A statement handle reusable with different arguments

    The handle belongs to the pool.

  6. When does Prepare cost more than it saves?

    Answer

    For a single call

    Exec caches statements behind the scenes.

  7. Why use ExecContext in a service?

    Answer

    The request context stops the query when the client goes away

    A query without a context outlives its request.

  8. What does BeginTx with a context add?

    Answer

    The transaction is rolled back when the context ends

    It bounds the transaction by the request.

  9. Why keep a transaction short?

    Answer

    It holds a connection and row locks while it is open

    A long transaction blocks other writers.

  10. Can two goroutines share one *sql.Tx?

    Answer

    No, the transaction belongs to one goroutine

    A shared Tx races on its connection.

The code on this page was run with go 1.26.10 on Alpine Linux 3.24.3 (x86_64) in a Docker container.

Article author: Arthur Isaev