【MySQL】ROLLBACKが効かないケース

先日、新人研修でMySQLをやっていてトランザクションを学びました。

その時に、ROLLBACKを打てばどんな処理でも戻せると思って、

DROPを使ってテーブルを消し、ROLLBACKを実行すると…

元に戻せませんでした orz

よくよく調べてみると、ROLLBACKで戻せる処理、戻せない処理があることがわかりました。

AutoCommitの存在

まず、MySQL（InnoDB）では、デフォルトでAutoCommitがオンになっています。

この状態では

MariaDB [testDB] > START TRANSACTION;

または

MariaDB [testDB] > BEGIN;

をしない限りSQL文ごとに自動でコミットされます。
言い換えると、↑のコマンドでトランザクションが開始されます。

逆に、AutoCommitがオフの時はコミットコマンドをしない限りコミットされません。
つまり、ずっとトランザクションが開始されています。

AutoCommitをオフにしたいときは

MariaDB [testDB] >　set autocommit=0;

でできます。オンは１にすればできます。

ROLLBACKできないSQL文

さて、本題のROLLBACKでも戻せないSQL文を解説します。

AutoCommitがオンでもオフでも、取り消せません。

本来トランザクションが開始されていればコミットをしない限りROLLBACK;で操作をなかったことにできます。

しかし以下のコマンドはROLLBACK;でもなかったことにできないので気をつけてください。

• ALTER TABLE
• CREATE INDEX
• DROP DATABASE
• DROP INDEX
• DROP TABLE
• LOAD MASTER DATA
• LOCK TABLES
• RENAME TABLE
• TRUNCATE TABSE
• START TRANSACTION
• BEGIN

テーブルに対して行った操作は大体戻せない、という認識でしょうか。
あと、当たり前ですがトランザクションの開始文は無かったことにできません。

ROLLBACKできるSQL文

ロールバックできる代表的なSQL文を以下に挙げますが、他にも可能なSQL文はあります。

• INSERT INTO
• UPDATE
• DELETE

まとめ

今回研修で、初めてSQL文を書いていたのですが、ロールバックは最強だと思っていました。

しかしなんでも戻せる万能薬ではないことを知ったので現場に配属されていじることがあったら慎重に作業したいと思います…