Go MySQL Tutorial [Part 4: Transactions]

Photo by Nathan Dumlao on Unsplash

Reminder:

This little tutorial guides you through setting up a MySQL/MariaDB server and simple programming in Go. The idea is to present the minimum needed to have a working application. It is part of a small series of articles I wanted to write about databases.

Table of Contents

• Install your MySQL server, click here.
• Open a connection to your MySQL server, click here.
• MySQL data transfer with Go, click here.
• MySQL transactions with Go, click here.

MySQL Transactions with Go

A transaction in SQL is a set of operations that must all succeed or all fail. This is the well-known ACID principle (Atomicity, Consistency, Isolation, Durability). In practice, if a query fails midway, everything done since the beginning of the transaction is rolled back.

In Go, you start a transaction with the db.Begin() method:

        tx, err := db.Begin()
        if err != nil {
                log.Fatalf("db.Begin failed: %v", err)
        }

From that point on, you no longer use db directly, but tx. All queries go through the transaction:

        _, err = tx.Exec(`INSERT INTO `+tableName+` SET data=?, creation=NOW()`, "transaction-1")
        if err != nil {
                tx.Rollback()
                log.Fatalf("tx.Exec(INSERT 1) failed: %v", err)
        }

        _, err = tx.Exec(`INSERT INTO `+tableName+` SET data=?, creation=NOW()`, "transaction-2")
        if err != nil {
                tx.Rollback()
                log.Fatalf("tx.Exec(INSERT 2) failed: %v", err)
        }

You'll notice: on error, we call tx.Rollback(). This cancels all operations performed since db.Begin(). Both INSERTs are undone, as if they never happened.

If everything goes well, we commit the transaction with tx.Commit():

        err = tx.Commit()
        if err != nil {
                log.Fatalf("tx.Commit failed: %v", err)
        }

Only at this point do the changes become visible to other connections.

A more robust pattern

In practice, you often use a defer to make sure Rollback is always called on error, even if you forget to do it explicitly:

        tx, err := db.Begin()
        if err != nil {
                log.Fatalf("db.Begin failed: %v", err)
        }
        defer tx.Rollback()

        _, err = tx.Exec(`INSERT INTO `+tableName+` SET data=?, creation=NOW()`, "safe-1")
        if err != nil {
                log.Fatalf("tx.Exec failed: %v", err)
        }

        _, err = tx.Exec(`INSERT INTO `+tableName+` SET data=?, creation=NOW()`, "safe-2")
        if err != nil {
                log.Fatalf("tx.Exec failed: %v", err)
        }

        err = tx.Commit()
        if err != nil {
                log.Fatalf("tx.Commit failed: %v", err)
        }

The defer tx.Rollback() has no effect if tx.Commit() has already been called. This is documented in the database/sql package: calling Rollback on an already committed transaction simply returns sql.ErrTxDone. So you can safely put it in a defer.

SELECT queries in a transaction

You can also run SELECT queries within a transaction. The benefit is that the data you read is consistent with the ongoing modifications:

        tx, err := db.Begin()
        if err != nil {
                log.Fatalf("db.Begin failed: %v", err)
        }
        defer tx.Rollback()

        _, err = tx.Exec(`INSERT INTO `+tableName+` SET data=?, creation=NOW()`, "pending")
        if err != nil {
                log.Fatalf("tx.Exec failed: %v", err)
        }

        rows, err := tx.Query(`SELECT id, data FROM ` + tableName + ` WHERE data=?`, "pending")
        if err != nil {
                log.Fatalf("tx.Query failed: %v", err)
        }
        defer rows.Close()

        for rows.Next() {
                var id uint64
                var data string
                if err := rows.Scan(&id, &data); err != nil {
                        log.Fatalf("rows.Scan failed: %v", err)
                }
                fmt.Printf("found: %d %s\n", id, data)
        }

        err = tx.Commit()
        if err != nil {
                log.Fatalf("tx.Commit failed: %v", err)
        }

Here, the SELECT sees the "pending" row that was just inserted, even though it is not yet visible to other connections.

Conclusion

Transactions in Go are straightforward. The three methods to remember:

• db.Begin() to start a transaction
• tx.Commit() to validate
• tx.Rollback() to cancel

The defer tx.Rollback() pattern right after Begin() is a good habit to adopt. And above all, don't forget: once in a transaction, use tx, not db.