Author: Francis Ndungu
Last Updated: Fri, Jan 7, 2022When 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.
To follow along with this guide, make sure you have the following:
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.
Log in to your MySQL server as root
.
$ sudo mysql -u root -p
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_0900_ai_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;
main.go
FileYour 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(¶ms)
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.
savings.go
FileIn 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.
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.
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.