Understanding Two-Phase Commit (2PC) by Building One in Go

Introduction

Imagine sending 100 from Alice to Bob.

If both accounts are in one database, this is easy: start a transaction, update both balances, and commit. But this example puts Alice in MySQL and Bob in PostgreSQL. One database cannot finish—or undo—the other database's work.

That is the problem two-phase commit, usually called 2PC, tries to solve.

This article follows a small Go program that performs the transfer and then crashes on purpose. The two crashes happen almost at the same moment, but recovery gives them opposite answers. That small difference is the easiest way to understand 2PC.

The promise we need

At the start:

Alice in MySQL:      500
Bob in PostgreSQL:   100

After a successful transfer:

Alice in MySQL:      400
Bob in PostgreSQL:   200

If anything goes wrong, both balances should stay as they were. We never want this:

Alice in MySQL:      400
Bob in PostgreSQL:   100

That would mean Alice lost money and Bob never received it.

Two-phase commit in simple words

2PC has one person in charge—the coordinator—and the databases doing the work.

First, the coordinator asks each database, "Can you do your part?" The databases save their work, hold on to it, and answer yes or no. This is the prepare phase.

If everyone says yes, the coordinator decides to commit. If anyone says no, it decides to undo everything. This is the decision phase.

The important thing is that a yes vote does not mean the database has committed. It means:

"I have saved my work, I am keeping the row locked, and I will wait for your final answer."

Where XA fits in

2PC is the idea. XA is a standard set of commands that helps a coordinator use that idea with different database systems.

MySQL calls its commands XA PREPARE and XA COMMIT. PostgreSQL uses PREPARE TRANSACTION and COMMIT PREPARED. Different names, same story:

What needs to happen	MySQL	PostgreSQL
Start work	XA START	BEGIN
Say “I am ready”	XA PREPARE	PREPARE TRANSACTION
Finish successfully	XA COMMIT	COMMIT PREPARED
Undo the work	XA ROLLBACK	ROLLBACK PREPARED

You do not need to memorize those commands to understand the example. The Go program uses the right command for each database and treats both answers in the same way.

Running the example

The program starts MySQL and PostgreSQL locally, creates the two accounts, and runs the normal transfer plus both crash tests.

docker run -d --name xa-mysql -e MYSQL_ROOT_PASSWORD=xa -e MYSQL_DATABASE=bank \
  -p 13306:3306 mysql:8
docker run -d --name xa-pg -e POSTGRES_PASSWORD=xa -e POSTGRES_DB=bank \
  -p 15432:5432 postgres:16 -c max_prepared_transactions=20
go run .

The PostgreSQL setting is needed because prepared transactions are turned off by default in many installations.

Reading the Go program

Setup

connect() opens one connection pool for MySQL and one for PostgreSQL. reset() starts every run with Alice at 500 and Bob at 100.

The transfer is given one shared name, such as tx-ok. That name lets recovery find the pieces of the same transfer later.

Doing the transfer

Inside transfer(), the program first makes the two local changes:

UPDATE accounts SET balance = balance - ? WHERE id='alice'
UPDATE accounts SET balance = balance + $1 WHERE id='bob'

The changes are not visible as a completed transfer yet. They are still waiting inside each database.

Asking both databases to prepare

Next, the coordinator asks both databases to get ready:

XA PREPARE 'tx','my',1
PREPARE TRANSACTION 'tx'

When those calls work, MySQL and PostgreSQL have both said yes. They have saved their changes and are waiting. They also keep locks on the affected rows, so nobody else can change Alice or Bob in a conflicting way.

The one line that matters most

After both yes votes, the coordinator writes its answer to a small file named decisions.log:

if err := decide(gtrid, "commit"); err != nil {
    rollback(my, pg, gtrid)
    return err
}

Inside decide(), the program calls f.Sync(). That makes sure the decision really reaches disk.

This is the real commit point. Not the later database commands. Once commit is safely written to the log, the coordinator has made its choice forever. If it crashes after this, recovery must finish the commit.

