Implement Redis® Transactions and Locks with Golang and MySQL on a Linux Server

Published on February 8, 2022•Updated on June 22, 2024

Introduction

In Redis®, a transaction is a single unit of work comprised of multiple commands that must be committed atomically. That is, either all of the commands are executed or nothing is executed. Redis® uses the MULTI, EXEC, DISCARD, and WATCH functions to achieve this capability.

To create a transaction via the redis-cli tool, you simply run the MULTI command first, followed by other subsequent commands. Finally, you should execute either the EXEC command to process the transaction or the DISCARD command to flush the queued commands.

The WATCH command allows you to implement a locking mechanism during the lifetime of a transaction, and in case your WATCHed key is modified by another session, the EXEC command should fail to avoid putting your Redis® database in an inconsistent state.

In this guide, you'll use the Redis® transaction functions to create a ticket-grabbing application with Golang and MySQL on a Linux server.

Prerequisites

To proceed with this tutorial, make sure you have the following:

1. Create a MySQL Database, a User Account, and a Table

Redis® is an in-memory database, and while it can persist data to disk, it was not designed for that purpose and may not perform optimally. Therefore, in this guide, you'll use the MySQL database to permanently store ticket information to a MySQL table once it has been generated by the Redis® server.

SSH to your server and follow the following steps to create the database.

Enter your MySQL root password when prompted and press Enter to proceed. Then, execute the commands below to create a bookings database and a bookings_user account. Replace EXAMPLE_PASSWORD with a strong value.

 mysql> CREATE DATABASE bookings DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        CREATE USER 'bookings_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
        GRANT ALL PRIVILEGES ON bookings.* TO 'bookings_user'@'localhost';
        FLUSH PRIVILEGES;

Switch to the new database.
```
 mysql> USE bookings;
```
Next, create a tickets table. In this sample application, you'll use the Redis® server to grab passengers' seats from a pool of available seats. Then, you'll permanently store the allocated seat_no's and ticket_id's information in the tickets table.
```
 mysql> CREATE TABLE tickets (
            ticket_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            seat_no BIGINT    
        ) ENGINE = InnoDB;
```
Your database, user account, and table are now in place. Log out from the MySQL server.
```
 mysql> QUIT;
```
In the next step, you'll create a Golang script to accept incoming ticket requests via HTTPS.

2. Create a `main.go` File

To separate this application from the rest of the Linux files, you need a separate directory for the source codes.

Create a project directory.
```
 $ mkdir project
```
Then, switch to the new project directory.
```
 $ cd project
```
Next, use nano to create a main.go file. This file holds the main script that fires when you run the application.
```
 $ nano main.go
```

With the main.go file opened, paste the following information into the file.

 package main

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

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

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

     var err error
     resp := map[string]interface{}{}

     resp, err = newTicket() 

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

     if err != nil {
         resp = map[string]interface{}{"error": err.Error(),}     
     }

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

 }

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

     seatNo, err := createTicket("test")

     if err != nil {
         return nil, err
     }

     resp := map[string]interface{}{"Response" : "Seat # " + strconv.FormatInt(seatNo, 10) + " booked successfully.",}

     return resp, nil          

 }

Save and close the file when you're through with editing.
In the above main.go file, you're importing the encoding/json package, which allows you to format JSON data. Next, you've included the fmt package to format and output strings. The strconv package allows you to convert other data types to string formats while the net/http library provides HTTP implementations.
Under the main function(func main() {...}), you're listening for incoming HTTP requests on port 8080 in the /tickets URL. Then, you're redirecting the HTTP requests to the func httpHandler(){...} function which in turn calls the newTicket() function using the statement resp, err = newTicket().
Under the func newTicket(){} function, you're calling the createTicket(...) function using the statement seatNo, err := createTicket("test") to a grab a passenger's seat number from the Redis® server. In the next step, you'll create the createTicket(...) function in a new tickets.go file.

3. Create a `tickets.go` File

In this step, you'll create a Golang script that connects to the Redis® server. First, the script will read a key named test to check the total seats available for booking. Then, if the number of remaining seats is greater or equal to 1, the script will reserve a seat number, decrement the remaining seats by 1 and return the allocated seat_no to the calling script.

