Luke Watts ⚡️
Using Go to identify SQL queries which are vulnerable to the ACIDRain attack

Using Go to identify SQL queries which are vulnerable to the ACIDRain attack


go sql mysql concurrency security ACIDRain attack

In this post, I will show you how to use Go to test MySQL/Postgres queries for race conditions and phantom reads. This is a very important part of testing your queries, especially in a large scale multi-user environment.

Disclaimer

I’m a relatively new to Go myself (3 months in), so if you see any mistakes in my code or something which could be improved, please let me know by leaving a comment on this gist which contains all the code in this article.

I wrote this code primarily to replicate the ACIDRain attack so I could quickly confirm without any doubt that a series of queries (most likely in a code review) would be susceptible to the attack or not, and so I could easily demonstrate the result of the issue.

Pre-requisites

  1. Go installed and configured correctly on your environment
  2. A MySQL (or Postgres) database, and the ability to create a new database and tables, and read and write to that database
  3. A basic understanding of concurrency in Go using Channels and WaitGroups
  4. (Optional) Some basic understanding of Laravel (or any other PHP framework) to understand the example code

This post assumes you have a basic understanding of Go (mainly Channels and WaitGroups) and SQL (mainly MySQL). If you don’t, you can still use the code provided to check your queries, but you may not understand the code fully. I will try to explain as much as I can, but I won’t be able to explain everything in detail. Especially not Channels and WaitGroups in Go. However I will link to additonal resources where you can learn more about these topics.

What are race conditions?

Let’s start by defining the issue we are trying to identify. A race condition (a concept at the core of the ACIDRain attack) in an SQL database, is a situation in which two or more threads, processes, sessions, or requests read a unit of data at the same time, with the intention of using that unit of data to update something else (the database and other systems). This can lead to a situation where the data is not consistent, and the result of the update is not what was expected. This is a very common issue in large-scale multi-user environments, and it is very important to be able to quickly identify and proove it is possible.

Why you need to guard yourself from race conditions?

Let’s take a look at an example to understand. This will be the example we will use throughout this post.

NOTE: In this example we will use MySQL, but the same principles also apply to Postgres. However, this is not something which is possible on SQLite, as SQLite locks the entire database when a read/write happens.

Here is our example table:

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `balance` decimal(8,2) NOT NULL DEFAULT '100.00',
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

NOTE: I am using Laravel to create, migrate and seed my database for this example. However, we will only be using the id and balance fields throughout this article.

A simple withdrawl

Now let’s say we have a simple situation where a system/process is requesting to withdraw from this users balance.

Here are the steps we would take:

  1. Requested user starts with a balance of 100.00. The requester want to withdraw 100.00.
  2. Our system checks the requested users balance is greater than or equal to the amount requested using a simple SELECT query.
  3. The system confirms the user has enough in their balance, so it begins the withdrawl/transfer process.
    1. Some other processing (updating microservices or the requesting process/system) happens here which also uses the balance.
  4. The system updates the users balance: $user->balance -= $amount (or in SQL UDPATE users SET balance = balance - 100 WHERE id = :user_id).

That first check to see if the requested user have enough in their balance and the subtraction of the amount from the balance are two separate queries, because we also need to do some additional processing between validating the balance, and updating the database. Therefore, it was not possible to just make this a subquery of our update because that balance is also used to do some other steps between the two queries.

Here is some poorly architected code (using Laravel) for the withdrawl:

<?php

namespace App\Http\Controllers;

use App\Models\User;
use Illuminate\Http\Request;

class BalanceController extends Controller
{
    public function withdraw(int $userId, Request $request)
    {
        $requestedAmount = $request->input('requested_amount');

        // Check user has enough in balance
        $user = User::where('id', $userId)->where('balance', '>=', $requestedAmount)->first();

        if ($user) { // We have enough in our balance, continue withdrawl...
            $originalBalance = $user->balance;
            dispatch(function () use ($user, $originalBalance) {
                // Other processing (notifications, updating microservices or other apps etc) 
                // happens here which also uses the original balance (100.00)
            });

            // Subtrack requestedAmount from balance
            $user->balance -= $requestedAmount;
            $user->save();
        }
    }
}

