概要

本記事はSQL 第2版ゼロからはじめるデータベース操作で学んだ内容をまとめたものです。
- 前回は「第4章：データの更新」4-1: データの登録, 4-2: データの削除, 4-3: データの更新で学んだ内容をまとめました：SQL 第2版ゼロからはじめるデータベース操作アウトプット（第6回）
- 今回は「第4章：データの更新」4-4: トランザクションについてまとめていきます！

トランザクションとは？

トランザクション(transaction)とは、RDBMSの世界では「テーブルのデータに対する更新の単位」を表す
- トランザクションは、複数のSQL文をまとめて1つの処理として扱うことができる
- 「データベースに対する1つ以上の更新をまとめて呼ぶ時の名称」とも言える
- テーブルに対する更新（INSERT, UPDARE, DELETE）を束ねる
トランザクションとは、セットで実行されるべき1つ以上の更新処理の集まりのこと
- 1つのトランザクションに「どの程度の数の更新処理を含むか？」「どんな処理を含むか？」についてはDBMS側には基準はない
- トランザクションの単位は、アプリケーション側で決める
トランザクションの構文は以下

トランザクション開始文
 DML文1
 DML文2
 DML文3
トランザクション終了文（COMMITまたはROLLBACK）

トランザクションの開始については、DBMSによって異なる
- MySQL：START TRANSACTION
- PostgreSQL, SQL Server：BEGIN TRANSACTION

トランザクション実行

コミット

コミット（COMMIT）：トランザクションに含まれていた処理の変更を全て反映して、トランザクションを終了すること
- コミットしたら元に戻せないので注意！

-- トランザクション開始前
mysql> select * from Shohin;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

-- トランザクション開始
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = 'Dress Shirt';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T-shirt';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- トランザクション終了（コミット）
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

-- 変更あり
mysql> select * from Shohin;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         2000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         3000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

ロールバック

ロールバック（ROLLBACK）：トランザクションに含まれていた処理の変更を全て取り消して、トランザクションを終了すること
- ロールバックしたら元に戻せる！

-- トランザクション開始前
mysql> select * from Shohin;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.01 sec)

-- トランザクション開始
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ShohinSET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = 'Dress Shirt';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= hanbai_tanka - 1000 WHERE shohin_mei = 'Dress Shirt'' at line 1
mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = 'Dress Shirt';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T-shirt';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- トランザクション終了（ロールバック）
mysql> ROLLBACk;
Query OK, 0 rows affected (0.01 sec)

-- 変更なし
mysql> select * from Shohin;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

トランザクションの開始について

MySQL・SQL Server・PostgreSQLでは、コマンドを使わずにトランザクションが開始された場合は以下の挙動をする
- 1つのSQLで1つのトランザクション（自動コミット）

mysql> select * from Shohin;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         2000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         3000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

-- トランザクション1（自動コミット）
mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka + 100 WHERE shohin_mei = 'T-shirt';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- トランザクション（自動コミット）
mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka + 200 WHERE shohin_mei = 'T-shirt';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 上記2つのトランザクションの内容がコミットされている
mysql> select * from Shohin WHERE shohin_mei = 'T-shirt';
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T-shirt    | Clothing      |         2300 |          500 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
1 row in set (0.00 sec)

自動コミットで注意すべきDELETE文の実行

自動コミットでDELETE文を実行すると、意図せずデータが削除される可能性がある
- DELETE文は、WHERE句を指定しないと全ての行が削除される
- Rollbackをつけて安全に消せることを確認した上で削除しよう！

ACID特性

DBMSのトランザクションには、守るべき4つの大事な約束事が標準規格によって取り決められている
この4つの約束事をACID特性と呼ぶ
- Atomicity（原子性）
- Consistency（一貫性）
- Isolation（独立性）
- Durability（永続性）

Atomicity（原子性）

トランザクションが終わった時、そこに含まれていた更新処理は、「すべて実行された」か「すべて実行されなかった」のどちらかになること
- オールオアナッシング（all or nothing）とも言われる、トランザクションはCOMMITかROLLBACKのどちらかで終了することを保証する

-- トランザクション開始
begin transaction;

-- 1つ目の更新処理
update Shohin set hanbai_tanka = hanbai_tanka + 100 where shohin_mei = 'T-shirt';

-- 2つ目の更新処理
update Shohin set hanbai_tanka = hanbai_tanka + 200 where shohin_mei = 'T-shirt';

-- トランザクション終了（コミット）
commit;

-- トランザクション開始
begin transaction;

-- 1つ目の更新処理
update Shohin set hanbai_tanka = hanbai_tanka + 100 where shohin_mei = 'T-shirt';

-- 2つ目の更新処理(エラー発生)
update Shohin set hanbai_tanka = hanbai_tanka + 200 where shohin_mei = 'T-shirt';

-- トランザクション終了（ロールバック）
rollback

Consistency（一貫性）

トランザクションに含まれる処理は、DBにあらかじめ定義された制約を満たすことを保証すること
- 例えば、NOT NULL制約やUNIQUE制約、主キーなどの制約を満たすこと
実行されなかったのと同じ状態になることによって、データベースの状態が不整合な状態にならないことを保証する
- トランザクションが終了した時点で、データベースの状態が一貫していることを保証する

-- トランザクション開始
begin transaction;

