C言語+MySQL入門|同時操作を確かめる

C言語+MySQL入門|同時操作を確かめる

前の記事では、在庫数を条件に含めたUPDATEを使い、在庫が足りる場合だけ出庫する処理を作成しました。

今回は、その処理を二つの接続から同じ商品に対して実行したとき、どのように動くのかを確かめていきます。

在庫が5個の商品に対して、二人がそれぞれ4個の出庫を要求する場面を考えてみましょう。両方の出庫が確定してしまうと、在庫が足りません。

この記事では、次の内容を確認します。

  • 二つの接続を用意する方法
  • 同じ商品へのUPDATEが待ち状態になる様子
  • 先の接続がCOMMITした場合の結果
  • 先の接続がROLLBACKした場合の結果
  • Cプログラムでも、UPDATEの応答を待つこと
  • 在庫数と入出庫履歴が対応していることの確認

最初はMySQLクライアントでSQLを一つずつ実行し、処理の順番を見えるようにします。その後、Cプログラムからも同じ動作を確認します。

同時操作とは、処理の実行期間が重なること

同時操作を確かめるために、二人がまったく同じ瞬間にキーを押す必要はありません。

今回の確認では、次の順番で操作します。

  1. 接続Aで在庫を更新する。
  2. 接続Aのトランザクションを終了させずに待つ。
  3. 接続Bから同じ商品を更新する。
  4. 接続AをCOMMITまたはROLLBACKする。
  5. 接続Bの処理がどう進むかを確認する。

接続Aの処理が終わっていない間に、接続Bの処理を重ねることがポイントです。

二つの接続は、同じユーザー名でログインしていても構いません。接続が別であれば、それぞれ独立したトランザクションを持ちます。

接続役割
接続A先に在庫を更新し、未確定の状態で待つ
接続B同じ商品を更新しようとする

Cプログラムでも、別々に起動したプログラムがそれぞれ接続を作れば、同じように二つの接続から操作できます。

図1:二つの接続から同じ商品を出庫する

この図から分かること

二つの接続は、同じデータベースの同じ商品を操作します。

それぞれの接続が独立して処理を進めるため、ほかの接続による更新を考慮できる仕組みが必要です。

同じ行を更新すると、なぜ待つのか

InnoDBでは、UPDATEによって更新する行にロックが設定されます。

今回のSQLは、主キーのproduct_idで対象商品を指定しています。

UPDATE products
SET stock = stock - 4
WHERE product_id = 14
  AND stock >= 4;

接続Aがこの商品を更新している間、接続Bが同じ商品を更新しようとすると、必要なロックを取得できず、待ち状態になります。

大切なのは、UPDATEの実行が終わっても、トランザクションが終わっていなければロックが保持されることです。

接続AがCOMMITまたはROLLBACKすると、そのトランザクションで取得したロックが解放され、待っていた接続Bが処理を進められるようになります。

MySQLクライアントを二つ起動する

ターミナルを二つ開き、それぞれからMySQLに接続します。

以降は、一方を接続A、もう一方を接続Bと呼びます。

両方のターミナルで、次のコマンドを実行してください。

MySQLクライアントのあるフォルダーへ移動します。

cd /d "C:\Program Files\MySQL\MySQL Server 8.4\bin"

MySQLクライアントへ接続

mysql --protocol=TCP -h 127.0.0.1 -P 3306 -u inventory_user -pInventoryPass_2026

-pとパスワードの間には、空白を入れません。

まず、新しく接続した両方のMySQLクライアントで、inventory_dbを選択しましょう。

USE inventory_db;
実行結果の例
Database changed

次のSQLを実行します。

SET SESSION autocommit = 1;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET SESSION innodb_lock_wait_timeout = 300;

SELECT CONNECTION_ID() AS connection_id;

接続Aの実行結果の例です。

+---------------+
| connection_id |
+---------------+
|            41 |
+---------------+
1 row in set (0.01 sec)

