Tag: MySQL

When developing an e-commerce application, unless you work at United Airlines, you generally want to avoid overselling.

Instead of punching your customers in the face why not use MySQL Deadlocks? (Turns out this is a feature not a bug!)

First attempt, creating deadlocks

MySQL has 4 transaction isolation levels: SERIALIZABLE, REPEATABLE READ, READ UNCOMMITTED, READ COMMITTED.

In the following proof of concept, where we have 50 of the same product in stock, and we run seige to represent concurrent customers buying the same product at the same time, we expect 50 “Success!” messages in our log files.

When we use any of REPEATABLE READ, READ UNCOMMITTED, or READ COMMITTED we oversell. (boo!)

When we use SERIALIZABLE we do not oversell (yay!) but some users get deadlock errors while others do not. (SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

<?php

error_reporting(E_ALL | E_STRICT); // Development

/*
SQL:
CREATE DATABASE `deadlocktest` COLLATE 'utf8_general_ci';
CREATE TABLE `products` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `inventory` int NOT NULL );
INSERT INTO `products` (`id`, `inventory`) VALUES ('123', '50');

USEFUL LINUX COMMANDS:
$ rm log.txt; touch log.txt; chmod 777 log.txt
$ seige http://host/file.php
*/

// ------------------------------------------------------------------
// Config
// ------------------------------------------------------------------

$mysqlIsolation = 'SERIALIZABLE'; // ( SERIALIZABLE, REPEATABLE READ, READ UNCOMMITTED, READ COMMITTED )
$productId = 123;
$logFile = __DIR__ . '/log.txt';

$host = '127.0.0.1';
$db = 'deadlocktest';
$user = 'root';
$pass = '';
$charset = 'utf8';
$opt = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

// ------------------------------------------------------------------
// Functions
// ------------------------------------------------------------------

/**
 * Simulate time it takes to call the payment gateway and do stuff
 */
function doPaymentGatewayStuff()
{
    usleep(500000); // Wait for 0.5 seconds
}

/**
 * Simulate buying a product from our inventory
 *
 * @param PDO $pdo
 * @param int $productId
 * @return int
 * @throws Exception
 */
function buyProduct(PDO $pdo, int $productId): int
{
    $pdo->beginTransaction();

    $selectStmt = $pdo->prepare('SELECT inventory FROM products WHERE id = :id ');
    $selectStmt->execute(['id' => $productId]);
    $res = $selectStmt->fetch();
    if ($res['inventory'] <= 0) {
        throw new Exception("Oh no! Sorry we're out inventory!");
    }

    $newInventory = $res['inventory'] - 1;
    $updateStmt = $pdo->prepare('UPDATE products SET inventory = :inventory WHERE id = :id ');
    $updateStmt->execute(['inventory' => $newInventory, 'id' => $productId]);

    doPaymentGatewayStuff();

    $pdo->commit();

    return $newInventory;
}

// ------------------------------------------------------------------
// Procedure
// ------------------------------------------------------------------

$uniqueUser = uniqid();
try {
    // Set up DB driver
    $pdo = new PDO("mysql:host={$host};dbname={$db};charset={$charset}", $user, $pass, $opt);
    $pdo->query("SET TRANSACTION ISOLATION LEVEL {$mysqlIsolation} ");

    // Simulate buying a product and decreasing inventory
    $newInventory = buyProduct($pdo, $productId);

    // No exceptions were thrown, we consider this successful
    $successMsg = "{$uniqueUser} - Success! Product {$productId} inventory has been decreased to {$newInventory}" . PHP_EOL;
    file_put_contents($logFile, $successMsg, FILE_APPEND);
    echo "$successMsg";
}
catch (Exception $e) {
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    $errorMsg = "{$uniqueUser} - Error! " . $e->getMessage() . PHP_EOL;
    file_put_contents($logFile, $errorMsg, FILE_APPEND);
    echo "$errorMsg";
}

Second attempt, handling deadlocks

The above code has good intentions but many users get the dreaded deadlock message.

Turns out deadlocks are OK! You just have to handle them somehow.

Here’s a fixed proof of concept:

<?php

// ------------------------------------------------------------------
// Config
// ------------------------------------------------------------------

$mysqlIsolation = 'SERIALIZABLE';
$productId = 123;
$logFile = __DIR__ . '/log.txt';

$host = '127.0.0.1';
$db = 'deadlocktest';
$user = 'root';
$pass = '';
$charset = 'utf8';
$opt = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

// ------------------------------------------------------------------
// Functions
// ------------------------------------------------------------------

/**
 * Check if $e is of type MySQL deadlock
 *
 * @param PDO $pdo
 * @param mixed $e
 * @return bool
 */
function isDeadlock(PDO $pdo, $e): bool
{
    return (
        $e instanceof PDOException &&
        $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql' &&
        $e->errorInfo[0] == 40001 &&
        $e->errorInfo[1] == 1213
    );
}

/**
 * Simulate time it takes to call the payment gateway and do stuff
 */
function doPaymentGatewayStuff()
{
    usleep(500000); // Wait for 0.5 seconds
}

/**
 * Simulate buying a product from our inventory
 *
 * @param PDO $pdo
 * @param int $productId
 * @return int
 * @throws Exception
 */
function buyProduct(PDO $pdo, int $productId): int
{
    $pdo->beginTransaction();

    $selectStmt = $pdo->prepare('SELECT inventory FROM products WHERE id = :id ');
    $selectStmt->execute(['id' => $productId]);
    $res = $selectStmt->fetch();
    if ($res['inventory'] <= 0) {
        throw new Exception("Oh no! Sorry we're out inventory!");
    }

    $newInventory = $res['inventory'] - 1;
    $updateStmt = $pdo->prepare('UPDATE products SET inventory = :inventory WHERE id = :id ');
    $updateStmt->execute(['inventory' => $newInventory, 'id' => $productId]);

    doPaymentGatewayStuff();

    $pdo->commit();

    return $newInventory;
}

// ------------------------------------------------------------------
// Procedure
// ------------------------------------------------------------------

$uniqueUser = uniqid();
$retry = true;
while ($retry)
{
    try {
        // Set up DB driver
        $pdo = new PDO("mysql:host={$host};dbname={$db};charset={$charset}", $user, $pass, $opt);
        $pdo->query("SET TRANSACTION ISOLATION LEVEL {$mysqlIsolation} ");

        // Simulate buying a product and decreasing inventory
        $newInventory = buyProduct($pdo, $productId);

        // No exceptions were thrown, we consider this successful
        $successMsg = "{$uniqueUser} - Success! Product {$productId} inventory has been decreased to {$newInventory}" . PHP_EOL;
        file_put_contents($logFile, $successMsg, FILE_APPEND);
        echo "$successMsg";
        $retry = false;
    }
    catch (Exception $e) {
        if (isset($pdo) && isDeadlock($pdo, $e)) {
            $retry = true;
        } else {
            $retry = false;
            if (isset($pdo) && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            $errorMsg = "{$uniqueUser} - Error! " . $e->getMessage() . PHP_EOL;
            file_put_contents($logFile, $errorMsg, FILE_APPEND);
            echo "$errorMsg";
        }
    }
}

Huge gaping caveat: With 15 concurrent users the 15th user would be waiting for a long time. Patches welcome.