MySQL
MYSQL STEP 05

更新とトランザクション

複数の変更を安全に確定し、失敗したときに元へ戻します。

MySQL入門の講座一覧

01環境構築と接続02SQLと基本操作03検索・集計・結合04テーブル設計と制約05更新とトランザクション06PHPからMySQLを使う07MySQL練習問題
01

トランザクションの基本

トランザクションは、複数のSQLを一つのまとまりとして扱う仕組みです。注文を登録し、在庫を減らす処理を別々に確定すると不整合が起きるため、同時に成功させます。

確定する流れMYSQL
START TRANSACTION;

UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;

INSERT INTO orders (product_id, quantity, ordered_at)
VALUES (1, 1, CURRENT_DATE);

COMMIT;
実行結果の確認OUTPUT
UPDATE: 1 row affected
INSERT: 1 row affected
COMMIT: 完了

在庫が足りない場合など、アプリケーション側で更新件数を確認してからCOMMITします。失敗時は必ずROLLBACKします。

02

ROLLBACKで取り消す

次の結果は、節01を実行していない状態で、開始時の在庫が5の場合の例です。節01を実行してCOMMITしたあとなら、開始時の値は4になり、表示も1ずつ小さくなります。

確認後に取り消すMYSQL
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT id, name, stock FROM products WHERE id = 1;
ROLLBACK;

SELECT id, name, stock FROM products WHERE id = 1;
結果の考え方OUTPUT
トランザクション中: 4
ROLLBACK後:        5

InnoDBはトランザクションをサポートします。テーブルのエンジンやDDLの暗黙コミットなど、実行前に公式マニュアルで確認してください。

03

同時更新とロック

複数の利用者が同じ在庫を読むと、両方が「在庫あり」と判断してしまうことがあります。更新対象をロックしてから値を確認する方法があります。

行をロックして確認する例MYSQL
START TRANSACTION;

SELECT id, stock
FROM products
WHERE id = 1
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;

COMMIT;
確認結果の例OUTPUT
id  stock
1   5

ロックは長く保持すると他の処理を待たせます。必要な行だけを短時間で処理し、タイムアウトやデッドロックをエラーとして扱います。

04

SAVEPOINTを使う

一部だけ戻すMYSQL
START TRANSACTION;
INSERT INTO products (name, price, stock)
VALUES ('テスト商品', 100.00, 1);
SAVEPOINT after_product;

UPDATE products SET price = 0
WHERE name = 'テスト商品';

ROLLBACK TO SAVEPOINT after_product;
COMMIT;
確定後に残る値の例OUTPUT
商品名: テスト商品
価格: 100.00
（価格を0にした変更だけが取り消される）

外側のトランザクション全体ではなく、保存点以降だけを取り消せます。複雑な処理で使う前に、どこまで戻るのかを紙に書いて確認しましょう。

05

バックアップを考える

データベースは壊れないとは限りません。開発中でも、重要なデータを定期的に書き出し、復元できることを確認します。

論理バックアップの例COMMAND
mysqldump -h 127.0.0.1 -u backup_user -p \
  --single-transaction study_mysql > study_mysql.sql

mysqldumpのオプション、バックアップの保管場所、復元手順は環境によって異なります。取得できたかだけでなく、復元テストまで行うことが重要です。