接続Bでは、異なる番号が表示されます。

+---------------+
| connection_id |
+---------------+
|            42 |
+---------------+
1 row in set (0.00 sec)

番号そのものは環境によって異なります。二つの番号が異なることを確認してください。

今回の確認では、動作条件をそろえるために、分離レベルをREPEATABLE READに設定しています。

また、innodb_lock_wait_timeoutは、InnoDBの行ロックを待つ時間です。操作の切り替えに余裕を持てるよう、今回の接続だけ300秒にしています。サーバー全体の設定変更ではありません。

確認用の商品を二つ用意する

今回は、次の二つのケースを試します。

確認するケース最初の在庫接続Aの操作
COMMITするケース54個の出庫を確定する
ROLLBACKするケース54個の出庫を取り消す

ケースごとに別の商品を使うと、在庫を戻す操作が不要になり、結果を追いやすくなります。

接続Aで、次のSQLを実行してください。

START TRANSACTION;

INSERT INTO products (product_name, price, stock)
VALUES ('同時操作確認用COMMIT', 250.00, 5);

SET @commit_product_id = LAST_INSERT_ID();

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (@commit_product_id, 'IN', 5);

INSERT INTO products (product_name, price, stock)
VALUES ('同時操作確認用ROLLBACK', 250.00, 5);

SET @rollback_product_id = LAST_INSERT_ID();

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (@rollback_product_id, 'IN', 5);

COMMIT;

SELECT
    @commit_product_id AS commit_product_id,
    @rollback_product_id AS rollback_product_id;

途中のSQLでエラーが出た場合は、COMMITへ進まず、ROLLBACKしてください。

実行結果の例です。

+-------------------+---------------------+
| commit_product_id | rollback_product_id |
+-------------------+---------------------+
|                14 |                  15 |
+-------------------+---------------------+
1 row in set (0.00 sec)

14と15は説明用の例です。以降のSQLでは、実際に表示された商品IDを使用してください。

また、@commit_product_idなどのユーザー変数は接続ごとに管理されます。接続Aで設定した変数を、そのまま接続Bから使うことはできません。

この記事では、接続間で値を取り違えないよう、確認できた商品IDをSQLに直接指定します。

接続Aの出庫を未確定のままにする

まず、COMMITするケースを確認しましょう。

接続Aで、次のSQLを実行します。

START TRANSACTION;

UPDATE products
SET stock = stock - 4
WHERE product_id = 14
  AND stock >= 4;

実行結果の例です。

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

更新できたことを確認してから、出庫履歴を登録します。

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (14, 'OUT', 4);

実行結果の例です。

Query OK, 1 row affected

接続Aから在庫を確認すると、1個になっています。

SELECT product_id, stock
FROM products
WHERE product_id = 14;

+------------+-------+
| product_id | stock |
+------------+-------+
|         14 |     1 |
+------------+-------+
1 row in set (0.00 sec)

ここでは、まだCOMMITしないでください。

在庫の減算と履歴登録は、接続Aのトランザクション内で未確定の状態です。

接続Bから同じ商品を更新する

接続Bで、まず通常のSELECTを実行します。

SELECT product_id, stock
FROM products
WHERE product_id = 14;

実行結果の例です。

+------------+-------+
| product_id | stock |
+------------+-------+
|         14 |     5 |
+------------+-------+
1 row in set (0.00 sec)

接続Aでは1個と表示されましたが、接続Bには、まだ確定している在庫5個が見えています。

今回の分離レベルでは、通常のSELECTは、ほかの接続の未確定の変更を読みません。また、この通常のSELECTが更新用のロックを取得するわけでもありません。

続いて、接続Bで次のSQLを実行します。

START TRANSACTION;

UPDATE products
SET stock = stock - 4
WHERE product_id = 14
  AND stock >= 4;

ここで、UPDATEの結果がすぐには返ってこなくなります。