Two crashes that explain everything

The program crashes in two places: once just before the log is written, and once just after. Only one f.Sync() sits between them.

Crash before the decision is saved

Both databases have prepared their work. But the coordinator crashes before writing commit to decisions.log.

When the program comes back, it asks the databases, "Do you still have unfinished work?" They both say yes. Then it checks the log. There is no commit decision there.

So it rolls both sides back:

mysql:     XA ROLLBACK
postgres:  ROLLBACK PREPARED

This is called presumed abort. In everyday language: if the coordinator did not save a decision to commit, assume the transfer did not happen.

Crash after the decision is saved

Now move the crash one line later. Both databases prepare their work. The coordinator writes commit to the log and safely syncs it to disk. Then it crashes before it can tell either database.

When it starts again, the balances may still look unchanged because the databases are still waiting. But the log says commit, so recovery has only one safe choice:

mysql:     XA COMMIT
postgres:  COMMIT PREPARED

The coordinator is not deciding again. It is simply delivering the decision it already made.

How recovery works

recoverInDoubt() is the cleanup worker in this example. Its rule is short:

Does the log say commit?

Yes -> commit the unfinished work.
No  -> roll it back.

It asks MySQL about unfinished XA transactions and PostgreSQL about unfinished prepared transactions. Then it checks decisions.log and gives both databases the same answer.

This works because the code always saves the commit decision before it starts sending commit commands to the databases.

The downside: other work can wait

While a database is prepared, it keeps locks. In the crash-after test, Alice's row is still locked while the coordinator is down.

showBlocking() opens another connection and tries to update Alice. It waits and then gets this error:

Error 1205 (HY000): Lock wait timeout exceeded

That is the real cost of 2PC. The databases are safe, but they cannot decide by themselves while they wait for the coordinator. This is why people call 2PC a blocking protocol.

Should you use it?

2PC is useful when a small number of dependable systems truly need one all-or-nothing decision. It is not the usual choice for every application.

Many systems use a transactional outbox or a saga instead. Those patterns are often easier to run because they avoid keeping locks across systems, but they make a different trade-off: they do not give the same single global commit.

The one thing to remember

A database saying yes has not committed. It has promised to wait. The transfer becomes committed when the coordinator safely records its decision.

In this program, one call to f.Sync() separates a transfer that recovery must roll back from a transfer that recovery must commit.

Complete main.go

The following source is embedded verbatim from the demo.

// One global transaction across two different databases: 100 leaves alice in
// MySQL and arrives at bob in Postgres, atomically. Then we kill the coordinator
// at the worst possible moment and let recovery clean up.
//
//  docker run -d --name xa-mysql -e MYSQL_ROOT_PASSWORD=xa -e MYSQL_DATABASE=bank \
//    -p 13306:3306 mysql:8
//  docker run -d --name xa-pg -e POSTGRES_PASSWORD=xa -e POSTGRES_DB=bank \
//    -p 15432:5432 postgres:16 -c max_prepared_transactions=20
//  go run .
package main

import (
    "bufio"
    "context"
    "database/sql"
    "errors"
    "fmt"
    "log"
    "os"
    "strings"

    _ "github.com/go-sql-driver/mysql"
    _ "github.com/lib/pq"
)

const (
    mysqlDSN = "root:xa@tcp(127.0.0.1:13306)/bank"
    pgDSN    = "postgres://postgres:xa@127.0.0.1:15432/bank?sslmode=disable"

    // The coordinator's durable decision log. This file is the source of truth
    // for whether a global transaction committed — not the databases.
    decisionLog = "decisions.log"
)

var errCrashed = errors.New("coordinator crashed")

var ctx = context.Background()