The key thing to realize here is that once we have queried the user’s balance, we are not locking the row. This means that if two such systems try to withdraw from the balance at the same time, the first SELECT would tell them both the user has 100.00. Subsequently, both sessions would believe they can subtract 100.00 from the balance. This would leave the balance at -100.00 and both systems would have been approved 100.00. This is the goal of an ACIDRain attack.

Aside from the face that this is a poorly architected example in the first place, you might be thinking, just set the balance column as UNSIGNED and this would solve the problem. However, this is not a solution. The problem is not that the balance can go negative since in many system this is a requirement (overdrafts etc).

TLDR; If you just want to see the various ways to fix/prevent this issue, skip to the “Ways to prevent ACIDRain vulnerabilities” section.

Checking for ACIDRain attack vulnerability in SELECT/UPDATE sequences

Why Go and not PHP?

Reason 1: Concurrency

PHP is great for many things, but it is not a good language for concurrency. It’s just not designed for that. In fact, I wouldn’t even know where to start to quickly and accurately test for this with PHP, without using AMPHP or ReactPHP. However, Go is a great language for concurrency, therefor, it is reasonably simple to test for this issue using Go with very little setup once you understand some basics of concurrency in Go.

Reason 2: Go makes it easy to access the database and run queries

Go is a great language for testing SQL queries in general, as it has a great SQL package out of the box. You will need to install one additional package to use Go with MySQL, but it’s just one terminal command to do this.

Reason 3: Go is not hard to use for PHP devs

If you are a modern PHP developer (7.4+/8+), you will find Go to be a pretty easy language to learn. It has it’s quirks, but so does PHP. In this example the only library we will be using is the database/sql package along with the mysql driver package, which is similar to PHP’s PDO driver.

Setting up main.go

Let’s set up our main.go file. This is the file which will test for the ACIDRain vulnerability.

package main

import (
    "database/sql"
    "fmt"
    "sync"

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

As you can see, we’re using a third-party package called github.com/go-sql-driver/mysql to connect to our MySQL database. You will need to install this by running the following two commands in your terminal from the same directory as your main.go file:

go get -u github.com/go-sql-driver/mysql
go mod tidy

Connecting to the DB

First, let’s create a function called connectDatabase which will connect to our MySQL database. This function will return a pointer to a sql.DB object, which we will use to run our queries.

func connectDatabase() *sql.DB {
    db, err := sql.Open("mysql", "root@tcp(localhost)/sql_race_tests")
    if err != nil {
        fmt.Println("Error opening database: ", err)
    }

    fmt.Println("Main: Opened database connection")
    return db
}

NOTE: The format for the connection string is username:password@tcp(hostname)/database_name.

The SELECT query and selectUser function

Next, we add a constant with our select query and a function called selectUser which will run a SELECT query to get the user’s balance. This function will take a pointer to a sql.DB object and a channel of type float32 as arguments. We will use the channel to send the user’s balance to the updateQuery function which will check if the balance is enough to withdraw the amount requested. We’re essentially recreating the withdraw query sequence from our Laravel example, without any of the additional logic.

package main

// ... import statements ...

const selectQuery = `SELECT balance 
FROM users 
WHERE 
    id = 1 
    AND 
    balance >= 100`

// ... connectDatabase function ...

func selectUser(db *sql.DB, usersBalance chan float32) {
    fmt.Println("Running select query")
    rows, err := db.Query(selectQuery)
    if err != nil {
        fmt.Println("Error selecting balance: ", err)
        return
    }
    defer rows.Close()
    
    // Gets next row
    rows.Next()
    
    // Get balance
    var balance float32
    rows.Scan(&balance)
    fmt.Println("Balance: ", balance)

    // Set usersBalance channel to balance
    usersBalance <- balance
}

The UPDATE query and updateQuery function

Next, we add a constant with our update query and a function called updateQuery which will run an UPDATE query to subtract the amount from the user’s balance. This function will take a pointer to a sql.DB object, a channel of type float32 We will use the channel to get the user’s balance which the selectUser function previously set. To ensure our select happen at the same time, and happen first, we will use a sync.WaitGroup to wait for the select to finish before running the updates in another WaitGroup. More on this later.

package main

// ... import statements ...
// ... select query ...

const updateQuery = `UPDATE users
SET balance = balance - 100
WHERE 
    id = 1`

// ... connectDatabase function ...
// ... selectUser function ...

func updateUser(usersBalance chan float32, numSuccessfulUpdates int64, db *sql.DB) int64 {
    defer func() {
        if r := recover(); r != nil {
            fmt.Println("Recovered from panic: ", r)
        }
    }()

    if <-usersBalance < 100 {
        fmt.Println("Balance is less than 100...not updating balance")

        return numSuccessfulUpdates
    }

    fmt.Println("Balance is >= 100...updating balance")
    res, err := db.Exec(updateQuery)
    if err != nil {
        fmt.Println("Error updating balance: ", err)
        return numSuccessfulUpdates
    }
    fmt.Println("Balance updated")

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        fmt.Println("Error getting rows affected: ", err)
        return numSuccessfulUpdates
    }

    fmt.Println("Rows affected: ", rowsAffected)

    return rowsAffected + numSuccessfulUpdates
}