MySQLクライアントの入力待ち表示も戻らず、接続Aのトランザクションが終わるのを待ちます。

この時点では、接続Bに続きのSQLを貼り付けず、接続Aへ切り替えてください。

接続AでCOMMITする

接続Aで、次のSQLを実行します。

COMMIT;

接続Aの在庫の減算と出庫履歴が確定し、ロックが解放されます。

接続Bへ戻ると、待っていたUPDATEの結果が表示されます。

Query OK, 0 rows affected
Rows matched: 0  Changed: 0  Warnings: 0

続けて、接続Bで次のSQLを実行してください。

SELECT ROW_COUNT() AS updated_rows;

+--------------+
| updated_rows |
+--------------+
|            0 |
+--------------+
1 row in set (0.01 sec)

ROW_COUNTは直前の文の結果を調べるため、UPDATEの後、ほかのSQLを挟まずに実行します。

接続AのCOMMITによって、在庫は1個になっています。接続BのUPDATEは、その在庫に対してstock >= 4を満たせず、更新されません。

UPDATEによる更新は、先ほどの通常のSELECTで見えた5個をそのまま使う処理ではありません。更新時の状態に基づいて処理されます。

更新行数が0なので、接続Bでは出庫履歴を登録せず、トランザクションを終了します。

ROLLBACK;

COMMITしたケースの結果を確認する

両方のトランザクションが終了したら、接続Aで確認します。

SELECT product_id, stock
FROM products
WHERE product_id = 14;

SELECT movement_type, quantity
FROM stock_history
WHERE product_id = 14
ORDER BY history_id;

実行結果の例です。

+------------+-------+
| product_id | stock |
+------------+-------+
|         14 |     1 |
+------------+-------+
1 row in set (0.00 sec)

+---------------+----------+
| movement_type | quantity |
+---------------+----------+
| IN            |        5 |
| OUT           |        4 |
+---------------+----------+
2 rows in set (0.00 sec)

二つの接続から4個ずつ出庫しようとしましたが、確定した出庫は一つだけです。

在庫数も、5 − 4 = 1で一致しています。

図2:先の接続がCOMMITすると、後の出庫は在庫不足になる

この図から分かること

接続Bは、接続Aの未確定の更新が終わるまで待ちます。

接続AがCOMMITすると、減算後の在庫に基づいて接続Bの更新が判断されます。両方が古い在庫数を使って出庫を確定することはありません。

接続AがROLLBACKする場合も確認する

次は、もう一つの商品を使います。説明用の商品IDは15です。

接続Aで、次のSQLを実行します。

START TRANSACTION;

UPDATE products
SET stock = stock - 4
WHERE product_id = 15
  AND stock >= 4;

実行結果の例です。

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新行数が1であることを確認してから、履歴を登録します。

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (15, 'OUT', 4);

ここでも、COMMITせずに待ちます。

接続Bで、次のSQLを実行してください。

START TRANSACTION;

UPDATE products
SET stock = stock - 4
WHERE product_id = 15
  AND stock >= 4;

接続Bが待ち状態になったら、接続Aへ切り替え、今回はROLLBACKします。

ROLLBACK;

接続Aの在庫の減算と履歴登録が取り消され、在庫は5個の状態に戻ります。

接続Bへ戻ると、今度は更新行数が1になります。

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

直後に更新行数を確認します。

SELECT ROW_COUNT() AS updated_rows;

+--------------+
| updated_rows |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

接続Bは、在庫が5個ある状態で4個を減算できました。

更新行数が1であることを確認できた場合だけ、接続Bで履歴を登録し、COMMITします。

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (15, 'OUT', 4);

COMMIT;

ROLLBACKしたケースの結果を確認する

接続Aで、次のSQLを実行します。

SELECT product_id, stock
FROM products
WHERE product_id = 15;

SELECT movement_type, quantity
FROM stock_history
WHERE product_id = 15
ORDER BY history_id;

実行結果の例です。

