[OracleDB To MySQL] 移行に際しての注意事項 ～AutoCommit機能～

AutoCommit は、データベース・エンジンの既定のトランザクション管理機能です。
AutoCommit の設定を ON（DML発行毎にCOMMITする）/OFF（明示的に COMMIT 実行要）にすることで、トランザクションの COMMITのタイミングが変化します。

今回は、OrcleDB と MySQL の AutoCommit 動作の違いと、移行を実施する場合の注意事項について説明します。
なお OracleDB のバージョンは問いませんが、MySQL は主に 5.7 Community Edition（InnoDB）を想定しています。

OracleDB の AutoCommit

デフォルト値及びデフォルト値の変更方法

AutoCommit はデフォルト OFF になっています。
DB 単位でデフォルトを変更する事は不可能です。

トランザクション開始文との関係

トランザクション開始句「SET TRANSACTION」の実行有無は、AutoCommit の動作に影響を与えません。

[参考]Oracle Database SQL言語リファレンス 12cリリース1 (12.1)
https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_10005.htm#sthref7510

各言語のデータベースの接続仕様

各言語のデータベース接続仕様に基づき、AutoCommit の ON/OFF を設定します。
例えば JDBC の場合、「conn.setAutoCommit (false);」により AutoCommit を OFF に変更します。
※JDBC では、新規の接続オブジェクトがデフォルトで AutoCommit ONになっています。

[参考]Oracle Database JDBC開発者ガイド　12cリリース1 (12.1)
https://docs.oracle.com/cd/E57425_01/121/JJDBC/apxtips.htm#sthref1043

SQL*Plusとの関係

SQL*Plus下記構文にてAutoCommitのON/OFFを設定できます。

[参考]SQL*PlusRユーザーズ・ガイドおよびリファレンス リリース1 (12.1)
https://docs.oracle.com/cd/E57425_01/121/SQPUG/GUID-58BCF280-6F11-4787-AC1E-C0D40E9F3B68.htm#GUID-58BCF280-6F11-4787-AC1E-C0D40E9F3B68

なお AutoCommit の ON/OFF 設定に関わらず、SQL*Plus を EXIT で終了（正常終了）した場合、トランザクションは COMMIT されます。

MySQL の AutoCommit

デフォルト値及びデフォルト値の変更方法

AutoCommit はデフォルト ON（autocommit=1) になっています。
5.5.8以降であれば、my.cnf に autocommit = 0 を記載することで変更が可能です。

トランザクション開始文との関係

START TRANSACTION 文の有無によって動作が変わります。

AutoCommit を ON にした場合（デフォルト）

START TRANSACTION 文を実行した場合は、単一の SQL 文が実行されただけではコミットせず、COMMIT 文を実行した時点で初めてコミットされます。ROLLBACK 文を実行すればトランザクションをロールバックできます。

START TRANSACTION 文を実行しなかった場合は、単一の SQL 文が実行された時点で自動的にコミットします。
そのため、ROLLBACK 文を実行してもトランザクションはロールバックできません。

AutoCommit を OFF にした場合

START TRANSACTION 文の実行有無に関わらず、自動的にはコミットせず、COMMIT 文を実行した時点で初めてコミットされます。
ROLLBACK 文を実行すればトランザクションをロールバックできます。

[参考]MySQL 5.7 Reference Manual 14.5.2.2 autocommit, Commit, and Rollback
https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html

[参考]MySQL 5.7 Reference Manual 5.1.5 Server System Variables
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

[参考]MySQL 5.7 Reference Manual13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax
https://dev.mysql.com/doc/refman/5.7/en/commit.html

各言語のデータベースの接続仕様

各言語のデータベース接続仕様に基づき、AutoCommit の ON/OFF を設定します。
例えば JDBC の場合、「conn.setAutoCommit (false);」により AutoCommit を OFF に変更します。
※JDBC では、新規の接続オブジェクトがデフォルトで AutoCommit ONになっています。

[参考]MySQL JDBC Transaction
http://www.mysqltutorial.org/mysql-jdbc-transaction/

セッション単位でのAutoCommitのON/OFF設定

下記構文にてセッション単位で AutoCommit の ON/OFF を設定できます。

移行に際するトランザクション制御の注意点

OracleDB を使用しているアプリケーションでは、以下のトランザクション制御を実施している場合が非常に多いです。

• デフォルトの AutoCommit OFF をそのまま使用している
• 明示的にトランザクション開始文（SET TRANSACTION）を使用していない

OracleDB から MySQL に移行する場合、アプリケーションの修正量を考慮し MySQL のデフォルトの AutoCommit ON から AutoCommit OFF に変更する場合が多いと考えます。

MySQL で AutoCommit OFF に変更した場合、明示的に MySQL のトランザクション開始文（START TRANSACTION）を使用しなくてもトランザクションが開始されます。
SELECT 文でもメタデータロックが保持され、DDL または書き込みロック操作を試みるとトランザクションの終了時にメタデータロックが解放されるまでブロックされる事に注意して下さい。

メタデータロックについては、以下に詳細が記載されています。合わせてご確認ください。

[参考] 8.11.4 Metadata Locking
https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html