func main() {
    my, pg := connect()
    defer my.Close()
    defer pg.Close()
    reset(my, pg)

    fmt.Println("\n=== 1. a normal cross-database commit ===")
    balances(my, pg)
    if err := transfer(my, pg, "tx-ok", 100, crashNever); err != nil {
        log.Fatal(err)
    }
    balances(my, pg)

    fmt.Println("\n=== 2. the coordinator dies just AFTER the commit point ===")
    fmt.Println("   transfer returned:", transfer(my, pg, "tx-after", 100, crashAfter))
    fmt.Println("   the transfer is invisible — balances unchanged from step 1:")
    balances(my, pg)
    fmt.Println("   ...but both databases hold locks, so unrelated writes stall:")
    showBlocking(my)

    fmt.Println("\n=== 3. recovery must COMMIT it: the decision was already made ===")
    recoverInDoubt(my, pg)
    balances(my, pg)

    fmt.Println("\n=== 4. now the coordinator dies just BEFORE the commit point ===")
    fmt.Println("   same two YES votes, but nothing was written down.")
    fmt.Println("   transfer returned:", transfer(my, pg, "tx-before", 100, crashBefore))

    fmt.Println("\n=== 5. recovery must ROLL BACK: no decision means it never committed ===")
    recoverInDoubt(my, pg)
    balances(my, pg)
    fmt.Println("\n   Same votes, one instant apart, opposite outcomes. That's 2PC.")
}

// Where to kill the coordinator. The two crash points sit on either side of a
// single fsync, and they must lead to opposite recoveries.
type crashPoint int

const (
    crashNever crashPoint = iota
    crashBefore
    crashAfter
)

// transfer runs the full two-phase commit.
func transfer(my, pg *sql.DB, gtrid string, amount int, crash crashPoint) error {
    // Each branch of the transaction needs its own dedicated connection.
    myConn, err := my.Conn(ctx)
    if err != nil {
        return err
    }
    defer myConn.Close()
    pgConn, err := pg.Conn(ctx)
    if err != nil {
        return err
    }
    defer pgConn.Close()

    // ---- do the work, inside a branch on each database ----
    // MySQL speaks the XA dialect: 'gtrid','branch-qualifier',format-id.
    xid := fmt.Sprintf("'%s','my',1", gtrid)
    if err := exec(myConn, "XA START "+xid); err != nil {
        return err
    }
    if err := exec(myConn, "UPDATE accounts SET balance = balance - ? WHERE id='alice'", amount); err != nil {
        return err
    }
    // XA END says "no more work on this branch".
    if err := exec(myConn, "XA END "+xid); err != nil {
        return err
    }

    // Postgres has no XA API at all — just the same two phases as plain SQL,
    // identified by a single string instead of a structured XID.
    if err := exec(pgConn, "BEGIN"); err != nil {
        return err
    }
    if err := exec(pgConn, "UPDATE accounts SET balance = balance + $1 WHERE id='bob'", amount); err != nil {
        return err
    }

    // ---- PHASE 1: ask both databases to vote ----
    // A successful prepare means: "my changes are on disk, I've kept my locks,
    // and I will do whatever you say next — even if I crash in between."
    if err := exec(myConn, "XA PREPARE "+xid); err != nil {
        fmt.Println("   mysql voted NO:", err)
        rollback(my, pg, gtrid)
        return err
    }
    fmt.Println("   phase 1: XA PREPARE           -> mysql voted YES")

    if err := exec(pgConn, "PREPARE TRANSACTION '"+gtrid+"'"); err != nil {
        fmt.Println("   postgres voted NO:", err)
        rollback(my, pg, gtrid)
        return err
    }
    fmt.Println("   phase 1: PREPARE TRANSACTION  -> postgres voted YES")

    if crash == crashBefore {
        return errCrashed
    }

    // ---- THE COMMIT POINT ----
    // The transaction commits the instant this write is durable, even though
    // neither database knows it yet. Before this line, recovery is free to
    // abort. After it, recovery MUST commit. Everything below is bookkeeping.
    if err := decide(gtrid, "commit"); err != nil {
        rollback(my, pg, gtrid)
        return err
    }
    fmt.Printf("   COMMIT POINT: %q fsynced to %s\n", gtrid+" commit", decisionLog)

    if crash == crashAfter {
        return errCrashed
    }

    // ---- PHASE 2: deliver the news ----
    // A prepared transaction belongs to the server, not to the session that
    // created it, so these can run on any connection — or after a reboot.
    if err := exec(my, "XA COMMIT "+xid); err != nil {
        return err
    }
    fmt.Println("   phase 2: XA COMMIT            -> mysql done")
    if err := exec(pg, "COMMIT PREPARED '"+gtrid+"'"); err != nil {
        return err
    }
    fmt.Println("   phase 2: COMMIT PREPARED      -> postgres done")
    return decide(gtrid, "forget")
}

