トランザクションとは
トランザクションは、複数の SQL の実行をまとめて行うための仕組みである。トランザクションを指定しない場合は、SQL は 1 文ずつ実行されてデータベースに書き込まれる。
一方で、いくつかの SQL を 1 つのトランザクションとして扱うと、操作がまとめて実行され、全体として「全て実行された」か「全く実行されていない」かのいずれかの状態となる。
このように、一連の SQL 文をひとまとめにし、All or Nothing の状態をとるのがトランザクションの特徴となる。
データの不整合
トランザクションが存在する理由は「データの不整合」を防ぐためである。
例えば、営業一課の A さんがシステム課に異動になったとする。この際、営業一課の名簿から A さんを削除し、システム課に A さんを追加する操作が必要になる。
しかし、何らかのトラブルにより、A さんを営業一課の名簿から削除できないまま、システム課の名簿に A さんを追加してしまうと、どちらにも A さんが存在することになってしまう。
このような不整合を防ぐために、トランザクションを利用して SQL 文をまとめて実行する。
トランザクションは START TRANSACTION (または BEGIN) で始め、最後に COMMIT と記述する。これにより、この間の一連の SQL がまとめて処理され、COMMIT; と記述したところで処理が確定する。
また、トランザクションを設定している状態で START TRANSACTION と COMMIT の間に囲んだ SQL のうちのいずれかの実行に失敗した場合、囲んだ部分の SQL の実行はすべてなかったことにされ、START TRANSACTION を実行する前に戻る (Roll Back)。
なお、ロールバックは自ら ROLLBACK; と宣言することでも実行できる。
トランザクションを実行する
以下の二つのテーブルを用いてトランザクションを実行する。
mysql> SELECT * FROM shukko;
+-----------+-------+--------+---------------------+
| idko | taiou | outnum | outdate |
+-----------+-------+--------+---------------------+
| 201890001 | 8010 | 300 | 2018-08-05 05:18:04 |
| 201890002 | 8002 | 300 | 2018-08-05 05:18:04 |
| 201890003 | 8003 | 300 | 2018-08-05 05:18:04 |
| 201890004 | 8004 | 500 | 2018-08-05 05:18:04 |
| 201890005 | 8005 | 500 | 2018-08-05 05:18:04 |
| 201890006 | 8006 | 500 | 2018-08-05 05:18:04 |
| 201890007 | 8007 | 200 | 2018-08-05 05:18:04 |
| 201890008 | 8008 | 200 | 2018-08-05 05:18:04 |
| 201890009 | 8009 | 150 | 2018-08-05 05:18:04 |
| 201890010 | 8010 | 100 | 2018-08-05 05:18:04 |
| 201890011 | 8008 | 30 | 2018-08-05 05:18:04 |
+-----------+-------+--------+---------------------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM zaiko;
+-------+-----------------------+-------+
| idzai | product | stock |
+-------+-----------------------+-------+
| 8002 | Pen Case Blue | 1000 |
| 8003 | Pen Case Green | 1000 |
| 8004 | Mannen Hitsu A | 2000 |
| 8005 | Mannen Hitsu B | 2000 |
| 8006 | Mannen Hitsu C | 2000 |
| 8007 | Ball Pen Black 1 case | 1500 |
| 8008 | Ball Pen Red 1 case | 1500 |
| 8009 | Eraser 1 case | 700 |
| 8010 | Pen Case Red | 1000 |
+-------+-----------------------+-------+
9 rows in set (0.00 sec) shukko はある商品が出庫された際の記録を保持し、zaiko はある商品の在庫を保持している。
ここで、ある商品が売れ、特定の数の在庫が出庫された場合、shukko にその記録を取りつつも、zaiko 側で商品の在庫の数を減らす必要がある。
このような場合に、前者のクエリのみが実行されたりすると不整合が発生するために、トランザクションを利用する。
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO shukko (idko, taiou, outnum, outdate) VALUES (201890012, 8004, 500, NOW());
Query OK, 1 row affected (0.04 sec)
mysql> UPDATE zaiko SET stock = stock - 500 WHERE idzai = 8004;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec) トランザクション分離レベル
トランザクション分離レベルとは、トランザクション処理中に、ほかのユーザーによる操作がどの程度見えるのかを定めるもの。
分離レベルが低いほど、ほかのユーザーの処理を待たずにトランザクションを実行できるようになり、パフォーマンスが向上する。一方、他のユーザーに処理中のデータが見えてしまい、誤動作の原因となることがある。
トランザクション分離レベルは SET TRANSACTION ISOLATION LEVEL で指定し、デフォルトは REPEATABLE READ となる。
トランザクション分離レベルには以下の 4 つの種類がある。
READ UNCOMMITTED
一番低い分離レベルであり、SELECT 文を実行する際、まだコミットされていないデータを読み込む可能性がある (ダーティリード) 。
正しい結果が出るとは言えないため、概算レポートをだしたいなど、データの正確性を犠牲にしてでもパフォーマンスを向上したいときだけに使われる。
MySQL ではそこまでパフォーマンスが向上しないため、使われることはほとんどない。
READ COMMITTED
ダーティリードを解決したもの。コミットされたデータしか読み取らない。MySQL 以外の多くのデータベースではこれがデフォルトとなる。
あくまでもコミットされたデータしか読み取らないというだけなので、レコードを参照したあとには他のユーザーが書き換えれば、その書き換えた内容はすぐに反映される。
つまり、同じ SELECT 文を実行しても、ほかのユーザーの処理が割り込むことによって結果が変わる可能性がある (ノンリピータブルリード/ファジーリード)。
REPEATABLE READ
MySQL のデフォルトのトランザクション分離レベル。ノンリピータブルリードの問題を解決したもの。
トランザクションを開始する際に、現在のテーブルの状態をそのまま取り出したスナップショットを作ることで、トランザクションの処理中にほかのユーザーがそのトランザクションに対して行った変更の影響を受けないようにしたもの。
ただし、トランザクションの処理中に他のユーザーが追加したレコードが現れることはある (ファントムリード)。
MySQL では特殊な方法でファントムリードを防止しているため、問題とはならない。
SERIALIZABLE
トランザクションを完全に分離して処理する。
もっとも高い分離レベルだが、読み込んだ全てのレコードに行ロックをかけるため、ロック競合が多発し、パフォーマンスが大きく低下する可能性があり、あまり使われない。
MySQL のトランザクションレベルについては以下のブログが参考になる。