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
- 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?
- Move the second insert outside the transaction. Does the duplicate still stop the first insert, and why not?
- 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.
-
Which calls form a transaction?
Answer
Begin, statements on the Tx, Commit or Rollback
The Tx carries the transaction.
-
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.
-
When does a constraint fail inside a transaction?
Answer
At the statement, not at the commit
The driver reports it from Exec.
-
What does a failed Tx do to later statements?
Answer
They return sql.ErrTxDone
The transaction is finished.
-
What does db.Prepare return?
Answer
A statement handle reusable with different arguments
The handle belongs to the pool.
-
When does Prepare cost more than it saves?
Answer
For a single call
Exec caches statements behind the scenes.
-
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.
-
What does BeginTx with a context add?
Answer
The transaction is rolled back when the context ends
It bounds the transaction by the request.
-
Why keep a transaction short?
Answer
It holds a connection and row locks while it is open
A long transaction blocks other writers.
-
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