+------------+-------+
| product_id | stock |
+------------+-------+
|         15 |     1 |
+------------+-------+
1 row in set (0.00 sec)

+---------------+----------+
| movement_type | quantity |
+---------------+----------+
| IN            |        5 |
| OUT           |        4 |
+---------------+----------+
2 rows in set (0.00 sec)

結果の数字は先ほどと同じですが、確定した出庫の接続が違います。

ケース接続A接続B最終在庫
接続AがCOMMIT出庫を確定在庫不足で更新しない1
接続AがROLLBACK出庫を取り消す出庫を確定1

後者では、接続Aの出庫履歴が取り消され、接続Bが登録した出庫履歴だけが残っています。

Cプログラムでも待ち状態を確認する

MySQLクライアントで確認した仕組みは、C言語からSQLを実行するときも同じです。

今回は、更新の直前と直後にメッセージを表示する、動作確認用のプログラムを作成します。

前の記事と同じ条件付きUPDATEを使用し、更新できた場合だけOUTの履歴を登録します。

条件付きUPDATEの応答を待つ様子を確認するプログラム

ファイル名: stock_concurrency_probe.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <limits.h>
#include <mysql.h>

/* 接続側のエラー情報を表示する */
static void print_mysql_error(
    MYSQL *conn,
    const char *operation
)
{
    fflush(stdout);

    fprintf(
        stderr,
        "%s: error=%u, SQLSTATE=%s, message=%s\n",
        operation,
        mysql_errno(conn),
        mysql_sqlstate(conn),
        mysql_error(conn)
    );
}

/* ステートメントが有効な間にエラー情報を表示する */
static void print_statement_error(
    MYSQL_STMT *stmt,
    const char *operation
)
{
    fflush(stdout);

    fprintf(
        stderr,
        "%s: error=%u, SQLSTATE=%s, message=%s\n",
        operation,
        mysql_stmt_errno(stmt),
        mysql_stmt_sqlstate(stmt),
        mysql_stmt_error(stmt)
    );
}

/* 数字だけで構成された文字列を正の整数へ変換する */
static int parse_positive_int(
    const char *text,
    int *result
)
{
    const unsigned char *p;
    char *end = NULL;
    long value;

    if (text == NULL || result == NULL || *text == '\0') {
        return -1;
    }

    for (p = (const unsigned char *)text; *p != '\0'; ++p) {
        if (*p < '0' || *p > '9') {
            return -1;
        }
    }

    errno = 0;
    value = strtol(text, &end, 10);

    if (errno == ERANGE ||
        end == text ||
        *end != '\0' ||
        value <= 0 ||
        value > INT_MAX) {
        return -1;
    }

    *result = (int)value;
    return 0;
}

/*
 * 最大三つの整数をバインドして更新系SQLを実行する。
 * トランザクションの開始と終了はmainで行う。
 */
