How to Implement Table Locks in Go with Vultr Managed Databases for PostgreSQL

Published on January 4, 2023•Updated on June 23, 2024

Introduction

In PostgreSQL, a lock is a mechanism that prevents multiple database users from updating the same row or table simultaneously. Therefore, locks are useful in multi-user environments to secure shared resources during transactions to avoid breaking the application's logic. For instance, in an order processing application with thousands of users, you should design your software to lock a stock table to avoid overselling products. Similarly, in a ticketing application, locking the tickets table during the ticket grabbing process prevents overbooking.

To ensure the integrity and the maximum possible concurrent access to your data, the PostgreSQL server supports different lock modes. The most crucial database lock for Enterprise Resource Planning (ERP) applications is the access exclusive lock. This guide uses a managed PostgreSQL database cluster's access exclusive lock to implement an order processing application with Go on Ubuntu 20.04.

Prerequisites

To complete this guide:

Provision an Ubuntu 20.04 server.
Create a non-root sudo user.
Install Go.
Deploy a managed PostgreSQL database cluster.
Locate the PostgreSQL database cluster's Connection Details under the Overview tab. This guide uses the following sample connection details:
- username: vultradmin
- password: EXAMPLE_POSTGRESQL_PASSWORD
- host: SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com
- port: 16751

1. Set Up a Sample PostgreSQL Database

In this sample application, you require a sample database and the following tables:

customers: This table stores customers' data using the customer_id, first_name, and last_name fields.
products: This table stores products' data using the product_id, product_name, retail_price, and remaining_stock fields.
customer_orders. This table tracks customers' orders using the order_id, customer_id, product_id, and quantity fields.

When customers request an order, your sample application should lock the products table to check the remaining_stock field. If a customer orders a quantity that's greater than the remaining stock, the PostgreSQL application should cancel the transaction. Otherwise if the quantity is within the limit, the application should lock the products table, update the remaining_stock field and commit the transaction.

Follow the steps below to initialize the database and set up the sample tables:

Update your server's package information index.
```
 $ sudo apt update 
```
Install the postgresql-client package. This lightweight command-line package lets you connect to your managed PostgreSQL cluster from your Linux server.
```
 $ sudo apt install -y postgresql-client
```
Log in to the managed PostgreSQL cluster. Replace SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com with the correct host.
```
 $ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com -p 16751 -U vultradmin defaultdb
```
Output.
```
 Password for user vultradmin:
```
Enter the managed PostgreSQL database cluster password and press Enter to proceed.

Output.
```
 defaultdb=>
```

Set up a sample company_db database.

 defaultdb=> CREATE DATABASE company_db;

Output.

 CREATE DATABASE

Connect to the new company_db database.

 defaultdb=> \c company_db;

Output.

 ...
 You are now connected to database "company_db" as user "vultradmin".

Create the customers table. The SERIAL keyword instructs the PostgreSQL server to automatically generate unique customer_ids when you insert new records into the table.

 company_db=> CREATE TABLE customers (
                  customer_id SERIAL PRIMARY KEY,
                  first_name VARCHAR(50),
                  last_name VARCHAR(50)        
              );

Output.

 CREATE TABLE

Insert sample data into the customers table.

 company_db=> INSERT INTO customers (first_name, last_name) VALUES ('JOHN', 'DOE');
              INSERT INTO customers (first_name, last_name) VALUES ('MARY', 'SMITH');
              INSERT INTO customers (first_name, last_name) VALUES ('PETER', 'JONES');

Output.

 ...
 INSERT 0 1

Query the customers table to ensure the data is in place.

 company_db=> SELECT
                  customer_id,
                  first_name,
                  last_name
              FROM customers;

Output.

  customer_id | first_name | last_name
 -------------+------------+-----------
            1 | JOHN       | DOE
            2 | MARY       | SMITH
            3 | PETER      | JONES
 (3 rows)

Create a products table. This table uses the remaining_stock field to monitor the remaining stock for different products.

 company_db=> CREATE TABLE products (
                  product_id SERIAL PRIMARY KEY,
                  product_name VARCHAR(50),
                  retail_price DECIMAL(17, 2),
                  remaining_stock INTEGER        
              );

Output.

 CREATE TABLE

Insert sample data into the products table.

 company_db=> INSERT INTO products (product_name, retail_price, remaining_stock) VALUES ('4G ROUTER', 55.23, 100);
              INSERT INTO products (product_name, retail_price, remaining_stock) VALUES ('SMARTWATCH', 75.25, 50);
              INSERT INTO products (product_name, retail_price, remaining_stock) VALUES ('PLIERS', 4.85, 20);

