Implement Database Consistency in Golang with MySQL Locks

Published on January 7, 2022•Updated on November 21, 2023

When designing multi-user database applications, your primary concern is how to deal with data consistency and concurrency. Consistency is the ability to accept changes from different users without breaking your business logic. Then, concurrency is a mechanism that allows multiple users to access the same database without compromising data integrity.

For instance, assume you have got a balance of $200 on your debit card. Then you've made 10 subscriptions with different providers that take $70 each month. Precisely at the first day of each month at 00:00:00 hours, all subscribers will attempt to charge your card. If your debit card issuer has not set any consistency and concurrency mechanisms, all the 10 parallel subscriptions should succeed. In the end , you'll have a debt of $500 to pay($200 - ($70 X 10) = -$500).

The example above shows you how awful things can get. Your card issuer may not have an overdraft provision, and their poorly designed app has broken their business logic. On the other hand, there is no guarantee that you'll use their card again; this leaves them with an unpaid balance. If this happens to thousands or millions of bank users, it's a huge loss to the business in terms of revenue and customers' trust. Other examples include overbooked airplane seats and oversubscribed soccer match tickets.

Luckily MySQL provides LOCKS to overcome this challenge. A client session can acquire a table LOCK to prevent other sessions from accessing the same database object when executing an operation. This allows you to run operations serially and only release the lock when you're through with a single transaction. In other words, you're executing transactions one by one and waitlisting any incoming requests.

In this guide, you'll use MySQL table locks to implement database consistency and concurrency with Golang on your Linux server.

Prerequisites

To follow along with this guide, make sure you have the following:

1. Create a Database, User Account, and Table

In this tutorial, you'll create sample bank applications to log customers' deposits and withdrawals. In the end, your application should handle simultaneous withdrawal transactions without any provisions for overdrafts (negative balances).

Under the hood, your application should obtain a WRITE LOCK to a savings table, check the customer's balance, and proceed with a withdrawal transaction only if the remaining balance can cover the amount you're attempting to debit.

SSH to your server, then follow the steps below to set up a database.

Then, enter the root password for the MySQL server and press Enter to proceed. Next, execute the following SQL commands to create a sample_db database and a sample_db_user account. Replace EXAMPLE_PASSWORD with a strong value.

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

Next, switch to the new sample_db database.
```
 mysql> USE sample_db;
```
Then, execute the statement below to create a savings table. You'll use this table to store clients' bank deposits and withdrawals. The ref_id column is the primary key for the table. You'll use the account_id column to identify individual clients' transactions. Then, the trans_type column allows you to mark a transaction as either a deposit or a withdrawal using the characters D and W respectively. For deposits, you'll populate the credit column. Then, you'll input any withdrawn amount to the debit column.
```
 mysql> CREATE TABLE savings (
            ref_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            account_id BIGINT,
            trans_type CHAR(1),
            debit  DECIMAL(17, 4),
            credit DECIMAL(17, 4)
        ) ENGINE = InnoDB;
```
Log out from the MySQL server.
```
 mysql> QUIT;
```

2. Create a `main.go` File

Your sample bank application runs under Golang's inbuilt web server. In this step, you'll create a file that handles the main function that fires when you start the application. To distinguish your source code files from the rest of the Linux files, you'll need a project directory.

Set up the project directory.
```
 $ mkdir project
```
Then, navigate to the new directory.
```
 $ cd project
```
Use nano to create and open a new main.go file for editing.
```
 $ nano main.go
```

Then, enter the following information into the file.

 package main

 import (

  "encoding/json"
 "fmt"
 "net/http" 

 )  

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

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

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

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

     err = json.NewDecoder(req.Body).Decode(&params)

     response, err = addEntry(params)            

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

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

     if encodingErr := enc.Encode(response); encodingErr != nil {
         fmt.Println("{ error: " + encodingErr.Error() + "}")
     }
 }

Save and close the main.go file.
In the above file you have got a main() function that runs an HTTP server on port 8080. In the httpHandler function, you're parsing and assigning the req.Body JSON values to the params map of string]interface{}. Next, you're calling an addEntry(...) function which you'll create in a new file in the next step.
Your objective in the above file is to accept a bank transaction (either a deposit or a withdrawal) in a JSON format and send it to the addEntry(params) function for further processing.