Checking final balance

Next, we add a function called checkFinalBalance which will simply run a SELECT query to get the user’s balance after the update. This function will take a pointer to a sql.DB object as an argument, and return a float32.

pacakge main

// ... everything else up to this point ...

func checkBalance(db *sql.DB) float32 {
    fmt.Println("Checking final balance")
    var finalBalance float32
    err := db.QueryRow("SELECT balance FROM users WHERE id = 1").Scan(&finalBalance)
    if err != nil {
        fmt.Println("Error selecting balance: ", err)
    }

    return finalBalance;
}

Putting it all together

Rather than putting everything in the main function we will create a function called MySQL which will setup our channels, waitgroups and goroutines, and run our select and update in parallel, but in the correct order. This function will then return a float32 which will be the final balance after the update.

NOTE: I’ve put fmt.Println statements in the code to show you the order in which the queries are run. This is not necessary in your own code, but it can be useful for debugging.

func MySQL() (float32, int64) {
    // 2 workers, which will run the select and update queries in parallel
    var workers int = 2
    // userBalance channel will store the balance
    // of the user between the select and update queries
    usersBalance := make(chan float32, workers)

    // Waitgroups will ensure the selects happen before the updates
    // otherwise the order would not be guaranteed
    var wg sync.WaitGroup

    db := connectDatabase()
    defer db.Close()

    fmt.Println("Begin WaitGroups")

    // First 2 waitgroup for the select queries
    for i := 0; i < workers; i++ {
        wg.Add(1)
        // we will run 2 select queries in parallel using goroutines
        go func(i int) {
            fmt.Printf("WaitGroup 1 Start: Worker %d: Running selectUser\n", i)
            // Once this go routine has completed is done this will let the next waitgroup know it can start
            defer wg.Done()
            selectUser(db, usersBalance)

            fmt.Printf("WaitGroup 1 End: Worker %d: Running selectUser\n", i)
        }(i)
    }

    var numSuccessfulUpdates int64 = 0
    // Third and 4th Waitgroup for the update queries
    for i := 0; i < workers; i++ {
        wg.Add(1)
        go func(i int) {
            fmt.Printf("WaitGroup 2 Start: Worker %d: Running updateUser\n", i)
            defer wg.Done()
            numSuccessfulUpdates = updateUser(usersBalance, numSuccessfulUpdates, db) + numSuccessfulUpdates
            fmt.Printf("WaitGroup 2 End: Worker %d: Running updateUser\n", i)
        }(i)
    }
    // Wait for all waitgroups to complete
    wg.Wait()

    // Get final balance
    var finalBalance float32 = 0
    finalBalance = checkBalance(db)

    return finalBalance, numSuccessfulUpdates
}

Next, we will add the main function which will call the MySQL function and then use the results to check if the SELECT and UPDATE queries are vulnerable to an ACIDRain attack.