Use nano to create the tickets.go file.
```
 $ nano tickets.go
```

Then, enter the following information into the tickets.go file.

 package main

 import (
 "context"
 "errors"
 "strconv"
 "github.com/go-redis/redis"
 )

 func createTicket(key string) (int64, error) {

     ctx := context.Background()

     redisClient := redis.NewClient(&redis.Options{
         Addr: "localhost:6379",
         Password: "",
         DB: 0,
     })

     var seatNo int64

     err := redisClient.Watch(ctx, func(tx *redis.Tx) error {

         val, err := tx.Get(ctx, key).Int64()

         if err != nil && err != redis.Nil {
             return err
         }

         seatNo = val

         if (seatNo - 1) < 0 {
             return errors.New("Unable to secure a seat.\r\n")
         }

         _, err = tx.Pipelined(ctx, func(pipe redis.Pipeliner) error {

             pipe.Set(ctx, key, strconv.FormatInt(seatNo - 1, 10), 0)    

             return nil
         })

         if err == nil {
             insertRecord(seatNo)
         }

 return err

 }, key)

     if err == redis.TxFailedErr {               
         return createTicket(key)
 }

 return seatNo, err
 }

Save and close the file.
In the above file, you've imported the context package to provide a non-limited deadline to the Redis® calls using the statement ctx := context.Background(). Then, you're using the errors package to return custom errors to the calling function. The github.com/go-redis/redis package allows you to implement Redis® functions inside the Golang Script.
In the func createTicket(key string) (int64, error){}, you're accepting 1 parameter. That is the name of the key you're using to reserve the available seats in your application. In this tutorial, you're using test as the key name. In a production environment, you might consider using a more meaningful/descriptive name like available_seats.
The statement redisClient := redis.NewClient(...) allows you to connect and create a new Redis® client instance. Then, you're initializing an empty seatNo variable using the statement var seatNo int64. You'll populate this variable once your script allocates the seat number.
Next, you're using the Redis® WATCH function using the statement err := redisClient.Watch(ctx, func()...{...}, key) to monitor the test key during the lifetime of the transaction. In case the test key is modified in any way by another session, the whole transaction should abort, and you've coded the script to retry the script using the statement if err == redis.TxFailedErr { return createTicket(key) }. Remember, in a production environment, customers can buy tickets from different applications—for instance, mobile apps, APIs, desktop applications, portals, and more. The idea here is to issue one ticket at a time to avoid overbooking.
Inside the WATCH function, you retrieving the value of the remaining seats using the statement val, err := tx.Get(ctx, key).Int64(). In case no seat is remaining, you're throwing a custom error using the statement if (seatNo - 1) < 0 { return errors.New("Unable to secure a seat.\r\n") }.
Next, once you reserve a seat, you're reducing the number of available seats using the statement pipe.Set(ctx, key, strconv.FormatInt(seatNo - 1, 10), 0). A Redis® pipe allows you to transmit multiple commands to the Redis® server in one network call. While you're executing only one command in this tutorial, you should always use the pipe model to allow making modifications easier in the future in case your application logic changes.
Then, you're calling the insertRecord() function to save the ticket information to the MySQL database in case there are no errors executing the pipelined command using the statement if err == nil { insertRecord(seatNo) }. Once your entire createTicket() function runs, it should return a seatNo to the main.go file or any error in case any is encountered.
In the next step, you'll create the insertRecord() function that you're calling in this tickets.go in a different database.go file.

4. Create a `database.go` File

The last script you'll create for this ticket grabbing application is the database.go file. This file holds the logic for storing ticket information permanently to a MySQL database.

Use Nano to create the database.go file.
```
 $ nano database.go
```

Then, enter the following information into the database.go file.

 package main

 import (
     "database/sql"
     _ "github.com/go-sql-driver/mysql"      
 )

 func insertRecord(seatNo int64) error {

     dbUser     := "bookings_user"
     dbPassword := "EXAMPLE_PASSWORD"
     dbName     := "bookings"

     db, err := sql.Open("mysql", dbUser + ":" + dbPassword + "@tcp(127.0.0.1:3306)/" + dbName) 

     if err != nil {
         return err
     }

     defer db.Close()

     queryString := "insert into tickets (seat_no) values (?)"

     stmt, err   := db.Prepare(queryString) 

     if err != nil {
         return err       
     }

     defer stmt.Close()     

     _, err = stmt.Exec(seatNo) 

     if err != nil {
         return err
     }

     return nil
 }