-- 1つ目の更新処理(エラー発生)
-- NOT NULL制約のある列にNULLを入れようとしている
insert into Shohin (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) values (NULL, 'New Item', 'New Category', 1000, 800, '2023-10-01');

-- 2つ目の更新処理
update Shohin set hanbai_tanka = hanbai_tanka + 200 where shohin_mei = 'T-shirt';

-- トランザクション終了（ロールバック）
rollback

Isolation（独立性）

トランザクション同士が互いに干渉を受けないことを保証する
- あるトランザクションによる変更は、他のトランザクションからは見えない（他のトランザクションからは隠蔽される）
トランザクション分離レベルといった概念もあり、トランザクション同士の干渉をどの程度許容するかを設定できる
- わかりやすいリンク：トランザクション分離レベルについてのまとめ
トランザクション分離レベルについては、別記事で詳しくまとめる予定です

Durability（永続性）

トランザクションが終了したら、その時点でのデータの状態が保存されることを保証する
- 例えシステム障害が発生してデータが失われたとしても、何らかの方法でデータが復元できることを保証できなければならない
トランザクションの実行ログを保存することで、障害発生時にデータを復元できるようにする
- トランザクションログ（redo log）や、undo logといったログが利用される

練習問題を解いてみる

4.1 - 4.4を解いてみた内容を掲載

4-1

セッションAでCOMMITが確定されるまでは、他のセッションで更新結果が見えない（トランザクションの独立性）

-- 複数セッションでクエリを実行

-- セッション1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Shohin VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20')
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Shohin VALUES ('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11')
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Shohin VALUES ('0003', 'カッターシャツ', '衣服', 4000, 2800, NULL)
Query OK, 1 row affected (0.00 sec)

-- セッション2
-- トランザクションの独立性：トランザクション1の変更はセッション2からは見えない
mysql> SELECT * FROM Shohin;
Empty set (0.00 sec)
          
-- セッション1をコミットする
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

-- セッション2で再度クエリを実行
-- セッション1の変更が見える
mysql> SELECT * FROM Shohin;
+-----------+-----------------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei            | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------------+---------------+--------------+--------------+------------+
| 0001      | Tシャツ               | 衣服          |         1000 |          500 | 2009-09-20 |
| 0002      | 穴あけパンチ          | 事務用品      |          500 |          320 | 2009-09-11 |
| 0003      | カッターシャツ        | 衣服          |         4000 |         2800 | NULL       |
+-----------+-----------------------+---------------+--------------+--------------+------------+
3 rows in set (0.00 sec)

4-2

PK重複違反 = INSERT文で同じ主キーを持つ行を追加しようとした場合に発生するエラー（トランザクションの一貫性）

mysql> INSERT INTO Shohin SELECT * FROM Shohin;
ERROR 1062 (23000): Duplicate entry '0001' for key 'Shohin.PRIMARY'

-- 重複エラーが発生するため登録できない！

4-3

CREATE TABLE ShohinSaeki
(shohin_id    CHAR(4)       NOT NULL,
 shohin_mei   VARCHAR(100)  NOT NULL,
 hanbai_tanka INTEGER,
 shiire_tanka INTEGER,
 saeki        INTEGER,
 PRIMARY KEY(shohin_id));

-- ShohinSaekiテーブルにデータを登録
-- Shohinテーブルのデータを参照して、saeki列に値を設定
mysql> INSERT INTO ShohinSaeki (shohin_id, shohin_mei, hanbai_tanka, shiire_tanka, saeki) SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka, (hanbai_tanka - shiire_tanka
  ) FROM Shohin;
mysql> SELECT * FROM ShohinSaeki;
+-----------+-----------------------+--------------+--------------+-------+
| shohin_id | shohin_mei            | hanbai_tanka | shiire_tanka | saeki |
+-----------+-----------------------+--------------+--------------+-------+
| 0001      | Tシャツ               |         1000 |          500 |   500 |
| 0002      | 穴あけパンチ          |          500 |          320 |   180 |
| 0003      | カッターシャツ        |         4000 |         2800 |  1200 |
+-----------+-----------------------+--------------+--------------+-------+
3 rows in set (0.00 sec)

4-4

-- ShohinSaekiテーブルのhanbai_tanka列とsaeki列を更新
mysql> START Transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ShohinSaeki SET hanbai_tanka = 3000 WHERE shohin_id = '0003';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE ShohinSaeki SET saeki = (hanbai_tanka - shiire_tanka) WHERE shohin_id = '0003';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM ShohinSaeki;
+-----------+-----------------------+--------------+--------------+-------+
| shohin_id | shohin_mei            | hanbai_tanka | shiire_tanka | saeki |
+-----------+-----------------------+--------------+--------------+-------+
| 0001      | Tシャツ               |         1000 |          500 |   500 |
| 0002      | 穴あけパンチ          |          500 |          320 |   180 |
| 0003      | カッターシャツ        |         3000 |         2800 |   200 |
+-----------+-----------------------+--------------+--------------+-------+
3 rows in set (0.01 sec)

まとめ

本記事では「第4章：データの更新」4-4: トランザクションについてまとめました！
次回は「第5章：複雑な問い合わせ」についてまとめていきます！

参考

SQL 第2版ゼロからはじめるデータベース操作