static int execute_change(
    MYSQL *conn,
    const char *sql,
    int *values,
    unsigned long value_count,
    my_ulonglong *affected_rows
)
{
    MYSQL_STMT *stmt = NULL;
    MYSQL_BIND bind[3] = {0};
    int status = -1;

    *affected_rows = 0;

    if (value_count == 0 || value_count > 3) {
        fflush(stdout);
        fprintf(stderr, "パラメーター数が不正です。\n");
        return -1;
    }

    stmt = mysql_stmt_init(conn);

    if (stmt == NULL) {
        print_mysql_error(conn, "mysql_stmt_init");
        goto cleanup;
    }

    if (mysql_stmt_prepare(
            stmt,
            sql,
            (unsigned long)strlen(sql)
        ) != 0) {
        print_statement_error(stmt, "mysql_stmt_prepare");
        goto cleanup;
    }

    if (mysql_stmt_param_count(stmt) != value_count) {
        fflush(stdout);
        fprintf(stderr, "プレースホルダーの数が一致しません。\n");
        goto cleanup;
    }

    for (unsigned long i = 0; i < value_count; ++i) {
        bind[i].buffer_type = MYSQL_TYPE_LONG;
        bind[i].buffer = &values[i];
        bind[i].buffer_length = sizeof(values[i]);
        bind[i].is_unsigned = 0;
    }

    if (mysql_stmt_bind_param(stmt, bind) != 0) {
        print_statement_error(stmt, "mysql_stmt_bind_param");
        goto cleanup;
    }

    /* 同じ行がロックされている場合、ここで応答を待つ */
    if (mysql_stmt_execute(stmt) != 0) {
        print_statement_error(stmt, "mysql_stmt_execute");
        goto cleanup;
    }

    *affected_rows = mysql_stmt_affected_rows(stmt);

    if (*affected_rows == (my_ulonglong)-1) {
        print_statement_error(stmt, "mysql_stmt_affected_rows");
        goto cleanup;
    }

    status = 0;

cleanup:
    if (stmt != NULL) {
        /* 終了後はstmtを参照せず、接続側のエラーを使う */
        if (mysql_stmt_close(stmt) != 0) {
            print_mysql_error(conn, "mysql_stmt_close");
            status = -1;
        }

        stmt = NULL;
    }

    return status;
}