Output.

 ...
 INSERT 0 1

Query the products table to verify the data.

 company_db=> SELECT
                  product_id,
                  product_name,
                  retail_price,
                  remaining_stock
              FROM products;

Output.

  product_id | product_name | retail_price | remaining_stock
 ------------+--------------+--------------+-----------------
           1 | 4G ROUTER    |        55.23 |             100
           2 | SMARTWATCH   |        75.25 |              50
           3 | PLIERS       |         4.85 |              20
 (3 rows)

Create a customer_orders table. Instead of repeating the customers' names, this table uses the customer_id column that links back to the customers table to track customers' orders.

 company_db=> CREATE TABLE customer_orders (
                  order_id SERIAL PRIMARY KEY,
                  customer_id INTEGER,
                  product_id INTEGER,
                  quantity INTEGER               
              );

Output.

 CREATE TABLE

Log out from the managed PostgreSQL cluster.
```
 company_db=> \q
```

The database schema is now ready. Follow the next step to create the main file that runs when you start your Go application.

2. Create a Go Application's Entry Point

Every Go application requires a main() function that executes when the application starts. Follow the steps below to create the function.

Make a new project directory for the application.
```
 $ mkdir project
```
Navigate to the new project directory.
```
 $ cd project
```
Open a new main.go file on a text editor.
```
 $ nano main.go
```

Enter the following information into the main.go file.

 package main

 import (
     "net/http"
     "encoding/json"
     "fmt"
 )

 func main() {
     http.HandleFunc("/orders", httpHandler)
     http.ListenAndServe(":8080", nil)
 }

 func httpHandler(w http.ResponseWriter, req *http.Request) {

     jsonPayload := map[string]interface{}{}

     err := json.NewDecoder(req.Body).Decode(&jsonPayload) 

     if err != nil {
         fmt.Fprintf(w, "JSON error.")
     } else {

         response, err := saveData(jsonPayload)

         if err != nil {

              fmt.Fprintf(w, err.Error() + "\r\n") 

         } else {
             enc := json.NewEncoder(w)
             enc.SetIndent("", "  ")

             if err := enc.Encode(response); err != nil {
                 fmt.Println(err.Error())
             }  
         }
     }
 }

Save and close the main.go file.

The main.go file explained:

The import(...) section loads packages required by the application. The net/http module provides HTTP functionalities to the application. The encoding/json package allows the application to work with the JSON data format. The fmt package allows you to format strings and generate output.
```
 import (
     "net/http"
     "encoding/json"
     "fmt"
 )
 ...
```
The main(){...} functions runs when your application starts. Under the function, you're establishing a web server that listens for incoming HTTP requests on port 8080. Then, you're delegating the requests to a httpHandler function.
```
 func main() {
     http.HandleFunc("/orders", httpHandler)
     http.ListenAndServe(":8080", nil)
 }
```
The httpHandler(...) function uses the jsonPayload := map[string]interface{}{} and err := json.NewDecoder(req.Body).Decode(&jsonPayload) statements to parse the JSON payloads from HTTP clients. If an HTTP client sends a valid JSON, the application passes the requests to a saveData(jsonPayload) function using the response, err := saveData(jsonPayload) statement. This guide later shows you how to code the saveData(...) function in a new file.
```
 func httpHandler(w http.ResponseWriter, req *http.Request) {
     ...
 }
```

After creating the main.go file, follow the next step to create a function for interacting with the managed PostgreSQL database cluster.

3. Create a Database Gateway File

You should always separate your Go application into multiple manageable files to assist troubleshooting when a problem occurs. This step shows you how to create a database file that connects to the managed PostgreSQL cluster to update several database tables to fulfill customers' orders.

Follow the steps below to create the file:

Open a new database.go file on a text editor.
```
 $ nano database.go
```

