Implementing transactions in PostgreSQL using Go lang database/sql package

Introduction

A transaction is a unit of work that you want to treat as “a whole.” It has to either happen in full or not at all. In Go lang, a transaction is essentially an object that reserves a connection to the database. You begin a transaction with a call to db.Begin() and close it with a commit() or rollback() method on the resulting Tx variable.

In this post, we will learn how to implement database transactions in your Go lang application.

Prerequisites

To be able to achieve our objective i.e. Transactions in PostgreSQL from Go , there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with some references

Install PostgreSQL, setup an instance and create a test database – https://www.postgresqltutorial.com/install-postgresql/

Install Go and configure workspace – https://www.callicoder.com/golang-installation-setup-gopath-workspace/

For this tutorial, I’m using Postgres 12 and Go 1.13.xx

Objective – Transactions in PostgreSQL using Go lang database/sql package

Step 1. SQL Table definition

We’ll re-use our test table EMP that we created in the last post

-- create a sample table EMP
CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT CHECK (sal>0),
  email TEXT UNIQUE NOT NULL 
);

Step 2. Basic SQL transactions

For this example, let’s just assume that we want following SQL statements to be executed together

-- Stmt #1. INSERT a new employee record
INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com');

-- Stmt #2. UPDATE sal for this new employee
UPDATE emp SET sal = 800 WHERE ename = 'Smith';

Now think of a scenario where only the first SQL statement  succeeds, however, the second statement fails: you now have a new employee Smith, however, does not have a salary allocated,

  • Can we treat this as a success, since Smith does not have a salary allocated
  • Also, Is this a failure, since Smith employee record is created
    • If we consider this a failure and retry the transaction, then employee record of ‘Smith’ will be inserted twice

This is a classic situation and in relational systems, we handle them using database transactions i.e. allowing both the SQL statements to Pass or Fail together. Below is an example of database transactions to handle the above scenario.

BEGIN;
	INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com');
	UPDATE emp SET sal = 800 WHERE ename = 'Smith';
COMMIT;

Here

  • First, open a transaction by issuing the BEGIN command
  • Second, issue SQL statements to SELECT or INSERT or UPDATE data in the database. The SQL statements are executed one after the other
  • Third, commit the changes to the database by using the COMMIT statement
  • If you do not want to save the changes, you can roll back using the ROLLBACK statement

Step 3. Implementing Transactions in Go lang

You begin a transaction with a call to db.Begin(), and close it with a Commit() or Rollback() method on the resulting Tx variable. Under the covers, the Tx gets a connection from the pool, and reserves it for use only with that transaction. The methods on the Tx map one-for-one to methods you can call on the database itself, such as Query() and so forth.

// First You begin a transaction with a call to db.Begin()
ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
	log.Fatal(err)
}

While you are working inside a transaction you should be careful not to make calls to the db variable. Make all of your calls to the Tx variable that you created with db.Begin()db is not in a transaction, only the Tx object is. If you make further calls to db.Exec() or similar, those will happen outside the scope of your transaction, on other connections.

// Second, execute SQL queries against on the transaction instance. Note these are not applied to the database yet
_, err = tx.ExecContext(ctx, "INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com')")
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, "UPDATE emp SET sal = 0 WHERE ename = 'Smith'")
if err != nil {
	tx.Rollback()
	fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
	return
}

A Tx will maintain that single connection for its entire life cycle, releasing it only when Commit() or Rollback() is called. You should take care to call at least one of these, or else the connection will be held until garbage collection.

// close the transaction with a Commit() or Rollback() method on the resulting Tx variable. 
err = tx.Commit()
if err != nil {
	log.Fatal(err)
}

Step 4. Putting this all together

package main

// modifying data and using transactions
import (
	"context"
	"database/sql"
	"fmt"
	"log"
	
	_ "github.com/lib/pq"
)
const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "postgres"
	dbname = "connect"
)

var (
	ename string
	sal int
)

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
	"password=%s dbname=%s sslmode=disable",
	host, port, user, password, dbname)
	
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// First You begin a transaction with a call to db.Begin()
	// `tx` is an instance of `*sql.Tx` through which we can execute our queries
	ctx := context.Background()
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatal(err)
	}
	
	// Second, execute SQL queries against on the transaction instance. Note these are not applied to the database yet
	_, err = tx.ExecContext(ctx, "INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com')")
	if err != nil {
		// Incase we find any error in the query execution, rollback the transaction
		tx.Rollback()
		fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
		return
	}

	// The next query is handled similarly
	_, err = tx.ExecContext(ctx, "UPDATE emp SET sal = 0 WHERE ename = 'Smith'")
	if err != nil {
		tx.Rollback()
		fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
		return
	}

	// close the transaction with a Commit() or Rollback() method on the resulting Tx variable. 
	// this applies the above changes to our database
	err = tx.Commit()
	if err != nil {
		log.Fatal(err)
	} else {
		fmt.Println("....Transaction committed\n")
	}
}

Conclusion

In this post we learned how to implement database transactions in PostgreSQL using Go lang  database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. This is also concludes my blog post series for on Working with PostgreSQL in Go Lang. Below are all other post in this series:

1. My journey from DBA to DevOps and why I started learning Go lang

2. Connecting to PostgreSQL from Go lang project

3. Inserting rows in PostgreSQL db from Go lang project

4. Updating and Deleting rows in PostgreSQL from Go lang project

5. Querying rows from PostgreSQL from Go lang project

If you find my post helpful, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook.

Thank you 🙋‍♂️

Author: Varun Dhawan

Hello dear reader, I'm DevOps Engineer based in Minnesota, US (beautiful land of 10,000 lakes). I am perpetually curious and always willing to learn and engineer systems that can help solve complex problems using data. When I am not engineering or blogging, you’ll find me cooking and spending time with my wife and daughter. Varun.Dhawan@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s