int main(int argc, char *argv[])
{
    const char *update_sql =
        "UPDATE products "
        "SET stock = stock - ? "
        "WHERE product_id = ? AND stock >= ?";

    const char *history_sql =
        "INSERT INTO stock_history "
        "(product_id, movement_type, quantity) "
        "VALUES (?, 'OUT', ?)";

    MYSQL *conn = NULL;
    int product_id;
    int quantity;
    int update_values[3];
    int history_values[2];

    my_ulonglong affected_rows = 0;
    int transaction_started = 0;
    int outcome_uncertain = 0;
    int exit_status = EXIT_FAILURE;

    if (argc != 3) {
        fprintf(
            stderr,
            "使い方: %s 商品ID 出庫数量\n",
            argv[0]
        );
        return EXIT_FAILURE;
    }

    if (parse_positive_int(argv[1], &product_id) != 0 ||
        parse_positive_int(argv[2], &quantity) != 0) {
        fprintf(
            stderr,
            "商品IDと出庫数量には、"
            "int型の範囲内の正の整数を指定してください。\n"
        );
        return EXIT_FAILURE;
    }

    update_values[0] = quantity;
    update_values[1] = product_id;
    update_values[2] = quantity;

    history_values[0] = product_id;
    history_values[1] = quantity;

    conn = mysql_init(NULL);

    if (conn == NULL) {
        fprintf(stderr, "mysql_initに失敗しました。\n");
        return EXIT_FAILURE;
    }

    /* 使用する環境に合わせて接続設定を変更する */
    if (mysql_real_connect(
            conn,
            "127.0.0.1",
            "inventory_user",
            "InventoryPass_2026",
            "inventory_db",
            3306,
            NULL,
            0
        ) == NULL) {
        print_mysql_error(conn, "mysql_real_connect");
        goto cleanup;
    }

    if (mysql_set_character_set(conn, "utf8mb4") != 0) {
        print_mysql_error(conn, "mysql_set_character_set");
        goto cleanup;
    }

    /* 新しく作成した専用接続で、処理開始前に設定する */
    if (mysql_autocommit(conn, 1) != 0) {
        print_mysql_error(conn, "mysql_autocommit");
        goto cleanup;
    }

    if (mysql_query(
            conn,
            "SET SESSION TRANSACTION ISOLATION LEVEL "
            "REPEATABLE READ"
        ) != 0) {
        print_mysql_error(conn, "SET TRANSACTION ISOLATION LEVEL");
        goto cleanup;
    }

    /* 動作確認のため、この接続の行ロック待ちを300秒にする */
    if (mysql_query(
            conn,
            "SET SESSION innodb_lock_wait_timeout = 300"
        ) != 0) {
        print_mysql_error(conn, "SET innodb_lock_wait_timeout");
        goto cleanup;
    }

    printf("Connection ID: %lu\n", mysql_thread_id(conn));
    printf("Product ID: %d\n", product_id);
    printf("Outgoing quantity: %d\n", quantity);

    if (mysql_query(conn, "START TRANSACTION") != 0) {
        print_mysql_error(conn, "START TRANSACTION");
        goto cleanup;
    }

    transaction_started = 1;
    printf("Transaction started.\n");

    /*
     * 待ち状態を観察できるよう、
     * UPDATEの前に表示内容を確実に出力する。
     */
    printf("Executing conditional UPDATE...\n");
    fflush(stdout);

    if (execute_change(
            conn,
            update_sql,
            update_values,
            3,
            &affected_rows
        ) != 0) {
        goto cleanup;
    }

    printf(
        "UPDATE returned. Affected rows: %llu\n",
        (unsigned long long)affected_rows
    );

    if (affected_rows == 0) {
        printf(
            "商品が存在しないか、在庫が不足しているため、"
            "出庫できませんでした。\n"
        );

        exit_status = 2;
        goto cleanup;
    }

    if (affected_rows != 1) {
        fflush(stdout);
        fprintf(stderr, "在庫の更新行数が想定と異なります。\n");
        goto cleanup;
    }

    if (execute_change(
            conn,
            history_sql,
            history_values,
            2,
            &affected_rows
        ) != 0) {
        goto cleanup;
    }

    if (affected_rows != 1) {
        fflush(stdout);
        fprintf(stderr, "履歴の登録行数が想定と異なります。\n");
        goto cleanup;
    }

    printf("History inserted.\n");

    if (mysql_commit(conn) != 0) {
        print_mysql_error(conn, "mysql_commit");

        /* 確定済みか分からない可能性があるため区別する */
        outcome_uncertain = 1;
        goto cleanup;
    }

    transaction_started = 0;
    exit_status = EXIT_SUCCESS;

    printf("Transaction committed.\n");
    printf("出庫が完了しました。\n");

cleanup:
    if (transaction_started) {
        if (mysql_rollback(conn) != 0) {
            print_mysql_error(conn, "mysql_rollback");
            outcome_uncertain = 1;
        } else if (outcome_uncertain) {
            /*
             * 後のROLLBACKが成功しても、
             * 先のCOMMITの結果までは断定しない。
             */
            printf(
                "ROLLBACK request succeeded; "
                "check the outcome.\n"
            );
        } else {
            printf("Transaction rolled back.\n");
        }
    }

    if (outcome_uncertain) {
        fflush(stdout);

        fprintf(
            stderr,
            "処理結果を確認できませんでした。"
            "再実行前に在庫と履歴を確認してください。\n"
        );

        exit_status = 3;
    }

    if (conn != NULL) {
        mysql_close(conn);
        conn = NULL;
        printf("Connection handle released.\n");
    }

    return exit_status;
}

プログラムの確認ポイント

UPDATEの前後にメッセージを表示する

プログラムは、UPDATEを実行する前に次のメッセージを表示します。

Executing conditional UPDATE...

その直後にfflushを呼んでいるため、SQLの応答を待つ前に、メッセージがターミナルに出力されます。

UPDATEが完了すると、次のメッセージが表示されます。

UPDATE returned. Affected rows: 0

この二つのメッセージの間で表示が止まれば、今回の手順では、mysql_stmt_executeが行ロックの解放を待っている状態を観察できます。

待っている間は履歴登録に進まない

mysql_stmt_executeの応答が返るまで、その後の更新行数の確認や履歴登録には進みません。

待ち状態から戻った後に、更新行数を確認します。

UPDATEの結果プログラムの処理
更新1行OUT履歴を登録してCOMMIT
更新0行履歴を登録せずROLLBACK
SQLエラーエラーを表示してROLLBACKを試みる

終了ステータスを分ける

