Load testing API (Vanilla PHP vs Laravel vs Go) with MySQL pessimistic locking

import http from "k6/http";

export const options = {
    // A number specifying the number of VUs to run concurrently.
    vus: 10,
    // A string specifying the total duration of the test run.
    duration: "30s",
};

// The function that defines VU logic.
export default function () {
    http.post(
        "http://localhost:8080/api/test",
        JSON.stringify({ username: "usertest" }),
        {
            headers: {
                "Content-Type": "application/json",
                Accept: "application/json",
            },
        }
    );
}

<?php

function initDB() {
    try {
        // Create a new PDO instance
        $pdo = new PDO("mysql:host=localhost;dbname=dbname", 'root', '');

        // Set PDO to throw exceptions on error
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $pdo;
    } catch (PDOException $e) {
        // Display error message if connection fails
        echo "Connection failed: " . $e->getMessage();
        die(); // Terminate script
    }
}

function isRoute(string $method, string $route, array ...$handlers): int
{
    global $params;
    $uri = parse_url($_SERVER['REQUEST_URI'])['path'];
    $route_rgx = preg_replace('#:(\w+)#', '(?<$1>(\S+))', $route);
    return preg_match("#^$route_rgx$#", $uri, $params);
}

(match (1) {
    isRoute('POST', '/api/test') => function () {
        $request = json_decode(file_get_contents('php://input'), true);

        $maxRetries = 1; // Maximum number of retries
        $retryCount = 0;

        while (true) {
            // Retry logic
            if ($retryCount >= $maxRetries) {
                return json_encode(['error' => 'Failed to update user balance after maximum retries']);
            }

            try {
                $pdo = initDB();

                // Start a transaction
                $pdo->beginTransaction();
                $stmt = $pdo->prepare("SELECT id, balance, version FROM users WHERE username = ? FOR UPDATE");
                $stmt->execute([$request['username']]);
                $user = $stmt->fetch(PDO::FETCH_ASSOC);

                if (!$user) {
                    $pdo->rollBack();
                    return json_encode(['error' => 'user does not exist']);
                }

                // Save the user (update with optimistic locking)
                $stmt = $pdo->prepare("UPDATE users SET balance = balance - 1, version = version + 1 WHERE id = ? AND version = ?");
                $updated_rows = $stmt->execute([$user['id'], $user['version']]);

                if ($updated_rows !== 0) {
                    // Commit the transaction
                    $pdo->commit();
                    return json_encode(['balance' => $user['balance']]);
                }
            } catch (PDOException $e) {
                // Rollback the transaction if there is any error
                $pdo->rollBack();

                // Handle other query exceptions
                return json_encode(['error' => 'Failed to update user balance']);
            }

            $pdo->rollBack();
            $retryCount++;
        }
    }
})();

<?php

use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;

Route::post('/test', function (Request $request) {
    $max_retries = 1; // Maximum number of retries
    $retry_count = 0;

    while (true) {
        if ($retry_count >= $max_retries) {
            return response()->json(['error' => 'Retry count exceeded'], 500);
        }

        try {
            // Start a transaction
            DB::beginTransaction();

            $rows = DB::select('SELECT id, balance, version FROM users WHERE username = ? FOR UPDATE', [$request->username]);

            if (count($rows) === 0) {
                DB::rollBack();
                return response()->json(['error' => 'user not exists'], 404);
            }

            // Update the user balance (update with optimistic locking)
            $updated_rows = DB::affectingStatement('UPDATE users SET balance = balance - 1, version = version + 1 WHERE id = ? AND version = ?', [$rows[0]->id, $rows[0]->version]);

            if ($updated_rows !== 0) {
                // Commit the transaction
                DB::commit();

                return response()->json(['message' => $user->balance]);
            }
        } catch (\Throwable $th) {
            // Rollback the transaction if there is any error
            DB::rollBack();

            // Handle other query exceptions
            return response()->json(['error' => 'Failed to update user balance'], 500);
        }

        DB::rollBack();
        $retry_count++;
    }

    return response()->json(['error' => 'Failed to update user balance'], 500);
});

package main

import (
    "database/sql"
    "errors"
    "fmt"
    "log"
    "net/http"
    "time"

    "github.com/gin-gonic/gin"
    "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func initDB() {
    // Capture connection properties
    cfg := mysql.Config{
        User:   "root",
        Passwd: "",
        Net:    "tcp",
        Addr:   "127.0.0.1:3306",
        DBName: "dbname",
    }
    // Get a database handle.
    var err error
    db, err = sql.Open("mysql", cfg.FormatDSN())
    if err != nil {
        log.Fatal(err)
    }

    pingErr := db.Ping()
    if pingErr != nil {
        log.Fatal(pingErr)
    }

    db.SetMaxIdleConns(10)
    db.SetMaxOpenConns(100)
    db.SetConnMaxIdleTime(5 * time.Minute)
    db.SetConnMaxLifetime(1 * time.Hour)
}

func init() {
    initDB()
}

func main() {
    router := gin.Default()
    router.POST("/api/test", test)

    router.Run("localhost:8080")
}

func test(c *gin.Context) {
    requestBody := struct {
        Username string `json:"username"`
    }{}

    if err := c.BindJSON(&requestBody); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"message": err.Error()})
        return
    }

    err := updateBalance(requestBody.Username, 0)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"code": 1, "msg": "error", "data": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"code": 0, "msg": "success", "data": 0})
}

var maxRetries = 1

func updateBalance(username string, retry int) error {
    // check retry count
    if retry >= maxRetries {
        return errors.New("max retries exceeded")
    }

    user := struct {
        ID uint64 `json:"id"`
        Balance float64 `json:"balance"`
        Version uint64 `json:"version"`
    }{}

    dbt, err := db.Begin()
    if err != nil {
        return err
    }

    row := dbt.QueryRow("SELECT id, balance, version FROM users WHERE username = ? FOR UPDATE", username)
    if err := row.Scan(&user.ID, &user.Balance, $user.Version); err != nil {
        dbt.Rollback()
        return err
    }

    res, err := dbt.Exec("UPDATE users SET balance = balance - 1, version = version + 1 WHERE id = ? AND version = ?", user.ID, user.Version)
    if err != nil {
        dbt.Rollback()
        return err
    }

    rowAffected, err := res.RowsAffected()
    if err != nil {
        dbt.Rollback()
        return err
    }

    if rowAffected == 0 {
        dbt.Rollback()
        return updateBalance(username, retry+1)
    }

    err = dbt.Commit()
    if err != nil {
        return err
    }

    return nil
}

go run main.go