Enter the following information into the database.go file. Replace the dbHost, and dbPass values with the correct managed PostgreSQL cluster's host and password.

 package main 

 import (
     "database/sql"
     "fmt"
     "errors"
     _ "github.com/lib/pq"    
 )

 func saveData(jsonPayload map[string]interface{}) (string, error){ 

     customerId := jsonPayload["customer_id"]
     productId  := jsonPayload["product_id"]     
     orderQty   := jsonPayload["quantity"].(float64)

     dbHost  := "SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com"
     dbPort  := 16751
     dbUser  := "vultradmin"        
     dbPass  := "EXAMPLE_POSTGRESQL_PASSWORD"        
     dbName  := "company_db"

     conString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require", dbHost, dbPort, dbUser, dbPass, dbName)

     db, err := sql.Open("postgres", conString)

     if err != nil {         
         return "", err        
     }

     defer db.Close()

     tx, err := db.Begin() 

     if err != nil {           
         return "", err       
     }

     queryString := "lock table products in access exclusive mode"

     _, err = tx.Exec(queryString)  

     if err != nil {
         tx.Rollback()
         return "", err       
     }

     var remainingStock float64

     queryString = "select remaining_stock from products where product_id = $1"

     row := tx.QueryRow(queryString, productId)
     err = row.Scan(&remainingStock)

     if err != nil {
         return "", err
     }

     if (remainingStock < orderQty) {
         tx.Rollback()
         return "", errors.New("The stock you are requesting is unavailable.")
     }  

     queryString = "insert into customer_orders (customer_id, product_id, quantity) values ($1, $2, $3)"
     _, err = tx.Exec(queryString, customerId, productId, orderQty)  

     if err != nil {
         tx.Rollback()
         return "", err       
     }

     queryString = "update products set remaining_stock = remaining_stock - " + fmt.Sprintf("%f", orderQty) + " where product_id = $1"
     _, err = tx.Exec(queryString, productId)  

     if err != nil {
         tx.Rollback()
         return "", err       
     }

     err = tx.Commit()

     if err != nil {
         return "", err       
     }

     return "Success", nil
 }

Save and close the database.go file.

The database.go file explained:

The import(...) section loads several packages required by your application. The database/sql package provides a lightweight interface to the PostgreSQL database cluster. The fmt package allows you to format strings. You're using the errors package to craft and return custom errors. The github.com/lib/pq package is a PostgreSQL database driver for Go.
```
 import (
     "database/sql"
     "fmt"
     "errors"
     _ "github.com/lib/pq"    
 )
 ...
```