この動作確認用プログラムでは、次の終了ステータスを使用します。

終了ステータス意味
0出庫を確定できた
2商品が存在しないか、在庫不足で出庫できなかった
3確定または取り消しの結果を確認できなかった
その他の非0引数やSQL実行などのエラー

在庫不足とSQLエラーを、同じ結果として扱わないようにしています。

コンパイルする

MSYS2 UCRT64のターミナルで、ソースファイルを保存したフォルダーへ移動します。

cd /c/c-mysql/chap08

次のコマンドでコンパイルします。

gcc -std=c17 -Wall -Wextra -Wpedantic \
  stock_concurrency_probe.c -o stock_concurrency_probe.exe \
  $(pkg-config --cflags --libs mysqlclient)

Cプログラム用の商品を用意する

先ほどの二つの商品は、すでに在庫が1個になっています。

新しい確認用商品を作り、在庫5個とINの履歴をまとめて登録します。接続Aで実行してください。

START TRANSACTION;

INSERT INTO products (product_name, price, stock)
VALUES ('C言語同時操作確認用', 250.00, 5);

SET @probe_product_id = LAST_INSERT_ID();

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (@probe_product_id, 'IN', 5);

COMMIT;

SELECT @probe_product_id AS product_id;

実行結果の例です。

+------------+
| product_id |
+------------+
|         16 |
+------------+
1 row in set (0.00 sec)

以降の商品IDも、実際に表示された値へ置き換えてください。

接続AのロックをCプログラムから待つ

接続Aで、次のSQLを実行します。

START TRANSACTION;

UPDATE products
SET stock = stock - 4
WHERE product_id = 16
  AND stock >= 4;

更新行数が1であることを確認してから、履歴を登録します。

INSERT INTO stock_history
    (product_id, movement_type, quantity)
VALUES
    (16, 'OUT', 4);

COMMITせずに、そのまま待ちます。

続いて、MySQLクライアントとは別のMSYS2 UCRT64ターミナルで、Cプログラムを実行してください。

./stock_concurrency_probe.exe 16 4

待ち状態での実行結果の例

Connection ID: 26
Product ID: 16
Outgoing quantity: 4
Transaction started.
Executing conditional UPDATE...

この状態では、まだUPDATE returnedの行が表示されません。

接続Aへ戻り、300秒以内にCOMMITしてください。

COMMIT;

Cプログラム側の処理が進みます。

COMMIT後の実行結果の例

Connection ID: 26
Product ID: 16
Outgoing quantity: 4
Transaction started.
Executing conditional UPDATE...
UPDATE returned. Affected rows: 0
商品が存在しないか、在庫が不足しているため、出庫できませんでした。
Transaction rolled back.
Connection handle released.

プログラム終了後、次のコマンドで終了ステータスを確認できます。

echo $?
2
echo $?
2

C言語側で特別な待機処理を書かなくても、同じ商品の更新に必要なロックが取得できるまで、SQLの実行が待つことを確認できます。

最終的な在庫と履歴を照合する

接続Aで、次のSQLを実行します。

SELECT product_id, stock
FROM products
WHERE product_id = 16;

SELECT movement_type, quantity
FROM stock_history
WHERE product_id = 16
ORDER BY history_id;

実行結果の例です。

+------------+-------+
| product_id | stock |
+------------+-------+
|         16 |     1 |
+------------+-------+
1 row in set (0.00 sec)

+---------------+----------+
| movement_type | quantity |
+---------------+----------+
| IN            |        5 |
| OUT           |        4 |
+---------------+----------+
2 rows in set (0.00 sec)

今回の商品は、初期在庫0からINの履歴で5個を登録したものなので、履歴から計算した在庫とも比較できます。

SELECT
    p.product_id,
    p.stock,
    COALESCE(
        SUM(
            CASE
                WHEN h.movement_type = 'IN' THEN h.quantity
                WHEN h.movement_type = 'OUT' THEN -h.quantity
                ELSE 0
            END
        ),
        0
    ) AS stock_from_history
