TRANSACTION / COMMIT / ROLLBACK

トランザクションを開始します（MySQL）。

START TRANSACTION;

トランザクションを開始します（標準SQL・PostgreSQL）。

BEGIN;

変更を確定します。

COMMIT;

変更をすべて取り消します。

ROLLBACK;

セーブポイントを設定します。

SAVEPOINT savepoint_name;

セーブポイントまで取り消します。

ROLLBACK TO SAVEPOINT savepoint_name;

以下の『accounts』テーブルを例に説明します。

送金処理でA口座からB口座へ10,000円を移す例です。対話モードで1行ずつ実行する場合は以下のようになります。

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

COMMIT後の確定結果を確認します。user_aの残高が10,000円減り、user_cの残高が10,000円増えています。

SELECT * FROM accounts;
+------------+--------+---------+
| account_id | name   | balance |
+------------+--------+---------+
|          1 | user_a |   40000 |
|          2 | user_c |   30000 |
|          3 | user_e |   75000 |
+------------+--------+---------+
3 rows in set (0.00 sec)

実務ではアプリケーション側のプログラムからトランザクションを制御するのが一般的です。.sqlファイルに記述する場合は以下のようになります。

sample_transfer.sql

START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;

COMMIT;

セーブポイントを活用した例です。

sample_transaction.sql

START TRANSACTION;

-- user_eの残高を増やします。
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 3;
SAVEPOINT after_update;

-- 誤ってuser_aの残高を減らしてしまった操作をここで取り消します。
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1;

-- user_aの更新だけ取り消してuser_eの更新は残します。
ROLLBACK TO SAVEPOINT after_update;

COMMIT;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

sample_transaction.sql

SELECT * FROM accounts;
+------------+--------+---------+
| account_id | name   | balance |
+------------+--------+---------+
|          1 | user_a |   50000 |
|          2 | user_c |   20000 |
|          3 | user_e |   80000 |
+------------+--------+---------+
3 rows in set

MySQL では『START TRANSACTION』または『BEGIN』でトランザクションを開始します。

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

PostgreSQL では『BEGIN』でトランザクションを開始します。『START TRANSACTION』も使用できます。

BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

SQLite では『BEGIN TRANSACTION』でトランザクションを開始します。デフォルトは自動コミットモードです。

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

トランザクションが安全にデータを扱うための4つの原則を『ACID特性』と呼びます。原子性（Atomicity）・一貫性（Consistency）・独立性（Isolation）・永続性（Durability）です。特に「口座振替」「在庫引き落とし＋注文登録」のような複数テーブルをまたぐ処理では、途中で失敗しても中途半端な状態が残らないよう必ずトランザクションを使いましょう。

MySQLのデフォルトは自動コミット（AUTO_COMMIT=1）モードです。『START TRANSACTION』を発行するとその間だけ自動コミットが無効になります。『SET AUTOCOMMIT = 0』で永続的に無効にすることもできますが、COMMITを忘れやすいため注意が必要です。

権限の設定・管理については『GRANT / REVOKE』を参照してください。