3. Create a `savings.go` File

In this step, you'll set up a file with a function to save data to the MySQL database. This file will also house other MySQL table locking and balance query functions.

Create the savings.go file
```
 $ nano savings.go
```

Then, enter the following information into the savings.go file. Use the appropriate values for the dbUser, dbPassword, and dbName depending on your MySQL account details and database name.

 package main

 import (

 "database/sql"
     "errors"
     "fmt" 
     "strconv" 

 _ "github.com/go-sql-driver/mysql"

 ) 

 var (

     enableTableLocking bool = false

 )  

 func getDB()(*sql.DB, error) {

     dbUser     := "sample_db_user"
     dbPassword := "EXAMPLE_PASSWORD"
     dbName     := "sample_db"

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

     if err != nil {
         return nil, err
     }

     return db, nil

 }

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

     accountId, err := strconv.ParseInt(fmt.Sprint(p["account_id"]), 10, 64)

     if err != nil {
         return nil, err
     }

     transType := p["trans_type"].(string)
     amount    := p["amount"].(float64)

     credit := 0.0
     debit  := 0.0

     if transType == "D" {
         credit = amount
         debit  = 0.00
     } else {
         credit = 0.00
         debit  = amount
     }

     db, err := getDB()

     if err != nil {
         return nil, err
     }

     defer db.Close()


     if enableTableLocking == true {
         lockTables(db)
     }


     resp, err := getBalance(db, accountId) 

     accountBalance := resp["account_balance"].(float64)          

     if amount > accountBalance && transType == "W" {

         if enableTableLocking  == true {
             unlockTables(db)
         }     

        return nil, errors.New("Insufficient balance. " + fmt.Sprint(accountBalance))

     }

     queryString := "insert into savings (account_id, trans_type, debit, credit) values (?, ?, ?, ?)"

     stmt, err   := db.Prepare(queryString) 

     if err != nil {
         return nil, err       
     }

     defer stmt.Close()     

     res, err := stmt.Exec(accountId, transType, debit, credit)  

     if err != nil {
         return nil, err
     }

     refId, err := res.LastInsertId()

     if err != nil {
         return nil, err
     }

     resp, err = getBalance(db, accountId) 

     accountBalance = resp["account_balance"].(float64) 


     if enableTableLocking {
         unlockTables(db)
     }


     response := map[string]interface{}{
                     "ref_id" :    refId,
                     "account_id": accountId,
                     "amount":     amount,
                     "balance":    accountBalance,
                 } 

     return response, nil  
 }

 func getBalance(db *sql.DB, accountId int64) (map[string]interface{}, error) {    

     queryString := "select ifnull(sum(credit - debit), 0) as account_balance from savings where account_id = ?"

     stmt, err := db.Prepare(queryString) 

     if err != nil {
         return nil, err       
     }

     accountBalance := 0.00

     err = stmt.QueryRow(accountId).Scan(&accountBalance)

     if err != nil {
        return nil, err
     }

     response := map[string]interface{}{
                     "account_balance" : accountBalance,
                 }

     return response, nil
  }

 func lockTables(db *sql.DB) error {

     queryString := "lock tables savings write"

     _, err := db.Exec(queryString) 

     if err != nil {
         return err       
     }

     return nil
 }

 func unlockTables(db *sql.DB) error {

     queryString := "unlock tables"

     _, err := db.Exec(queryString) 

     if err != nil {
         return err       
     }

     return nil
 }

Save and close the file.
In the above file, you're using the boolean variable enableTableLocking to toggle MySQL table locking functions(lockTables and unlockTables). You'll later test your application with the different table locking modes(true and false) and see if there will be any noticeable change in terms of database integrity.
You're using the getDB() function to connect to the MySQL database that you set up earlier.
Next, you're using the addEntry(...) function to save incoming transactions to the MySQL database. Under this function, you're checking the customer's account balance and comparing it with the requested withdrawal amount before accepting any withdrawals.
Then, you're checking the value of the bool enableTableLocking in your application using the statement if enableTableLocking == true { ... } to decide whether to call the lockTables and unlockTables functions. If the enableTableLocking variable is set to true, you're obtaining a WRITE lock to the savings table ensuring no other transaction can hit the table before you've completed the account balance check and insert operations. Otherwise, if the enableTableLocking is set to false, your application can accept incoming requests without any waitlisting. This puts your database in an inconsistent state as you will see in the testing step.