func main() {
    finalBalance, numSuccessfulUpdates := MySQL()

    fmt.Println("Main: Final balance: ", finalBalance)

    fmt.Println("----- PRE-FLIGHT CHECKS -----")
    // This prevents a false pass after a failed test, where the data was not reset
    if numSuccessfulUpdates == 0 {
        fmt.Println(">>> PRE-FLIGHT FAILED: No updates were successful. Please check your dataset and code for issues and try again.")
        return
    }
    fmt.Println(">>> PRE-FLIGHT PASSED: The dataset is ready to be tested.")

    fmt.Println("----- TEST RESULTS -----")
    if finalBalance == -100 && numSuccessfulUpdates == 2 {
        fmt.Println(">>> FAIL:  This SELECT and UPDATE sequence are vulnerable to an ACIDRain Attack! Please use FOR UPDATE or FOR SHARE on the SELECT to fix this issue!")
    } else {
        fmt.Println(">>> PASS: This SELECT and UPDATE are not vulnerable to an ACIDRain Attack.")
    }
}

Running the test

To run the test, simply run the following command in your terminal from the same directory as your main.go file:

go run main.go

Results: FAIL

Main: Opened database connection
Begin WaitGroups
WaitGroup 2 Start: Worker 1: Running updateUser
WaitGroup 1 Start: Worker 1: Running selectUser
Selecting balance from users table where id = 1 and balance >= 100
WaitGroup 1 Start: Worker 0: Running selectUser
Selecting balance from users table where id = 1 and balance >= 100
WaitGroup 2 Start: Worker 0: Running updateUser
Balance:  100
Balance:  100
WaitGroup 1 End: Worker 0: Running selectUser
WaitGroup 1 End: Worker 1: Running selectUser
Balance is >= 100...updating balance
Balance is >= 100...updating balance
Balance updated
Rows affected:  1
WaitGroup 2 End: Worker 1: Running updateUser
Balance updated
Rows affected:  1
WaitGroup 2 End: Worker 0: Running updateUser
Checking final balance
Main: Final balance:  -100
----- PRE-FLIGHT CHECKS -----
>>> PRE-FLIGHT PASSED: The dataset is ready to be tested.
----- TEST RESULTS -----
>>> FAIL:  This SELECT and UPDATE sequence are vulnerable to an ACIDRain Attack! Please use FOR UPDATE or FOR SHARE on the SELECT to fix this issue!

As we can see, both WaitGroups start at the same time, but WaitGroup 2 knows to wait for WaitGroup 1 to finish before running the updates. This is because we are using a sync.WaitGroup to ensure the order of the queries is correct. This is important because we need to ensure the SELECT happens before the UPDATE, otherwise the order would not be guaranteed.

We can see the SELECT queries are both returning 100.00 and the UPDATE queries are both running and updating the balance to balance - 100.00. After this operation have a final balance of -100.00 and the number of times the update was successul is 2. This is a clear indication that the SELECT and UPDATE queries are vulnerable to an ACIDRain attack.

Results: Pre-flight check failed

If you run the script again before resetting the data the pre-flight check will fail, as the data is not in the correct state to be tested. This is why we have the pre-flight check. You could instead reset the data to the original state if you want to omit this step. I am resetting my data via laravels artisan tinker, so I opted not to reset it here.

# ... same as above ...
Balance is less than 100...not updating balance
WaitGroup 2 End: Worker 1: Running updateUser
Balance is less than 100...not updating balance
WaitGroup 2 End: Worker 0: Running updateUser
Checking final balance
Main: Final balance:  -100
----- PRE-FLIGHT CHECKS -----
>>> PRE-FLIGHT FAILED: No updates were successful. Please check your dataset and code for issues and try again.

Transactional SELECT/UPDATE queries

You might be thinking, “Why not just use a transaction to lock the row until the transaction is complete?“. This is a good question, and it is part of the solution to this problem. However, it is not the full solution. With that said, we will need to add a transaction to our selectUser and updateUser functions before we can look at the complete solution.

Adding a transaction to each goroutine

As mentioned, a goroutine in our application can be thought of as a seperate application or user requesting to withdraw from the balance. Therefore, we need to ensure each goroutine is running in a transaction.

To do this we will add 3 new functions to keep things more readable, as we will potentially be using a rollback anywhere we are handling an error. These functions will be beginTransaction, commitTransaction and rollbackTransaction.

package main

// ... imports, connectDatabase, selectQuery, updateQuery, checkBalance ...

func startTransaction(db *sql.DB) {
    fmt.Println("Starting transaction")
    _, err := db.Exec("START TRANSACTION")
    if err != nil {
        fmt.Println("Error starting transaction: ", err)
    }
}

