A Guide On SQL Database Transactions In Go

This article explains what SQL database transactions are, and how to implement them in Go (Golang).

Transactions are very useful when you want to perform multiple operations on a database, but still treat them as a single unit. We will go into detail on why this is useful, and how you can use it in your Go applications.

To illustrate our examples, we will use Postgres as our database of choice.

If you just want to see the source code, you can find it here

Transactions

A normal SQL insert query looks like this:

INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat');

This query can represent something like adopting two new pets.

Sometimes, the event you want to record needs to be expressed as multiple queries: For example, what if someone adopted pets and bought food for them?

We could then write two queries to do just that:

INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat');
INSERT INTO food (name, quantity) VALUES ('Dog Biscuit', 3), ('Cat Food', 5);

Now lets think about what happens if the first query succeeds, but the second query fails: you now have data which shows that two new pets are adopted, but no food has been bought.

• We cannot treat this as a success, since one of the queries failed
• We cannot treat this as a failure, since the first query passed. If we consider this a failure and retry, we would have to insert Fido and Albert for a second time.

To avoid situations like this, we want both the queries to pass or fail together. This is where SQL transactions come in. If you see the example below, it’s just a slight modification from executing the queries normally:

BEGIN;
INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat');
INSERT INTO food (name, quantity) VALUES ('Dog Biscuit', 3), ('Cat Food', 5);
END;

• The BEGIN statement starts a new transaction
• Once the transaction has begun, SQL statements are executed one after the other, although they don’t reflect in the database just yet.
• The END statement commits the above transactions atomically
• Incase we want to abort the transaction in the middle, we could have used the ROLLBACK statement

Here, “atomically” means both of the SQL statements are treated as a single unit - they pass or fail together

Implementing Transactions in Go

Now that we’ve seen how transactions are useful, let’s go through how we can implement them in our Go application…

Setup

Let’s create a new project to see how transactions work. To start, we will be:

• Creating a new directory
• Adding a main.go file
• Installing our Postgres driver pq

## Create new directory
mkdir golang-sql-transactions
cd golang-sql-transactions

## Create main.go file
touch main.go

## Install pq postgres driver
go mod init
go get github.com/lib/pq

Creating Our Tables

Let’s create the pets and food tables from the previous examples. Execute the following statements from the Postgres shell:

CREATE TABLE pets (id SERIAL PRIMARY KEY,name TEXT,species TEXT);
CREATE TABLE food (id SERIAL PRIMARY KEY, name TEXT, quantity INT);

Basic Transactions

We will now make use of the pq library, along with the database/sql standard library, and implement the two insert queries we discussed in the previous example:

package main

import (
	"context"
	"database/sql"
	"log"

	_ "github.com/lib/pq"
)

func main() {
	// Create a new connection to our database
	connStr := "user=soham dbname=pet_shop sslmode=disable"
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}

	// Create a new context, and begin a transaction
	ctx := context.Background()
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatal(err)
	}
	// `tx` is an instance of `*sql.Tx` through which we can execute our queries

	// Here, the query is executed on the transaction instance, and not applied to the database yet
	_, err = tx.ExecContext(ctx, "INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat')")
	if err != nil {
		// Incase we find any error in the query execution, rollback the transaction
		tx.Rollback()
		return
	}

	// The next query is handled similarly
	_, err = tx.ExecContext(ctx, "INSERT INTO food (name, quantity) VALUES ('Dog Biscuit', 3), ('Cat Food', 5)")
	if err != nil {
		tx.Rollback()
		return
	}

	// Finally, if no errors are recieved from the queries, commit the transaction
	// this applies the above changes to our database
	err = tx.Commit()
	if err != nil {
		log.Fatal(err)
	}
}

Here, we can commit or rollback the transaction from our Go code itself. This is very useful since:

1. We can inspect the result of each query and write custom logic to decide if we should rollback or not.
2. We can even rollback if we decide from some external factors that we don’t want to commit the transaction:

   if err != nil || iDontFeelLikeIt() {
     tx.Rollback()
   }
   

Read-and-Update Transactions

In some cases, we want to update data in a table depending on the previous values stored in some other table. Transactions can help us do this atomically as well.

For example, consider the case where we want to add cat food to our inventory, and the quantity that we want to add is proportional to the number of cats that we have.

In this case, we would need to find the count of the total number of cats first, and then update the quantity of cat food.

package main

import (
	"context"
	"database/sql"
	"log"

	_ "github.com/lib/pq"
)

func main() {
	// Initialize a connection, and begin a transaction like before
	connStr := "user=soham dbname=pet_shop sslmode=disable"
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}

	ctx := context.Background()
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatal(err)
	}

	_, err = tx.ExecContext(ctx, "INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat')")
	if err != nil {
		tx.Rollback()
		return
	}

	// Run a query to get a count of all cats
	row := tx.QueryRow("SELECT count(*) FROM pets WHERE species='cat'")
	var catCount int
	// Store the count in the `catCount` variable
	err = row.Scan(&catCount)
	if err != nil {
		tx.Rollback()
		return
	}

	// Now update the food table, increasing the quantity of cat food by 10x the number of cats
	_, err = tx.ExecContext(ctx, "UPDATE food SET quantity=quantity+$1 WHERE name='Cat Food'", 10*catCount)
	if err != nil {
		tx.Rollback()
		return
	}

	// Commit the change if all queries ran successfully
	err = tx.Commit()
	if err != nil {
		log.Fatal(err)
	}
}

Here, we can allow our application to decide how much cat-food to add to our inventory (which in this case is 10x the number of cats).

It’s important to note why the read query is executed within the transaction: any read query outside the transaction doesn’t consider the values of an uncommitted transaction. This means that if our read query was outside the transaction, we would not consider the pets added in the first insert query.

Conclusion

Transactions are really useful if you want to modify data in multiple places, or use data from previous modifications. Of course, you should only use transactions when needed, since they carry their own overhead when compared to regular queries: transactions sometimes hold a lock on the row being modified, blocking other transactions from modifying it.

To read more about how to deal with SQL databases in Golang applications, you can read my other post on using a SQL database in Go

If you want to see the source code for these examples, you can find it on Github, and if you feel I didn’t cover something important, let me know in the comments!