4. Test the Application

Your application is now ready to accept deposits and withdrawals. You'll test the application in two phases. First, you'll disable table locking and see if your application can handle concurrency without breaking your system logic. That is, debiting too much money in the client's account.

Before running the application, import the Golang MySQL driver package from GitHub.
```
 $ go get github.com/go-sql-driver/mysql
```
Next, install the Apache Bench(ab) tool. You'll use the ab utility to send parallel transactions to your application.
```
 $ sudo apt install -y apache2-utils
```
Then, run the application. Don't enter any other command on this SSH session when the application starts. Your applications should start a web server on port 8080.
```
 $ go run ./
```
Remember, you had set the enableTableLocking boolean to false in the savings.go file. This means your application will run without any table locking mechanism in place.
```
 ....  

 var (

     enableTableLocking bool = false

 ) 

 ...
```

Next, open a new terminal window and use the curl command to create a deposit(D) transaction of $2500 for account 100731.

 $ curl -X POST localhost:8080/savings -H "Content-Type: application/json" -d '{"account_id": 100731, "trans_type": "D", "amount": 2500.00}'

You'll get a response that shows the client # 100731 has a bank balance of $2500.

 {
   "account_id": 100731,
   "amount": 2500,
   "balance": 2500,
   "ref_id": 1
 }

Create a new json file. You'll use it with the ab command.
```
 $ nano json 
```
Populate the json file with the JSON payload below. This files allows you to send a withdrawal request of $1,000 to your application.
```
 {"account_id": 100731, "trans_type": "W", "amount": 1000.00}
```
Save and close the file.
Next, use the ab command to send 20 parallel transactions of $1,000 to the application. These amount to $20,000. If your application can handle concurrency, the account balance of $2500 should only be enough to cover for two transactions($1000 x 2) and the rest 18 transactions should fail with an Insufficient balance error.
```
 $ ab -v 2 -n 20 -c 20 -H 'Content-Type: application/json' -p json http://localhost:8080/savings
```
Depending on the output received from your system, it is now apparent that your application logic has failed. Because you've run all transactions concurrently without any form of table locking, the account holder's balance is now negative. Your application couldn't handle your load and the business logic.
```
 {
   "account_id": 100731,
   "amount": 1000,
   "balance": 1500,
   "ref_id": 2
 }

 ...

 {
   "account_id": 100731,
   "amount": 1000,
   "balance": -5500,
   "ref_id": 9
 }

 ...

 {
   "error": "Insufficient balance. -5500"
 }

 ...
```
Log in to the MySQL database as root to confirm the entries from the savings table.
```
 $ sudo mysql -u root -p
```
Enter the root password and press Enter to proceed. Then, switch to the sample_db database.
```
mysql> USE sample_db;
```
Query the savings table.
```
mysql> SELECT * FROM savings;
```

You might see different results from the output below depending on the number of parallel transactions you managed to execute. However, it's very clear that your application state is now inconsistent. You should only have two $1,000 withdrawal(W) transactions to cover the deposit of $2,500, but you've several of them.

+--------+------------+------------+-----------+-----------+
| ref_id | account_id | trans_type | debit     | credit    |
+--------+------------+------------+-----------+-----------+
|      1 |     100731 | D          |    0.0000 | 2500.0000 |
|      2 |     100731 | W          | 1000.0000 |    0.0000 |
|      3 |     100731 | W          | 1000.0000 |    0.0000 |
|      4 |     100731 | W          | 1000.0000 |    0.0000 |
|      5 |     100731 | W          | 1000.0000 |    0.0000 |
|      6 |     100731 | W          | 1000.0000 |    0.0000 |
|      7 |     100731 | W          | 1000.0000 |    0.0000 |
|      8 |     100731 | W          | 1000.0000 |    0.0000 |
|      9 |     100731 | W          | 1000.0000 |    0.0000 |
|     10 |     100731 | W          | 1000.0000 |    0.0000 |
+--------+------------+------------+-----------+-----------+
10 rows in set (0.00 sec)