func rollbackTransaction(db *sql.DB) {
    fmt.Println("Rolling back transaction")
    _, err := db.Exec("ROLLBACK")
    if err != nil {
        fmt.Println("Error rolling back transaction: ", err)
    }
}

func commitTransaction(db *sql.DB) {
    fmt.Println("Committing transaction")
    _, err := db.Exec("COMMIT")
    if err != nil {
        fmt.Println("Error committing transaction: ", err)
    }
}

// ... MySQL function ...

Next, we will add a startTransaction to the goroutines calling selectUser and our defer commitTransaction to the goroutines calling updateUser functions. We will also add rollbackTransaction to the updateUser function where we are handling errors.

In our MySQL function:

func MySQL() (float32, int64) {

    // ... everything before first for loop ...

    for i := 0; i < workers; i++ {
        wg.Add(1)
        go func(i int) {
            fmt.Printf("WaitGroup 1 Start: Worker %d: Running selectUser\n", i)
            defer wg.Done()
            startTransaction(db)
            selectUser(db, usersBalance)
            fmt.Printf("WaitGroup 1 End: Worker %d: Running selectUser\n", i)
        }(i)
    }

    var numSuccessfulUpdates int64 = 0
    // Third and 4th Waitgroup for the update queries
    for i := 0; i < workers; i++ {
        wg.Add(1)
        go func(i int) {
            fmt.Printf("WaitGroup 2 Start: Worker %d: Running updateUser\n", i)
            defer wg.Done()
            defer commitTransaction(db)
            numSuccessfulUpdates = updateUser(usersBalance, numSuccessfulUpdates, db) + numSuccessfulUpdates
            fmt.Printf("WaitGroup 2 End: Worker %d: Running updateUser\n", i)
        }(i)
    }
    // Wait for all waitgroups to complete
    wg.Wait()

    // ...

And in our updateUser function:

func updateUser(usersBalance chan float32, numSuccessfulUpdates int64, db *sql.DB) int64 {
    defer func() {
        if r := recover(); r != nil {
            rollbackTransaction(db)
            fmt.Println("Recovered from panic: ", r)
        }
    }()

    if <-usersBalance < 100 {
        fmt.Println("Balance is less than 100...not updating balance")
        rollbackTransaction(db)
        return numSuccessfulUpdates
    }

    fmt.Println("Balance is >= 100...updating balance")
    res, err := db.Exec(updateQuery)
    if err != nil {
        fmt.Println("Error updating balance: ", err)
        rollbackTransaction(db)
        return numSuccessfulUpdates
    }
    fmt.Println("Balance updated")

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        fmt.Println("Error getting rows affected: ", err)
        rollbackTransaction(db)
        return numSuccessfulUpdates
    }

    fmt.Println("Rows affected: ", rowsAffected)

    return rowsAffected + numSuccessfulUpdates
}

With that we are all set to try a few approaches to prevent the ACIDRain attack.

NOTE: Feel free to re-run the test here to confirm this alone does not fix the issue.

Ways to prevent ACIDRain vulnerabilities

Method 1: Use FOR UPDATE or FOR SHARE in your SELECT query

The quickest way to prevent ACIDRain vulnerabilities is to use FOR UPDATE or FOR SHARE in your SELECT query. This will lock the row until the transaction is complete, and prevent other sessions from reading the row until the transaction is complete.

When to use FOR UPDATE or FOR SHARE will depend on your use case. If you’re unsure, try FOR UPDATE first. FOR SHARE is used when you want to allow other transactions to read the row, but not update it. This can be useful if you want to allow queued transactions to still perform other operations with the data before doing an update/delete. It can also be useful if you wish to allow queued transactions use the selected data for an insert or read-only operations.

Essentially, FOR UPDATE is a fully blocking operation (blocks both read and writes on rows in question), while FOR SHARE is a partially blocking operation (blocks only writes on rows in question).

You should read the MySQL documentation on both here. You should also be sure to understand the different locking behaviour defined by NOWAIT and SKIP LOCKED. You can read more about them here.

Here is an example of how you would use FOR UPDATE in your SELECT query:

package main

// ... imports ...

const selectQuery = `SELECT balance
FROM users
WHERE
    id = 1
    AND
    balance >= 100
FOR UPDATE`