// recoverInDoubt asks each database what it has prepared but never resolved,
// then looks up the verdict. The rule is asymmetric, and that asymmetry is the
// whole protocol:
//
//  "commit" in the log -> commit the branch, retrying until it succeeds
//  nothing in the log  -> roll it back ("presumed abort")
//
// It is safe because the decision is always logged before phase 2, so a
// prepared branch with no recorded decision cannot have committed anywhere.
func recoverInDoubt(my, pg *sql.DB) {
    decisions := decisions()

    // MySQL: XA RECOVER returns formatID, gtrid_length, bqual_length, data.
    rows, err := my.Query("XA RECOVER")
    if err != nil {
        log.Fatal(err)
    }
    var myPending []string
    for rows.Next() {
        var format, gtridLen, bqualLen int
        var data string
        if err := rows.Scan(&format, &gtridLen, &bqualLen, &data); err != nil {
            log.Fatal(err)
        }
        myPending = append(myPending, data[:gtridLen])
    }
    rows.Close()

    for _, gtrid := range myPending {
        fmt.Printf("   mysql is in doubt about %q; decision log says %q\n", gtrid, decisions[gtrid])
        xid := fmt.Sprintf("'%s','my',1", gtrid)
        if decisions[gtrid] == "commit" {
            must(exec(my, "XA COMMIT "+xid))
            fmt.Println("     -> XA COMMIT")
        } else {
            must(exec(my, "XA ROLLBACK "+xid))
            fmt.Println("     -> XA ROLLBACK (presumed abort)")
        }
    }

    // Postgres: the same question, asked of pg_prepared_xacts.
    rows, err = pg.Query("SELECT gid FROM pg_prepared_xacts")
    if err != nil {
        log.Fatal(err)
    }
    var pgPending []string
    for rows.Next() {
        var gid string
        if err := rows.Scan(&gid); err != nil {
            log.Fatal(err)
        }
        pgPending = append(pgPending, gid)
    }
    rows.Close()

    for _, gtrid := range pgPending {
        fmt.Printf("   postgres is in doubt about %q; decision log says %q\n", gtrid, decisions[gtrid])
        if decisions[gtrid] == "commit" {
            must(exec(pg, "COMMIT PREPARED '"+gtrid+"'"))
            fmt.Println("     -> COMMIT PREPARED")
        } else {
            must(exec(pg, "ROLLBACK PREPARED '"+gtrid+"'"))
            fmt.Println("     -> ROLLBACK PREPARED (presumed abort)")
        }
    }
}

// rollback abandons a transaction on both databases, at any stage.
func rollback(my, pg *sql.DB, gtrid string) {
    _ = exec(my, fmt.Sprintf("XA ROLLBACK '%s','my',1", gtrid))
    _ = exec(pg, "ROLLBACK PREPARED '"+gtrid+"'")
    _ = decide(gtrid, "abort")
}

// ---------------------------------------------------------------------------
// the coordinator's decision log
// ---------------------------------------------------------------------------

// decide appends a verdict and fsyncs it. The fsync is the entire point: an
// unflushed decision is not a decision.
func decide(gtrid, verdict string) error {
    f, err := os.OpenFile(decisionLog, os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0o600)
    if err != nil {
        return err
    }
    defer f.Close()
    if _, err := fmt.Fprintf(f, "%s %s\n", gtrid, verdict); err != nil {
        return err
    }
    return f.Sync()
}