Under the saveData(...) function, you're loading the JSON payload from HTTP clients into the customerId, productId, and orderQty variables.

 func saveData(jsonPayload map[string]interface{}) (string, error){ 
     customerId := jsonPayload["customer_id"]
     productId  := jsonPayload["product_id"]     
     orderQty   := jsonPayload["quantity"].(float64)
  ...

The following lines establish a connection to the managed PostgreSQL database cluster.

     ...
     dbHost  := "SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com"
     dbPort  := 16751
     dbUser  := "vultradmin"        
     dbPass  := "EXAMPLE_POSTGRESQL_PASSWORD"        
     dbName  := "company_db"

     conString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require", dbHost, dbPort, dbUser, dbPass, dbName)

     db, err := sql.Open("postgres", conString)

     if err != nil {         
         return "", err        
     }

     defer db.Close()

In a PostgreSQL database, you must place the LOCK TABLE statement inside a transaction. You're accomplishing this using the tx, err := db.Begin() command. Then, you're passing the lock table products in access exclusive mode command to lock the products table. The LOCK TABLE statement ensures only one database session acquires access to the products table when creating an order
```
     ...

     tx, err := db.Begin() 

     if err != nil {           
         return "", err       
     }

     queryString := "lock table products in access exclusive mode"

     _, err = tx.Exec(queryString)  

     if err != nil {
         tx.Rollback()
         return "", err       
     }
```

After obtaining a products table lock, you're using the select remaining_stock from products where product_id = $1 query to check the remaining stock. Then, to ensure you're not overselling the product, you're using the if (remainingStock < orderQty) {} statement to verify the stock available before completing an order.

     ...
     var remainingStock float64

     queryString = "select remaining_stock from products where product_id = $1"

     row := tx.QueryRow(queryString, productId)
     err = row.Scan(&remainingStock)

     if err != nil {
         return "", err
     }

     if (remainingStock < orderQty) {
         tx.Rollback()
         return "", errors.New("The stock you are requesting is unavailable.")
     }
     ...

After confirming the stock is available from the products table, you're inserting a new order in the customer_orders table using the insert into customer_orders (customer_id, product_id, quantity) values ($1, $2, $3) command. Then, you're updating the products table to decrement the stock depending on the customer's ordered quantity using the update products set remaining_stock = remaining_stock - " + fmt.Sprintf("%f", orderQty) + " where product_id = $1 statement.

     queryString = "insert into customer_orders (customer_id, product_id, quantity) values ($1, $2, $3)"
     _, err = tx.Exec(queryString, customerId, productId, orderQty)  

     if err != nil {
         tx.Rollback()
         return "", err       
     }

     queryString = "update products set remaining_stock = remaining_stock - " + fmt.Sprintf("%f", orderQty) + " where product_id = $1"
     _, err = tx.Exec(queryString, productId)  

     if err != nil {
         tx.Rollback()
         return "", err       
     }

     ...

The tx.Commit() is the last command you're issuing to the PostgreSQL database cluster to commit the order to the database. After the tx.Commit() command, the PostgreSQL server releases all locks from the products table. This allows other clients to connect to the database and repeat the same procedure to make an order. The saveData(...) function, then returns a Success message to the calling main() function that you created inside the main.go file.
```
     err = tx.Commit()

     if err != nil {
         return "", err       
     }

     return "Success", nil
 }
```

With the Go source code files ready, you can now test the application in the next step to ensure everything is working as expected.

4. Test the Sample Application

The final step is downloading the PostgreSQL driver for Go, running the application, and sending multiple Linux curl commands to test the application. Follow the steps below:

Download the PostgreSQL driver for Go from GitHub.
```
 $ go get github.com/lib/pq
```
Start the Go application.
```
 $ go run ./
```
Do not enter any other command in your active SSH window because the previous command has a blocking function.

Establish another SSH connection to your server and execute the following Linux curl commands to send three sample order requests to the Go application.

 $ curl -i -X POST localhost:8080/orders -H "Content-Type: application/json" -d '{"customer_id": 1, "product_id": 1, "quantity": 3}'
 $ curl -i -X POST localhost:8080/orders -H "Content-Type: application/json" -d '{"customer_id": 2, "product_id": 2, "quantity": 5}'
 $ curl -i -X POST localhost:8080/orders -H "Content-Type: application/json" -d '{"customer_id": 3, "product_id": 3, "quantity": 15}'

Verify the output below after running each curl command. The Success message shows that your Go application is working as expected.
```
 ...
 "Success"
```
Log in to the managed PostgreSQL database cluster to verify the data. Replace SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com with the correct host.
```
 $ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com -p 16751 -U vultradmin defaultdb
```
Enter the password for the PostgreSQL database cluster and press Enter to proceed.
```
 Password for user vultradmin:
```

Connect to the company_db database.

 defaultdb=> \c company_db;

Output.

 ...
 You are now connected to database "company_db" as user "vultradmin".

Query the products table to ensure the stock is up to date depending on the orders you've placed using the previous Linux curl commands.

 defaultdb=> SELECT
                 product_id,
                 product_name,
                 retail_price,
                 remaining_stock
             FROM products;

Output.

  product_id | product_name | retail_price | remaining_stock
 ------------+--------------+--------------+-----------------
           1 | 4G ROUTER    |        55.23 |              97
           2 | SMARTWATCH   |        75.25 |              45
           3 | PLIERS       |         4.85 |               5
 (3 rows)

Query the customer_orders and customers tables using a JOIN statement to ensure the orders are in place.

 defaultdb=> SELECT
                 order_id,
                 customer_orders.customer_id,
                 customers.first_name,
                 customers.last_name,
                 quantity
             FROM customer_orders
             LEFT JOIN customers
             ON customer_orders.customer_id = customers.customer_id;

Output.

  order_id | customer_id | first_name | last_name | quantity
 ----------+-------------+------------+-----------+----------
         1 |           1 | JOHN       | DOE       |        3
         2 |           2 | MARY       | SMITH     |        5
         3 |           3 | PETER      | JONES     |       15
 (3 rows)

The above query results confirm that your application's logic is working as expected.

Conclusion

This guide shows you how to use the PostgreSQL access exclusive lock to implement an order processing application with Go. With the table lock mechanism, the application supports concurrent orders without overselling the products. Use the same logic when designing mission-critical applications to ensure data consistency.

Check out the following guides to test more projects with Vultr's managed database clusters:

How to Implement Table Locks in Go with Vultr Managed Databases for PostgreSQL

Introduction

Prerequisites

1. Set Up a Sample PostgreSQL Database

2. Create a Go Application's Entry Point

3. Create a Database Gateway File

4. Test the Sample Application

Conclusion

Products

Features

Marketplace

Resources

Company