That’s it! Now when you run the test, you should see that the SELECT and UPDATE queries are no longer vulnerable to an ACIDRain attack.

Main: Opened database connection
WaitGroup 2 Start: Worker 1: Running updateUser
WaitGroup 1 Start: Worker 1: Running selectUser
Starting transaction
WaitGroup 2 Start: Worker 0: Running updateUser
WaitGroup 1 Start: Worker 0: Running selectUser
Starting transaction
Selecting balance from users table where id = 1 and balance >= 100
Selecting balance from users table where id = 1 and balance >= 100
Balance:  100
WaitGroup 1 End: Worker 0: Running selectUser
Balance is >= 100...updating balance
Balance updated
Rows affected:  1
WaitGroup 2 End: Worker 1: Running updateUser
Committing transaction
Balance:  0
WaitGroup 1 End: Worker 1: Running selectUser
Balance is less than 100...not updating balance
Rolling back transaction
WaitGroup 2 End: Worker 0: Running updateUser
Committing transaction
Checking final balance
Main: Final balance:  0
----- PRE-FLIGHT CHECKS -----
>>> PRE-FLIGHT PASSED: The dataset is ready to be tested.
----- TEST RESULTS -----
>>> PASS: This SELECT and UPDATE are not vulnerable to an ACIDRain attack.

We can see now only 1 transaction is able to perform an update:

  1. Both Worker 1 and Worker 2 start at the same time and start a transaction in their own session/goroutine.
  2. Transaction A (WaitGroup 1 End: Worker 0:) gets Balance: 100 from the select query, this time with FOR UPDATE, which tells all other transactions trying to access this row while the Transaction A remains open they have to wait until the Transaction A is committed or rolled back.
  3. Transaction B (WaitGroup 2 End: Worker 0:) gets Balance: 0 from the select query and rolls back the transaction as expected. This is because the row was locked until Transaction A was committed.

Shared Lock and Lock for Update in Laravel

Laravel allows you to use both FOR UPDATE and FOR SHARE in your queries. You can use the sharedLock method or lockForUpdate in your Laravel query using the Schema Builder.

$user = DB::table('users')->where('id', 1)->where('balance', '>=', 100)->sharedLock()->get();
// or
// $user = DB::table('users')->where('id', 1)->where('balance', '>=', 100)->lockForUpdate()->get();

if ($user) {
    // ... rest of your code ...
    $user->balance -= $requestedAmount;
    $user->save();
}

You can read more about this in the Laravel documentation here.

Method 2: Setting the isolation level to SERIALIZABLE

Another way to prevent ACIDRain vulnerabilities is to set the isolation level to SERIALIZABLE. This will prevent phantom reads and non-repeatable reads, and will also prevent the ACIDRain attack.

IMPORTANT: Setting the isolation level to SERIALIZABLE for a session or globally will tell MtSQL to treat all select statements in a transaction as if they are a SELECT...FOR SHARE.

You can set the isolation level to SERIALIZABLE in your MySQL connection string like so:

func connectDatabase() *sql.DB {
    db, err := sql.Open("mysql", "root@tcp(localhost)/dbname?transaction_isolation='SERIALIZABLE'")
    if err != nil {
        fmt.Println("Error opening database: ", err)
    }

    fmt.Println("Main: Opened database connection")
    return db
}

You can also set the isolation level at a session/transaction level using the SET TRANSACTION statement:

func startTransaction(db *sql.DB) {
    fmt.Println("Starting transaction")
    _, err := db.Exec("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
    if err != nil {
        fmt.Println("Error starting transaction: ", err)
    }
}

Or, if you wish to set the isolation level at a across all transactions, you can set the transaction_isolation in your MySQL configuration file, or via the SET GLOBAL ISOLATION LEVEL SERIALIZABLE statement.

You can rread more about this in the MySQL documentation here.

Conclusion

In this post, we have learned how to use Go to test MySQL queries for the ACIDRain attack. We have also learned two ways to prevent the ACIDRain attack: using FOR UPDATE or FOR SHARE in your SELECT query, and setting the isolation level to SERIALIZABLE. Which method you use will depend on your use case, but both methods will prevent the ACIDRain attack.