Database Transactions

In this lesson, explore database transactions through one small Go program, and connect its output to the language rules behind it.

A transaction groups related database changes so they commit together or roll back. Begin with db.BeginTx, execute using the returned *sql.Tx, and call Commit only after every required operation succeeds.

Do not mix transaction statements with calls on the parent *sql.DB, because those calls may use a different connection. The runnable example illustrates rollback logic with local values; the supplemental code shows actual transaction APIs.

Follow this diagram before executing the example. Check where a value or decision changes what runs next.

Transaction body pattern

tx, err := db.BeginTx(ctx, nil)
if err != nil { return err }
defer tx.Rollback()
if _, err := tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID); err != nil { return err }
if _, err := tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID); err != nil { return err }
return tx.Commit()

Illustrative only: production money transfers also need sufficient-funds constraints, row-count checks, appropriate isolation/locking, and retry handling. See Executing transactions.

This example is a complete program. Save it as main.go or paste it into the Go Playground.

Go code

package main

import "fmt"

func main() {
	balanceA, balanceB := 100, 50
	amount := 30
	if amount > balanceA {
		fmt.Println("Transfer rejected")
		return
	}
	balanceA -= amount
	balanceB += amount
	fmt.Println("After transfer:", balanceA, balanceB)
}

Output

After transfer: 70 80

The application checks available funds before changing both local balances. In a real SQL transaction, database constraints and isolation must protect this rule against simultaneous transfers.

• Two assignments in memory are not a durable ACID transaction.
• On errors, roll back the transaction and preserve the first meaningful error.

Implement the transfer with a SQL transaction and test that an invalid transfer changes neither stored balance.

Tip: Edit the example above, predict the output, and run the modified code to test your reasoning.

You have practiced Database Transactions in a small, self-contained program. The important habit is to predict what runs, inspect the actual output and distinguish the language rule from the particular example.

Before continuing, make sure you can explain the program to someone who has not seen it, including the line that changes its result.