// decisions replays the log. A later "forget" clears a resolved transaction.
func decisions() map[string]string {
    out := map[string]string{}
    f, err := os.Open(decisionLog)
    if err != nil {
        return out
    }
    defer f.Close()
    sc := bufio.NewScanner(f)
    for sc.Scan() {
        parts := strings.Fields(sc.Text())
        if len(parts) != 2 {
            continue
        }
        if parts[1] == "forget" {
            delete(out, parts[0])
            continue
        }
        out[parts[0]] = parts[1]
    }
    return out
}

// ---------------------------------------------------------------------------
// plumbing
// ---------------------------------------------------------------------------

type execer interface {
    ExecContext(context.Context, string, ...any) (sql.Result, error)
}

func exec(db execer, q string, args ...any) error {
    _, err := db.ExecContext(ctx, q, args...)
    return err
}

func must(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

func connect() (*sql.DB, *sql.DB) {
    my, err := sql.Open("mysql", mysqlDSN)
    must(err)
    if err := my.Ping(); err != nil {
        log.Fatalf("mysql not reachable: %v\n(see the docker command at the top of this file)", err)
    }
    pg, err := sql.Open("postgres", pgDSN)
    must(err)
    if err := pg.Ping(); err != nil {
        log.Fatalf("postgres not reachable: %v", err)
    }
    return my, pg
}

// reset gives every run a clean slate, including any prepared transactions and
// decisions left behind by a previous run.
func reset(my, pg *sql.DB) {
    os.Remove(decisionLog)
    recoverInDoubtQuietly(my, pg)
    must(exec(my, `CREATE TABLE IF NOT EXISTS accounts (
        id VARCHAR(32) PRIMARY KEY, balance INT NOT NULL) ENGINE=InnoDB`))
    must(exec(pg, `CREATE TABLE IF NOT EXISTS accounts (
        id TEXT PRIMARY KEY, balance INT NOT NULL)`))
    must(exec(my, "DELETE FROM accounts"))
    must(exec(pg, "DELETE FROM accounts"))
    must(exec(my, "INSERT INTO accounts VALUES ('alice', 500)"))
    must(exec(pg, "INSERT INTO accounts VALUES ('bob', 100)"))
}

func recoverInDoubtQuietly(my, pg *sql.DB) {
    rows, err := my.Query("XA RECOVER")
    if err == nil {
        var gtrids []string
        for rows.Next() {
            var format, gl, bl int
            var data string
            if rows.Scan(&format, &gl, &bl, &data) == nil {
                gtrids = append(gtrids, data[:gl])
            }
        }
        rows.Close()
        for _, g := range gtrids {
            _ = exec(my, fmt.Sprintf("XA ROLLBACK '%s','my',1", g))
        }
    }
    rows, err = pg.Query("SELECT gid FROM pg_prepared_xacts")
    if err == nil {
        var gids []string
        for rows.Next() {
            var g string
            if rows.Scan(&g) == nil {
                gids = append(gids, g)
            }
        }
        rows.Close()
        for _, g := range gids {
            _ = exec(pg, "ROLLBACK PREPARED '"+g+"'")
        }
    }
}

func balances(my, pg *sql.DB) {
    var a, b int
    must(my.QueryRow("SELECT balance FROM accounts WHERE id='alice'").Scan(&a))
    must(pg.QueryRow("SELECT balance FROM accounts WHERE id='bob'").Scan(&b))
    fmt.Printf("   alice (mysql) = %-4d bob (postgres) = %d\n", a, b)
}

// showBlocking demonstrates 2PC's real cost: a prepared branch keeps its row
// locks, so unrelated traffic stalls until someone resolves the transaction.
func showBlocking(my *sql.DB) {
    conn, err := my.Conn(ctx)
    must(err)
    defer conn.Close()
    must(exec(conn, "SET SESSION innodb_lock_wait_timeout = 1"))
    err = exec(conn, "UPDATE accounts SET balance = 999 WHERE id='alice'")
    fmt.Println("     ", err)
}