Query the customer's balance by summing the credit and debit columns.

mysql> SELECT IFNULL(SUM(credit-debit), 0) as account_balance FROM savings WHERE account_id = 100731;

You should now get a negative value.

+-----------------+
| account_balance |
+-----------------+
|      -6500.0000 |
+-----------------+
1 row in set (0.00 sec)

Delete all entries from the savings table to prepare the database for a second testing phase with table LOCKs enabled.
```
mysql> TRUNCATE savings;
```
Exit from the MySQL interface.
```
mysql> QUIT;
```
Next, go to the main SSH session window where you started your application from. Stop the application by pressing Ctrl + C. Then, open the savings.go file. Make sure you're still under the project directory.
```
$ nano savings.go
```

Change the value of enableTableLocking from false to true.

....  

var (

    enableTableLocking bool = true

) 

...

Save and close the savings.go file. Then, run the application one more time. This time around, you've enabled table locking to ensure the database handles data consistency and concurrency.
```
$ go run ./
```

In a new terminal window, execute the same deposit(D) transaction of $2500 that you had run earlier.

$ curl -X POST localhost:8080/savings -H "Content-Type: application/json" -d '{"account_id": 100731, "trans_type": "D", "amount": 2500.00}'

The account balance of the client is now $2500

{
  "account_id": 100731,
  "amount": 2500,
  "balance": 2500,
  "ref_id": 1
}

Again, attempt sending 20 concurrent $1,000 transactions to your application. Remember, you saved this JSON payload on a json file.
```
$ ab -v 2 -n 20 -c 20 -H 'Content-Type: application/json' -p json http://localhost:8080/savings
```

This time around, only two transactions have succeeded and the rest have failed as you can confirm from the output below.

{
  "account_id": 100731,
  "amount": 1000,
  "balance": 1500,
  "ref_id": 2
}

{
  "account_id": 100731,
  "amount": 1000,
  "balance": 500,
  "ref_id": 3
}

{
  "error": "Insufficient balance. 500"
}

...

Log back to the MySQL database as root to confirm the new entries.
```
$ sudo mysql -u root -p
```
Key in the root password and press Enter to proceed. Then, switch to the sample_db database.
```
mysql> USE sample_db;
```
Query the savings table.
```
mysql> SELECT * FROM savings;
```

Your application logic is now working as expected. Only two withdrawal(W) transactions have succeeded.

+--------+------------+------------+-----------+-----------+
| ref_id | account_id | trans_type | debit     | credit    |
+--------+------------+------------+-----------+-----------+
|      1 |     100731 | D          |    0.0000 | 2500.0000 |
|      2 |     100731 | W          | 1000.0000 |    0.0000 |
|      3 |     100731 | W          | 1000.0000 |    0.0000 |
+--------+------------+------------+-----------+-----------+
3 rows in set (0.00 sec)

Check the client's balance.

mysql> SELECT IFNULL(SUM(credit-debit), 0) as account_balance FROM savings WHERE account_id = 100731;

The account balance is now $500. The remaining balance wasn't enough to cover for a third $1,000 transaction.

+-----------------+
| account_balance |
+-----------------+
|        500.0000 |
+-----------------+
1 row in set (0.00 sec)

Your application logic is now working as expected.

Conclusion

In this tutorial, you've implemented database consistency with MySQL LOCKs and Golang on your Linux server. You've seen how MySQL table locks allowed you to isolate transactions and perform your business logic in a serial manner without compromising data integrity.

In a MySQL database, consistency and concurrency go hand in hand with transactions; follow the link below to learn more about MySQL transactions.

How to Implement MySQL Transactions with Golang On Linux Server

Implement Database Consistency in Golang with MySQL Locks

Prerequisites

1. Create a Database, User Account, and Table

2. Create a `main.go` File

3. Create a `savings.go` File

4. Test the Application

Conclusion

Products

Features

Marketplace

Resources

Company

Implement Database Consistency in Golang with MySQL Locks

Prerequisites

1. Create a Database, User Account, and Table

2. Create a main.go File

3. Create a savings.go File

4. Test the Application

Conclusion

2. Create a `main.go` File

3. Create a `savings.go` File