Save and close the file.
In the above file, you're using the database/sql and github.com/go-sql-driver/mysql packages to implement the SQL and MySQL functionalities in Golang. Under the func insertRecord(...) error {...} function, you're connecting to the MySQL database using the credentials that you created earlier. Then, you're saving the tickets information into the tickets table.
You've now coded all the scripts for running Redis® transactions with MySQL and Golang. In the next step, you'll test if everything is working as expected.

5. Test the Redis® Transaction Application

Your Golang transaction application is now ready for testing purposes.

Before you execute the application, import all the packages that you've implemented in the application.
```
 $ go get github.com/go-redis/redis
 $ go get github.com/go-sql-driver/mysql
```
Next, open the Redis® command-line interface.
```
 $ redis-cli
```
Avail 10 seats by setting the value of a test key to a value of 10.
```
 $ SET test 10
```
Log out from the Redis® server.
```
 $ QUIT
```
Ensure you're still under the project directory and execute the following command to run the Golang application.
```
 $ go run ./
```
The above command has a blocking function that spins a web server under port 8080. Don't run any other commands on this terminal window.
Next, SSH to your server in a new terminal window and install the Apache Bench (ab) package. You'll use this tool to send parallel ticket request commands to your application to see if it can handle transactions without any cases of overbooking or race conditions.
```
 $ sudo apt install -y apache2-utils
```
Next, send 20 parallel ticket requests to the application. Remember, you've only availed 10 seats in the Redis® server. Therefore, only 10 transactions should succeed, and the rest should fail. Also, there should be no cases of different sessions having the same seat_no since you've implemented the Redis® lock using the WATCH function.
```
 $ ab -v 2 -n 20 -c 20 http://localhost:8080/tickets
```

You should get the following response.

 ...
 {
   "Response": "Seat # n booked successfully."
 }

 ...

 {
   "error": "Unable to secure a seat.\r\n"
 }

 ...

Next, while still logged in on your second terminal window, log in to the MySQL database as root to confirm the new changes.
```
$ sudo mysql -u root -p
```
Enter the root password for the MySQL server and press Enter to proceed. Then, switch to the booking database.
```
mysql> USE bookings;
```

Run a SELECT statement against the tickets table.

mysql> SELECT
           ticket_id,
           seat_no
       FROM tickets;

You should now see the following tickets and the associated seat_no's. As you can see from the following output, there are no cases of overbooking. Also, the script has successfully eliminated any chances of race condition since no two tickets bear the same seat_no.

+-----------+---------+
| ticket_id | seat_no |
+-----------+---------+
|         1 |      10 |
|         2 |       9 |
|         3 |       7 |
|         4 |       8 |
|         5 |       6 |
|         6 |       5 |
|         7 |       3 |
|         8 |       4 |
|         9 |       1 |
|        10 |       2 |
+-----------+---------+
10 rows in set (0.00 sec)

Your script is now working as expected.

Conclusion

In this guide, you've implemented Redis® transactions and locking with Golang and MySQL database in your Linux server to create a ticket-grabbing application. Use the logic in this guide to avoid race conditions and database inconsistency when creating multi-user applications.

To read more Golang and Redis® tutorials, follow the links below:

Implement Redis® Transactions and Locks with Golang and MySQL on a Linux Server

Introduction

Prerequisites

1. Create a MySQL Database, a User Account, and a Table

2. Create a `main.go` File

3. Create a `tickets.go` File

4. Create a `database.go` File

5. Test the Redis® Transaction Application

Conclusion

Products

Features

Marketplace

Resources

Company

Implement Redis® Transactions and Locks with Golang and MySQL on a Linux Server

Introduction

Prerequisites

1. Create a MySQL Database, a User Account, and a Table

2. Create a main.go File

3. Create a tickets.go File

4. Create a database.go File

5. Test the Redis® Transaction Application

Conclusion

2. Create a `main.go` File

3. Create a `tickets.go` File

4. Create a `database.go` File