FROM products AS p
LEFT JOIN stock_history AS h
    ON h.product_id = p.product_id
WHERE p.product_id = 16
GROUP BY p.product_id, p.stock;

実行結果の例です。

+------------+-------+--------------------+
| product_id | stock | stock_from_history |
+------------+-------+--------------------+
|         16 |     1 |                  1 |
+------------+-------+--------------------+
1 row in set (0.03 sec)

この照合が成り立つのは、今回のように初期在庫を含めたすべての増減が履歴に記録されている場合です。

在庫数が負になっていないことに加えて、出庫できなかった接続の履歴が追加されていないことも確認しましょう。

図3:待機後の結果に応じて、在庫と履歴を対応させる

この図から分かること

待っていた接続の結果は、先の接続がCOMMITするか、ROLLBACKするかによって変わります。

どちらの場合も、成功した出庫だけを履歴へ記録し、在庫数と履歴を対応させることが大切です。

想定した結果にならない場合に確認すること

待ち状態にならない

まず、次の点を確認してください。

  • 二つの接続が、同じ商品IDを指定している。
  • 接続AでUPDATEの更新行数が1になっている。
  • 接続AがSTART TRANSACTIONを実行している。
  • 接続Aで、まだCOMMITもROLLBACKも実行していない。
  • productsとstock_historyがInnoDBで作成されている。

テーブルのエンジンは、次のSQLで確認できます。

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('products', 'stock_history');

期待する結果は、両方のENGINEがInnoDBになっていることです。

実行結果の例です。

+---------------+--------+
| TABLE_NAME    | ENGINE |
+---------------+--------+
| products      | InnoDB |
| stock_history | InnoDB |
+---------------+--------+
2 rows in set (0.02 sec)

また、通常のプログラムを二つ起動しただけでは、片方がすぐに処理を終え、待ち状態が目で見えない場合があります。今回のように、一方を未確定の状態で止めておくと観察しやすくなります。

ロック待ちのタイムアウトが発生した

設定した時間を超えて待つと、次のようなエラーが発生します。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

これは、在庫不足による更新行数0とは異なります。UPDATEの実行自体がエラーで終了した状態です。

MySQLの標準的な設定では、ロック待ちのタイムアウトで取り消されるのは、そのSQL文であり、トランザクション全体とは限りません。そのため、MySQLクライアントで確認中にエラーが出た場合も、ROLLBACKで処理を区切ってください。

今回のCプログラムも、SQLエラー時は履歴登録へ進まず、トランザクション全体のROLLBACKを試みます。

ロック待ちとデッドロックを区別する

今回の基本手順では、接続Bが接続Aを待っています。接続Aは接続Bを待っていないため、待ち関係が循環していません。

一方、複数の商品を異なる順番で更新する処理では、互いに相手のロック解放を待つデッドロックが発生することがあります。

状態概要
ロック待ち必要なロックが解放されるのを待っている
ロック待ちタイムアウト待ち時間の上限に達した
デッドロック複数のトランザクションの待ち関係が循環している

InnoDBがデッドロックを検出すると、対象となったトランザクションの一つが取り消されます。アプリケーションでは、こうしたエラーも処理する必要があります。

実際の出庫処理ではトランザクションを短くする

この記事では、待ち状態を観察するために、意図的にトランザクションを未確定のまま止めました。

実際のアプリケーションでは、商品IDや数量の入力、入力内容の確認などを済ませてからトランザクションを開始します。

開始後は、在庫更新、履歴登録、COMMITまたはROLLBACKまでを、できるだけ速やかに実行しましょう。利用者の入力待ちを挟むと、ほかの接続を長く待たせる原因になります。

次の記事では、SQLの失敗、履歴登録の失敗、接続エラーが発生した場合について、トランザクションとステートメントの後片付けを整理していきます。

Follow me!