MariaDB のオートコミット設定と切替方法

編集

この記事の要点

MariaDB / MySQL は既定で autocommit = ON。INSERT・UPDATE・DELETE は 1 文ごとに自動でコミットされる
現在値の確認は SHOW VARIABLES LIKE 'autocommit'; または SELECT @@autocommit;
今の接続だけ OFF にするなら SET autocommit = 0;、サーバー全体の既定を変えるなら設定ファイルの [mysqld] に autocommit=0
一時的にまとめて処理したいだけなら、設定を変えずに START TRANSACTION 〜 COMMIT を使うのが安全
OFF のまま COMMIT を忘れると、変更が消える・ロックが残るなどの事故につながる

オートコミットとは

オートコミット（autocommit）は、INSERT・UPDATE・DELETE などのデータ変更（DML）を実行した直後に、データベースが自動的に COMMIT を行う動作のことです。MariaDB・MySQL ではどちらも既定で ON になっており、ふだん mysql コマンドで UPDATE を打つと、その瞬間に変更が確定して他の接続からも見えるようになります。

オートコミットが OFF の場合は、最初の DML を実行した時点でトランザクションが暗黙的に始まり、明示的に COMMIT するまで変更は確定しません。ROLLBACK で取り消すこともできます。なお、トランザクションに対応しているのは InnoDB などのストレージエンジンだけで、MyISAM のテーブルはオートコミットの設定にかかわらず即時に反映されます。

現在の設定を確認する方法

以下のどちらかで、今の接続（セッション）のオートコミット設定を確認できます。

> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

SELECT @@session.autocommit, @@global.autocommit;

@@session は今の接続の値、@@global は新しく接続したときに使われる既定値です。1 が ON、0 が OFF を意味します。

設定を変更する方法

変更したい範囲によって方法が 3 つあります。

範囲	方法	有効期間
今の接続だけ	`SET autocommit = 0;`	切断するまで
以降の新しい接続すべて	`SET GLOBAL autocommit = 0;`（管理者権限が必要）	サーバー再起動まで。実行中の既存接続には効かない
サーバーの既定値	設定ファイルの `[mysqld]` に記述して再起動	恒久的

今の接続だけ切り替える

OFF にしたい場合は 0、ON に戻したい場合は 1 を指定します（OFF / ON と書いても同じです）。

> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

サーバーの既定値を変える（my.cnf）

設定ファイル（Linux なら /etc/my.cnf や /etc/mysql/my.cnf 配下、Windows なら my.ini など。配置はディストリビューションやインストール方法で異なります）の [mysqld] セクションに追記し、サービスを再起動します。

[mysqld]
autocommit=0

再起動後、新しく接続して SELECT @@autocommit; が 0 になっていれば反映されています。

設定を変えずに複数の更新をまとめる方法

「複数の UPDATE を一括で確定したい」「失敗したら全部取り消したい」という目的なら、オートコミットを OFF にするより、必要な箇所だけトランザクションを明示するほうが安全です。START TRANSACTION（または BEGIN）を実行すると、その時点から COMMIT か ROLLBACK までの間だけオートコミットが止まります。

START TRANSACTION;
UPDATE account SET balance = balance - 1000 WHERE id = 1;
UPDATE account SET balance = balance + 1000 WHERE id = 2;
-- 結果を確認して問題なければ確定
COMMIT;
-- 取り消す場合は ROLLBACK;

オートコミットを OFF にするときの注意点

COMMIT 忘れで変更が消える: OFF のまま切断すると、未コミットの変更はロールバックされます。作業の最後に必ず COMMIT します
ロックが残り続ける: UPDATE した行のロックはトランザクション終了まで保持されます。開きっぱなしのセッションがあると、他の処理が待たされたり、ALTER TABLE がメタデータロック待ちで止まったりします
暗黙のコミットがある: CREATE TABLE・ALTER TABLE・DROP TABLE・TRUNCATE などの DDL を実行すると、それまでの変更はその場でコミットされます。ROLLBACK では戻せません
autocommit を 1 に戻すとコミットされる: トランザクション中に SET autocommit = 1; を実行すると、その時点の変更が確定します
アプリ側の既定値は別物: 接続ライブラリによって既定値が異なります。たとえば JDBC や PHP の PDO は通常オートコミット有効ですが、Python の DB-API 準拠ドライバ（mysqlclient、PyMySQL など）は既定で無効のため conn.commit() が必要です。サーバー側の設定だけで判断しないようにします

よくある質問

SET GLOBAL したのに今の画面で OFF にならない

SET GLOBAL はこれから接続するセッションの既定値を変えるもので、実行した接続自身には反映されません。今の接続も変えたい場合は SET autocommit = 0; も実行します。

本番環境では OFF にすべき？

一般的には ON のまま運用し、まとめて確定したい処理だけアプリ側でトランザクションを張るのが主流です。サーバー全体を OFF にすると、COMMIT を前提としていないツールやバッチの変更が確定しなくなるおそれがあります。

ページ一覧

その他