SQL総復習② トランザクション
こんにちは！
スマレジ テックファームのMichiです！
トランザクションとは？
トランザクションとは、「データに対する更新の単位」のことを表します。
もっと簡単に言えば、「データベースに対して複数の更新を行う時の一連の処理のセット」のことです。
テーブルに対する更新は、INSERT、DELETE、UPDATEという3つのクエリを使って行います。
しかし、更新は1回きりの操作だけで終わることは少なく、複数の操作を伴うことが多いです。
例えば、ある商品管理テーブルに対して、「カッターシャツの販売単価を1,000円下げて、その代わりにTシャツの販売単価を1,000上げる」という更新を行いたいとします。 クエリは次の通りです。
①カッターシャツの単価を1,000円下げる
UPDATE items SET price = price - 1000 WHERE name = 'カッターシャツ';
②Tシャツの単価を1,000円上げる
UPDATE items SET price = price + 1000 WHERE name = 'Tシャツ';
この時、①と②の更新は必ずセットで行われる必要があります。
①だけ実行して②を実行するのを忘れたら、お店は大損害ですよね。
このように、「ワンセットで行われるべき更新の集合」をトランザクションと呼び、必ずひとまとめにして扱う必要があるわけです。
トランザクションを作るには
トランザクションを作るには、次のような構文でSQLを記述します。
トランザクションの開始文;
DML文①;
DML文②;
DML文③;
...
トランザクションの終了文;
トランザクションの開始
トランザクションの開始文は、各データベースによって差異があります。
START TRANSACTION
BEGIN TRANSACTION
■ Oracle
なし
Oracleについては、そもそもトランザクション開始文を用意していません。
実は、標準SQL規格では、「トランザクションは暗黙的に開始される」と決まっているのです。
なので、Oracleだけでなく、MySQLやPostgreSQLでもトランザクション開始文は書かなくても問題ありません。
このことが、普段私たちがトランザクションを意識することなくSQLを使っている原因でもあります。
トランザクションの終了
一方で、トランザクションを終了するには、ユーザーが明示的に区切ってやる必要があります。
トランザクションを終わらせるコマンドには、COMMTとROLLBACKの2つが存在します。
COMMIT - 処理の確定
COMMITは 、トランザクションに含まれていた処理による変更をすべて反映して、トランザクションを変更させるコマンドです。
ファイルでいうところの「上書き」に相当します。
一度コミットしてしまったら、もうトランザクションの開始前の状態に戻すことはできません。
冒頭の商品価格の変更のトランザクションをコミットするには、次のようなSQLになります（MySQL）。
START TRANSACTION UPDATE items SET price = price - 1000 WHERE name = 'カッターシャツ'; UPDATE items SET price = price + 1000 WHERE name = 'Tシャツ'; COMMIT
ROLLBACK - 処理の取り消し
ROLLBACKは 、トランザクションに含まれていた処理による変更をすべて破棄して、トランザクションを終了させるコマンドです。
ファイルでいうところの「保存せずに終了」に相当します。
ロールバックしたら、データベースの状態はトランザクションを開始する前の状態に戻ります。
冒頭の商品価格の変更のトランザクションをロールバックするには、次のようなSQLになります（MySQL）。
START TRANSACTION UPDATE items SET price = price - 1000 WHERE name = 'カッターシャツ'; UPDATE items SET price = price + 1000 WHERE name = 'Tシャツ'; ROLLBACK
SQL単体でロールバックを使うことはあまりないかもしれません。
メジャーな使い方としては、アプリのテスト時に挿入したモックデータを、テスト終了とともにロールバックで削除するといった例が挙げられます。
ACID特性
DBMSのトランザクションにおいて、守るべき4つの重要な約束事を、それぞれの頭文字を取って「ACID特性」呼びます。
これらの約束事は、どんなDBMSも守らなければならない一般的なルールです。
1. Atomicity（原子性）
トランザクションは、一連の操作をまとめて1つの処理として実行します。
原子性とは、そのトランザクションが全て完了するか、もしくは一つでもエラーが発生した場合は全てを取り消すことができる性質です。
つまり、トランザクションは全て成功するか、全て失敗するかのどちらかしかありません。
冒頭でも述べた通り、「カッターシャツの単価を下げたが、Tシャツの単価は上がっていない」といった事態になれば困りますよね。
そのためにも、この原始性が重要になるわけです。
2. Consistency（一貫性）
トランザクションが実行される前と実行された後で、データベースの状態に一貫性があることを保証します。
違う言い方をすれば、「トランザクションが実行された後も、データベースの制約条件やルールが守られている」ということです。
例えば、「NOT NULL制約があるカラムに対してはNULLに更新できない」「INTEGER型のカラムに文字列を挿入できない」といった具合です。
まあ、考えてみれば当たり前のことですね。
3. Isolation（独立性）
トランザクションが同時に複数実行された場合でも、互いに影響しないように独立して処理されます。
トランザクションが実行されている間は、他のトランザクションからデータが変更されたり、アクセスされたりしないように制御されます。
4. Durability（耐久性）
トランザクションが完了した後は、その結果がデータベースに永続的に保存されることを保証します。
トランザクションが完了した後にシステムがクラッシュしても、データベースは何らかの手段でこれを復旧させる手段を持たねばなりません。
耐久性を保証するメジャーな方法は、「トランザクションの実行記録（ログ）をDB外に保存しておき、障害が発生した場合には、このログを使って復旧する」といった方法があります。
まとめ
実は私も、これまでトランザクションについて意識したことはありませんでした。
フレームワークのORMを使っていると、最後にsave()やcommit()といった文を書きますが、これはトランザクションの終了宣言だったんだなということを、今更になって理解しました。
ここまでお読みいただきありがとうございました。
