【SQL】DBのデータ変更はトランザクションを使おう

みなさん、日々の業務や急な不具合対応で本番DBのデータを変更せざるを得ない時はありませんか。うん、お気持ちお察しします🙇‍♂️
本番DBのデータを触るのはいつだって緊張しますよねー、間違えたらどうしようという不安との闘いですよね。もちろん、データを変更する前にバックはとります、チェックだってこまめにします。それでも、作業中に間違えたりします。だって、人間だもの。😋
不備の発見や間違える度にバックアップデータから変更前の状態に戻すのは効率がわるいし腰が折れます。本当に変更前の状態に戻っているのかも不安にもなります。
本記事は以上のことを踏まえてトランザクションを使用したデータ変更の活用について述べていきます。

トラザクションとは

データベース管理システム (DBMS)　には、クリエの実行結果をテーブルに反映せず、保留しておく機能があり、それをトランザクションと呼びます。
トランザクション範囲内では、実行結果をデータに反映するためのコマンドを明示的に実行するまで反映を保留できます。もし実行結果に不備があった場合に、実行済みの処理を切り戻すことができます。
トランザクションの中で行ったデータ変更を反映してトランザクションを正常終了させることをCOMMIT、変更を切り戻すことをROLLBACKと呼びます。

ただし、MySQLなどのDBMSではDROP TABLEなどのDDLは、実行時に必ずCOMMITされるため、ROLLBACKできない命令が存在します。
使用しているSQLでトランザクションによるROLLBACKできる範囲については各RDMSのドキュメントを参考してください。

データ変更の実例

実行環境としてWeb上ですぐ試せるSQL Fidle、言語はMySQLを使用します。

前準備

-- テーブル作成
CREATE TABLE test (
  id INT AUTO_INCREMENT KEY,
  name VARCHAR(100),
  description VARCHAR(255)
);
-- 初期データ格納
INSERT INTO test(name, description) VALUES ('MySQL', 'MySQL is Good!');
INSERT INTO test(name, description) VALUES ('PostgreSQL', 'PostgreSQL is Very Good!');

トランザクションを開始

START TRANSACTION;

データ変更する前にデータを確認

SELECT * FROM test;

実行結果

id  name            description
----------------------------------------------------
1   MySQL           MySQL is Good!
2   PostgreSQL      PostgreSQL is Very Good!

データ更新処理を実行して、正しく変更されたかを確認

UPDATE test SET name = 'OracleSQL', description = 'OracleSQL is Good!';

SELECT * FROM test;

実行結果

id  name            description
----------------------------------------------------
1   OracleSQL       OracleSQL is Good!
2   OracleSQL       OracleSQL is Good!

誤って全データを更新してしまったので変更を巻き戻す

ROLLBACK;

変更が取り消されていることを確認

SELECT * FROM test;

実行結果

id  name            description
----------------------------------------------------
1   MySQL           MySQL is Good!
2   PostgreSQL      PostgreSQL is Very Good!

正しいデータに変更

START TRANSACTION;

UPDATE test SET name = 'OracleSQL', description = 'OracleSQL is Good!' WHERE id = 1;

SELECT * FROM test;

実行結果

id  name            description
----------------------------------------------------
1   OracleSQL       OracleSQL is Good!
2   PostgreSQL      PostgreSQL is Very Good!

変更をCOMMIT

COMMIT;

変更が反映されていることを確認

SELECT * FROM test;

実行結果

id  name            description
----------------------------------------------------
1   OracleSQL       OracleSQL is Good!
2   PostgreSQL      PostgreSQL is Very Good!