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

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“,
},
}
);
}

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++;
}
}
})();

use AppModelsUser;
use IlluminateHttpRequest;
use IlluminateSupportFacadesDB;
use IlluminateSupportFacadesRoute;

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);
});

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
}